5. SQL Server資料庫效能監控 - 當前請求

標籤：des   style   blog   http   color   io   os   ar   strong   

原文:5. SQL Server資料庫效能監控 - 當前請求

對於線上啟動並執行系統，當前資料庫效能監控，通常監視以下幾點：

(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) 部署作業：定時檢查阻塞，發郵件警示。

 

5. SQL Server資料庫效能監控 - 當前請求