監控SQL Server的運行狀況(3)

阻塞

運行下面的查詢可確定阻塞的會話。

select blocking_session_id, wait_duration_ms, session_id from  
sys.dm_os_waiting_tasks 
where blocking_session_id is not null 

使用此調用可找出 blocking_session_id 所返回的 SQL。例如，如果 blocking_session_id 是 87，則運行此查詢可獲得相應的 SQL。

dbcc INPUTBUFFER(87) 

下面的查詢顯示 SQL 等待分析和前 10 個等待的資源。

select top 10 * 
from sys.dm_os_wait_stats 
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR') 
order by wait_time_ms desc 

若要找出哪個 spid 正在阻塞另一個 spid，可在數據庫中創建以下存儲過程，然後執行該存儲過程。此存儲過程會報告此阻塞情況。鍵入 sp_who 可找出 @spid；@spid 是可選參數。

create proc dbo.sp_block (@spid bigint=NULL) 
as 
select  
    t1.resource_type, 
    'database'=db_name(resource_database_id), 
    'blk object' = t1.resource_associated_entity_id, 
    t1.request_mode, 
    t1.request_session_id, 
    t2.blocking_session_id     
from  
    sys.dm_tran_locks as t1,  
    sys.dm_os_waiting_tasks as t2 
where  
    t1.lock_owner_address = t2.resource_address and 
    t1.request_session_id = isnull(@spid,t1.request_session_id) 

以下是使用此存儲過程的示例。

exec sp_block 
exec sp_block @spid = 7