[SQL Server][資料封存]Truncate指定Partition Number的資料(SQL Server 2016)

想移除歷史交易資料時,很直覺想到串DML Delete;但如果刪除交易量大,怕影響線上交易時,辛苦的攻城師需要改成預存程序分批刪除,每次刪2,000筆還放慢半秒,但鎖定物件較多,批次時間也久;如果資料表能獲得DBA大人同意設置成Partition Table,用上Switch Partition可以飛快的轉出資料;不過Switch Partition手續有些繁瑣,得先建立相同分割檔案群組、相同結構的空資料表再轉移。

SQL Server 2016更方便了,TRUNCATE TABLE支援指定分割編號,秒殺的概念,來試試。

...繼續閱讀 »

[SQL Server][T-SQL]新增資料表欄位(Default Value加上Not Null)之續集(SQL冰魔法)

延續上週新增資料表欄位Default Value + Not Null是否會給舊資料預設值的題目。上一篇確認Default Value + Not Null會給也必須要給舊資料預設值,但為何相同資料量測試環境飛快但正式環境跑1小時?這種飛快,感覺不太真實,資料分頁真的有修改到?還是只有改定義?

...繼續閱讀 »

[SQL Server][DMV]透過動態管理檢視(DMV)抓子樹成本較高的SQL 指令或是預存程序

想透過cached plan抓最近經常被使用,而且SQL子樹成本(SubTreeCost)較高的執行計畫作年底上線前的最後衝刺。排進Backlog之前,希望有SQL Text、資料列筆數、執行時間、cpu時間以及圖形執行計畫的資訊綜合輔助自己開Jira Issue。

可惜這家客戶使用的版本不是SQL 2016,超想試試Query Store,今晚我們先組合幾個動態管理檢視(DMV)解任務。

...繼續閱讀 »

[SQL Server][T-SQL]新增資料表欄位(Default Value加上Not Null會不會自動將舊資料給預設值)

今晚的程式過版中,有一部分是大型資料表要加開10幾個欄位來跟上行動支付潮流,到家後接到客戶電話,DBA大人執行DDL Alter Table欄位新增語法(Add Column)時,執行了1小時(....)。印象中同事的語法沒有加With Values應該只修改到定義,沒有改到原始的資料分頁,另外,測試環境類似的交易量跑起來也很快,呼~~~

卡住的時候就是該重新測試填補SQL知識了,這篇先確認SQL是否會將舊資料給預設值,下一篇來查效能問題

...繼續閱讀 »

[SQL Server][安裝]確認鎖定記憶體分頁(Lock Pages in Memory, LPIM)是否生效了

SQL Server安裝後,在本機安全性原則中,除了執行磁碟區維護工作權限外,另一個最佳化的設定就是鎖定記憶體分頁了,他可以防止Windows系統將記憶體資料傳送到磁碟上的虛擬記憶體,這邊可以參考德瑞庫克老師或是Rico老師的文章都有更詳盡的說明,但我們想知道怎麼確認安裝廠商已經有正確設定LPIM了?除了監控Paging File查看虛擬記憶體使用,來找找LPIM在SQL的出生證明。

...繼續閱讀 »

[SQL Server]Read Committed Snapshot Isolation(RCSI)隔離層級的效能副作用

為了減輕查詢交易被大型更新交易封鎖(Blocked),也理解樂觀鎖定(Optimistic Locking)可能的交易衝突後,也許我們會在SQL Server上使用Oracle預設相同的隔離層級: Read Committed Snapshot Isolation(RCSI),她是一種Row Version Base的隔離層級,與snapshot isolation不太相同的是她是單一版本。當交易更新資料時,系統會自動將修改前的版本放到Tempdb提供給可能發生的查詢交易。因為也有搬分頁到其他資料庫的工作會執行,在效能上會不會有明顯的副作用?

...繼續閱讀 »

[SQL Server][Snapshot]快照資料庫(Database Snapshots)的副作用

快照資料庫(Database Snapshots)可以提供資料庫特定時間點的靜態檢視,她是唯讀的資料庫,經常被使用在報表用途,由於資料靜止在指定的時間點,可以有效避免新交易造成報表間的數字差異。多年以後發現有個副作用,一直以為搬Data Page到快照資料庫只會影響一點點點效能,沒想到批次型的大量更新或刪除的交易,影響很明顯。

...繼續閱讀 »

[SQL Server][Lock]透過擴充事件找鎖定的物件以及被誰Blocking

交易一多起來,慢慢會碰上交易封鎖(Block),人若剛好在案發現場能下SQL Query鑑識,透過DMV、sp_trace、profiler、sqldiag等都能快速釐清交易間不單純的案情;但如果想長期追蹤,可以試試SQL Server 2008推出的擴充事件(Extended events),利用blocked_process_report事件幫我們紀錄過長時間的封鎖(block)。

