In SSMS, open Object Explorer and navigate to Extended Events > Sessions > system_health > package0.event_file. With SQL Server 2012+, we have a better tool to see when deadlocks occur – and the deadlock graphs are saved by default, so we don’t have to read the text version to figure it out, or run a separate trace to capture them. This one isn’t bad, but imagine a multi-statement deadlock, or a server with several deadlocks in an hour – how do you easily see if there were other errors on the server at the same time? Every bit of information is on a separate line. If I open the error log, I can see the details of the deadlock. When I run the last statement, I receive an error that one of the processes was the deadlock victim. * Now, open a second query and paste this */ * Create deadlock - query 1 */ĬREATE TABLE tbl1 (id INT NOT NULL PRIMARY KEY CLUSTERED,ĬREATE TABLE tbl2 (id INT NOT NULL PRIMARY KEY CLUSTERED, You can use this as an example in a test environment to replicate it. Let me walk through what a deadlock looks like with TF 1222 and compare that to the XE session. With SQL Server 2012+, I also prefer to use the Extended Events default system_health session to view deadlock graphs – with no extra work required! For example, I enable TF 3226 to suppress “Log was backed up” messages. I like it neat and clean, so I can see errors easily. We could also enable trace flags 1204 and/or 1222 to write the information to the event log – better if we knew there were issues, but they weren’t predictable. Many of us used to run a Profiler or server trace to capture the Deadlock Graph event – useful if we knew when they were occurring (or they occurred all the time). How do you find deadlocks? Over the years, there have been various methods, depending on what tools were available in SQL Server. As a DBA or developer, it’s very helpful to be able to find deadlocks, review what caused them, and fix it permanently, if possible. They can cause application errors, slow performance, and unhappy users. Deadlocks in a SQL Server instance are problematic.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |