SQL Server 效能調校起手式 (Performance Tunning Starter Pack)


  1. 說明
    1. 確認 Tempdb
    2. 掌握資料庫的資訊
    3. READ_COMMITTED_SNAPSHOT
    4. 確認連線數
    5. 確認維護計畫
    6. 確認 MAXDOP
    7. 鎖定記憶體中的分頁 & 執行磁碟區維護工作
    8. 索引判斷
  2. 相關連結

規劃從課程以及實務經驗中,關於 SQL Server 進行效能調校與最佳化的起手式。

SQL Server Logo

說明

確認 Tempdb

tempdb 檔案數量 = Min(8, 伺服器核心數)

並且給 tempdb 適當的初始大小與成長大小。

掌握資料庫的資訊

確認目前的資料庫數量以及使用的檔案資訊,確認重點包含復原模式相容性等級

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

ALTER DATABASE [databaseName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

確認連線數

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 觀察缺少的索引以及需要重建的索引。

相關連結

SQL Server 閃電般快速查詢指南⚡

SQL Server Performance Tuning 效能調校 🚀