0

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.

enter image description here

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!

  • You're asking the wrong people - you'll need to take this up with VMWare as it's their database and their product. – Sean Gallardy Jan 30 '17 at 2:43
2

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.

  • Thanks for the answer. Yes both trace flag 1022 and 1024 are enabled . I ran an extended event session on the server yesterday. By the time I open the server, the deadlocks are getting resolved automatically. By question was specific to vcac6 database and if someone faced a similar issue. Thanks ! – user2923332 Jan 26 '17 at 22:27

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.