October 11, 2012 at 7:16 am
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>
October 23, 2012 at 1:28 pm
--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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply