SQLSERVER查看阻塞和CPU占用的会话

SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid)='TEST';	--TEST数据库的进程数
SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info;	--CPU和SCHEDULER数量
SELECT max_workers_count FROM sys.dm_os_sys_info;		--最大工作线程数

--CPU占比高的10条SQL语句
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

--查看当前排队正在执行的语句
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

--查看TEST数据库下面是否有阻塞，前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)='TEST'  
ORDER BY cpu_time DESC