笔记288 排查CPU占用高的情况 2013-6-12
1 --排查CPU占用高的情况 2013-6-12
2 USE master
3 GO
4 --如果要指定数据库就把注释去掉
5 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
6 SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50
7 ------------------------------------------------------------------------------
8 SELECT TOP 10
9 [session_id],
10 [request_id],
11 [start_time] AS '开始时间',
12 [status] AS '状态',
13 [command] AS '命令',
14 dest.[text] AS 'sql语句',
15 DB_NAME([database_id]) AS '数据库名',
16 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
17 [wait_type] AS '等待资源类型',
18 [wait_time] AS '等待时间',
19 [wait_resource] AS '等待的资源',
20 [reads] AS '物理读次数',
21 [writes] AS '写次数',
22 [logical_reads] AS '逻辑读次数',
23 [row_count] AS '返回结果行数'
24 FROM sys.[dm_exec_requests] AS der
25 CROSS APPLY
26 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
27 WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'
28 ORDER BY [cpu_time] DESC
29
30 -----------------------------------------------------------------------------------
31 --在SSMS里选择以文本格式显示结果
32 SELECT TOP 10
33 dest.[text] AS 'sql语句'
34 FROM sys.[dm_exec_requests] AS der
35 CROSS APPLY
36 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
37 WHERE [session_id]>50
38 ORDER BY [cpu_time] DESC
39
40 ---------------------------------------------------------------------------
41 --查看CPU数和user scheduler数和最大工作线程数，检查worker是否用完
42
43 --查看CPU数和user scheduler数目
44 SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
45 --查看最大工作线程数
46 SELECT max_workers_count FROM sys.dm_os_sys_info
47
48
49 --查看机器上的所有schedulers包括user 和system
50 --通过下面语句可以看到worker是否用完，当达到最大线程数的时候就要检查blocking了
51 --对照下面这个表
52 --各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
53 --CPU数 32位计算机 64位计算机
54 --<=4 256 512
55 --8 288 576
56 --16 352 704
57 --32 480 960
58 SELECT
59 scheduler_address,
60 scheduler_id,
61 cpu_id,
62 status,
63 current_tasks_count,
64 current_workers_count,active_workers_count
65 FROM sys.dm_os_schedulers
