patternsqlMinor
Prevent Deadlocks in Vendor Databases
Viewed 0 times
preventdatabasesvendordeadlocks
Problem
We have a Windows Update (WSUS) database that is used by Windows Update (obviously). A couple of times a day we will receive deadlocks on this database caused by the WSUS application running 2 update queries on the same record. The 2 queries come from the same process on one server.
We have tried changing the database to read committed snapshot, however that hasn't improved the situation. The WSUS process isn't reporting any errors, and the sys admin team is happy with how it's running.
Is there anything we should do to try and fix this, or should we just ignore the alerts for deadlocks on that database?
UPDATE: Deadlock has happened again. See below for the graph details:
For some reason stack exchange won't let me post the xml here.
We have tried changing the database to read committed snapshot, however that hasn't improved the situation. The WSUS process isn't reporting any errors, and the sys admin team is happy with how it's running.
Is there anything we should do to try and fix this, or should we just ignore the alerts for deadlocks on that database?
UPDATE: Deadlock has happened again. See below for the graph details:
For some reason stack exchange won't let me post the xml here.
Solution
As you are on SQL Server 2014, you can simply harvest historical deadlocks from the
No need for Trace flag
As the WSUS database is relatively public domain (and not your secret business logic database) you could publish some of the deadlock XML here and we can help with the investigation. Deadlocks are sometimes due to missing indexes, or if there are excessive exchange events then might be down to parallelism. Let's have a look!
system_health Extended Events (XE) session and do some analysis. It should be running all the time and will contain deadlocks since that last SQL Server service restart, although the ring_buffer could have cycled and ejected some older records. Try code from here or here. This No need for Trace flag
1222 and no need to run Profiler / server side trace with the Deadlock Graph event which can only capture deadlocks from the time you start them / turn the trace flag on. You can just look back in time.As the WSUS database is relatively public domain (and not your secret business logic database) you could publish some of the deadlock XML here and we can help with the investigation. Deadlocks are sometimes due to missing indexes, or if there are excessive exchange events then might be down to parallelism. Let's have a look!
Context
StackExchange Database Administrators Q#130724, answer score: 2
Revisions (0)
No revisions yet.