性能調優2：CPU

關系型資料庫嚴重依賴底層的硬體資源，CPU是伺服器的大腦，當CPU開銷很高時，記憶體和硬碟系統都會産生不必需要的壓力。CPU的性能問題，直覺來看，就是任務管理器中看到的CPU使用率始終處于100%，而偵測CPU壓力的工具，最精确的就是性能監控器。

在SQL Server中，有五類操作非常消耗CPU資源：

• 編譯執行計劃，生成執行計劃是非常消耗CPU資源的操作，當一個語句生成執行計劃之後，SQL Server把其存儲在Plan Cache中，以便重用執行計劃。
• 執行排序（Sort），聚合計算（Aggregation），哈希連接配接操作都需要消耗CPU來完成計算
• IO指令的執行需要CPU的協助，大量Disk IO會消耗一定的CPU資源來執行；
• 以并發方式執行查詢請求，并發控制受到配置選項 Maximum Degree of Parallelism 和 Cost Threshold of Parallelism的影響；
• 資料的壓縮和解壓縮，資料的壓縮和解壓縮都需要CPU的計算來完成

一，使用性能監控器偵測CPU壓力

性能監控器（PerfMon）是偵測CPU壓力的首選工具，對于CPU高使用率，在使用性能監控器時可以重點關注下面的3個計數器：

• Processor/ %Privileged Time：花費在執行Winidows核心指令上的處理器時間的百分比
• Processor/ %User Time：花費在處理應用程式上的處理器時間的百分比
• Process(sqlserver.exe)/ % Processor Time：每個處理器所有程序的總處理時間

 除了上面這3給計數器之外，還可以使用SQL Statistics計數器來監控：

• SQL Server:SQL Statistics/Auto-Param Attempts/sec
• SQL Server:SQL Statistics/Failed Auto-params/sec
• SQL Server:SQL Statistics/Batch Requests/sec
• SQL Server:SQL Statistics/SQL Compilations/sec
• SQL Server:SQL Statistics/SQL Re-Compilations/sec
• SQL Server:Plan Cache/Cache Hit Ratio

二，使用DMV偵測CPU壓力

使用DMV來偵測目前系統CPU的壓力，正常的步驟是：

• step1：使用sys.dm_os_wait_stats 檢查等待，檢視是否存在CPU壓力
• step2：根據等待類型，通過sys.dm_os_wait_stats 和 sys.dm_os_schedulers 确定CPU問題的種類
• step3：通過sys.dm_exec_query_stats 和 sys.dm_exec_sql_text 找出計劃緩存中CPU消耗最高的查詢
• step4：通過sys.dm_os_waiting_tasks找到目前任務中CPU相關的等待類型中CPU消耗最高的任務
• step5：從sys.dm_exec_requests中找到目前查詢中CPU資源使用最高的查詢。

三，CPU相關的等待

從sys.dm_os_wait_stats 中檢查等待，對于CPU壓力，通常相關的等待類型是：SOS_SCHEDULER_YIELD和CXPACKET

1，CXPACKET

CXPACKET是最常見的并行等待，如果一個查詢由多個線程組成，那麼隻有在最慢的那個線程完成之後，整個查詢才會完成。這就是并行查詢的木桶效應，一個木桶的容量取決于組成木桶最短的那塊木條的長度。

在多CPU的環境中，一個單獨的查詢可以使用多個線程來共同完成，每個線程單獨處理資料集的一部分。在并行處理的過程中，如果某個線程處于落後狀态，CXPACKET等待就會産生。但是，應該注意，CXPACKET等待并不總是表示系統存在性能問題。需要測試，合理設定并行度門檻值（Cost Threshold for Parallelism，CTP）和最大并發度(Max Degree of Parallelism，MDP)，這兩個配置項的用途是：

• CTP是指隻有查詢的開銷超過一定的門檻值之後，才會使用并發操作
• MDP應設定為CPU的核心數量，表示最多使用多少個線程同時處理任務

出現CXPACKET等待的原因是：

• 在可變類型中，資料的分布存在嚴重的傾斜，比如某列nvarchar類型的資料，有些資料的長度是幾個字元，有些的幾千個字元，對這樣的資料進行查詢時，會導緻某些線程執行很快，但另一個線程執行很慢。
• 查詢所需要的資料存放在不同的IO子系統中，而這些子系統的性能又存在差異
• 查詢所需要的資料中，不同部分的碎片不同，所需的IO也不同。IO數量直接影響運作速度和資源開銷，進而導緻查詢過程中不同線程的運作速度不同。

