笔记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