--查找被阻塞進程 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--進程正在執(zhí)行的 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--連接數(shù)總數(shù) SELECT COUNT(session_id) AS session_count FROM sys.dm_exec_sessions where database_id=9--各服務(wù)器連接數(shù) 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

?

轉(zhuǎn)載于:https://www.cnblogs.com/honghong75042/p/4223171.html

總結(jié)

以上是生活随笔為你收集整理的sql数据库监控语句的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。