2

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

3

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

  • I'm using SP_WHOisActive at the moment, I have noticed various concerns with blocking on other databases within the SQL Server instance - namely the sp_LogPurge stored procedure - part of a 'off the shelf' product that we bought - I will be asking the manufacturer about that. I am trying to understand the results from the query related to the database I'm trying to improve. Question about SP_WHOisActive for you: is it a snapshot? I.e. is running it once going to actually help anything? Or would I have to get really lucky to run it at a intensive blocking period? – user3585420 Jun 14 '15 at 14:25
  • @user3585420, depends how you want to customize and use it. You can log the records in table for a duration you believe would be a good for troubleshooting. On how to do this would suggest you to read brentozar.com/responder/log-sp_whoisactive-to-a-table – KASQLDBA Jun 15 '15 at 10:38
1

Both of these are user processes and are part of an application, you can check the outer query for the cursor with the following

SELECT , c.session_id
       , c.properties
       , c.creation_time
       , c.is_open
       , t.text
FROM sys.dm_exec_cursors (0) c
 CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

and the text for the sp_LogPurge with SP_HELPTEXT sp_LogPurge Your question is bit to generic as it is about application behavior without knowing anything about the application and if this is it's normal behavior or not.

  • Thanks for your help, when I run the query you suggested it returns no rows. What database am I supposed to run the SP_HELPTEXT sp_LogPurge query against? Thanks – user3585420 Jun 14 '15 at 13:26
  • The database you are monitoring. Skip the where condition on the query to get all the running cursors on the server – Spörri Jun 14 '15 at 13:41
  • Hmm, the sp_LogPurge isn't on the database that I filtered by. When I remove the where condition I still get no results. – user3585420 Jun 14 '15 at 14:01
  • Check @kasqldba answer – Spörri Jun 14 '15 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.