查看每个数据库堵塞资源

USE master 
GO
-- =============================================    
-- Author name: <大唐过客>    
-- Create date: <2019,01,18>    
-- Description: <查看每个数据库堵塞资源>     
-- =============================================    
CREATE PROCEDURE sp_get_lock    
AS    
 
SET NOCOUNT ON     
 
DECLARE @SQL NVARCHAR(MAX)    
 
DECLARE @resource_type VARCHAR(128),    
        @database_id INT, 
		@db_name VARCHAR(128),   
        @resource_description VARCHAR(400),    
        @resource_associated_entity_id BIGINT,    
        @objId BIGINT,    
        @objName NVARCHAR(128),    
        @schemaName NVARCHAR(128),    
        @colName NVARCHAR(128),    
        @objColVal NVARCHAR(200),    
        @cnt INT,
		@index_id INT,
		@index_name sysname     
 
 
DECLARE @row_cnt INT,    
        @idx INT = 1    
 
IF OBJECT_ID('tempdb..#locks', 'U') IS NOT NULL    
    DROP TABLE #locks;    
 
SELECT DISTINCT IDENTITY(BIGINT, 1, 1) AS id,    
       l.resource_type AS blocked_resource_type,    
       l.request_session_id AS blocked_session_id,    
       l.resource_database_id,    
       l.resource_description AS blocked_resource_description,    
       l.resource_associated_entity_id AS blocked_resource_associated_entity_id,    
       l.request_mode AS blocked_request_mode,    
       RTRIM(lp.hostname) AS blocked_hostname,    
       RTRIM(lp.loginame) AS blocked_loginame,    
       RTRIM(lp.[program_name]) AS [blocked_program_name],    
       SUBSTRING(   lt.[text],    
                    CASE    
                        WHEN lp.stmt_start = -1 THEN    
                            1    
                        ELSE    
                            lp.stmt_start / 2    
                    END,    
                    CASE    
                        WHEN lp.stmt_end = -1 THEN    
                            LEN(lt.[text])    
                        ELSE    
                            lp.stmt_end / 2 - lp.stmt_start / 2 + 2    
                    END    
                ) AS blocked_sql_text,    
       r.resource_type,    
       r.request_session_id,    
       r.request_mode,    
       RTRIM(rp.hostname) AS hostname,    
       RTRIM(rp.loginame) AS loginame,    
       RTRIM(rp.[program_name]) AS [program_name],    
       SUBSTRING(   rt.[text],    
                    CASE    
                        WHEN rp.stmt_start = -1 THEN    
                            1  
                        ELSE    
                            rp.stmt_start / 2    
                    END,    
                    CASE    
                        WHEN rp.stmt_end = -1 THEN    
                            LEN(rt.[text])    
                        ELSE    
                            rp.stmt_end / 2 - rp.stmt_start / 2 + 2    
                    END    
                ) AS sql_text,    
    CONVERT(NVARCHAR(800),'') AS lock_resource    
INTO #locks    
FROM sys.dm_tran_locks l WITH (NOLOCK)    
    INNER JOIN sys.dm_tran_locks r WITH (NOLOCK)    
        ON l.resource_type = r.resource_type    
           AND r.request_status = 'GRANT'    
           AND l.resource_database_id = r.resource_database_id    
           AND l.resource_associated_entity_id = r.resource_associated_entity_id    
    INNER JOIN sys.sysprocesses lp WITH (NOLOCK)    
        ON l.request_session_id = lp.spid    
    INNER JOIN sys.sysprocesses rp WITH (NOLOCK)    
        ON r.request_session_id = rp.spid    
    CROSS APPLY sys.dm_exec_sql_text(lp.sql_handle) lt    
    CROSS APPLY sys.dm_exec_sql_text(rp.sql_handle) rt    
WHERE l.resource_type IN ( 'KEY', 'OBJECT', 'PAGE', 'RID' )    
      AND l.request_status = 'WAIT' 
	  AND l.resource_database_id=r.resource_database_id   
 
SET @row_cnt = @@ROWCOUNT    
 
 
IF @row_cnt = 0    
 BEGIN     
   SELECT '没有发现堵塞信息...' AS msg    
   RETURN    
 END     
 
-- 循环解析每一条死锁记录,解析死锁的行Id 
 
