sql数据库监控语句

最新推荐文章于 2023-06-27 22:29:22 发布

转载最新推荐文章于 2023-06-27 22:29:22 发布 · 159 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/honghong75042/p/4223171.html

文章标签：

#数据库

本文提供了一系列SQL Server性能调优的方法，包括如何查找被阻塞的进程、高CPU及IO消耗的进程、长时间等待的进程等，并展示了如何通过SQL语句来定位问题根源。

--查找被阻塞进程
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