SELECT
request_session_id spid,
OBJECT_NAME( resource_associated_entity_id ) DtableName
FROM
sys.dm_tran_locks
WHERE
resource_type ='OBJECT'
查询各主机占用数据库的连接数
SELECT hostname 主机名,COUNT(*) 连接数量 FROM master.dbo.sysprocesses
GROUPBY hostname
ORDERBYCOUNT(*)DESC
选取前10个最耗CPU时间的会话
SELECTTOP10[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 CROSSAPPLY sys.[dm_exec_sql_text]( der.[sql_handle])AS dest
WHERE[session_id]>50AND DB_NAME( der.[database_id])='gposdb'ORDERBY[cpu_time]DESC
查询CPU占用高的语句
SELECTTOP10 total_worker_time / execution_count AS avg_cpu_cost,
plan_handle,
execution_count,(SELECT SUBSTRING
(text, statement_start_offset /2+1,(CASEWHEN statement_end_offset =-1THENLEN(CONVERT( nvarchar ( MAX ),text))*2ELSE statement_end_offset END- statement_start_offset )/2)FROM sys.dm_exec_sql_text ( sql_handle ))AS query_text
FROM
sys.dm_exec_query_stats
ORDERBY[avg_cpu_cost]DESC
查询会话中有多少个worker在等待
SELECTTOP10[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
INNERJOIN[sys].[dm_os_wait_stats]AS dows ON der.[wait_type]=[dows].[wait_type]CROSSAPPLY sys.[dm_exec_sql_text]( der.[sql_handle])AS dest
WHERE[session_id]>50ORDERBY[cpu_time]DESC