[SQL Server]暫存資料表(#TABLE)引發的重新編譯Re-Compilations(Ad hoc)

我們有時在SQL Server監控CPU的效能時,會額外多觀察每秒陳述式重新編譯次數(SQL Re-Compilations/sec)的效能計數,透過收集的資訊及後續的調校來降低生產環境出現大量的重新編譯。

最近專案裡,奉旨把SQL程式也加入持續整合(CI)的測試項目內,在掃描SQL程式時發現幾位成員在頻繁的線上交易語法中,使用了少許的暫存資料表(#TABLE),解釋給同事聽,順便筆記可能引發的大量持續不斷的重新編譯。

...繼續閱讀 »

[SQL Server][Performance]INSERT INTO SELECT啟用平行寫入(SQL Server 2016)

在SQL 2016以前,Insert Into Select一直都只能用一條執行緒執行資料表插入,SQL Server先在2014版本時優化了Select Into新增了平行,到了SQL 2016版本也優化了Insert Into Select,只要資料庫相容性層級設置為130(SQL 2016),搭配WITH (TABLOCK)的Table Hint,我們也可以在非叢集索引資料表平行執行Insert Into Select了。來筆記實驗過程。

Parallel process icon

...繼續閱讀 »

[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][DMV]透過動態管理檢視(DMV)抓子樹成本較高的SQL 指令或是預存程序

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

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

...繼續閱讀 »

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

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

...繼續閱讀 »

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

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

...繼續閱讀 »

[SQL Server]記憶體配置與效能

最近兩個客戶不約而同碰到SQL Server記憶體配置問題,一個客戶諮詢SCOM(System Center Operations Manager)監測到BufferCacheHitRatio比例過低以及Page Life Expectancy分頁停留在快取中的時間太低的警示;另一個客戶則是在測試環境因為資源太少(2GB記憶體)使得Table Scan執行語法跑很久,來筆記一下記憶體壓力對SQL執行效能上的影響。

...繼續閱讀 »