SQL Server中CPU使用率过高的排查

CPU使用率过高有许多可能原因，但以下原因最为常见：

1.由于以下情况，表或索引扫描导致的高逻辑读取：
·过期统计信息
·缺少索引
·参数敏感计划 (PSP) 问题
·设计不佳的查询
2.工作负荷增加

对于安装了sqlserver的服务器，可以先看下任务管理器中sqlserver对cpu的占用情况，确定是否是sqlserver导致cpu消耗过高。

如果sqlserver进程导致CPU使用率过高，则最常见的原因是执行表或索引扫描的查询，其次是排序、哈希操作和循环 (嵌套循环运算符或 WHILE (T-SQL) ) 。

查询sqlserver当前在总CPU使用率中的占比：

DECLARE @init_sum_cpu_time int,@utilizedCpuCount int--get CPU count used by SQL ServerSELECT @utilizedCpuCount = COUNT( * )FROM sys.dm_os_schedulersWHERE status = 'VISIBLE ONLINE'--calculate the CPU usage by queries OVER a 5 sec intervalSELECT @init_sum_cpu_time = SUM(cpu_time)FROM sys.dm_exec_requests WAITFOR DELAY '00:00:05'SELECT CONVERT(DECIMAL(5,2),((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU FROM Queries AS Percent of Total CPU Capacity]FROM sys.dm_exec_requests

 

查看当前占用CPU高的查询：

SELECT TOP 100 s.session_id,r.status,r.cpu_time,r.logical_reads,r.reads,r.writes,r.total_elapsed_time / (1000 * 60) 'Elaps M',SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,((CASE r.statement_end_offsetWHEN -1 THEN DATALENGTH(st.TEXT)ELSE r.statement_end_offsetEND - r.statement_start_offset) / 2) + 1) AS statement_text,COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,r.command,s.login_name,s.host_name,c.client_net_address,s.program_name,s.last_request_end_time,s.login_time,r.open_transaction_countFROM sys.dm_exec_connections cJOIN sys.dm_exec_sessions s ON c.session_id= s.session_idJOIN sys.dm_exec_requests r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st-- WHERE r.session_id != @@SPIDORDER BY r.cpu_time DESC

 

查找历史当前占用CPU高的查询：

SELECT TOP 100 st.text AS batch_text,SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1,((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,qs.execution_count,qs.total_worker_time,qs.last_execution_time,(qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,(qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,qs.total_logical_reads / qs.execution_count AS avg_logical_reads,(qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,(qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_msFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) stORDER BY(qs.total_worker_time / qs.execution_count) DESC

 

查询识别导致CPU使用率高且在查询计划中至少包含一个缺失索引的查询：

-- Captures the Total CPU time spent by a query along with the query plan and total executionsSELECTqs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,q.[text],p.query_plan,qs_cpu.execution_count,q.dbid,d.name,q.objectid,q.encrypted AS text_encryptedFROM(SELECT TOP 500 qs.plan_handle,qs.total_worker_time,qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpuCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qJOIN master.dbo.sysdatabases d on q.dbid=d.dbidCROSS APPLY sys.dm_exec_query_plan(plan_handle) pWHERE p.query_plan.exist('declare namespaceqplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";//qplan:MissingIndexes')=1

 

使用以下查询检查是否缺少索引，并应用具有高改进度量值的任何建议索引。
从输出中具有最高improvement_measure值的前5或10条建议开始。这些索引对性能有最显著的积极影响。

SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,mig.index_group_handle,mid.index_handle,CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULLAND mid.inequality_columns IS NOT NULL THEN ','ELSE ''END + ISNULL(mid.inequality_columns,'') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')','') AS create_index_statement,migs.*,mid.database_id,mid.[object_id]FROM sys.dm_db_missing_index_groups migINNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handleWHERE CONVERT (DECIMAL (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC