- 受影响对象、粒度和锁类型
- 锁和块的持续时间
- 发出的 SQL 命令(存储过程名称、SQL 语句 within)
- 有关阻塞链的信息(如果相关的话)
- 系统消耗其可用锁定能力的方式
运行类似于以下所示的脚本来捕捉阻塞的信息
SELECT er.wait_time AS WaitMSQty
, er.session_id AS CallingSpId
, LEFT(nt_user_name, 30) AS CallingUserName
, LEFT(ces.program_name, 40) AS CallingProgramName
, er.blocking_session_id AS BlockingSpId
, DB_NAME(er.database_id) AS DbName
, CAST(csql.text AS varchar(255)) AS CallingSQL
, clck.CallingResourceId
, clck.CallingResourceType
, clck.CallingRequestMode
, CAST(bsql.text AS varchar(255)) AS BlockingSQL
, blck.BlockingResourceType
, blck.BlockingRequestMode
FROM master.sys.dm_exec_requests er WITH (NOLOCK)
JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
ON er.session_id = ces.session_id
CROSS APPLY fn_get_sql (er.sql_handle) csql
JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
SELECT cl.request_session_id AS CallingSpId
, MIN(cl.resource_associated_entity_id) AS CallingResourceId
, MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType
, MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode
-- (i.e. schema, update, etc.)
FROM master.sys.dm_tran_locks cl WITH (nolock)
WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting
GROUP BY cl.request_session_id
) AS clck
ON er.session_id = clck.CallingSpid
JOIN (
-- Retrieve lock information for blocking process
-- Only one record will be returned (one possibility, for instance,
-- is for multiple row locks to occur)
SELECT bl.request_session_id AS BlockingSpId
, bl.resource_associated_entity_id AS BlockingResourceId
, MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType
, MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode
FROM master.sys.dm_tran_locks bl WITH (nolock)
GROUP BY bl.request_session_id
, bl.resource_associated_entity_id
) AS blck
ON er.blocking_session_id = blck.BlockingSpId
AND clck.CallingResourceId = blck.BlockingResourceId
JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
ON er.blocking_session_id = ber.session_id
CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE ces.is_user_process = 1
AND er.wait_time > 0
要进一步确定被阻塞数据的 ObjectName,您可以运行类似于下面 所示的脚本:
DECLARE @SQL nvarchar(max)
, @CallingResourceType varchar(30)
, @Objectname sysname
, @DBName sysname
, @resource_associated_entity_id int
-- TODO: Set the variables for the object you wish to look up
SET @SQL = N'
USE ' + @DbName + N'
DECLARE @ObjectId int
SELECT @ObjectId = CASE
WHEN @CallingResourceType = ''OBJECT''
THEN @resource_associated_entity_id
WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = @resource_associated_entity_id)
WHEN @CallingResourceType = ''ALLOCATION_UNIT''
THEN (SELECT CASE
WHEN type IN (1, 3)
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = allocation_unit_id)
WHEN type = 2
THEN (SELECT object_id
FROM sys.partitions
WHERE partition_id = allocation_unit_id)
ELSE NULL
END
FROM sys.allocation_units
WHERE allocation_unit_id = @resource_associated_entity_id)
ELSE NULL
END
SELECT @ObjectName = OBJECT_NAME(@ObjectId)'
EXEC dbo.sp_executeSQL
@SQL
, N'@CallingResourceType varchar(30)
, @resource_associated_entity_id int
, @ObjectName sysname OUTPUT'
, @resource_associated_entity_id = @resource_associated_entity_id
, @CallingResourceType = @CallingResourceType
, @ObjectName = @ObjectName OUTPUT
下面提供一种简单监控锁的方法:
-- 查询当前数据库的锁, 并可以对应的object_name: 表名, 源自《Inside SQL Server 2005: storage engine》
CREATE VIEW DBlocks AS
SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = 'OBJECT' THEN object_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id)
END as entity_name, index_id,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
ON p.hobt_id = t.resource_associated_entity_id
WHERE resource_database_id = db_id();
-- 可以改造成存储过程
create procedure p_DBlocks(@dbname sysname)
AS
declare @str_sql varchar(4000);
set @str_sql='
SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = ''OBJECT'' THEN object_name(resource_associated_entity_id,'+convert(varchar,db_id(@dbname))+ ')
WHEN resource_associated_entity_id = 0 THEN ''n/a''
ELSE object_name(p.object_id,'+convert(varchar,db_id(@dbname))+ ')
END as entity_name, index_id,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN '+@dbname+'.sys.partitions p
ON p.hobt_id = t.resource_associated_entity_id
WHERE resource_database_id = ' + convert(varchar,db_id(@dbname));
execute (@str_sql);
go
-- SQL Server等待情况
select * from sys.dm_os_waiting_tasks ; --当前等待事件
select * from sys.dm_os_wait_stats --历史等待次数,是sqlserver启动后的累计值,需使用下一条语句清空
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
找出进程阻塞:
运行下面的查询可确定阻塞的会话
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