5

I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the database and found some potential culprits: 17 seconds worth of blocking.

Below is such log ( there are many similar ):

<blocked-process-report>
 <blocked-process>
  <process id="process308898748" taskpriority="0" logused="0" waitresource="OBJECT: 99:774293818:0 " waittime="17146" ownerId="66317995993" transactionname="UPDATE" lasttranstarted="2015-06-15T12:59:05.817" XDES="0x3bc9cd970" lockMode="IX" schedulerid="7" kpid="11204" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.817" lastbatchcompleted="2015-06-15T12:59:05.817" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG10" hostpid="6832" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995993" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000075c8f5236facd1f18bca0258f9d4babad99091d6"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3111573   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 99:774293818:0 " waittime="17182" spid="278" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.780" lastbatchcompleted="2015-06-15T12:59:05.780" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG12" hostpid="9944" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995589" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000001c6452aee248fd8f9f2be0e27c7038cfa4e334f"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3807096   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

As you can see it appears that two updates are blocking each other and I'm struggling to see why.

Here are the database specifics that might help you help me:

TABLE: SP_ScheduleEvent
------
|ScheduleEventID| PK| bigint| not null|
...
|DateLastProcessed| | datetime| not null|
...

INDEX:
ScheduleEventID, "use page locks when accessing the index is set to" FALSE ( row locks is on ) 

Typically I have many processes running on different machines that would trigger this UPDATE, I run the update from a DBDataContext within a .NET application ( Linq to SQL) like so:

string sqlUpdateString = "UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = " + scheduleEvent.ScheduleEventID;
db.ExecuteCommand(sqlUpdateString);

Thanks

1

The Blocked process report (which you have) can be a little confusing at first. There are a few important sections

Blocking-Process This is the process(SP, query etc) that is causing the blocking. The process is using a resource that is required by the Blocked-Process

Blocked-Process This is the process(SP, query etc) that is blocked and is waiting on a a resource that is in use by the Blocking-Process.

waitresource This details the resource that the process is waiting on. Taking your example of waitresource="OBJECT: 99:774293818:0 "

The 99 refers to the Database ID of your data and the 774293818 refers to the table. Using the ID 774293818 and systables you should be able to find the table that is causing the blocking.

I would also set up an Extended Events Trace to capture the parameters of the offending blocking process and review the execution plans.

A good starting point is https://support.microsoft.com/en-us/kb/224453

  • Hello, unsurprisingly 774293818 table is the SP_ScheduleEvent table. I'm reading through that kb page now to set up Extended Events Trace. Will let you know. – user3585420 Jun 15 '15 at 15:04
  • Here is the execution plan of the update: pastie.org/10241403 – user3585420 Jun 15 '15 at 15:09
-1

If you have access to the db itself, you can run your application and take a look in the running requests (sys.dm_exec_request). There you'll find what query currently runs. You can also take a look at sys.dm_tran_locks which will give you a good overview of the requested locks on which ressource.

Another idea is to run a profiler using the tsql replay template on the database table and go throw the results. This would help you, I think.

If you need further information. Pls let me know.

  • I would recommend NOT running profiler in PROD. A server side trace or XEvents would be a much better choice. Also, your answer does not really provide answer to OP's question, since the blocked process report already has lock information in it. – Kin Shah Jun 15 '15 at 13:29
  • Well not in prod, but normally you have an test instance as well. But you can capture workload using profiler on the prod server and rerun it on the test system. Well I would like to comment on the OP's question. But due to the bad reputation system in stackexchange, I won't be able to comment unless I have a reputation of 50. – Ionic Jun 15 '15 at 13:35
  • But you can capture workload using profiler on the prod server and rerun it on the test system. NO, as I said, a server side trace or XEvent is much lighter. – Kin Shah Jun 15 '15 at 13:38
  • Yes but you don't have to configure it manually. Normally you configure it using profiler. Configure it to write it to a table or file. Start it, pause it. Close profiler and start it using standard sql. Therefore no results were send to your client. – Ionic Jun 15 '15 at 13:40
  • Hello, thanks for your comments. I don't understand why any of the suggestions in the comments / answer are helpful. I already know what query is causing the blocking right? So I don't need to do any further trace? Or have I got that wrong? – user3585420 Jun 15 '15 at 13:48

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.