SqlServer cpu 100% 占用高 sql语句问题排查

脚本如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- 卡死中的语句
SELECT TOP 100
[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])='yourdbname'
ORDER BY [cpu_time] DESC

-- 查看具体语句

--在SSMS里选择以文本格式显示结果
SELECT TOP 10
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC

--服务器 查看CPU数和user scheduler数目
SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
--服务器 查看最大工作线程数
SELECT max_workers_count FROM sys.dm_os_sys_info

--查询缺失索引
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;



declare m_cursor cursor scroll for

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;

open m_cursor
declare @TotalCost int,@avg_user_impact float, @TableName varchar(50), @EqualityUsage varchar(500),@InequalityUsage varchar(500), @IncludeCloumns varchar(500)
--填充数据
fetch next from m_cursor into @TotalCost,@avg_user_impact,@TableName,@EqualityUsage,@InequalityUsage,@IncludeCloumns
--假如检索到了数据,才处理
while @@FETCH_STATUS=0
begin
print @TableName+@@IncludeCloumns
fetch next from m_cursor into @TotalCost,@avg_user_impact,@TableName,@EqualityUsage,@InequalityUsage,@IncludeCloumns
end
-- 关闭游标
close m_cursor
--释放游标
deallocate m_cursor
----定位问题后,新建非聚集索引
-- ……