資料庫效能調教 — 資料庫的設定值
這篇文章主要討論幾個主要資料庫對於效能影響的設定值
- 記憶體的設定
- 平行處理的設定
- Ad Hoc query 最佳化的設定
- Blocking 的設定
- 壓縮
記憶體的設定
記憶體對 SQL Server 來說其實多多益善,
因為資料庫可以將許多執行過的 query與存取的資料存放在記憶體的 cache
讓下一次的執行不需要到 Disk 存取
因此記憶體的設定,保留動態設定
Minimum(MB) = 0
Maximum(MB) = 通常會保留 2-4 G 給 OS 使用後,其他設定給 SQL Server
平行處理的設定
當有多個 CPU的狀況下,可以透過多個 CPU 平行處理來增加效能
設定 cost threshold for parallelism,預設值為 5
表示如果執行該 query 超過 5秒時,SQL Server 就會進行平行處理。
通常將該值調高,效能會有一些改善。
因為平行處理對於 SQL Server 來說不一定最好的執行方式。
例如,下圖所示,該 Query cost 為 10.7496 > 預設值 5 ,所以就會進行平行處理。
http://technet.microsoft.com/zh-tw/library/ms188603(v=sql.105).aspx
Max Degree of Parallelism (MAXDOP)
這個設定值是告訴 SQL server 有多少 CPU 可以做平行處理的運算,
多半情況下平行處理不一定是好的,因為CPU 平行處理會造成…
- 瓶頸可能會在 memory or Disk I/O , not CPU
- 當工作平行處理時,但是每一個工作完成的時間不一樣,最後完成的時間還是要等待到最長完成的那個thread 完成,整體才算完成
http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx
Ad-Hoc query
如果許多的 query 都是 ad-hoc 臨時性的執行,而非 stored procedure
可以考慮這個選項,”optimize for ad hoc workloads”
當設定值為 1的時候,SQL 對於 ad hoc query plan 的儲存就會以簡化的方式儲存,
以避免佔用記憶體太多的空間
http://msdn.microsoft.com/zh-tw/library/cc645587.aspx
Blocked process threshold
當 query blocked 超過 20秒的時候記錄通知。
sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO sp_configure 'blocked process threshold', 20 ; GO RECONFIGURE ; GO
http://technet.microsoft.com/en-us/library/ms181150(v=sql.110).aspx
檔案的位置
- Database file 與 transaction log 可以放在不同的硬碟
- 避免 RAID 5當作 transaction log 的磁碟,因為會額外造成磁碟讀取的次數
資料庫的壓縮
對於資料庫來說,資料壓縮後,在記憶體也是壓縮的資料
因此,有助於每次存取的資料量的提升,相對的,就會耗用比較多的 CPU
資料壓縮有兩種選擇,一種針對每一筆的資料,另一種是針對每一個 Page
對於 Page or Row 的壓縮選擇,可以參考下表。
通常來說,資料 update 頻率越低,讀取 Scan 頻率越高的資料,越適合壓縮。
Table | Savings ROW % | Savings PAGE % | Scan | update | 選擇 | 原因 |
---|---|---|---|---|---|---|
T1 | 80% | 90% | 3.80% | 57.27% | ROW | Low S, very high U. ROW savings close to PAGE |
T2 | 15% | 89% | 92.46% | 0% | PAGE | Very high S |
T3 | 30% | 81% | 27.14% | 4.17% | ROW | Low S |
T4 | 38% | 83% | 89.16% | 10.54% | ROW | High U |
T5 | 21% | 87% | 0.00% | 0% | PAGE | Append ONLY table |
T6 | 28% | 87% | 87.54% | 0% | PAGE | High S, low U |
T7 | 29% | 88% | 0.50% | 0% | PAGE | 99% appends |
T8 | 30% | 90% | 11.44% | 0.06% | PAGE | 85% appends |
T9 | 84% | 92% | 0.02% | 0.00% | ROW | ROW savings ~= PAGE |
T10 | 15% | 89% | 100.00% | 0.00% | PAGE | Read ONLY table |
http://msdn.microsoft.com/en-us/library/cc280449.aspx
http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
資料庫管理與維護
- 保持統計資訊的更新,有助於 execution plan 的最佳化計畫執行
- 避免使用 AUTO_close 或是 AUTO_shrink
- 注意 index fragmentation 的問題
總結
這篇文章主要討論一些資料庫的設定跟效能的關係,
- 記憶體的設定
- 平行處理的設定 “Cost threshold for parallelism” “MaxDOP”
- 當query 有很多都是Adhoc query 時的設定
- Block 的通知設定
- 資料庫的資料檔案與交易檔案存放位置
- 資料庫的壓縮
當然,還有資料庫的管理與維護工作,例如 index 維護與統計資訊的更新等。