Sql-server – Blocked Process Report SQL Server 31 hour duration

blockingprofilersql server

I have an application (.NET EF 6 ) which is getting timeouts when submitting/reading data from the database. Whilst trying to diagnose the problem I used Blocked Process Reports from SQL server profile to see if I was getting any long blocks. I ran the trace and came across some peculiar very long blocks – 31 hour blocks. I want to know what these are and if I should be worried about them. I filtered the trace by Database name that I was concerned with.

example 1:

    <blocked-process-report>
 <blocked-process>
  <process id="process703b88" taskpriority="0" logused="0" waitresource="OBJECT: 33:485576768:0 " waittime="115168485" ownerId="64139099452" transactionname="PurgeAllEmptyExtents2" lasttranstarted="2015-06-13T04:52:31.830" XDES="0x6b1731270" lockMode="X" schedulerid="6" kpid="4996" status="background" spid="10" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack/>
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="444" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-06-14T12:51:46.637" lastbatchcompleted="2015-06-14T12:51:46.637" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="63627468968" currentdb="33" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack/>
   <inputbuf>
FETCH API_CURSOR0000000000043F19   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

Example 2:

<blocked-process-report>
 <blocked-process>
  <process id="process34a20f288" taskpriority="0" logused="0" waitresource="OBJECT: 33:485576768:0 " waittime="107460436" ownerId="64211456029" transactionname="implicit_transaction" lasttranstarted="2015-06-13T07:00:07.107" XDES="0x1dc183340" lockMode="IX" schedulerid="4" kpid="6712" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-13T07:01:00.090" lastbatchcompleted="2015-06-13T07:00:07.110" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="64211456029" currentdb="33" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
   <executionStack>
    <frame line="240" stmtstart="16612" stmtend="16774" sqlhandle="0x03002100f8128430bcc115016fa200000100000000000000"/>
    <frame line="1" sqlhandle="0x01002100f0e6ac0d90d6a500010000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_LogPurge &apos;2015-06-13 07:01:00&apos;;   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 33:485576768:0 " waittime="114659439" spid="78" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-13T05:01:00.883" lastbatchcompleted="2015-06-13T05:00:59.767" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="64144056143" currentdb="33" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
   <executionStack>
    <frame line="240" stmtstart="16612" stmtend="16774" sqlhandle="0x03002100f8128430bcc115016fa200000100000000000000"/>
    <frame line="1" sqlhandle="0x0100210009260608307d4565030000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_LogPurge &apos;2015-06-13 05:01:00&apos;;   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

The first one seems to be FETCH API_CURSOR0000000000043F19 and the second one is some sort of log purge: exec sp_LogPurge &apos;2015-06-13 05:01:00&apos;;

What are these and should I be worired?

Thanks

Best Answer

Not a huge fan of reporting profiler for checking blocking, but to get proper analysis would suggest you to either:

1) Use Adam Mechanic's SP_WHOisActive which is indeed very simple way of analyzing what and where could the blocking be a scenario with detailing on database name, app name along with its process.

2) If possible you can use SqlDiag utility to analyse same.

Ultimately, you need to find out who is doing the blocking first – why is their transaction taking so long? If it’s due to inefficiencies in the query – can you rewrite it? If it’s due to inefficiencies in the plan – can you add an index? If it’s modifying a large amount of data – can you break it down into smaller chunks so that each set is locked for a shorter period of time? These are ALWAYS the thing to try first.

However, if you still would like to go with reports to analyse blocking i would suggest to read this article from Jonathan Kehayias on how to use the block process report in sql server 2005/8

Related Question