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

青旅半醒 2022-07-12 13:51 235阅读 0赞

第一部分摘自： [http://www.cnblogs.com/songsu/articles/1199723.html][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)
    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

[http_www.cnblogs.com_songsu_articles_1199723.html]: http://www.cnblogs.com/songsu/articles/1199723.html