很多時候，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，執行下面語句
如下圖所，我們可以看到阻塞其它會話以及被阻塞會話的資訊，如下所示
現在上面sql已經基本實作了檢視阻塞具體資訊的功能，但是現在又有幾個問題：
1：上面sql腳本隻适合已經出現阻塞情況下檢視阻塞資訊，如果沒有出現阻塞情況，我總不能傻傻的一直在哪裡點選執行吧，因為阻塞這種情況有可能在那段時間都不會出現，隻會在特定的時間段出現。
2：我想了解一段時間内資料庫出現的阻塞情況，那麼需要将阻塞資訊保留下來。
3：有時候忙不過來，我想将這些具體阻塞資訊發送給相關開發人員，讓他們了解具體情況。
于是我想通過一個存儲過程來實作這方面功能，通過設定參數@outtype，預設為輸出阻塞會話資訊，當參數為"table" 時，将阻塞資訊寫入資料庫表，如果參數為 "email"表示将阻塞資訊通過郵件發送開發人員。
正好這段時間，我在yoursqldba上擴充一些功能，于是我将這個存儲過程放置在yousqldba資料庫中。
use [yoursqldba]
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
存儲過程如下所示：
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]
set ansi_nulls on
set quoted_identifier on
--==================================================================================================================
-- 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
set nocount on;
declare @htmlcontent nvarchar(max) ;
if @outtype not in ('default', 'table','email')
print 'the parameter @outtype is not correct,please check it';
return;
if @outtype ='default'
select db.name as databasename
,wt.blocking_session_id as blockingsessesionid
,coalesce(sp.loginame, sp.nt_username) as username
cross apply sys.dm_exec_sql_text(ec2.most_recent_sql_handle) as h2;
else if @outtype='table'
insert into [maint].[blockingsqlhistory]
select getdate() as recordtime
else if @outtype='email'
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, ''
for xml path('tr'), type
) as nvarchar(max) ) +
n'</table>'
if @htmlcontent is not null
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename ,
@recipients = @recipientslst ,
@subject = @emailsubject ,
@body = @htmlcontent ,
@body_format = 'html' ;
最後在資料庫建立一個作業，調用該存儲過程，然後在某段時間啟用作業監控資料庫的阻塞情況，作業的執行頻率是個比較難以定奪的頭痛問題，具體要根據系統情況來決定，我習慣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;
english »
afrikaansalbanianarabicarmenianazerbaijanibasquebengalibelarusianbulgariancatalanchinese (simp)chinese (trad)croatianczechdanishdutchenglishesperantoestonianfilipinofinnishfrenchgaliciangeorgiangermangreekgujaratihaitian creolehebrewhindihungarianicelandicindonesianirishitalianjapanesekannadakoreanlaolatinlatvianlithuanianmacedonianmalaymaltesenorwegianpersianpolishportugueseromanianrussianserbianslovakslovenianspanishswahiliswedishtamilteluguthaiturkishukrainianurduvietnamesewelshyiddish
<a target="_blank"></a>
text-to-speech function is limited to 100 characters
最新内容請見作者的github頁：http://qaseven.github.io/