Description
This query will report on blocking by pulling blocking information directly from the SQL Server DPA repository.
SQL Query
--This query will report on blocking directly from the DPA repository for a specific date / time range and a instance.
DECLARE @start_time VARCHAR(16)
, @end_time VARCHAR(16)
, @DBNAME VARCHAR(100)
, @DBID VARCHAR(3)
, @SQL NVARCHAR(MAX)
SELECT @start_time = '8/10/13 4:30:00'
SELECT @end_time = '10/10/13 4:40:00'
SELECT @DBNAME = 'INSTANCE NAME HERE'
SELECT @DBID = ID
FROM ignite.COND
WHERE NAME = @DBNAME
SET @SQL = 'SELECT x.BLOCKER AS [BLOCKER HASH], x.BLOCKED AS [BLOCKEE HASH], x.DURATION AS [DURATION (sec)], st.ST AS [BLOCKER SQL], st2.ST as [BLOCKEE SQL] ' + 'FROM ' + '(' + '( ' + 'SELECT sw.IZHO AS [BLOCKER], sw2.IZHO AS [BLOCKED], ISNULL(SUM(sw2.QP)/100,0) AS [DURATION] ' + 'FROM ignite.CONSW_' + @DBID + ' sw ' + 'INNER JOIN ignite.CONSW_' + @DBID + ' sw2 ON sw.BLER = sw2.BLEE ' + 'WHERE sw.D BETWEEN ''' + @start_time + ''' AND ''' + @end_time + ''' ' + 'AND sw2.D BETWEEN ''' + @start_time + ''' AND ''' + @end_time + ''' ' + 'GROUP BY sw.IZHO, sw2.IZHO ' + ') ' + 'UNION ' + '( ' + 'SELECT null [BLOCKER], sw2.IZHO AS [BLOCKED], ISNULL(SUM(sw2.QP)/100,0) AS [DURATION] ' + 'FROM ignite.CONSW_' + @DBID + ' sw2 ' + 'WHERE sw2.D BETWEEN ''' + @start_time + ''' AND ''' + @end_time + ''' ' + 'AND sw2.BLEE < 0 ' + 'GROUP BY sw2.IZHO ' + ') ' + ') x ' + 'LEFT JOIN ignite.CONST_' + @DBID + ' st ON x.BLOCKER = st.H AND st.P = 0 ' + 'LEFT JOIN ignite.CONST_' + @DBID + ' st2 ON x.BLOCKED = st2.H AND st2.P = 0 ' + 'order by x.DURATION desc '
EXEC (@SQL)