WHILE @idx <= @row_cnt    
BEGIN    
    SELECT @resource_type = RTRIM(blocked_resource_type),    
           @database_id = resource_database_id,    
           @resource_description = RTRIM(blocked_resource_description),    
           @resource_associated_entity_id = blocked_resource_associated_entity_id    
    FROM #locks    
    WHERE id = @idx
 
    SELECT @db_name = name
	FROM sys.databases WITH (NOLOCK)
	WHERE database_id = @database_id
 
    /**--行锁**/    
    IF @resource_type IN ( 'KEY', 'RID' )    
    BEGIN    
 
  /*      
  如果 type = 1 或 3，则 container_id = sys.partitions.hobt_id。     
  如果 type 为 2，则 container_id = sys.partitions.partition_id。    
  */    
 
  SET @SQL=N'SELECT @p_objId = p.[object_id] ,    
                    @p_objName = obj.name ,
					@p_index_id=p.index_id    
  FROM '+@db_name+'.sys.partitions p WITH (NOLOCK)    
   INNER JOIN '+@db_name+'.sys.allocation_units au WITH (NOLOCK)    
    ON (    
      p.partition_id = au.container_id    
      AND au.type = 2    
     )    
     OR    
     (    
      p.hobt_id = au.container_id    
      AND au.type IN ( 1, 3 )    
     )    
  INNER JOIN '+@db_name+'.sys.objects  obj WITH (NOLOCK)  ON p.[object_id]=obj.[object_id]
  WHERE @p_resource_associated_entity_id IN ( p.hobt_id, p.[partition_id], au.allocation_unit_id );    
    '


   EXEC sp_executesql  @SQL,N'@p_objId INT OUTPUT,
	            @p_objName NVARCHAR(128) OUTPUT,
				@p_index_id INT OUTPUT,
				@p_resource_associated_entity_id BIGINT',
				@p_objId=@objId OUTPUT ,
				@p_objName=@objName OUTPUT ,
				@p_index_id=@index_id OUTPUT,
				@p_resource_associated_entity_id=@resource_associated_entity_id
 
     IF @objId IS NULL    
        BEGIN    
            PRINT ('第' + QUOTENAME(LTRIM(@idx)) + '条记录没有找到对象ID,associated_entity_id='    
                   + LTRIM(@resource_associated_entity_id)    
                  )    
            SET @idx = @idx + 1;    
            CONTINUE    
        END    
 
	-- 查询死锁行对应的索引.
	IF @index_id > 0 
	 BEGIN 
		SET @SQL=N'SELECT @p_index_name=name
		FROM '+QUOTENAME(@db_name)+'.sys.indexes
		WHERE object_id = @p_objId
			  AND index_id = @p_index_id '


        EXEC sp_executesql @SQL,N'@p_objId INT ,@p_index_id INT,@p_index_name sysname OUTPUT',
		                          @p_objId=@objId,
								  @p_index_id=@index_id,
								  @p_index_name=@index_name OUTPUT 


	 END 


	SET @SQL=N'SELECT @p_schemaName = SCHEMA_NAME([schema_id])    
    FROM '+@db_name+'.sys.objects WITH (NOLOCK)    
    WHERE [object_id] = @p_objId    
    '


	EXEC sp_executesql @SQL,N'@p_schemaName varchar(128) OUTPUT,
	                          @p_objId int',
							  @p_schemaName=@schemaName OUTPUT ,
							  @p_objId=@objId
 


  /*--构建动态SQL找到在表中的值,按照 单主键、单唯一索引、第一列 依次查找*/    
 
  /*--单主键*/    
  SET @SQL=N'SELECT @p_colName = cc.name
	FROM '+@db_name+'.sys.key_constraints c WITH (NOLOCK)
		INNER JOIN '+@db_name+'.sys.index_columns ic WITH (NOLOCK)
			ON c.[type] = ''PK''
				AND c.parent_object_id = @p_objId
				AND ic.[object_id] = @p_objId
				AND c.unique_index_id = ic.index_id
		INNER JOIN '+@db_name+'.sys.columns cc WITH (NOLOCK)
			ON ic.object_id = cc.object_id
				AND ic.column_id = cc.column_id
    '
 
  EXEC sp_executesql @SQL,N'@p_colName varchar(128) OUTPUT,
                            @p_objId INT',
							@p_colName = @colName OUTPUT ,
							@p_objId=@objId
 
  IF @@ROWCOUNT != 1     
   SET @colName=NULL ;     
 
  /*--单唯一*/    
  IF @colName IS NULL    
  BEGIN    
 
   SET @SQL=N'SELECT @p_colName = cc.name
	FROM '+@db_name+'.sys.key_constraints c WITH (NOLOCK)
		INNER JOIN '+@db_name+'.sys.index_columns ic WITH (NOLOCK)
			ON c.[type] = ''UQ''
				AND c.parent_object_id = @p_objId
				AND ic.[object_id] = @p_objId
				AND c.unique_index_id = ic.index_id
		INNER JOIN '+@db_name+'.sys.columns cc WITH (NOLOCK)
			ON ic.object_id = cc.object_id
				AND ic.column_id = cc.column_id
    '
 
  EXEC sp_executesql @SQL,N'@p_colName varchar(128) OUTPUT,
                            @p_objId INT',
							@p_colName = @colName OUTPUT ,
							@p_objId=@objId
 
   IF @@ROWCOUNT != 1    
    SET @colName = NULL;    
 
  END    
 
     /*--最后默认第一列*/    
  IF @colName IS NULL     
       BEGIN 


	    SET @SQL=N' SELECT @p_colName=name 
		          FROM '+QUOTENAME(@db_name)+'.sys.columns 
				  WHERE object_id=@p_objId AND column_id=1   
        ' 
 
		EXEC sp_executesql @SQL,N'@p_colName varchar(128) OUTPUT,
								@p_objId INT',
								@p_colName = @colName OUTPUT ,
								@p_objId=@objId
 
	   END 
 
 
  IF @objId > 100      
  BEGIN     
 
	IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL
		DROP TABLE #tmp;


	CREATE TABLE #tmp
	(
		id SQL_VARIANT  
	)
 
	SET @SQL=N' use ?
		  IF db_name()='''+@db_name+''' 
		   BEGIN 
 
			 INSERT INTO #tmp (id)
			 SELECT '+@colName+'  FROM 
			 (
				 SELECT  ' + @colName + N' ,   
				          %%lockres%% as keyhashvalue 
				 FROM '+QUOTENAME(@db_name)+'.'+ QUOTENAME(@schemaName) + N'.' + QUOTENAME(@objName) 
				  + '  WITH(nolock'+CASE WHEN @index_id>0 THEN ',INDEX='+@index_name+')' 
				                         ELSE ')' END+N'
 
	         ) tmp 
			 WHERE keyhashvalue=''' + @resource_description + '''   


		   END '
 
 
	EXEC sp_msforeachdb @SQL 
 
	SELECT @objColVal=CONVERT(NVARCHAR(400),id,120) FROM #tmp ;
 


  END     
 
        IF @objColVal IS NOT NULL  
        BEGIN    
			UPDATE #locks
			SET lock_resource = N'SELECT * FROM ' + @objName + ' WITH(NOLOCK' + CASE
																					WHEN @index_id > 0 THEN
																						',INDEX=' + @index_name + ')'
																					ELSE
																						')'
																				END + ' WHERE ' + @colName + '=' + @objColVal
			WHERE id = @idx;
        END    
		ELSE   
        BEGIN    
            UPDATE #locks    
            SET lock_resource = @objName      
            WHERE id = @idx;    
        END    
 
    END    
 
    /**--页锁**/    
    ELSE IF @resource_type = 'PAGE'    
    BEGIN    
 
        UPDATE #locks    
        SET lock_resource = '页:' + blocked_resource_description    
        WHERE id = @idx;    
 
    END    
 
    /**--表锁**/    
    ELSE IF @resource_type = 'OBJECT'    
    BEGIN    
 
  SET  @objId=@resource_associated_entity_id
 
  SET @SQL=N'SELECT @p_objName = [name],    
    @p_schemaName=SCHEMA_NAME(p.schema_id)    
  FROM '+@db_name+'.sys.objects p WITH (NOLOCK)      
  WHERE p.[object_id] = @p_objId  
 '
 
 PRINT @SQL
 EXEC sp_executesql @SQL,N'@p_objName varchar(128) OUTPUT,
                            @p_schemaName varchar(128) OUTPUT,
							@p_objId   int',
							@p_objName=@objName OUTPUT,
							@p_schemaName=@schemaName OUTPUT,
							@p_objId=@objId
 


    UPDATE #locks    
    SET lock_resource = '表:' + @schemaName+'.'+@objName    
    WHERE id = @idx;    
 
    END    


    SET @idx = @idx + 1;    
 
END    
 
 
SELECT id AS '序号',     
       DB_NAME(resource_database_id) AS '数据库名称' ,
       blocked_session_id AS '等待的会话ID',  
       request_session_id AS '持有者的会话ID',       
       lock_resource AS '被锁住的资源',    
       blocked_loginame  AS '等待的登录名',    
       loginame AS '持有者的登录名',     
       blocked_hostname  AS '等待的主机名',    
       hostname  AS '持有者的登录名',    
       blocked_program_name AS  '等待的程序名',    
       [program_name] AS  '持有者的程序名',    
       blocked_request_mode AS   '等待的请求锁模式',    
       request_mode AS   '持有者的持有锁模式',    
       blocked_sql_text AS '等待的最后执行脚本',    
       sql_text AS '持有者的最后执行脚本'     
 
FROM #locks    
 
 
RETURN