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;
