We have a 2 node AAG setup with vCaC6 application running on it. Quite often, I get deadlock alerts from the server, and by the time I open the activity monitor, the deadlocks are no longer present.
I assumed that they were getting resolved automatically. But after Googling a little bit, I came across a VMware article which mentioned that this was a known problem and by design. The factors mentioned by Vmware are
1) Slow networks with large latency numbers between Manager Service (Application Server) and Model Manager (Repository) and DB
2) Unreliable networks with intermittent network/connectivity failures
3) Misconfiguration or underpowered MS SQL DB servers
4) Any factors that would slow down or prevent completing SQL DB stored procedures calls from remote clients, or result in the MS SQL DB server changing lock escalation levels from row to table.
I checked if there were any network waits happening on the server and could not find any.
This is what I find from the vCAc6 database, when I run sp_who2. Lot of these.
I also noticed this in a VMware KB article which says "The deadlock and retry is a standard part of the product and usually not a problem. Every site is different in load and configuration. In this case we identified a blocking scenario that would probably occur in other sites. However, this has not been reported and testing has not revealed this level of activity"
Does this mean, there is nothing to fix this issue? I dont think the AAG itself is under powered. What other ways can I explore to check if there are any network latencies between the application server and the Database?
Thanks!
Best Answer
SQL Server has tooling to provide information on the deadlocks and what was happening at the time. You can use TRACE FLAG 1222 to capture the details of the deadlock in the error logs.
Of course, you can make a better analysis tool by following Jonathan Kehayias's detailed discussion at:
Handling Deadlocks in SQL Server
In my environment I am always checking for deadlocks and other problems. I would recommend using extended events to capture and store the details of the deadlocks. By reading the XML data for the deadlock you can get better insight on the problems you are facing.
I would recommend using extended events to capture and store the details of the deadlocks. For a GUI method of setting this up see:
Monitor Deadlock Using Extended Events in SQL Serve 2008 and Later
You will never be 100% free from deadlocks, but you can trim down the problems by using the captured data to resolve many of the deadlocks.