Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Those logged timeouts are probably unrelated to yours. Could just be out of stats or a poor query.. –  gbn Dec 15 '11 at 20:21
    
updating statistics didn't help –  Manjot Dec 16 '11 at 2:39
    
The edition is 9.00.1406 (RTM). We know that we need to patch it but can't do it because of the legacy application. Is anyone aware of any similar issues with SQL 2005 RTM? –  Manjot Dec 16 '11 at 3:28

1 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. :)

share|improve this answer
    
Hi, the query is creating temporary object with a big table. The edition is 9.00.1406 (RTM). We know that we need to patch it but can't do it because of the legacy application. It used to work in past....but what to do next? –  Manjot Dec 16 '11 at 3:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.