SQL blocking report to table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL blocking report to table

SQL blocking report to table

Rate Topic

Display Mode

Topic Options

Author

Message

Learn_something_new_everyday

Posted Thursday, October 11, 2012 7:16 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:29 PM
Points: 84, Visits: 771

I would like to be able to slice and dice the XML text data - ntext data field type in the SQL blocking report.

Saving this to a table to review is a big help but with 1000's or rows it is not very fast. I would like to to pull out the SQLhandle and line number. If I could get a process to query this I could get a list of SQL handles to occur the most often and then focus on the problem ones.

Example
transactionId, sql handle as blocked, line_NO sql handle as blocked, Line_NO, SQLhandle as blocking, line_NO, SQLhandle as blocking, line_NO,start_time,Database_ID from saved_table_name

Sometimes it will be just two SQLhandles one for blocked and one for blocking, and at times their will be a few like this one that has four or more SQLhandles

Text data column

<blocked-process-report>
<blocked-process>
<process id="process9265048" taskpriority="0" logused="2588" waitresource="KEY: 6:72057594621526016 (ffffffffffff)" waittime="9392" ownerId="3706309312" transactionname="user_transaction" lasttranstarted="2012-10-10T09:55:14.783" XDES="0x8ca8c7970" lockMode="RangeI-N" schedulerid="16" kpid="4240" status="suspended" spid="563" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-10T09:55:14.787" lastbatchcompleted="2012-10-10T09:55:14.783" lastattention="2012-10-10T09:55:12.117" clientapp=".Net SqlClient Data Provider" hostname="HOST_NAME" hostpid="3736" loginname="username_HERE" isolationlevel="read committed (2)" xactid="3706309312" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame line="269" stmtstart="20116" stmtend="20926" sqlhandle="0x030006001ca8da408e165c01bda000000100000000000000"/>
<frame line="97" stmtstart="5128" stmtend="5268" sqlhandle="0x03000600aa5ff23ea40cf4003da000000100000000000000"/>
</executionStack>
<inputbuf>
Proc [Database Id = 8 Object Id = 1056081594] </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waitresource="KEY: 6:72057594621526016 (ffffffffffff)" waittime="29119" spid="543" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-10T09:54:55.057" lastbatchcompleted="2012-10-10T09:54:55.057" lastattention="2012-10-10T09:13:52.850" clientapp=".Net SqlClient Data Provider" hostname="HOST_NAME" hostpid="3736" loginname="USER_NAME" isolationlevel="read committed (2)" xactid="3706178561" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame line="269" stmtstart="20116" stmtend="20926" sqlhandle="0x030006001ca8da408e1755c01bda000000100000000000000"/>
<frame line="97" stmtstart="5128" stmtend="5268" sqlhandle="0x03000600aa5ff23ea10cf4303da000000100000000000000"/>
</executionStack>
<inputbuf>
Proc [Database Id = 8 Object Id = 1056071595] </inputbuf>
</process>
</blocking-process>
</blocked-process-report>

Post #1371454

Learn_something_new_everyday

Posted Tuesday, October 23, 2012 1:28 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:29 PM
Points: 84, Visits: 771

--save SQL blocking trace to a table then use this to query the table to pull out the sqlhandle

SELECT CAST(textdata AS XML)
, 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
,rownumber,duration
FROM dbo.tablename

Post #1376219

« Prev Topic | Next Topic »

Permissions