2022-03-09

Database

SQL Server Performance Tuning 效能調校 🚀

說明 SQL Server 效能調校與最佳化的各種設定方式、觀念心法，包含從伺服器安裝的組態設定、資料庫架構、交易設計、查詢優化及索引優化。

說明

關於伺服器組態設定，包含 tempdb、MAXDOP 以及記憶體的設置，請參考 SQL Server Installation Detailed 安裝流程與規劃詳細說明。

效能的評斷工具

SQLDiag

需要在資料庫伺服器環境執行，且執行需要系統管理者的權限，綜合 msinfo32, performance counter 以及 sql trace，可以蒐錄詳盡的 SQL Server 運行環境資訊。

sqldiag -Pc:\sqldiag

執行指令後會在指定路徑建立相關設定檔案，並且開始蒐錄，第一次執行後可以先使用 Ctrl + C馬上停止，先調整設定檔案 C:\sqldiag\SQLDiag.XML

其他 sqldiag 會一併建立的檔案：

C:\sqldiag\MSDiagProcs.sql
C:\sqldiag\SD_Detailed.XML
C:\sqldiag\SD_General.XML
C:\sqldiag\SQLDIAG

如果有安裝多個 SQL Server Instance 但只要蒐集特定 Instance 可以藉由調整 SQLDiag.XML 來設定。

<Instance name="*" windowsauth="true" ssver="15" user="">

PerfmonCollector、BlockingCollector 及 ProfilerCollector 都可以按需求開啟：

<PerfmonCollector enabled="false" pollinginterval="5" maxfilesize="256">

<BlockingCollector enabled="false" pollinginterval="5" maxfilesize="350" filecount="1"/>

<ProfilerCollector enabled="false" template="_GeneralPerformance90.xml" 
  pollinginterval="5" maxfilesize="350" filecount="1">

其中 PerfmonCollector 下預設的 PerfmonCounter 開啟 (enabled)，可以先全部取代為 false，再根據需要開啟特定的 PerfmonCounter

停止蒐集後，蒐錄到的資訊會儲存在 C:\sqldiag\SQLDIAG

internal
computerName_MSINFO32.TXT
computerName__sp_sqldiag_Shutdown.OUT
log_90.trc
log_91.trc
SQLDIAG.BLG
SQLDUMPER_ERRORLOG.log

關於 SQL Diag 實用的參數：

/B: 指定開始錄製時間，可以使用絕對時間 /B20220310_18:00:00 或相對時間 /B+01:00:00
/E: 指定結束錄製時間，可以使用絕對時間或相對時間，配合 /B 可以指定錄製時間區間。
/L: 循環錄製，可以搭配 /B 以及 /E 來指定循環錄製時段，並且搭配 /N2 避免覆蓋已錄製的檔案。
/N2: 為新的錄製以不同的檔名號碼標誌。
/R: 讓 SQL Diag 以 Windows 服務的方式錄製。

SSMS 標準報表

SQL Server Profiler

用於錄製即時的 SQL 活動，可以用 Locks : Deadlock graph 以圖形化的方式觀察 Deadlock。

特別的是為了要啟用事件擷取設定，必須要取消勾選 Deadlock graph 後再重新勾取才會出現 😅

DMVs & Catalog View

Catalog View: 系統檢視表， SQL Server 2005 後隱藏原本的系統資料表，改以檢視表的方式提供檢視。例如 sys.table, sys.objects。
DMVs, Dynamic Management View: 動態檢視表， SQL Server 2005 後提供資料庫運行的動態資訊，又稱為資料庫的黑盒子資料解密。例如 sys.dm_ 開頭系列。

資料蒐集器 Data Collection

可以用於蒐集基本的伺服器活動、查詢統計及磁碟使用量資訊，在效能調整之前可以先做一定期間的觀測，或者是保持長期的監測效能使用。

首先進行管理資料倉儲的設定，這邊會需要一個資料庫用於保存資料蒐集器的資料。

接著進行資料蒐集的設定：

