It’s not a new SQL trick, but it’s new to me. By adding -T1204 to SQL Server’s startup parameters, you get a lovely (perhaps not attractive, but informative) spew of information into the error log every time a deadlock occurs. You can then make helpful announcements like “your query failed because it was fighting with the index rebuilder for access to the statistics on this particular column, and the index rebuilder won.”
For example, around midnight this morning, we received an email from an app saying that a SQL statement it ran was chosen as the deadlock victim. A few minutes later, the database emailed me the deadlock trace from the error log.
The error log output contains a snippet of the statement that was killed:
Input Buf: Language Event: SELECT DISTINCT location.id, location.name, location.lineage FROM location INNER JOIN webuser_role_location_cascaded wrl ON location.id = wrl.idLocation INNER JOIN role_location ON wrl.idRoleLocation = role_location.id INNER
And what it was trying to use:
METADATA: database_id = 5 STATS(object_id = 494624805, stats_id = 8)
And what the other contender was doing:
SPID: 62 ECID: 0 Statement Type: ALTER INDEX Line #: 10
What does this globbety-gook tell me? Well, the index rebuilder was building an index — I can see that from the ALTER INDEX — and the METADATA bit tells me exactly what the two queries were fighting over — statistic number 8 on object 494624805 in database 5. I happen to know what database 5 is in my system, but if it’s one you don’t recognize, a quick trip to the sys.databases DMV will tell you:
SELECT name FROM sys.databases WHERE database_id = 5
Use the OBJECT_NAME() function in that database to see what table the statistic is on.
Look in the sys.stats DMV to see what statistic number 8 is.
SELECT name FROM sys.stats WHERE object_id = 494624805 AND stats_id = 8