SQL Server Express averages 2-3% CPU but gets stuck at 100% sometimes

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: https://i.stack.imgur.com/Uq2el.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 hardwa

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.

There was a request to deprecate the feature but it looks like they will be leaving it in.

StackExchange Database Administrators Q#13130, answer score: 7