SQL Server Nuts 堅果的堅持 Day1 (Setup & Configuration)


  1. Best Practice Checklist
  2. 細說優化設定
    1. Optimize for Ad-hoc Workloads
    2. 防毒軟體
    3. 確認交易紀錄的 VLFs
    4. Data Files 與 Filegroups
    5. Trace Flag
    6. NUMA Code
    7. MAXDOP & Cost Threshold for Parallelism
    8. 資料庫層級優化設定
  3. 參考資料

SQL Server 堅果的堅持學習筆記系列,不中斷的連續學習,深入 SQL Server 核心知識 🥜

SQL Server Nuts Logo

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.databases

Data 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

SQL Server Query Tuning and Optimization: Optimize Microsoft SQL Server 2022 queries and applications

Brent OZAR