完成後可以在資料蒐集器下看到相關統計及報表，其實際的資源則是來自於資料庫當中的資料：

Extended events

擴充事件，微軟規劃取代 SQL Server Profiler 但還有一段路需要去努力，底層是使用 Service Broker XML 非同步的資料傳輸機制。

資料庫架構

資料庫依照使用需求分為 OLTP 與 OLAP 兩大類，OLTP 需要較大的記憶體資源、OLAP 則需要較大的 CPU 運算資源，此外 OLTP 主要是寫入、更新的動作，所以資料表需要做正規化設計；OLAP 則是讀取的動作居多，設計上以雪花或者是星狀架構，並且會有反正規化的資料表設計方式。

為讓資料庫伺服器配合 OLTP 或者 OLAP 為主的使用需求，應該在架構上拆分為不同的資料庫伺服器。

資料庫伺服器所使用的磁碟可以區分，高速的磁碟提供 log 資料寫入使用，因為 log 是資料庫一切交易行為的資料寫入開端，其他的動作都必須等待 log 寫入完成，因此把速度最好的磁碟留給 log (SSD 或者是 RAID 1, RAID 10)。

此外也適合將 tempdb 的資料檔及 log 檔放入高速的磁碟中，以提高資料庫的效能。

而如果資料庫伺服器記憶體較小，且資料庫檔案是放在 Shared Folder 上，但資料庫伺服器本身有高速的 SSD 等多餘空間可以使用時，可以使用 Buffer Pool Extension 以類似於虛擬記憶體的方式，減少資料庫伺服器記憶體置換資料頁的頻率。當然 BPE 效能與真正的記憶體相比仍是有相當程度的落差。

ALTER SERVER CONFIGURATION   
SET BUFFER POOL EXTENSION ON  
    (FILENAME = 'Z:\SSD\Extension.BPE', SIZE = 64 GB);

資料庫的資料檔 (mdf & ndf) 可以拆分多個，分布於不同磁碟上，讓多個實體讀寫頭同時發揮作用，並可以結合 Partition Table 來優化資料檔的寫入。

交易設計

交易設計說來玄玄，但其實就是構思業務邏輯實踐在資訊應用系統上的相互調適，避免會有資料庫 CRUD 效能困境的業務邏輯，而是保持業務目標達到的前提下巧妙避開資料庫 CRUD 的效能限制。

例如與其競爭搶資源的網站模式，不如改採登記報名，統一公告結果的方式。

交易優化

Shared Lock (S): SELECT 資源所進行的鎖定，分為交易結束才釋放 (悲觀 🙁) 或者是讀取完就釋放 (樂觀 🙂)，預設為樂觀。在 Shred Lock 下，資源可以被加入 Update Lock，但不可以被加入 Exclusive Lock。
Update Lock (U): 將資源獨佔前的鎖定，排除其他交易對資源進行更新，可以加入目前處於 Shared Lock 的資源，而被加入 Update Lock 後，其他交易不能再加入讀取目的的 Shared Lock。
Exclusive Lock (X): 對資源進行獨佔的排除鎖定，用以進行 Insert, Delete, Update 動作。
Intent Lock (IS, IU, IX): 對資源進行意圖鎖定，包含意圖 Shared Lock 的 IS 以及意圖更新的 IU 以及意圖排他的 IX。如果資料在 Row Level 被鎖定，會自動在 Page 以及 Table 等級進行 IX 意圖鎖定，以排除其他交易重複鎖定資源。

查詢 Lock 的方式

使用 DBCC OPENTRAN 可以看到目前最久尚未完成的交易，根據需求關閉它並且找到發生的原因 🧐

DBCC OPENTRAN WITH TABLERESULTS

使用 sp_lock 可以看到目前存在的 Lock 相關資訊，在 SSMS 的快捷鍵為 Ctrl + 1

使用 sp_who2 可以看到目前 Session 之間被 Blcok 的關係。

使用標準報表的所有進行封鎖交易確認目前是否存在任何 Block 問題。

Isolation Level

