MSSQL 블러킹 및 리스트, BUFFER 데이터 보기 (DBA 관리용 시스템 프로시저)

다음은 MSSQL DBA용 프로시저로 블러킹 및 블러킹 리스트, BUFFER 내 데이터를 볼 수 있는 프로시저이다.

/************************************************************************************************

-- sp_block0

-- 블러킹 대상과 블러킹된 대상리스트, BUFFER내의 데이터를 보여준다.

************************************************************************************************/

USE MASTER

GO

IF OBJECT_ID('sp_block0') IS NOT NULL

   DROP PROCEDURE sp_block0

GO

CREATE PROC sp_block0    

AS

  SET NOCOUNT ON  

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  -- 처음 결과 시작

  DECLARE @probclients TABLE(spid SMALLINT

                            , ecid SMALLINT

                            , blocked SMALLINT

                            , waittype BINARY(2)

                            , dbid SMALLINT

                            , ignore_app TINYINT

                            , PRIMARY KEY (blocked, spid, ecid)

                            )

  

  INSERT @probclients 

  SELECT spid, ecid, blocked, waittype, dbid

  ,      CASE WHEN CONVERT(VARCHAR(128),hostname) = 'PSSDIAG' THEN 1 

              ELSE 0 

         END

  FROM sysprocesses 

  WHERE blocked!=0 

    OR waittype != 0x0000

  

  INSERT @probclients 

  SELECT DISTINCT blocked, 0, 0, 0x0000, 0, 0 

  FROM @probclients

  WHERE blocked NOT IN (SELECT spid 

                        FROM @probclients

                       ) 

  and blocked != 0

  

  SELECT spid, status, blocked, open_tran, waitresource, waittype

       , waittime, cmd, lastwaittype, cpu, physical_io

       , memusage, last_batch=CONVERT(VARCHAR(26), last_batch,121)

       , logIN_time=CONVERT(VARCHAR(26), logIN_time,121),net_address

       , net_library, dbid, ecid, kpid, hostname, hostprocess

       , loginame, program_name, nt_domain, nt_username, uid, sid

       , sql_handle, stmt_start, stmt_end

  FROM master..sysprocesses

  WHERE blocked!=0 

  OR waittype != 0x0000

  OR spid IN (SELECT blocked 

              FROM @probclients 

              WHERE blocked != 0

             )

  OR spid IN (SELECT spid 

              FROM @probclients 

              WHERE blocked != 0

             )

  -- 처음 결과 끝

  -- 두번째 결과 시작

  DECLARE @spid INT

  DECLARE @msg VARCHAR(100)

  

  CREATE TABLE #infoevent(id INT IDENTITY

                         , spid INT

                         , eventtype VARCHAR(255)

                         , parameters INT

                         , eventinfo VARCHAR(255)

                         )

  DECLARE cur_infoevent CURSOR READ_ONLY

  FOR SELECT spid

      FROM @probclients

  OPEN cur_infoevent

  FETCH NEXT FROM cur_infoevent

  INTO @spid

  WHILE (@@fetch_status = 0)

  BEGIN

    SET @msg = 'dbcc inputbuffer (' + CONVERT(VARCHAR(20), @spid) + ')'  

    INSERT #infoevent (eventtype, parameters, eventinfo)

    EXEC (@msg)

    UPDATE #infoevent

    SET spid = @spid

    WHERE id = @@identity

    FETCH NEXT FROM cur_infoevent

    INTO @spid

  END

  CLOSE cur_infoevent

  DEALLOCATE cur_infoevent

  SELECT * from #infoevent

  

  DROP TABLE #infoevent

  -- 두번째 결과 끝

  -- 세번째 결과 시작

  SELECT CONVERT (SMALLINT, req_spid) As spid

       , rsc_dbid As dbid

       , db_name(rsc_dbid) As dBName

       , rsc_objid As ObjId                       

       , rsc_indid As IndId

       , substring (v.name, 1, 4) As Type

       , substring (rsc_text, 1, 16) as Resource

       , substring (u.name, 1, 8) As Mode

       , substring (x.name, 1, 5) As Status

  INTO #BlockLock_t

  FROM master.dbo.syslockinfo,

       master.dbo.spt_values v,

       master.dbo.spt_values x,

       master.dbo.spt_values u

  WHERE master.dbo.syslockinfo.rsc_type = v.number

    and v.type = 'LR'

    and master.dbo.syslockinfo.req_status = x.number

    and x.type = 'LS'

    and master.dbo.syslockinfo.req_mode + 1 = u.number

    and u.type = 'L'

    and req_spid in (SELECT spid 

                     FROM master..sysprocesses 

                     WHERE blocked = 0 and spid IN (SELECT blocked 

                                                    FROM master..sysprocesses

                                                   )

                    )

  CREATE TABLE #lockreturn_t

  ( spid SMALLINT

  , dbname SYSNAME

  , objname SYSNAME

  , indid SMALLINT

  , type VARCHAR(5)

  , resource VARCHAR(20)

  , mode VARCHAR(10)

  , status VARCHAR(5)

  )   

  

  DECLARE @dbid INT

  DECLARE @dbname SYSNAME

  DECLARE @sql VARCHAR(300)

  DECLARE cur_lockobject CURSOR 

  FOR 

    SELECT DISTINCT dbid, dbname

    FROM #BlockLock_t

    ORDER BY dbid

 

  OPEN cur_lockobject

 

  FETCH cur_lockobject INTO @dbid, @dbname

 

  WHILE @@fetch_status <> -1

  BEGIN

    IF @@fetch_status <> -2

    BEGIN

      SET @sql = 'select #BlockLock_t.spid, #BlockLock_t.dbname, a.name, '

      SET @sql = @sql + '#BlockLock_t.indid, #BlockLock_t.type, #BlockLock_t.resource, '

      SET @sql = @sql + '#BlockLock_t.mode, #BlockLock_t.status '

      SET @sql = @sql + 'FROM #BlockLock_t, '

      SET @sql = @sql + @dbname+'..sysobjects a  WHERE dbid = ' 

      SET @sql = @sql + CAST(@dbid AS VARCHAR(10)) 

      SET @sql = @sql + ' AND #BlockLock_t.ObjID = a.id ORDER BY spid'

      INSERT INTO #lockreturn_t EXEC(@sql)

    END

    FETCH cur_lockobject INTO @dbid, @dbname

  END

  SELECT * 

  FROM #lockreturn_t

  ORDER BY spid, dbname, objname

  CLOSE cur_lockobject

  DEALLOCATE cur_lockobject

  DROP TABLE #BlockLock_t

  DROP TABLE #lockreturn_t

  -- 세번째 결과 끝

  SET NOCOUNT OFF

  RETURN(0) --sp_block0

GO

  

GRANT EXECUTE ON sp_block0 TO PUBLIC

GO