对于在线运行的系统,当前数据库性能监控,通常监视以下几点: (1) 是否有阻塞 (Blocking); (2) 是否有等待 (Waiting),阻塞就是锁 (Lock) 等待; (3) 是否运行时间过长(Long running); (4) 是否有死锁 (Deadlock); sys.dm_exec_query_stats之类,等一些统
对于在线运行的系统,当前数据库性能监控,通常监视以下几点:
(1) 是否有阻塞 (Blocking);
(2) 是否有等待 (Waiting),阻塞就是锁 (Lock) 等待;
(3) 是否运行时间过长(Long running);
(4) 是否有死锁 (Deadlock);
sys.dm_exec_query_stats之类,等一些统计性的信息,通常不作为实时告警内容,而是在性能优化时,作为参考。
一. 阻塞/等待/长时间运行
1. SQL Server 2005 及以后版本检查
SELECT r.session_id ,r.blocking_session_id ,DB_Name(r.database_id) as database_name ,r.start_time ,r.total_elapsed_time ,r.[status] ,CASE WHEN r.blocking_session_id 0 THEN'Blocking' WHEN r.blocking_session_id= 0 AND r.wait_type is not null THEN 'Waiting' ELSE 'Long-running' END as slowness_type ,r.percent_complete ,r.command ,r.wait_type ,r.wait_time ,r.wait_resource ,r.last_wait_type ,r.cpu_time ,r.reads ,r.writes ,r.logical_reads ,t.[text] as executing_batch ,SUBSTRING(t.[text], r.statement_start_offset/2, (CASE WHENr.statement_end_offset =-1 THENDATALENGTH(t.[text]) --LEN(CONVERT(NVARCHAR(MAX),t.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset )/2+ 1) as executing_sql ,bt.[text] as blocking_batch ,SUBSTRING(bt.[text], br.statement_start_offset/2, (CASE WHENbr.statement_end_offset = -1 THENDATALENGTH(bt.[text]) --LEN(CONVERT(NVARCHAR(MAX),bt.text)) * 2 ELSE br.statement_end_offset END - br.statement_start_offset )/2+ 1) as blocking_sql --,p.query_plan FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) as p LEFT JOIN sys.dm_exec_requests br ON r.blocking_session_id =br.session_id OUTER APPLY sys.dm_exec_sql_text(br.session_id) as bt WHERE r.session_id > 50 and r.session_id @@SPID AND r.total_elapsed_time >30 * 60 * 1000 ORDER BY r.total_elapsed_timeDESC;
以上脚本返回运行超过30分钟的语句,需要注意的是:
(1) 如果返回执行计划,会让以上脚本变慢很多,可以不返回,在收到告警后检查语句时,再去查看执行计划;
(2) 显示TEXT,比如: xp_cmdshell这样的语句,start_offset, end_offset都为0,截取的 text是空白,只有看TEXT才知道是什么语句;还有就是有时需要知道这个请求来自哪个batch或者存储过程;
(3) 有时显示TEXT还不够,还以xp_cmdshell为例,需要dbcc inputbuffer才能看到完整的sql语句;另外已运行结束但还没有commit/rollback的事务,在requests中已经没有了,也需要借用dbcc inputbuffer来查看sql 语句;
dbcc inputbuffer(@@SPID)
(4) SQL Agent作业,在这里会被一并检查,也可以通过msdb..sysjobactivity另行检查;
select b.name, * from msdb..sysjobactivity a inner join msdb.dbo.sysjobs b on a.job_id = b.job_id where b.name like '%backup%'
2. SQL Server 2000沿用过来的方法
select p.dbid, p.spid, p.blocked, p.waittime/1000.0/60.0 as wait_minutes, ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0)elapsed_minutes, p.last_batch, p.status, p.program_name, (select [text] FROM::fn_get_sql(p.sql_handle)) sql_text from master..sysprocesses p where spid > 50 and spid @@SPID AND(status 'sleeping' AND ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) > 30)
以上脚本返回运行超过30分钟的语句,需要注意的是:
sysprocesses中把connection/session/request信息三者合一,其中没有请求开始的具体时间,通过last_batch监视运行时长并不准确。测试如下:
(1) 通过ISQL连接到SQL Server,如果当前连接没发起过任何请求,last_batch的时间为 1900-01-01 00:00:00,在此连接上发起请求时,通过last_batch计算当前请求运行时长不准确;
(2) 在SQL Analyzer/SSMS中新建查询窗口,未发起任何查询时,last_batch与login_time一样,而非1900-01-01 00:00:00,通过last_batch计算当前请求运行时长不准确;或者当前窗口发起的请求已结束,但窗口/连接未关闭,则在此连接上再次发起请求,last_batch为上次请求结束的时间,通过last_batch计算当前请求运行时长也不准确;
(3) SQL Agent作业运行结束后,会把在sysprocesses的连接关闭,下次运行时重新建立连接,新建连接中last_batch等于login_time,通过last_batch计算作业运行时长准确;
作为一个老的方法,估且不再去深究,不过用sysprocesses来监视阻塞/等待还是没有问题的,另外作业的运行时长也是可以监视的,脚本改动后如下:
select p.dbid, p.spid, p.blocked, p.waittime/1000.0/60.0 as wait_minutes, ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0)elapsed_minutes, p.last_batch, p.status, p.program_name, (select [text] FROM::fn_get_sql(p.sql_handle)) sql_text from master..sysprocesses p where spid > 50 and spid @@SPID and( (p.program_name like 'SQLAgent - TSQL JobStep (Job %' AND ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) > 30) or (p.blocked 0 and p.waittime/1000.0/60.0 > 30) )
这样一来,只剩下未被阻塞但长时间运行的sql请求未被监视到。如果一定要全面监视的话,可以选择开启跟踪,进而分析跟踪文件。
二. 死锁
死锁的监控可以通过监视SQL Server的ERRORLOG来实现,不过需要事先打开死锁的跟踪标记。脚本如下:
--sql server 2000 dbcc traceon(1204,-1) --sql server 2005 + dbcc traceon(1222,-1)
这样发生死锁时,死锁详细信息就会被写入ERRORLOG,检查deadlock或者victim关键字即可进行监控。
小结
各个语句的运行时长/基线并不一样,通常不好设置统一的阀值,有时会借用第三方工具针对不同的请求设置不同的时长阀值并告警,所以在数据库这层大多告警阻塞即可,大致步骤如下 :
(1) 部署数据库邮件;
(2) 部署作业:定时检查阻塞,发邮件告警。
已抢65729个
抢已抢31652个
抢已抢17401个
抢已抢8834个
抢已抢11402个
抢已抢23647个
抢