Database Server 100%, for no reason

Question

Hi,

We have a Windows Server 2003 (x64) running as a Database server. The Database is equipped with 32GB of RAM

Usually the Database memory usage (Task Manager) is between 5-10%. However sometimes the Database shoots up suddenly to 100% and remains there, randomly and without any changes to code or executions.

All sort of research, paid or by me, has pointed to a single stored procedure. When the database is 100%, disabling this procedure, will bring the database back to normal.

Now this sounds quite obvious but here is the strange part.

The stored procedure is optimized and the memory usage (from execution plan) is 0.01, which is extraordinarily good. Normally executing the stored procedure will return the resultset instantaneously. I also paid a RackSpace Fanatic Support DBA to look over this, and he said that he sees no problem with the stored procedure.

Now the extra wierd bit.

• Running the SP is instantaneous.
• When DB is 100%, running the SP, keeps on executing for minutes upon minutes.
• Disabling the SP, sends the DB down to 5-10%.
• Although the SP is enabled, DB is 100%, if I open a new query window and run the EXACT code from the SP, but as a query, not as a SP, the results are returned INSTANTANEOUSLY again

So, although at first glance, it sounds that the SP needs optimization, the actual code in the SP is not a problem.

I am desperate!

Answer 1

Profiler:

SQL Server comes with a great tool called Profiler which lets you see in real time the queries that are running on the server. You should run the profiler and find out what is actually happening and use that to find the culprit.

There are 4 measurements for Queries: Memory, CPU, Reads, Writes. The SQL statements that take up a lot of these (individually or combined), and are called with high frequency are the best candidates for optimization.

Once you run the profile and capture the output, you should be able to identify the items for optimization. You can then run the SQL Statements, review the execution plans and perform the necessary optimization on it.

(edit: added content)

It could be that it is not the statement itself that is not optimal, but some locking / blocking / deadlocks that could cause this. There may be something else running on the server at the same time that is taking up the resources needed for this SP and is causing the spike in CPU.

Read up on Profiler:

http://msdn.microsoft.com/en-us/library/ms187929.aspx

Answer 2

The execution plan can change depending on input parameters to the SP and the size of the result set.

You could try to add WITH RECOMPILE to the stored procedure to get a fresh execution plan for every call. It will make it a little bit slower but sometimes SQL Server gets stuck with a unusually bad execution plan for the majority of the queries and a recompile helps in those scenarios.