資料庫效能調教 — 資料庫的設定值

資料庫效能調教 — 資料庫的設定值

ID-100131738

這篇文章主要討論幾個主要資料庫對於效能影響的設定值

  • 記憶體的設定
  • 平行處理的設定
  • 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 ,所以就會進行平行處理。

Cost Threshold for Parallelism

 

 

 

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 維護與統計資訊的更新等。

 

Leave a Reply

Your email address will not be published. Required fields are marked *