一. 概述 上次在介紹性能調優中講到了I/O的開銷查看及維護，這次介紹CPU的開銷及維護， 在調優方面是可以從多個維度去發現問題如I/O，CPU, 記憶體，鎖等，不管從哪個維度去解決，都能達到調優的效果，因為sql server系統作為一個整體性，它都是緊密相連的，例如：解決了sql語句中I/O開銷較 ...
一. 概述
上次在介紹性能調優中講到了I/O的開銷查看及維護，這次介紹CPU的開銷及維護， 在調優方面是可以從多個維度去發現問題如I/O，CPU, 記憶體，鎖等，不管從哪個維度去解決，都能達到調優的效果，因為sql server系統作為一個整體性，它都是緊密相連的，例如：解決了sql語句中I/O開銷較多的問題，那對應的CPU開銷也會減少，反之解決了CPU開銷最多的，那對應I/O開銷也會減少。解決I/O開銷後CPU耗時也減少，是因為CPU下的Worker線程需要掃描I/O頁數就少了，出現的資源鎖的阻塞也減少了，具體可參考cpu的原理。
下麵sql語句的dmv：sys.dm_exec_query_stats和sys.dm_exec_sql_text 已經在上篇”sql server 性能調優 I/O開銷分析“中有講到。
--查詢編譯以來 cpu耗時總量最多的前50條(Total_woker_time) SELECT TOP 50 total_worker_time/1000 AS [總消耗CPU 時間(ms)], execution_count [運行次數], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 時間(ms)], last_execution_time AS [最後一次執行時間], max_worker_time /1000 AS [最大執行時間(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的語法], qt.text [完整語法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY total_worker_time DESC
查詢如下圖所示，顯示CPU耗時總量最多的前50條
在排名第38條，拿出耗時的sql腳本來分析，發現未走索引。如下圖
SELECT [PO_NO],[Qty] FROM [ORD_PurchaseLine] WITH(NOLOCK) WHERE ([PO_NO] IN (' '))
二. 維護註意點
1. 在生產資料庫下，CPU耗時查詢，並不限定只排查總耗時前50條，可以是前100~200條。具體看sql腳本沒有沒優化的需要，並不是每個表的查詢都必須走索引。如：有的表不走索引時並不會感覺很耗時平均I/0次數少，表中已建的索引已有多個，增刪改也頻繁,還有索引占用空間，這時需要權衡。
-- 快速查看索引數量 sp_help [RFQ_PurDemandDetail]
2. 不要在工作時間維護大表索引
當我們排查到有的大表缺失索引，數據在100w以上，如果在工作時間來維護索引，不管是創建索引還是重建索引都會造成表的阻塞， 這裡表的響應會變慢或者直接卡死，前端應用程式直接請求超時。這裡需要註意的。來看下新建一個索引的腳本會發現 開啟了行鎖與頁鎖(ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)。
CREATE NONCLUSTERED INDEX [ix_createtime] ON [dbo].[PUB_Search_Log] ( [CreateTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
