Every deadlock really is a special monster So there is, even when adding an index is a lower risk way to solve a deadlock, you wanna make sure you don’t skip the due diligence in your haste to become the deadlock hero. I also need to check how many indexes does the table have already, is it okay to add another index for the size of the table and the insert rate. You want to make sure that you’re not adding a near duplicate index to something that’s already there.įor instance in the case of the index I added, there might be an index on country ID that just doesn’t have the included column I used, so instead of adding a new index, modifying an existing index might be a better bet. There is more work than I showed for adding an index When you’re comparing changing TSQL involved and adding an index, there’s far less risk that you’re going to make the data wrong for someone by adding the index. They’re my favorite because it tends to be relatively low risk to add an index. Indexes are my favorite way to prevent deadlocks from coming back It is pretty fantastic when you have the situation where you get to be the deadlock hero and you can get there with work and with practice. The good news is, after you work with deadlocks for awhile and you get practice going, there will come a time when you know you’ve been working with more complex ones and you hit one and you’re able to quickly read the deadlock graph and quickly come up with ideas for how you can prevent it from happening again. And the fact that the input buffer and the deadlock graph doesn’t always give you the whole of the transaction that’s involved for that session does give you extra leg work to do. You can have more than two sessions involved in the deadlock things can get pretty complicated. Transcript Fighting deadlocks is tough in SQL Server
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |