0

One of my company's SQL database generates a lot of deadlocks per day recently (someday >100)

I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same tables, I'm a newbie in Database Administrator so i don't know what is the best way to do some statistics on the deadlock reports, like i want to calculate and visualize things like what tables involed, how manytimes, what time the deadlocks usually happen.... so i can report to my boss, is there an easy way to import the all the deadlock xml columns to something like microsoft excel?

I attached here the deadlock reports (.xel file)

Deadlocks_reports

Sorry for english is not my first language.

Thank you for your time,

closed as too broad by mustaccio, kevinsky, Marco, Colin 't Hart, MDCCL Aug 12 at 23:07

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

5

You should use the open source stored procedure sp_BlitzLock to analyze these deadlocks. It will give you what you're looking for in terms of timing, which tables / objects are involved in deadlocks most often, etc.

I downloaded your .xel file and ran the proc like this:

EXEC master.dbo.sp_BlitzLock 
    @EventSessionPath = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log\DL_0_132091861398080000.xel';

As an example, the output tells me that B7R2_CHKVN.dbo.B331GeneralLedger was involved in 217 deadlocks captured in this event session (out of the total 383 deadlocks for that database).

Adding the @ObjectName = 'B7R2_CHKVN.dbo.B331GeneralLedger' parameter to the procedure call filters things down to just deadlocks that involved that object.

Screenshot of blitzlock output

Note that the database names are a little wonky because it's pulling the names by id, and my instance has different databases than yours

This will let you start going through each deadlock and seeing if you can find patterns of the queries involved. Consider loading the deadlock XML (the last column of the first resultset) in SentryOne Plan Explorer in order to better visualize the locks being taken.

1

As you are a new DBA, viewing deadlock information via the default System_Health Extended Events session in Management Studio can be very helpful as it offers a graphical diagram of each deadlock showing the objects involved.

Here is a decent article which tells you how: https://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

TIP: When viewing the diagram of each deadlock, use "Ctrl" + Scroll Wheel of mouse to zoom in and zoom out as required.

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