Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

parsing blocked processes report Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 1:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 881, Visits: 1,404
Hi Folks- thanks in advance for any help you can provide.

I'm capturing a blocked processes report to a file one disk and I'm trying to query against it and parse the xml into readable information. Most of that is fairly well documented and I'm having success except for one small snag.

I'm trying to take the sqlhandle from the report and return the sql statement associated with it. When I do so, I'm getting the error:

Implicit conversion from data type varchar(max) to varbinary is not allowed. Use the CONVERT function to run this query.

here's what I'm doing:

1.) capture a blocked processes trace to a file called :\trace_blockedprocesses_2013-08-16_0400.trc and create a couple of entries by blocking (blocked processes threshold is set)

2.) drop that into a table, just to make things a little simple for now (will eventually just use a CTE)

SELECT CAST(textdata AS XML) as fullblockedprocessereportXML
, CAST(textdata AS XML).value( '(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','varchar(max)') as blockedprocess
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]','varchar(max)') AS BlockingProcess1
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[2]','varchar(max)') AS BlockingProcess2
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[3]','varchar(max)') AS BlockingProcess3
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[4]','varchar(max)') AS BlockingProcess4
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[5]','varchar(max)') AS BlockingProcess5
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[6]','varchar(max)') AS BlockingProcess6
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[7]','varchar(max)') AS BlockingProcess7
,@@servername as servername,Duration/1000000.0 as durationsecs,DatabaseName
into #tempwc1
from fn_trace_gettable(N'd:\trace_blockedprocesses_2013-08-16_0400.trc', default)

3.) attempt to return results like this and error is produced:

select * from #tempwc1 wc
cross apply sys.dm_exec_sql_text(wc.blockedprocess) AS st

What I have tried already:

When I do select * from #tempwc1 and take the value returned for blockedprocess and manually plug it into sys.dm_exec_sql_text like:

select text from sys.dm_exec_sql_text(0x03000d008702963122150300aca100000100000000000000)

I do then get the correct blocking query.

When I change the data type, or convert the value to varbinary like this:

.... CAST(textdata AS XML).value( '(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','varbinary)') as blockedprocess .....

in that case, the acutal value of the sqlhandle changes so I dont get any result...

So to summarize my question, how can return the sql statements involved in a blocked process along with the rest of the relevant information in the blocked processes report without having to manaully plug in the sqlhandle.

Thanks!

Post #1485359
Posted Monday, August 19, 2013 7:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 881, Visits: 1,404
So, I posted on a Friday afternoon and go no responses... maybe this reply on a Monday morning will help? Still can't get it, would appreciate a tip.
Post #1485783
Posted Thursday, January 16, 2014 4:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:59 AM
Points: 2, Visits: 246
try this

CAST(textdata AS XML).value('xs:hexBinary(substring((/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle)[1],3))',
'varbinary(max)')
etc

that would help
Post #1531484
Posted Thursday, January 16, 2014 4:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 881, Visits: 1,404
I think you misunderstand the question... but I figured it out some time ago. this is what I'm doing:


USE [DBA]
GO

/****** Object: Table [dbo].[blockingreport2013] Script Date: 01/16/2014 06:56:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[blockingreport2013](
[waitresourcetype] [varchar](500) NULL,
[waitresourcedatabase] [nvarchar](128) NULL,
[waitresourceid] [varchar](507) NULL,
[WAITTABLENAME] [nvarchar](500) NULL,
[WAITINDEXNAME] [varchar](500) NULL,
[starttime] [varchar](50) NULL,
[waittime] [varchar](1000) NULL,
[waitresource] [varchar](500) NULL,
[objectid] [varchar](500) NULL,
[databaseid] [varchar](10) NULL,
[indexid] [varchar](10) NULL,
[blockingdatabasename] [varchar](500) NULL,
[blockingobjectname] [varchar](4000) NULL,
[blockeddatabasename] [varchar](500) NULL,
[blockedobjectname] [varchar](4000) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO






USE [DBA]
GO

/****** Object: StoredProcedure [dbo].[Record_blockedProcesses] Script Date: 01/16/2014 06:49:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE proc
[dbo].[Record_blockedProcesses]
@pathandfilename varchar (500)=null
as
begin


--declare @pathandfilename varchar (500)
--set @pathandfilename='d:\trace_blockedprocesses_2013-12-10_0400.trc'

if (@pathandfilename is null)
begin
select @pathandfilename=path from sys.traces where path like '%blocked%'
end


select
WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ),
WaitResource = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waitresource','varchar(50)' ),
BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'),
BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ),
BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(50)' ),
BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'),
BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ),
BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(50)' ),
BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
OBJECTID,IndexID,databaseid
into #temp1
from fn_trace_gettable (@pathandfilename, default)
where eventclass = 137
and TextData not like '%Database Id = 32767%'
order by BlockedLastBatchStarted
/**
select
db_name(cast (Right(LEFT([blockingquery],23),2) as int)) as blockingdatabasename
,replace (replace( (Right(blockingquery,(CHARINDEX('Object Id = ',BlockingQuery)-10))),'=',''),']','')as blockingobjectid
,db_name(cast (Right(LEFT([blockedquery],23),2) as int)) as blockeddatabasename
,replace (replace( (Right(blockedquery,(CHARINDEX('Object Id = ',BlockedQuery)-10))),'=',''),']','')as blockedobjectid,
*
from #temp1**/