...繼續閱讀 »

[SQL Server][系統資訊收集]SQLDiag.exe(一)啟用SQLProfiler

最近重新和SQLdiag.exe見面,神話傳說中一種以主控台程式或是Windows服務形式來執行的SQL診斷偵測工具,執行檔隨附在SQL Server的安裝目錄BINN之下。她出道的很早,她可以收集Windows相關資源使用、事件紀錄、SQL內的錯誤、封鎖及SQL Profiler可以錄到的資訊,以前認識不深,這次重新認識。

...繼續閱讀 »

[SQL Server][Machine Learning]建模時發生記憶體不足(failure to allocate requested memory)

最近想把某個問題的答題方式從線性迴歸改用決策樹演算法,使用sp_execute_external_script呼叫revoscaler下的rxDTree演算法建模時,出現了外部指令碼錯誤 : failure to allocate requested memory訊息,相同的資料量,線性模型rxFastLinear卻可以處理,筆記偵錯之路。

...繼續閱讀 »

[SQL Server][安裝]執行磁碟區維護工作(SQL Server 2016)

SQL Server 2016開始,SQL安裝精靈到了"伺服器組態"步驟時,除了SQL服務帳戶的設定,還多了一個"執行磁碟區維護工作"(Perform Volume Manintenance Task)的選項,他是一個有關資料庫檔案立即初始化的優化,建立大型資料庫、資料庫還原、或是針對既有資料庫增加大型檔案或改變檔案大小(包含Autogrow)時,跳過補零的初始化作業,可以帶來IO效能上的提升。

...繼續閱讀 »

[SQL Server][Machine Learning]使用MicrosoftML套件預測紐約計程車司機是否會收到小費

MicrosoftML是Microsoft R Server最新的機器學習演算法套件包,直接內建在SQL Server 2017附加安裝的Machine Learning Services,除了之前CTP時曾經練習過one-class SVM,還包含了

  • 快速線性(同時支援L1、L2正規化)
  • 促進式決策樹
  • 快速隨機森林
  • 羅吉斯迴歸(同時支援L1、L2正規化)
  • 深度神經網路(DNNs)
...繼續閱讀 »

[SQL Server][AlwaysOn]交易紀錄備份後的截斷交易紀錄暫時失靈

在復原模式full or bulk-logged 的使用下,當SQL資料庫Transaction log增加速度變快時,我們會增加執行交易紀錄備份(Transaction log backup)的頻率來加速截斷交易紀錄,已經使用的交易紀錄空間(VLFs)就可以被標示inactive而被重複使用。

不過在AlwaysOn環境遇到一個特別的經驗,即使作了交易紀錄備份也沒辦法截斷交易紀錄,也因此讓Transaction log成長了幾次,來實驗看看當其中一台次要複本資料出現未進行同步處理狀態(NOT SYNCHRONIZING)的後續症狀。

...繼續閱讀 »

[SQL Server]SELECT INTO ON FILEGROUP(SQL Server 2017)

資料庫初始檔案規劃時,除了主要的檔案群組(PRIMARY),我們通常會和DBA大人商量新增一到兩個檔案群組放到更快存取速度的磁碟,當新增資料表時,我們就可以依據資料表受歡迎程度(熱度)來設定不同的選擇,但如果遇到資料表是以SELECT INTO產生出來的,以往就只能放在預設的檔案群組(通常是PRIMARY),  如果想讓SELECT INTO的資料表要放到其他檔案群組,只能改資料庫的預設檔案群組來解決。
 

...繼續閱讀 »

[SQL Server][T-SQL]SQL語法夾帶全形空白導致語法錯誤

這週有個客戶的案子幾百隻程式上線,上線後的隔天早上,同事S回報了系統程式出現很基本的SQL語法錯誤,明明年輕的工程師都測試過,還是發生了。

為了要掩蓋自己對這次問題原因的了解不足(是相容性層級嗎?),趕緊掛上電話花幾分鐘簡單的測試,試試改相容性層級然後回報同事S,最後發現問題在於不同SQL版本對全形空白的解析有差異,這次和相容性層級無關,晚上看F1賽車不練跑,快來筆記這個全形空白問題。

...繼續閱讀 »

[SQL Server][T-SQL]CONCAT_WS串接分隔字元強化(SQL Server 2017)

如果打算將SQL查詢結果串接成逗號分隔的csv檔案,用ETL工具、BCP、用管理工具在查詢結果按右鍵等都可以實現,但如果想單靠SQL語法,MySQL可以用concat-ws()函式,在SQL Server我們就得用上很長的語法串接(+號 or CONCAT),今晚是新加坡F1夜戰第10年,一邊看賽車一邊來吃SQL 2017新的語法糖。

...繼續閱讀 »