dbcc INPUTBUFFER(87)
dbcc INPUTBUFFER(87)
下面的查詢顯示 SQL 等待分析和前 10 個等待的資源。
view plaincopy to clipboardprint?
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
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 是可選參數。
view plaincopy to clipboardprint?
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)
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)
以下是使用此存儲過程的示例。
view plaincopy to clipboardprint?
exec sp_block
exec sp_block @spid = 7
文章來源于領測軟件測試網 http://www.kjueaiud.com/