declare @blockingdatabasename varchar(100), @blockingobjectid varchar(100),@sql nvarchar(4000)
declare @blockeddatabasename varchar(100), @blockedobjectid varchar(100)
declare @blockedquery varchar(4000), @blockingquery varchar(4000),@waitresource varchar(500)
declare @results table(starttime varchar(50),
waittime varchar(1000),
waitresource varchar(500),
objectid varchar(500)
,databaseid varchar(10)
,indexid varchar(10)
,blockingdatabasename varchar(500)
,blockingobjectname varchar(4000)
,blockeddatabasename varchar(500)
,blockedobjectname varchar(4000)
)
declare @waittime varchar(1000)
declare @BlockingLastBatchStarted varchar(50)
declare @objectid varchar(500), @databaseid varchar(10), @indexid varchar(10)

declare dbcursor cursor for
select
BlockingLastBatchStarted,
waittime,
waitresource,
CASE
WHEN blockingquery like '%Proc %' THEN
db_name(cast (Right(LEFT([blockingquery],23),2) as int))
ELSE 'NA'
end
,CASE
WHEN blockingquery like '%Proc %' THEN
replace (replace( (Right(blockingquery,(CHARINDEX('Object Id = ',BlockingQuery)-10))),'=',''),']','')
ELSE '-9999'
END
,CASE
WHEN blockedquery like '%Proc %' THEN
db_name(cast (Right(LEFT([blockedquery],23),2) as int))
ELSE 'NA'
END
,CASE
WHEN blockedquery like '%Proc %' THEN
replace (replace( (Right(blockedquery,(CHARINDEX('Object Id = ',BlockedQuery)-10))),'=',''),']','')
ELSE '-9999'
END
,blockingquery,blockedquery,objectid,indexid,databaseid
from #temp1

