0

We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour.

The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted mode for SQL Server disks, 4 vCPU (that are seen by SQL Server as 2 CPU with 2 cores). It runs Windows Server 2012.

As we already had the issue, our supplier asked to set MAXDOP to 1 as they experienced problems with parallelism. It didn't solve the situation.

I ran the DBCC TRACEON (1222, -1) to get extended informations about deadlocks and transmit it to the supplier.

Edit: Example deadlock graph enter image description here

In XML Format: (I was not able to add it to the post) XML Format

Edit2: Index Maintenance

The following Job runs Ola Hallengren MaintenanceSolution (IndexOptimize) :

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d SAIDBA -Q "EXECUTE maintenance.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationLevel1 = 3,@FragmentationLevel2 = 30,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y',@LogToTable = 'Y'" -b

Edit 3: update on the situation

I did some mining on the error log to get an overview of the time line for deadlock occurrences. It happened three to four times more frequently after a software update (120 per day), but there were already 30-40 per day previously.

As the application supplier doubts about our indexing strategy, he wants us to do it.. We will rebuild all the indices.. We will see if it has positive effects...

What else can I do to investigate/troubleshoot/Reduce the number of deadlocks ?

Short answer

  1. Check ERRORLOG for deadlock frequency (is it regular or very new ?)
  2. Ensure Index Maintenance exists and has run successfully.
  3. Setup a trace either with SQL Server Profiler, an Extended Events solution or via Trace Flag 1222.
  4. Analyze the results of the trace:
    • What queries were involved ?
    • Which tables/indices are accessed when it happens and how frequently ?

Anything else ?

Improve this question
4
  • You must know the apps that's produces deadlocks. There are several issues to see how the apps works
    – Pedro Faro
    Dec 30, 2015 at 12:34
  • The application is 3rd-Party. We don't have the code and can't change it easily... We only can do service requests to suppliers... but we then need to be precise...
    – Jefferson B. Elias
    Dec 30, 2015 at 12:47
  • 1
    It doesn't seem to be anything to do with DBCC or Ola's scripts. The select query in SP_GRPVIEW_LOAD is deadlocking with an Update query in sp_regvisit_save - UPDATE appointment SET ap_is_current = 0 WHERE ap_id = @vlngCurApId
    – Martin Smith
    Dec 30, 2015 at 13:13
  • 1
    Check the execution plans of the queries involved in the deadlocks for index and query tuning opportunities. The likelihood of deadlocks increases when more data than needed is touched.
    – Dan Guzman
    Dec 30, 2015 at 13:13

1 Answer 1

Reset to default
-2

Deadlocks are "simply" application logic errors. In SQL Server I think you can do the following:

See also:

How to Track Down Deadlocks Using SQL Server 2005 Profiler

written by Brad McGehee. I think that he is more authoritative than me. He states:

...Deadlocks have been the bane of many a DBA. While rare for a well-designed and written application, deadlocks can be a major problem for—how can I say this delicately?—“less efficient” application code. What is even more frustrating is there is not much the DBA can to do prevent deadlocks, as the burden of preventing them in the first place is on the developers of the application...."

Improve this answer
2
  • Giova actually you explained in general way what can be cause of deadlock but that is definitely not the answer to OP's question. I hope you would agree on that. You also suggested to change isolation level which might not be required always as a fact lot of deadlocks can eaisly be resolved with proper index and logic. The downvote says thatyour post does not answers the question it has no more significance
    – Shanky
    Dec 30, 2015 at 14:18
  • @Shanky: My comment asnwer the question: review the application logic. A dba tipically can't solve deadlock problems. Especially if deadlock are generated by a third party application. Actually this question can not be answered. Stop. You can mitigate this problem with indexes (not so frequent beacuse of locking behaviour) or with a different isolation level. But you can not solve this kind of errors without modifying the application logic.
    – Giova
    Dec 30, 2015 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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