SQLSERVER排查CPU占用高的情况

一般排查都是用下面的脚本，一般会用到三个视图 sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests

sys.sysprocesses ：系统表是一个很重要的系统视图，主要用来定位与解决Sql Server的阻塞和死锁；Sys.dm_exec_connections、 sys.dm_exec_sessions 和 sys.dm_exec_requests 动态管理视图映射到sys.sysprocesses系统表。

dm_exec_sessions ：是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。 此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等

dm_exec_requests：返回有关在 SQL Server 中正在执行的每个请求的信息。

USE master
GO
--如果要指定数据库就把注释去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='数据库名称'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

看一下各项指标是否正常，是否有阻塞，这个语句选取了前10个最耗CPU时间的会话

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='数据库名称'  
ORDER BY [cpu_time] DESC

查询CPU占用高的语句

SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
  (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
  (CASE WHEN statement_end_offset = -1
   THEN LEN(CONVERT(nvarchar(max), text)) * 2
    ELSE statement_end_offset
    END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

查询缺失索引

--查看每个库，未设置索引的总数
SELECT 
 DatabaseName = DB_NAME(database_id)
 ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

--查出未设置索引的具体情况，详细列出表名、数据库名等详细信息 
SELECT  TOP 10 
         [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
         , avg_user_impact
         , TableName = statement
         , [EqualityUsage] = equality_columns 
         , [InequalityUsage] = inequality_columns
          , [Include Cloumns] = included_columns
  FROM        sys.dm_db_missing_index_groups g 
  INNER JOIN    sys.dm_db_missing_index_group_stats s 
        ON s.group_handle = g.index_group_handle 
 INNER JOIN    sys.dm_db_missing_index_details d 
        ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

新建非聚集索引

--定位未建索引的表后，新建非聚集索引
CREATE NONCLUSTERED INDEX IX_t_AccessControl_F4 ON dbo.t_AccessControl
(
     FObjectType
 )include([FUserID], [FAccessType], [FAccessMask]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 
 drop index IX_t_AccessControl_F4 on t_AccessControl