open dbcursor
fetch next from dbcursor into @BlockingLastBatchStarted,@waittime,@waitresource,@blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid,@blockingquery,@blockedquery,@objectid,@indexid,@databaseid
while @@FETCH_STATUS =0
begin
--select @blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid
if (@blockingdatabasename='NA' and @blockeddatabasename!='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', ''NA'','''+ REPLACE(@blockingquery, '''', '"')+''' ,'''+@blockeddatabasename+''',b.name from '+@blockeddatabasename+'.dbo.sysobjects b where b.id='+@blockedobjectid
--select @sql
insert @results
exec (@sql)
end

if (@blockingdatabasename!='NA' and @blockeddatabasename!='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', '''+@blockingdatabasename+''',a.name ,'''+@blockeddatabasename+''',b.name from '+@blockingdatabasename+'.dbo.sysobjects a, '+@blockeddatabasename+'.dbo.sysobjects b where a.id='+@blockingobjectid+'and b.id='+@blockedobjectid
--select @sql
insert @results
exec (@sql)
end

if (@blockingdatabasename!='NA' and @blockeddatabasename='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', '''+@blockingdatabasename+''',a.name ,''NA'',''"'+REPLACE(@blockedquery, '''', '"')+'"'' from '+@blockingdatabasename+'.dbo.sysobjects a where a.id='+@blockingobjectid
--select @sql
insert @results
exec (@sql)
end

if (@blockingdatabasename='NA' and @blockeddatabasename='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', ''NA'',''"'+REPLACE(@blockedquery, '''', '"')+'"'',''NA'','''+ REPLACE(@blockingquery, '''', '"')+''''
--select @sql
insert @results
exec (@sql)
end


fetch next from dbcursor into @BlockingLastBatchStarted,@waittime,@waitresource,@blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid,@blockingquery,@blockedquery,@objectid,@indexid,@databaseid
end

select * into #temp3 from @results
order by starttime
close dbcursor


deallocate dbcursor
drop table #temp1



--end





select LEFT(waitresource,(charindex(':',waitresource))-1 ) as waitresourcetype,
DB_NAME(
CASE WHEN waitresource like 'KEY%' then

SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
)
ELSE
LEFT(

SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
),
(charindex(':',SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
)
))-1)
END) as waitresourcedatabase,

CASE WHEN waitresource like 'PAGE%' then waitresource
WHEN waitresource like 'KEY%' then
LEFT(
RIGHT(waitresource,charindex(':',reverse(waitresource))-1)
,CHARINDEX (' ', RIGHT(waitresource,charindex(':',reverse(waitresource))-1)))


WHEN waitresource like 'OBJECT%' then
objectid
ELSE 'OTHER -'+waitresource
END
AS waitresourceid
,CASE when waitresource like 'OBJECT%' then
OBJECT_NAME(objectid,databaseid)
When waitresource like 'KEY%' then 'notdone'
When waitresource like 'PAGE' then waitresource
end
as WAITTABLENAME
,'page_or_object_lock_no_index_captured' as WAITINDEXNAME,*
into #temp4
from #temp3

drop table #temp3

--select * from #temp4
-------------------------------------------
--begin block to update waitresource columns

----
declare @sql2 nvarchar(400)
declare updatecursor cursor for
select waitresourcedatabase, waitresourceid,waittablename from #temp4
where waittablename='notdone'

declare @waittable varchar(200)
declare @waitindex varchar(500)
declare @waitresourcedatabase varchar(200)
declare @waitresourceid varchar(200)
DECLARE @ParmDefinition nvarchar(500)
open updatecursor
fetch next from updatecursor into @waitresourcedatabase, @waitresourceid,@waittable
while @@FETCH_STATUS =0
begin
--select @waitresourceid


SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waittableout nvarchar(25) OUTPUT'

set @sql2='SELECT @waittableout=obj.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE par.hobt_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@waitresourceid,@waittableout=@waittable output;

--select @waittable

update #temp4
set waittablename=@waittable
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@waitresourceid and waittablename='notdone'



SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waitindexout nvarchar(500) OUTPUT'

set @sql2='SELECT @waitindexout=ind.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE par.hobt_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@waitresourceid,@waitindexout=@waitindex output;

if @waitindex is null
set @waitindex='notfound'

update #temp4
set waitindexname=@waitindex
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@waitresourceid and waitindexname='page_or_object_lock_no_index_captured'

fetch next from updatecursor into @waitresourcedatabase, @waitresourceid,@waittable
end
close updatecursor
deallocate updatecursor
--select * from #temp4



----START BLOCK TO UPDATE PAGE LOCK ROWS

--declare variables
declare @pagenumber varchar(100)
declare @command varchar(500)
declare @pageobjectid varchar(100)
declare @pageindexid varchar(100)

CREATE TABLE #PageData
(
ParentObject VARCHAR(1000)NULL,
Object VARCHAR(4000)NULL,
Field VARCHAR(1000)NULL,
ObjectValue VARCHAR(4000)NULL,
)
--define cursor
declare pagecursor cursor for
select distinct waitresourceid, waitresourcedatabase from #temp4 where waitresourcetype='PAGE'
open pagecursor

fetch next from pagecursor into @pagenumber, @waitresourcedatabase
while @@FETCH_STATUS =0
begin
set @command=REPLACE(@pagenumber,'PAGE: ' ,'DBCC PAGE(')+') WITH TABLERESULTS'
set @command=REPLACE(@command,':',',')
insert into #PageData (ParentObject, Object, Field, ObjectValue)
exec (@command)
--select * from #PageData
select top 1 @pageobjectid=ObjectValue from #PageData where Field ='Metadata: ObjectId'
select top 1 @pageindexid= ObjectValue from #PageData where Field ='Metadata: IndexId'
--select @pageobjectid
update #temp4 set objectid=@pageobjectid, indexid=@pageindexid where waitresource=@pagenumber
delete from #PageData

SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waittableout nvarchar(25) OUTPUT'

set @sql2='SELECT top 1 @waittableout=obj.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE obj.object_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@pageobjectid,@waittableout=@waittable output;

--select @waittable

update #temp4
set waittablename=@waittable
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@pagenumber and (waittablename='notdone' OR waittablename is null)


SET @ParmDefinition = N' @waitresourceidIN varchar(200), @objin varchar(100), @waitindexout nvarchar(500) OUTPUT'

set @sql2='SELECT @waitindexout=ind.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE ind.index_id=@waitresourceidIN and obj.object_id=@objin'

-- select @sql2
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@pageindexid,@objin=@pageobjectid,@waitindexout=@waitindex output;

if @waitindex is null
set @waitindex='notfound'

update #temp4
set waitindexname=@waitindex
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@pagenumber and (waitindexname='page_or_object_lock_no_index_captured' or waitindexname is null)


fetch next from pagecursor into @pagenumber, @waitresourcedatabase
end
close pagecursor
deallocate pagecursor
drop table #pagedata



--select * from #temp4


insert
dba.dbo.blockingreport2013
select * from #temp4 where starttime >(select max(starttime) from dba.dbo.blockingreport2013)
drop table #temp4

delete from dba.dbo.blockingreport2013
where starttime <getdate()-60

--drop table #temp1

--drop table #temp3


end






GO


Post #1531500
Posted Thursday, January 16, 2014 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:59 AM
Points: 2, Visits: 246
i dont care what you're doing now. i've just provided you with the exact answer to you question. Read it carefully
Post #1531693
Posted Thursday, January 16, 2014 11:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 881, Visits: 1,404
Actually you didn't answer my question at all. My question is how to return the SQL statement.

Jerk.
Post #1531740
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse