SQL Server 效能調校起手式 (Performance Tunning Starter Pack)
2022-03-30
規劃從課程以及實務經驗中，關於 SQL Server 進行效能調校與最佳化的起手式。
說明
確認 Tempdb
tempdb 檔案數量 = Min(8, 伺服器核心數)
並且給 tempdb 適當的初始大小與成長大小。
把最快的儲存空間留給 tempdb，本機優先於遠端、SSD 優先於磁碟。
掌握資料庫的資訊
確認目前的資料庫數量以及使用的檔案資訊，確認重點包含復原模式、相容性等級。
SELECT
d.name, -- 資料庫名稱
f.physical_name, --實體檔案路徑
d.state_desc, --資料庫狀態 線上、離線、復原模式
(cast(f.size AS FLOAT)*8)/1000 AS fileSize, --實體檔案大小 MB
d.recovery_model_desc, --復原模式 完整、批次、簡單
d.compatibility_level, --相容性等級
d.create_date , f.differential_base_time --建立日期與檔案異動日期
FROM sys.master_files as f
JOIN sys.databases AS d ON f.database_id = d.database_id
READ_COMMITTED_SNAPSHOT
調整並啟用 READ_COMMITTED_SNAPSHOT (RCSI)，允許資料表被寫入鎖定時，仍允許讀取儲存在 tempdb 中複本的行為。
⚠️注意潛在有資料髒讀 (Read Uncommitted) 以及 tempdb 大小膨脹的問題。
ALTER DATABASE [databaseName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;使用前宜先具備對於 RCSI 以及 Snapshot Isolation Level 的認識，以及注意其可能帶來的影響。推薦閱讀史丹利好熱的 讀取認可快照隔離(RCSI) vs 交易快照隔離(Snapshot Isolation)的查詢一致性 以及 LittleKendra 的 How to Choose Between RCSI and Snapshot Isolation Levels。
確認連線數
exec sp_who2確認維護計畫
觀察是否有定期進行完整備份、交易紀錄備份、備份記錄清除以及重建索引。
確認 MAXDOP
應該按照 NUMA 節點數來設置 MAXDOP，最大不超過 8 。
| 核心數 | MAXDOP |
|---|---|
| >= 8 Cores | 8 |
| < 8 Cores | 等於 Cores 數 |
| NUMA | 等於 NUMA 數 |
鎖定記憶體中的分頁 & 執行磁碟區維護工作
Windows Run secpol.msc
選擇「本機原則」後選擇「使用者權限指派」，並將 SQL Server Service 帳戶 NT Service\MSSQLSERVICE 授予 鎖定記憶體中的分頁 (Lock pages in memory) 與 執行磁碟區維護工作 (Perform volume maintenance tasks) 權限。
索引判斷
使用標準報表以及 DMVs 觀察缺少的索引以及需要重建的索引。