Read Uncommitted: 允許交易查詢到其他尚未完成交易的資料異動，可以避免鎖定發生，但存在資料正確性的問題。
Read Commited: 細分為預設的 With No Snapshot 以及 With Snapshot。
SNAPSHOT: 多版本控制
Repetable Read: 悲觀鎖定的方式
SERIALIZABLE: 悲觀鎖定的方式

預設上 SQL Server 的 Isolation Level 為 Read Commited With No Snapshot，會讓被更新或者寫入中的資料無法被讀取。藉由調整資料庫層的組態為，可以讓資料被更新或者是寫入的狀態，仍能夠以快照的方式提供其他交易進行讀取，避免鎖定的問題。

ALTER DATABASE [databaseName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

因應 Isolation Level 不同，可能會出現的問題：

Dirty Read
Non-Repeatable Read
Lost Updates
Phantom Read
Write Skews

使用 Stored Procedures

讓交易達到優化的重要觀念就是使用預存程序 (Stored Procedures)，利用預存程序會被編譯具有較佳效能的優點，並且按照預存程序的最佳設計實踐，例如操作資料庫物件的順序保持一致，來達到避免鎖定的發生。

一個對於預存程序的使用觀點為資料庫的直接新增、寫入、刪除都是透過預存程序來完成，應用程式端只具有呼叫預存程序的能力；同時複雜的運算以及商業邏輯則交由應用程式端負責，讓資料庫保持單純，讓資料庫的操作交給預存程序。

為了要做好交易優化，預存程序不可少；而要建構大型且高效率的應用系統，預存程序也不能少；無怪乎資深的資料庫開發人員、管理人員，都會把預存程序的使用視為呼吸、視為喝水般的自然，碰上從未使用預存程序的系統，紛紛都驚嘆不可思議了。而對於從未使用預存程序的開發新人而言，只覺得麻煩，殊不知預存程序是通往高效系統銜接橋梁 😀

查詢優化

避免使用 Distinct
SELECT 最小需求的資料列與欄位，不要 SELECT *
避免使用費時的運算子，尤其是 Negative Operator 系列 (<> 或者是 NOT LIKE)
不要在 WHERE 使用 funtions
不要對資料欄位進行運算，否則會讓索引無法發揮作用
多多使用預存程序及參數化查詢方式，以讓執行計畫能夠被重複使用。
不要用 cursor
避免使用過於複雜的 triggers
謹慎使用 temporary tables 以及 table variables
使用 CTE 或者 EXISTS 的方式，先縮小資料的範圍，避免大資料 JOIN 行為
搞懂 Locking 並且適當的使用 locking 以及 isolation level hint 來避免 locking 問題
不使用 Index Hint，讓 SQL Server Optimizer 判斷如何使用索引 (例如 MERGE, HASH, LOOP, and FORCE ORDER)
使用明確的資料庫物件名稱 Schema.Obejcts

使用預存程序 (Stored Procedures) 時，避免使用 temp table 而應改以資料表變數替代，避免造成預存程序需要重新編譯 (Re-Compilation)。如果有更新索引，要對預存程序 (Stored Procedures) 進行 WITH RECOMPILE。

索引優化

索引是以儲存空間以及寫入、更新資料時維護索引的成本作為代價，用以提升搜尋資料的性能優化。

如何進行索引優化

起手先用資料庫標準報表，確認索引使用量統計資料以及索引實體統計資料，確認目前索引的使用情形。使用標準報表分析費時、費資源的查詢，使用 Database Engine Tuning Advisor 判斷是否有需要建立的索引。

索引優化的觀念與技巧

選擇好的主鍵作為叢集索引 (整數、簡單型別、單一欄位、遞增)
常用的搜尋資料欄可以加入非叢集索引，相關會用到的資料欄可以加入 Included
移除不必要的索引

參考資料

Improving SQL Server Performance | Improving .NET Application Performance and Scalability

Application Architecture Guide 2.0 Designing Applications on the .NET Platform

The Skeptical Software Engineer