[SQL SERVER][Performance]CPU Bottleneck(三)--深入平行處理
第二部分我有提到不當的平行處理會造成高CPU用量，
至於不當的平行處理為什麼會是少見到的原因，
因為大部分的DBA都會關閉平行處理和HT，主要有2個因素，
那就是早期OS對CPU HT(超執行緒，HT（Hyper-threading))技術支援不成熟及CPU L2 cache太低，
如win2000、win2003幾乎都存在誤判的情況，而這也會間接影響到SQL SERVER，
但如今windows server family已經發展到win2012了，SQL SERVER也已經發展到SQL2012(近期也推出SQL2014 preview)，
硬體CPU L2 cache現在都是以MB為單位了，軟硬體技術快速成長，
那你是否還要繼續關閉HT技術或設定max degree of parallelismoptions=1呢?
第三部分我將針對這點進行深入探討，畢竟長期關閉平行處理對效能來說是有所降低的(雖然可以避開一些不必要問題)，
但DBA應該要好好思考一下(依照環境和負載類型)，
max degree of parallelismoptions和 cost threshold for parallelism這兩個值該如何設定呢?
SQL SERVER有兩個選項來控制平行處理:
a.max degree of parallelismoptions:控制單一查詢，要使用多少CPU數量來執行查詢。
b.cost threshold for parallelism:控制查詢優化程式，該使用多少條執行緒來執行查詢。
select * from sys.configurations
where name in ('cost threshold for parallelism','max degree of parallelism')
Max degree of parallelism:
每當一個序列化查詢成本超過 cost threshold for parallelism 設定值，
Database Engine將把查詢負載傳送給每個可用CPU，讓該查詢可以跨多CPU來執行，
而決定CPU數量取決於下面三項中最低一項
a.SERVER上可用CPU數量
b.max degree of parallelismoptions設定值
c.MAXDOP Hint(將覆寫 max degree of parallelismoptions)
在OLTP環境中，如果交易量都是小的，並且有很多並行交易要處理，
那麼max degree of parallelism建議設定=1是可行的，至於該值到底要設定多少呢?
我建議觀察並分析 CXPACKET 等待類型(第二部分我有提到)，並依工作負載類型來設定。
CXPACKET等待類型再大多案例中是一種症狀，而不是一個真實問題。
假如I/O子系統吞吐量已經無法提供平行處理所需，
那麼就會顯示IO_COMPLETION, ASYNC_IO_COMPLETION 或PAGEIOLATCH_*相關等待類型，
當這樣情況發生時，減少 max degree of parallelismoptions且不影響I/O子系統吞吐量的前提下(可繼續執行查詢)，
或許是最快又最有效的效能解決方法。另一情況是，
LATCH_* 和 SOS_SCHEDULER_YIELD混和等待類型，
進一步透過 sys.dm_os_latch_stats DMV調查已發生的統計資料，
來減少 max degree of parallelismoptions 並避開問題。
ps:SQL 2008後可以透過資源管理來控制CPU使用率。
*NUMA(Non-Uniform Memory Access)系統架構中
max degree of parallelism應該設定為每一個NUMA節點上最小可用的CPU數量，
這樣做是防止跨節點的平行處理情求問題發生，你永遠要知道，跨節點處理都是要付出很高的成本代價。
*SMP(Symmetric Multiprocessing)系統架構中
使用多個CPU的影響來自於L2 cache(L2 cache越大越好)，
如果開啟HT技術，對早期SQL SERVER來說會有緩衝區命中率過低的問題，
所以關閉HT或設定max degree of parallelismoptions=1可以避掉很多問題，
但現在是2013年，軟硬體技術飛快進步，以前問題早已不存在，
所以該選項設定值，絕大部分取決於工作負載類型(OLAP 、DSS、OLTP)。
Cost threshold for parallelism:
該值預設值是5秒，這表示說，如果一個查詢在系統上執行序列化查詢時間大於5秒時，
那麼該查詢就將使用平行執行計畫來處理，在一個大型資料庫中，該值可能是很低的，
這樣才能針對一般查詢使用平行處理，不過設定太低可能會導致CPU和I/O上的資源競爭情況，
所以你可以找出那些最常使用平行查詢的語法，然後設定適當的值，以利減少資源競爭情況。
省電選項:
環保意識抬頭，所以現在bios和OS都有相關電源選項設定，
一般windows server預設都是平衡，但有些管理者會設定為省電模式，
很不幸當你設定平衡或省電模式將直接影響CPU時脈速度，
當設定省電模式且SERVER不繁忙時，CPU會降低時脈速度以求省電，
當SERVER開始接收相關工作請求時，CPU時脈速度會慢慢增加，直到全時脈為止(可以透過CPUZ這類軟體來驗證)，
我個人都是直接設定高效能模式，並且確定bios電源設定控制權是交給OS來掌控。
超執行緒 HT（Hyper-threading):
早期很多人都告訴我，不要在SQL SERVER上開啟HT技術，
而這最大的問題就是老舊的CPU架構 L2 cache過低，當啟用HT技術時，
會造成SQL SERVER緩衝區命中率降低(兩者共用緩衝區)，再來就是早期OS對HT技術支援度不成熟，
常有誤判情況(把虛擬CPU當成實體CPU來處理...等問題)，但現在已經是2013年了，
早期CPU架構和OS問題早已不存在，
所以現在照理說SQL SERVER應該更適合開啟HT( 尤其在win2008以後版本的OS上)，
當然你硬要關閉HT也沒什麼大問題，只是我會建議先針對不同工作負載類型測試後再決定是否關閉，
這樣的測試可以讓你更知道HT，帶給SQL SERVER是優點或缺點。
ps:如果CPU超過8核心的話，那麼我不會開啟HT功能。
總結:
透過這一系列文章，你應該知道，有那些原因會造成過度使用CPU，
而DBA對CPU使用率管理不善的話，對SQL SERVER將有很明顯的影響，
一般來說，10~15分鐘期間，CPU使用率不會持續超過70%~80%(偶爾尖峰不是問題)，
但如果你的SERVER不幸出現這樣情況時，第一步要先確認CPU資源是否都為SQL SERVER所使用，
然後找出高CPU使用率問題的主因(高成本、高CPU 的SQL)，當你進行了一些SQL Tuning(修改不良語法、建立遺漏索引..等)或改變相關選項設定值後，
發現依然無法降低CPU使用率的話，那麼你可能就要增加或更換CPU了。
參考