I have a query that I’ve spent some time optimizing, it will normally run in less than a second. Occasionally, though, it will take a minute or two to run which causes the calling application to throw a timeout error.
It is just a report query, it’s not updating. I’m using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
Question: How can I tell what is blocking this query and get it fixed?
Advertisement
Answer
When you notice a long-running query, try this in a new query window:
exec sp_who2
Peruse the BlkBy column to see if you’re getting blocked by something, then find its SPID in the left column. This will let you know the SPID of the blocker, as well as some general context about the cause of the block. If you think it advisable, you could run KILL to stop the blocking SPID, but keep in mind it will disrupt whomever initiated that query. First, you might want to check with whomever’s Login or HostName is blocking.
Alternatively, if you have a cached execution plan for an expensive query, it may have expired.
If there’s any particular pattern to the performance dive, that will be a helpful clue, but if it seems random, then I’d keep an eye on sp_who2 so you can fire that off when you notice it happening.