SQL Server 2005/2008 锁和阻塞的监控

来源：互联网发布：跑鞋矩阵2017 编辑：程序博客网时间：2024/05/18 13:45

第一部分摘自：http://www.cnblogs.com/songsu/articles/1199723.html

对于块和锁，要监视其中的锁定对整体系统性能的影响，可在每次经过给定时间间隔（可能是一小时）后，轮询状态数据并捕捉被持有锁上的运行统计信息。要捕捉的关键信息包括：

受影响对象、粒度和锁类型
锁和块的持续时间
发出的 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)asselect     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 t2where     t1.lock_owner_address = t2.resource_address and    t1.request_session_id = isnull(@spid,t1.request_session_id)

以下是使用此存储过程的示例：

exec sp_blockexec sp_block @spid = 7