SQL Server 監控統計阻塞指令碼資訊
原文：點選開啟連結
資料庫產生阻塞（Blocking）的本質原因 ：SQL語句連續持有鎖的時間過長 ，數目過多， 粒度過大。阻塞是事務隔離帶來的副作用，它是不可避免的，而且是一個資料庫系統常見的現象。 但是阻塞的時間和出現頻率要控制在一定的範圍內，阻塞持續的時間過長或阻塞出現過多（過於頻繁），就會對資料庫效能產生嚴重的影響。
很多時候，DBA需要知道資料庫在出現效能問題時，有沒有發生阻塞？ 什麼時候開始的？發生在那個資料庫上？ 阻塞發生在那些SQL語句之間？ 阻塞的時間有多長？ 阻塞發生的頻率？ 阻塞有關的連線是從那些客戶端應用傳送來的？.......
如果我們能夠知道這些具體資訊，我們就能迅速定位問題，分析阻塞產生的原因， 從而找出出現效能問題的根本原因，並根據具體原因給出相應的解決方案（索引調整、優化SQL語句等）。
檢視阻塞的方法比較多， 我在這篇部落格MS SQL 日常維護管理常用指令碼(二)裡面提到檢視阻塞的一些方法：
方法1：檢視那個引起阻塞，檢視blk不為0的記錄，如果存在阻塞程式，則是該阻塞程式的會話 ID。否則該列為零。
EXEC sp_who active
方法2：檢視那個引起阻塞，檢視欄位BlkBy，這個能夠得到比sp_who更多的資訊。
EXEC sp_who2 active
方法3：sp_lock 系統儲存過程，報告有關鎖的資訊，但是不方便定位問題
方法4：sp_who_lock儲存過程
方法5：右鍵伺服器-選擇“活動和監視器”，檢視程式選項。注意“任務狀態”欄位。
方法6：右鍵服務名稱-選擇報表-標準報表-活動-所有正在阻塞的事務。
但是上面方法，例如像sp_who、 sp_who2，sp_who_lock等，都有或多或少的缺點：例如不能檢視阻塞和被阻塞的SQL語句。不能從檢視一段時間內阻塞發生的情況等；沒有顯示阻塞的時間....... 我們要實現下面功能：
1: 檢視那個會話阻塞了那個會話
2：阻塞會話和被阻塞會話正在執行的SQL語句
3：被阻塞了多長時間
4：像客戶端IP、Proagram_Name之類資訊
5：阻塞發生的時間點
6：阻塞發生的頻率
7：如果需要，應該通知相關開發人員，DBA不能啥事情都包攬是吧，那不還得累死，總得讓開發人員員參與進來優化（有些問題就該他們解決），多瞭解一些系統執行的具體情況，有利於他們認識問題、解決問題。
8：需要的時候開啟這項功能，不需要關閉這項功能
於是為了滿足上述功能，有了下面SQL 語句
SELECT wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName
,ec1.client_net_address AS ClientIpAddress
,db.name AS DatabaseName
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
我們做一個測試例子來驗證一下
1：開啟第一會話視窗1，執行下面語句
USE DBMonitor;
GO
BEGIN TRANSACTION
SELECT * FROM dbo.TEST(TABLOCKX);
--COMMIT TRANSACTION;
2：開啟第二個會話視窗2，執行下面語句
USE DBMonitor;
GO
SELECT * FROM dbo.TEST
3：開啟第三個會話視窗3，執行下面語句
SELECT wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName
,ec1.client_net_address AS ClientIpAddress
,db.name AS DatabaseName
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
如下圖所，我們可以看到阻塞其它會話以及被阻塞會話的資訊，如下所示
現在上面SQL已經基本實現了檢視阻塞具體資訊的功能，但是現在又有幾個問題：
1：上面SQL指令碼只適合已經出現阻塞情況下檢視阻塞資訊，如果沒有出現阻塞情況，我總不能傻傻的一直在哪裡點選執行吧，因為阻塞這種情況有可能在那段時間都不會出現，只會在特定的時間段出現。
2：我想了解一段時間內資料庫出現的阻塞情況，那麼需要將阻塞資訊保留下來。
3：有時候忙不過來，我想將這些具體阻塞資訊傳送給相關開發人員，讓他們瞭解具體情況。
於是我想通過一個儲存過程來實現這方面功能，通過設定引數@OutType，預設為輸出阻塞會話資訊，當引數為"Table" 時，將阻塞資訊寫入資料庫表，如果引數為 "Email"表示將阻塞資訊通過郵件傳送開發人員。
正好這段時間，我在YourSQLDba上擴充套件一些功能，於是我將這個儲存過程放置在YouSQLDba資料庫中。
USE [YourSQLDba]
GO
IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Maint].[BlockingSQLHistory]') AND type='U')
BEGIN
CREATE TABLE Maint.BlockingSQLHistory
(
RecordTime DATETIME ,
DatabaseName SYSNAME ,
BlockingSessesionId SMALLINT ,
ProgramName NCHAR(128) ,
UserName NCHAR(256) ,
ClientIpAddress VARCHAR(48) ,
WaitType NCHAR(60) ,
BlockingStartTime DATETIME ,
WaitDuration BIGINT ,
BlockedSessionId INT ,
BlockedSQLText NVARCHAR(MAX) ,
BlockingSQLText NVARCHAR(MAX) ,
CONSTRAINT PK_BlockingSQLHistory PRIMARY KEY(RecordTime)
)
END
GO
儲存過程如下所示：
USE [YourSQLDba]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Maint].[sp_who_blocking]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Maint].[sp_who_blocking]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--==================================================================================================================
-- ProcedureName : [Maint].[sp_who_blocking]
-- Author : Kerry http://www.cnblogs.com/kerrycode/
-- CreateDate : 2014-04-23
-- Description : 監控資料庫阻塞情況，顯示阻塞會話資訊或收集阻塞會話資訊或傳送告警郵件
/******************************************************************************************************************
Parameters ： 引數說明
********************************************************************************************************************
@OutType : 預設為輸出阻塞會話資訊，"Table", "Email"分別表示將阻塞資訊寫入表或郵件傳送
@EmailSubject : 郵件主題.預設為Sql Blocking Alert，一般指定，例如“ServerName Sql Blocking Alert"
@ProfileName : @profile_name 預設值為YourSQLDba_EmailProfile
@RecipientsLst : 收件人列表
********************************************************************************************************************
Modified Date Modified User Version Modified Reason
********************************************************************************************************************
2014-04-23 Kerry V01.00.00 新建儲存過程[Maint].[sp_who_blocking]
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [Maint].[sp_who_blocking]
(
@OutType
VARCHAR(8) ='Default' ,
@EmailSubject
VARCHAR(120)='Sql Blocking Alert' ,
@ProfileName
sysname='YourSQLDba_EmailProfile' ,
@RecipientsLst
VARCHAR(MAX) = NULL
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @HtmlContent NVARCHAR(MAX) ;
IF @OutType NOT IN ('Default', 'Table','Email')
BEGIN
PRINT 'The parameter @OutType is not correct,please check it';
return;
END
IF @OutType ='Default'
BEGIN
SELECT db.name AS DatabaseName
,wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS UserName
,ec1.client_net_address AS ClientIpAddress
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
END
ELSE IF @OutType='Table'
BEGIN
INSERT INTO [Maint].[BlockingSQLHistory]
SELECT GETDATE() AS RecordTime
,db.name AS DatabaseName
,wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS UserName
,ec1.client_net_address AS ClientIpAddress
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
END
ELSE IF @OutType='Email'
BEGIN
SET @HtmlContent =
N'<head>'
+ N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>'
+ N'<table border="1">'
+ N'<tr>
<th>DatabaseName</th>
<th>BlockingSessesionId</th>
<th>ProgramName</th>
<th>UserName</th>
<th>ClientIpAddress</th>
<th>WaitType</th>
<th>BlockingStartTime</th>
<th>WaitDuration</th>
<th>BlockedSessionId</th>
<th>BlockedSQLText</th>
<th>BlockingSQLText</th>
</tr>' +
CAST (
(SELECT db.name AS TD, ''
,wt.blocking_session_id AS TD, ''
,sp.program_name AS TD, ''
,COALESCE(sp.LOGINAME, sp.nt_username) AS TD, ''
,ec1.client_net_address AS TD, ''
,wt.wait_type AS TD, ''
,ec1.connect_time AS TD, ''
,wt.WAIT_DURATION_MS/1000 AS TD, ''
,ec1.session_id AS TD, ''
,h1.TEXT AS TD, ''
,h2.TEXT AS TD, ''
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
IF @HtmlContent IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName ,
@recipients = @RecipientsLst ,
@subject = @EmailSubject ,
@body = @HtmlContent ,
@body_format = 'HTML' ;
END
END
END
GO
最後在資料庫新建一個作業，呼叫該儲存過程，然後在某段時間啟用作業監控資料庫的阻塞情況，作業的執行頻率是個比較難以定奪的頭痛問題，具體要根據系統情況來決定，我習慣2分鐘執行一次。
最後，這個指令碼還有一個問題，如果阻塞或被阻塞的SQL語句是某個儲存過程裡面的一段指令碼，顯示的SQL是整個儲存過程，而不是正在執行的SQL語句，目前還沒有想到好的方法解決這個問題。我目前手工去檢視阻塞情況，如果非要檢視儲存過程裡面被阻塞的正在執行的SQL，一般結合下面SQL語句檢視（輸入阻塞或被阻塞會話ID替代@sessionid）
SELECT [Spid] = er.session_id
,[ecid]
,[Database] = DB_NAME(sp.dbid)
,[Start_Time]
,[SessionRunTime] = datediff(SECOND, start_time,getdate())
,[SqlRunTime]= RIGHT(convert(varchar,
dateadd(ms, datediff(ms, sp.last_batch, getdate()), '1900-01-01'),
121), 12)
,[HostName]
,[Users]=COALESCE(sp.LOGINAME, sp.nt_username)
,[Status] = er.status
,[WaitType] = er.wait_type
,[Waitime] = er.wait_time/1000
,[Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
( CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
* 2
ELSE er.statement_end_offset
END - er.statement_start_offset ) / 2)
,[Parent Query] = qt.text
,[PROGRAM_NAME] = program_name
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_Id = @sessionid;
相關文章
- 【SQL監控】SQL完全監控的指令碼SQL指令碼
- 監視 SQL Server 2000 阻塞SQLServer
- Oracle慢SQL監控指令碼實現OracleSQL指令碼
- 資料庫效能SQL監控指令碼資料庫SQL指令碼
- oracle 11g監控SQL指令碼OracleSQL指令碼
- SQL Server需要監控哪些計數器SQLServer
- SQL Server自動化運維繫列：監控效能指標指令碼SQLServer運維指標指令碼
- 監控指令碼指令碼
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- 監控系統告警指令碼集合指令碼
- SQL SERVER 2008 R2 監控指令碼自動化部署SQLServer指令碼
- mysql監控指令碼MySql指令碼
- DBA監控指令碼指令碼
- session指令碼監控Session指令碼
- 埠監控指令碼指令碼
- oracle 監控指令碼Oracle指令碼
- Solaris linux 系統監控指令碼Linux指令碼
- listener監聽監控指令碼指令碼
- 監控使用高cpu的sql語句指令碼SQL指令碼
- SQL Server資料庫監控SQLServer資料庫
- 監控寶SQL Server效能監控的功能和配置SQLServer
- 收集統計資訊的SQL指令碼（sosi.sql）--崔華大師SQL指令碼
- AIX系統資源監控SHELL指令碼AI指令碼
- 監控系統使用情況shell指令碼指令碼
- SQL Server停止指令碼SQLServer指令碼
- ogg監控指令碼指令碼
- stap監控IO指令碼指令碼
- 【shell】磁碟監控指令碼指令碼
- 通過shell指令碼監控sql執行頻率指令碼SQL
- 透過shell指令碼監控sql執行頻率指令碼SQL
- mysql 的一個監控指令碼，監控heartbeatMySql指令碼
- 監控Oracle系統中鎖的常用指令碼Oracle指令碼
- mysql mon 的一個監控指令碼，監控heartbeatMySql指令碼
- PostgreSQL之鎖監控指令碼SQL指令碼
- Oracle DBA常用監控指令碼Oracle指令碼
- memcached程式埠監控指令碼指令碼
- Nagios 監控ESXI指令碼iOS指令碼
- 監控cpu與memory指令碼指令碼