0%

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
----定位问题后，新建非聚集索引
-- ……