-- 查找被阻塞进程 Select * from master..sysprocesses where dbid = 9 and blocked > 0 -- 查找阻塞头进程 select * from master..sysprocesses p where dbid = 9 and blocked <= 0 and exists ( select 1 from master..sysprocesses where blocked = p.spid) -- 查找等待时间长的进程 Select * from master..sysprocesses where dbid = 9 order by waittime desc -- 进程正在执行的 SQL 语句 dbcc inputbuffer( 61 ); -- 中止进程 -- kill 177 -- 定位消耗cpu,io 最高的进程信息 select spid,dbid,cpu,physical_io,login_time,last_batch,status, hostname,program_name,hostprocess,cmd,nt_username from sysprocesses where dbid = 9 order by cpu,physical_io desc ; -- select OBJECT_NAME(1962490070) -- select db_id('CTI') -- IO消耗大的SQL SELECT TOP 20 [ Total IO ] = (qs.total_logical_reads + qs.total_logical_writes) , [ Average IO ] = (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count , qs.execution_count , SUBSTRING (qt. text ,(qs.statement_start_offset / 2 ) + 1 , (( CASE WHEN qs.statement_end_offset = - 1 THEN LEN ( CONVERT ( NVARCHAR ( MAX ), qt. text )) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 ) + 1 ) AS [ Individual Query ] , qt. text AS [ Parent Query ] , DB_NAME (qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY [ Total IO ] DESC -- 连接数总数 SELECT COUNT (session_id) AS session_count FROM sys.dm_exec_sessions where database_id = 9 -- 各服务器连接数 select host_name ,login_name, count (session_id) FROM sys.dm_exec_sessions where database_id = 9 group by host_name , login_name order by host_name