SQL Server Nuts 堅果的堅持 Day1 (Setup & Configuration)
2022-09-29
SQL Server 堅果的堅持學習筆記系列，不中斷的連續學習，深入 SQL Server 核心知識 🥜
Best Practice Checklist
掌握資料庫伺服器的實體設備資源 (伺服器、磁碟與網路實際配置方式)
清單管理資料庫伺服器的作業系統、版本 (Version, Edition 以及 CU & Patch)
確認資料庫伺服器啟用 檔案立即初始化 (instant file initialization)
確認啟用 Optimize for Ad-Hoc Workloads
確認資料庫伺服器記憶體上限
確認伺服器平行化 (parallelism) MAXDOP 與 Cost Threshold for Parallelism 設定
確認 tempdb 設定 是否符合最佳設定，如果在 SQL Server 2016 版本以前推薦包含 (T1118, T1117)
停止以及取消資料庫 AutoShrink 作業
確認資料庫 mdf 以及 ldf 的設置
確認交易紀錄的 VLFs 數量
確認 SQL Server Errors 的錯誤訊息
掌握常見且實用的 Trace Flag
細說優化設定
Optimize for Ad-hoc Workloads
伺服器層級的設定，讓 ad-hoc 查詢 (對資料表特定的子集內容查詢) ，避免無法被重複利用的 ad-hoc 查詢留下執行計畫，排擠到執行計畫快取資源。
設定完成後必須要重新啟動服務，或者在新的查詢才會生效。
SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO防毒軟體
考慮將防毒軟體的掃描路徑排除 SQL Server 的資料檔路徑。
確認交易紀錄的 VLFs
SQL Server 在內部管理上，會將交易紀錄分割為多個 Virtual Log Files (VLFs)，過多的 VLFs 與過少的 VLFs 都會造成效能上的影響。藉由決定適當的 ldf 檔案初始大小以及成長大小，可以控制 VLFs 的最適數量。
反過來說，可以藉由達到理想 VLFs 的數量目標，來設定適當的 ldf 檔案初始以及成長大小。
select * from sys.dm_db_log_info(database_id)
select name, database_id from sys.databasesData Files 與 Filegroups
藉由使用多份 Data Files，將檔案的分散到多的 Filegroups 可以增加 allocation maps，減少資料在寫入上的衝突。需要注意多個 Data Files 能夠平均的被寫入新的資料。
而值得思索的是在只有單一磁碟的情況下多份 Data Files 的優勢是否能仍發揮？因為畢竟是在相同的磁碟上做寫入 🤔
Trace Flag
| Trace Flag | 用途 |
|---|---|
| T902 | 在更新與升級失敗後，迴避失敗以緊急啟動 SQL Server Service |
| T1118 | tempdb 使用 mixed extents 效能優化 (SQL Server 2016 default) |
| T1117 | 檔案自動成長效能優化 (SQL Server 2016 default) |
| T2371 | 增加大資料表自動統計更新的頻率 |
| T3226 | 關閉備份成功的訊息加入 error log，減少資訊量 |
| T1222 | 關閉 deadlock gragh 加入 error log，減少資訊量 |
| T7412 | 啟用 lightweight execution profiling |
NUMA Code
SELECT
parent_node_id
,COUNT(*) as [Schedulers]
,SUM(current_tasks_count) as [Current]
,SUM(runnable_tasks_count) as [Runnable]
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY parent_node_id;MAXDOP & Cost Threshold for Parallelism
| 核心數 | MAXDOP |
|---|---|
| >= 8 Cores | 8 |
| < 8 Cores | 等於 Cores 數 |
| NUMA | 等於 NUMA 數 |
或是依據經驗法則，在 OLTP 的環境設定為 1/4 的 CPU數；在 OLAP 環境設定為 1/2 的 CPU數，注意 MAXDOP 上限 16。
Cost Threshold for Parallelism 則依據經驗法則設定為 50。
資料庫層級優化設定
ALTER DATABASE DatabaseName SET AUTO_SHRINK OFF
GO
ALTER DATABASE DatabaseName SET AUTO_CLOSE OFF
GO
ALTER DATABASE DatabaseName SET AUTO_UPDATE_STATISTICS ON
GO參考資料
SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Techniques