blocking details has perfect GUI
sent it customer or analysing in excel it is become problematic
if you run copy paste you details of blocking truncated
I tried to extract data using query provided in link https://thwack.solarwinds.com/product-forums/database-performance-analyzer-dpa/f/forum/2838/custom-report---blocking-report
but I could never get same result of blocking duration
bellow is same period shown in GUI and by Query
DECLARE @start_time VARCHAR(16), @end_time VARCHAR(16), @DBNAME VARCHAR(100), @DBID VARCHAR(3), @SQL NVARCHAR(MAX)
SELECT @start_time = '20210420 10:30'
SELECT @end_time = '20210420 10:40'
SELECT @DBNAME = 'WIN-11112222'
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)
using profiler I can also capture query executed when I click on blocking details ,but again can 't obtain same duration as specified on form
declare
@P0 datetime = '20210420 10:30',
@P1 datetime = '20210420 10:40'
select D,VDSI,XCUW,UDPW,PWMY,IZHO,HGOB,KEEQ,IXOY,DBML,RMAL,ORPH,CTFL,QP/100.0 WAITTIME,BLER,BLEE from ignite.CONSW_1
where D >= @P0 and D < @P1 and ((BLER is not null) or (BLEE is not null))
order by D
Customer want analyse details of blocking on daily basis
1. Is anyway extract data from blocking GUI to excel and show full text for each Blocker SQL , blocked SQL and duration and wait time ?
2. How extract correct duration from blocker and waiter from ignite. CONSW_ and /or other tables
Thank you
Alex
