Monday, December 20, 2010

Blocking - who blocks whom?

As a DBA, I got power to query meta system data to monitor any blocking going on. Although I monitor the blocking on all three enviroments (prod/dev/stg), but I tend to ignore the dev/stg. These 2 enviroments (dev/stg) where developers run their test codes and often block each other. Sometimes, I shoot them an email, but most of the time, i got buried with other prod stuff and no chance to bother, while developers they patiently wait and wait and have no clue why their queries takes so long to complete. Most of our developers are very knowlegeable and they can query dbo.sysprocesses from master db, but unfortunately they don't have permissions. Well... we do give them db_owner on all user-defined databases on dev/stg enviroments thou. Anyways,.. so to overcome this problem, i create a report (RDL) and publish on our main reporting services server, so they can go there and check themseves if any blocking occurs on specific server.

My objective is to run a store proc from centralized server. From there, I'm using linked server to query other server for blocking info.

USE [master]
CREATE PROCEDURE [dbo].[_dbaGetBlockingInfoByServer]
@linkedsrvName VARCHAR(255)
AS
/*
************************************************************************************************
-- //$Date: 12/15/2010 hh:mm $
-- //$Modtime: 12/15/2010 hh:mm $
-- //$Workfile: xxx.yyyy.zzz $
************************************************************************************************
-- // schema = xxx
-- // objname = yyyyyy
-- //
-- // Description:
-- //
-- // Modification history: Get blocking info details by server name
-- // Date  Author    PDW/SR  Description
-- // ---------- ---------------  -------  --------------------------------------------
-- // 12/15/2010 Thanh Nguyen  xxx   Originally created
************************************************************************************************
*/

-----------TESTING BLOCK------------
--DECLARE @linkedsrvName VARCHAR(255);
--SET @linkedsrvName = 'SQLSRV01';
----------------------------------
SET NOCOUNT ON ;
DECLARE @debug BIT;
SET @debug = 0;
DECLARE @strSQL VARCHAR(8000);
IF OBJECT_ID('tempdb..#temp_blocking') IS NOT NULL
    DROP TABLE #temp_blocking ;
CREATE TABLE #temp_blocking
    (
      SPID SMALLINT,
      Blocked_By SMALLINT,
      DBName varchar(255),
      WaitTime BIGINT,
      Wait_Info VARCHAR(255),
      Open_Tran SMALLINT,
      STATUS VARCHAR(255),
      HostName VARCHAR(255),
      AppName VARCHAR(255),
      Command VARCHAR(255),
      Login_Name VARCHAR(255),
      UserName VARCHAR(255),
      UserLocation VARCHAR(255),
      CPU INT,
      Physical_IO INT,
      Memory_Usage INT,
      Last_Batch DATETIME,
      Sql_Handle BINARY(20),
      SQL VARCHAR(8000)
    ) ;
SET @strSQL = 'SELECT  *
    FROM    ( SELECT    p.spid AS SPID,
                        p.blocked AS Blocked_By,
                        d.name AS DBName,
                        p.waittime,
                        ( CONVERT(VARCHAR, p.waittime / 1000) + ' + ''''
    + 'sec - ' + '''' + '
                          + COALESCE(p.lastwaittype, ' + '''' + '''' + ') + '
    + '''' + ' ' + '''' + '
                          + COALESCE(p.waitresource, ' + '''' + ''''
    + ') ) AS Wait_Info,
                        p.open_tran AS Open_Tran,
                        p.Status AS Status,
                        p.hostname AS HostName,
                        p.program_name AS AppName,
                        p.cmd AS Command,
                        p.loginame AS Login_Name,
                        NULL as UserName,
                        NULL as UserLocation,
                        p.cpu AS CPU,
                        p.physical_io AS Physical_IO,
                        p.memusage AS Memory_Usage,
                        p.last_batch AS Last_Batch,
                        p.sql_handle as Sql_Handle,
                        NULL as SQL
              FROM      ' + @linkedsrvName + '.master.dbo.sysprocesses p WITH (NOLOCK)
                        LEFT OUTER JOIN ' + @linkedsrvName
    + '.master.dbo.sysdatabases d WITH (NOLOCK) ON d.dbid = p.dbid
                        LEFT OUTER JOIN ( SELECT DISTINCT
                                                    blocked
                                          FROM      ' + @linkedsrvName
    + '.master.dbo.sysprocesses WITH (NOLOCK)
                                        ) AS b ON b.blocked = p.spid
            ) AS A
    ORDER BY A.Blocked_By' ;
IF @debug = 1
 PRINT @strSQL;
INSERT  #temp_blocking
        (
          SPID,
          Blocked_By,
          DBName,
          WaitTime,
          Wait_Info,
          Open_Tran,
          STATUS,
          HostName,
          AppName,
          Command,
          Login_Name,
          UserName,
          UserLocation,
          CPU,
          Physical_IO,
          Memory_Usage,
          Last_Batch,
          Sql_Handle,
          SQL
        )
        EXEC ( @strSQL
            ) ;
IF @debug = 1
 SELECT * FROM #temp_blocking
IF OBJECT_ID('tempdb..#temp_blocking_final') IS NOT NULL
    DROP TABLE #temp_blocking_final ;
   
SELECT  t.*
INTO #temp_blocking_final
FROM    ( SELECT    *
          FROM      #temp_blocking
          WHERE     Blocked_By <> 0
          UNION
          SELECT    *
          FROM      #temp_blocking
          WHERE     SPID IN ( SELECT    Blocked_By
                              FROM      #temp_blocking )
        ) AS t
ORDER BY t.Blocked_By,
        t.SPID ;
DECLARE @mystrSQL VARCHAR(500),
    @mysqlhandle BINARY(20)
DECLARE db_cursor CURSOR
    FOR SELECT  Sql_Handle
        FROM    #temp_blocking_final
        ORDER BY SPID
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @mysqlhandle
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @debug = 1
   PRINT sys.fn_sqlvarbasetostr(@mysqlhandle);
        IF object_id('tempdb..#okay') IS NOT NULL
            DROP TABLE #okay ;
        CREATE TABLE #okay ( txtSQL VARCHAR(8000) ) ;
        SET @mystrSQL = 'SELECT * FROM master.sys.dm_exec_sql_text('
            + sys.fn_sqlvarbasetostr(@mysqlhandle) + ')'
        SET @mystrSQL = N'SELECT text FROM OPENQUERY(' + @linkedsrvName + ', ''' + @mystrSQL
            + ''')'
        IF @debug = 1
   PRINT @mystrSQL;
        INSERT  #okay ( txtSQL )
                EXEC ( @mystrSQL
                    ) ;
        UPDATE  t
        SET     t.SQL = ( SELECT TOP 1
                                        txtSQL
                               FROM     #okay
                             )
        FROM    #temp_blocking_final AS t
        WHERE   t.Sql_Handle = @mysqlhandle
        FETCH NEXT FROM db_cursor INTO @mysqlhandle
    END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT  *, (LEFT(REPLACE(SQL, (CHAR(13) + CHAR(10)), ' '), 50) + ' ... (more)') AS short_SQL
FROM    #temp_blocking_final;

No comments: