![]() Then close the sessions, wait a while, and see if you can go back through the history to spot the lead blocker. Let the blocking go on for a while, and see what kinds of emails and alerts you get from the tool. Cause a blocking situation yourself by creating a table, then doing a BEGIN TRAN in a couple of different session windows and trying to update all of the rows. If you’ve got a monitoring tool, learn to use it well ahead of time. It’s not enough to just have it installed: just like a database, you need to understand how to leverage its power.Īfter you’ve had a monitoring tool installed for a few months, learn how to use it to troubleshoot blocking. It’s missing a lot of the juicy details sp_WhoIsActive has, but it’s still effective at capturing lead blockers. For blocking specifically, this is even better than logging sp_WhoIsActive to a table since it grabs blocking more frequently, but at a lower overhead. You don’t want to leave that running all the time, but if you know you’re heading into a time when blocking is traditionally a problem, fire it up and start keeping an eye on it. Jeremiah Peschka wrote an easy Extended Events session and a query for it. If you can prepare ahead of time, set up Extended Events. ![]() Download sp_BlitzLock in our free First Responder Kit, and read more documentation here. It checks the default system health session and shows you recent deadlocks, the queries that caused them, and the indexes involved. ![]() If you’re on SQL Server 2012 or newer, download the First Responder Kit, install sp_BlitzLock, and run it: Sometimes people call you up and want you to troubleshoot deadlocking that finished recently, but you didn’t have anything set up on the server ahead of time. If it’s 1-2 hours after the live emergency, use sp_BlitzLock. You can log sp_WhoIsActive’s results to a table, but don’t be a hoarder – don’t set this up to just continuously log to disk if you’re not actively doing something with the data. This is probably my favorite way to see blocking live, but it only works right now.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |