Edit: Skip to the last paragraph for the summary-version!

I am dealing with a SQL Server 2008 R2 Express installation.

I have a fairly complex stored procedure.

Depending upon the inputs, it may return 6500 records.

Each record is a web order. It returns 80 fields. Also, there is a bit of grouping to sum up line-item information and complex filtering on the SPROC itself.

Almost always, this query runs in 2 seconds.

Sometimes, when the query runs with the minimal amount of filtering that will result in all 6500 rows returning, the database server enters an error state of some sort.

The CPU spikes to 100% and stays there. Sometimes it exits this state after a few minutes, sometimes it stays there until I reset the SQL Service.

If I reset the SQL Service during this error condition, then run the query again, normally I will be able to run it a hundred times without the error condition repeating.

However, as the database ages (and expands) this error condition is becoming more and more common.

Here's what I know

  • I have analyzed the execution plan and likely have indexes on the appropriate fields in the appropriate tables
  • These indexes are rebuilt nightly
  • The error state lasts longer than it used to, on average.
  • Rebuilding the indexes seems to help when this error condition has just occurred (before resetting the SQL Service). I am including this here because maybe it has something to do with internal stats? More often than not, I just have to reset the sql service now.
  • As far as I can tell, the execution plan looks relatively healthy. You can see the busiest quarter of the plan here: http://i.imgur.com/bwAFn.png Note: I am showing this portion due to the 3 underlined items.
    • I have experimented with changing the joins for those 2 of those 3 items to hash joins, but it actually seems to slow it down further.
  • On beefier hardware, I have not encountered this error condition. For a variety of reasons, it I need to stay on the limited hardware for the production version.
  • DB Properties:

enter image description here

My hosting resources are fairly minimal.

  • ~2.5 Ghz single core
  • 1.7 GB RAM

With that said, the DB server is "happy" almost all the time, except when this error condition is trigger by this specific SPROC.

I really can't show my code here, though I'd like to.

I wonder if some sort of internal maintenance is being triggered? Is it possible for SQL Server to enter an infinite loop? (I utilize no loops in my code).

Is there a good set of debugging tips for handling this situation:?

Sometimes when a query runs, it triggers a state where SQL Server uses 100% of CPU for 90 seconds to 20+ minutes. 98% of the time when the query runs, there is no problem at all, but this 98% is shrinking slowly over time.

2012 Update

  • I setup a job that rebuilds all DB indexes each night.
  • Auto Close has been set to False

This largely mitigated the problem. However, I still do have some issues, but they come up about 5% as often, which I still have not solved.

  • Are you sure it's not something like a space issue? Are you system dbs on the same physical drive as your data? Could there be a space issue on one set of drives? – JNK Feb 15 '12 at 18:35
  • 6
    Red flag: Auto Close = True. You should pretty much never use that option. – JNK Feb 15 '12 at 18:39
  • Wish I could + a comment 10 times. You should never, ever, ever use the AutoClose option. Unless you are a SQL Server hosting provider and like to irritate the crap out of your customers. – Aaron Bertrand Feb 15 '12 at 18:43
  • JNK: 30 GB drive, 6GB Free – Brian Webster Feb 15 '12 at 19:00
  • You know you can delete your previous comment instead of adding another comment stating you're retracting it. :-) – Aaron Bertrand Feb 16 '12 at 0:02

It's probably related to your AutoClose setting. Almost every resource you will find recommends turning this setting OFF.

Essentially it closes connections automatically, but it can cause a lot of issues since it's not done well. It can and does cause all sorts of mysterious performance issues.

Here's a post from Buck Woody about the reasoning.

Here's a blog post from another SQL Server MVP about a situation where it caused issues.

Step 1 should be turning that setting off, then testing for a while.

As per Aaron's comment, there was a request to deprecate the feature but it looks like they will be leaving it in.

  • Only a small handful of people agreed with my suggestion to deprecate AutoClose: connect.microsoft.com/SQLServer/feedback/details/238888/… While the bug is marked as fixed, this is misleading - there is actually no plan, as far as I know, to remove this functionality. Cue the sad trombone. – Aaron Bertrand Feb 15 '12 at 18:55
  • @AaronBertrand didn't realize, all the resources I could find indicated it was being removed. I'll update the A. – JNK Feb 15 '12 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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