sql 2005: Timeout- Error 1222 Severity 16 State 18

Question

A user complained that a SQL query has started timing out. I couldn't see the timeouts in sql logs but when I started a trace, I could see timeouts: "Error 1222 Severity 16 State 18"

It was from spid 17 which is a system process but in user database. After some research I found that "This error means that a lock was requested in msdb and timed out. Usually, that's going to mean it's a big transaction on a big temp table or a big sort, or something of that type"

Source: Link.

As per this link, I tried disabling ghost record removal process using:

DBCC TRACEON (661,-1)

But that didn't help either so I changed it back.

Any idea what I can do?

Thanks!

Answer 1

I'd start by doing three things:

1. Make sure of what the user actually means by "the query is timing out". Sometimes you get false positives from someone who doesn't understand that a connection timeout and a query timeout are different things.

2. If it's not a connection timeout, go ahead and analyze the execution plan on the query the user is running. Especially if by "user" you mean "non-DBA who has ad-hoc query access to the database". 10 to 1 that query has a Cartesian join in it. Or nested cursors on a large dataset. Or some other such tomfoolery.

3. Check the Activity Monitor for recent long-running queries and also analyze those. Usually the SPID that kicks off a timeout message is not the actual culprit. It times out because another process is consuming resources inefficiently, shutting out subsequent processes that need those resources.

Even though #3 is the most likely solution, go ahead and do #2 because I've found by experience that any query a user complains about is likely to have something in it that can be significantly tuned for better performance, even if the main issue was something else outside the query.

Plus, it's good customer service to give their complaint a reasonable amount of due consideration. Doctors check the vitals even of known hypochondriacs. :)