2，SOS_SCHEDULER_YIELD

SOS_SCHEDULER_YIELD是在多任務排程系統中才會出現的等待類型，多任務是指伺服器擁有多核，可以同時處理多個任務。SOS_SCHEDULER_YIELD等待類型就發生在一個任務放棄目前占用的資源，讓其他任務使用資源以執行下去。

SQL Server以協同模式運作，在必要的時候，SQL Server會讓出資源給其他線程，通常來說，這種讓步是臨時的，但是，當長期、大量出現這種等待的時候，有可能意味着CPU存在壓力，這個時候，可以檢查 sys.dm_os_schedulers，看看目前有多少個 runnable的任務在運作，

select schedule_id, current_tasks_count, runnable_task_count, work_queue_count, pending_disk_io_count
from sys.dm_os_schedulers
where schedule_id<255      

通常情況下，如果 runnable_task_count 字段長時間存在兩位數的數值，就意味着CPU可能存在壓力，無法應對目前的工作負載。

四，常見的高CPU使用率的原因

 下面總結了7個常見的高CPU使用率的情況。

1，缺失索引

當沒有合适的索引用于支援查詢時，一般隻能通過大面積表掃描來擷取所需要的資訊，這會導緻SQL Server需要處理很多非必要的資料，由于需要加載很多非必要的資料到記憶體，這些IO操作需要消耗CPU資源，大量資料被加載到記憶體也會引起記憶體壓力，導緻計劃緩存被移除，使得SQL Server必須重新編譯執行計劃，編譯和生成執行計劃也是高CPU開銷操作。

2，統計資訊過時

SQL Server 優化器借助統計資訊來預估查詢開銷，如果統計資訊過時、不準确，會導緻優化器産生不合适的執行計劃。

可以檢查一下圖形執行計劃，如果預估行數和實際行數的的差異很大，就說明統計資訊過時，需要更新。

3，非SARG查詢

SARG是 Search Argumeng的縮寫，簡單來說，如果一個查詢條件（where，on）能用到索引查找操作（seek index），那麼該表達式就是SARG。

通常情況下，對索引列使用了計算式或函數，或者使用了 like '%str'等都會導緻索引失效，這類查詢都屬于非SARG查詢。

4，隐式轉換

由于SQL Server無法比對不同類型的資料，是以需要先把資料轉換為相同的類型，才能進行比對。

如果在實際的執行計劃中出現 CONVERT_IMPLICIT 操作符，就說明出現了類型的隐式轉換。

5，參數嗅探

參數嗅探是指在建立存儲過程，或者參數化查詢的執行計劃時，根據傳入的參數進行預估并生成執行計劃。SQL Server生成的執行計劃對目前參數來說是最優的，而對其他大多數參數來說，是非常低效的。有些時候，針對一個查詢的第一次傳參，已經産生了一個執行計劃，當後續傳參時，由于存在對應參數的資料分布等問題，導緻原有的執行計劃無法高效地響應查詢請求，這就出現參數嗅探問題。

對于參數嗅探問題，可以使用語句重編譯，編譯提示（optimize for）等功能來避免。

6，非參數Ad-Hoc查詢

 非參數Ad-Hoc查詢，是指SQL Server 緩存了大量的隻用一次的計劃緩存，造成記憶體資源和CPU資源的浪費，可以使用存儲過程、參數化的Ad-Hoc查詢或啟用 “Optimize for Ad Hoc Workloads”來避免。

參數化的Ad-Hoc查詢通常是指使用 sp_executesql 來執行一段TSQL代碼。

“針對即席工作負載進行優化”是一個Server級别的性能優化選項，用于提高包含許多臨時批處理的工作負載的計劃緩存的效率，如果把該選項設定為True，則資料庫引擎在首次編譯批處理時隻保留計劃緩存中的一個存根，而不是存儲整個執行計劃。當再次調用該批處理時，資料庫引擎識别出該批處理在之前被執行過，進而從計劃緩存中删除該執行計劃的存根，并把完全編譯的執行計劃添加到計劃緩存中。當非參數化的Ad-Hoc查詢較多時，可以避免計劃緩存存儲過多的不會被複用的執行計劃。

7，壓縮操作

壓縮和解壓縮都是CPU高開銷的操作，資料壓縮、備份壓縮和日志流壓縮通過增加CPU的使用率來降低IO子系統壓力和硬碟空間壓力。資料壓縮的優點是降低IO子系統的壓力，提高查詢的性能，其缺點是消耗CPU資源，對資料的插入和更新操作有負面影響。

參考文檔：

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。