SqlServer CPU过高处理

1，查看CPU数和user scheduler数目

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

2，查看当前的数据库用户连接有多少

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

3，看链接来自哪里

--hostname program_name 看链接来自哪里
 USE master
 GO
 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 
 AND DB_NAME([dbid])='databaseName'
 --and program_name=''                                                                                                  
--链接会话数
 SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

4，前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])='databaseName'  
ORDER BY [cpu_time] DESC

5，查看会话中有多少个worker在等待

 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',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

6，查看死锁

SELECT request_session_id spid,
	OBJECT_NAME(resource_associated_entity_id) tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'

7，根据spid 查看执行的sql

--查spid执行的sql
select er.session_id,CAST(csql.text AS varchar(1024)) AS CallingSQL
from master.sys.dm_exec_requests er
WITH (NOLOCK)
       CROSS APPLY MASTER.sys.fn_get_sql (er.sql_handle) csql
where er.session_id =784

8，杀掉死锁进程

kill spid

更多信息请关注公众号：