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.
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
- Check ERRORLOG for deadlock frequency (is it regular or very new ?)
- Ensure Index Maintenance exists and has run successfully.
- Setup a trace either with SQL Server Profiler, an Extended Events solution or via Trace Flag 1222.
- Analyze the results of the trace:
- What queries were involved ?
- Which tables/indices are accessed when it happens and how frequently ?
Anything else ?
SP_GRPVIEW_LOAD
is deadlocking with an Update query insp_regvisit_save
-UPDATE appointment SET ap_is_current = 0 WHERE ap_id = @vlngCurApId