本文提供了一種用於查詢SQL Server中發生阻塞問題的腳本，分別適用於SQL Server 2000和2005版本。該腳本能夠幫助定位導致數據庫性能瓶頸的具體進程及SQL語句，從而有效解決數據庫中的阻塞情況。
--查阻塞
/******************************************************************************************************************************************************
整理人：中国风(Roy)
日期:
******************************************************************************************************************************************************/
--SQL2000
declare Roy_lock cursor for
select
db_name(dbid),0,blocked
from
master..sysprocesses a
where
Blocked>0 and
not exists(select 1 from Master..Sysprocesses where blocked=a.spid)
union
select
db_name(dbid),spid,blocked
from
master..sysprocesses a
where
Blocked>0
declare @DBName sysname,@spid bigint,@Blocked bigint
open Roy_lock
fetch next from Roy_lock into @DBName,@spid,@Blocked
while @@fetch_status=0
begin
if @spid=0
print N'鎖定數據庫:'+@DBName+' 語句：'
else
print N'鎖定數據庫:'+@DBName+' 進程SPID：'+rtrim(@spid)+' 語句：'
dbcc inputbuffer(@Blocked)
fetch next from Roy_lock into @DBName,@spid,@Blocked
end
close Roy_lock
deallocate Roy_lock
GO
SQL2005:
with Lock(dbName,spid,blocked,sql_handle)
as
(
select
db_name(dbid),0,blocked,sql_handle
from
master..sysprocesses a
where
Blocked>0 and
not exists(select 1 from Master..Sysprocesses where blocked=a.spid)
union
select
db_name(dbid),spid,blocked,sql_handle
from
master..sysprocesses a
where
Blocked>0
)
select * from Lock a cross apply sys.dm_exec_sql_text(a.sql_handle)b
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
