對于在線運行的系統，當前數據庫性能監控，通常監視以下幾點：

(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 WHEN r.statement_end_offset = -1                        THEN DATALENGTH (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 WHEN br.statement_end_offset = -1                        THEN DATALENGTH (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_time DESC;

以上腳本返回運行超過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 2000dbcc traceon(1204,-1)--sql server 2005 +dbcc traceon(1222,-1)

這樣發生死鎖時，死鎖詳細信息就會被寫入ERRORLOG，檢查deadlock或者victim關鍵字即可進行監控。

小結

各個語句的運行時長/基線并不一樣，通常不好設置統一的閥值，有時會借用第三方工具針對不同的請求設置不同的時長閥值并告警，所以在數據庫這層大多告警阻塞即可，大致步驟如下 ：

(1) 部署數據庫郵件；

(2) 部署作業：定時檢查阻塞，發郵件告警。