Decreasing CPU usage on SQL Server 2005

Tags:
SQL Server 2005
SQL Server performance tuning
My SQL Server 2005 is installed in Windows Server 2003. CPU usage is always 90-100%. If more users connect to this SQL server using their local Management Studio, then the server will become very slow. Can you please tell me how to decrease CPU usage on this SQL Server – is there a step-by-step process you can share?

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no sure fire step by step process to trouble shooting high CPU load on the SQL Server.

A first step would be to use SQL Profiler to see what queries are taking the longest, and the most CPU time. Open SQL Profiler from Start > Programs > Microsoft SQL Server 2005 > Performance Tools > SQL Server Profiler. Click the new Trace button (upper left) and connect to the SQL Server. The important events that you want to look at are the RPC:Completed and SQL:BatchCompleted. You will probably want to sent the filter to limit the data which is returned to you so that you don’t get all the commands which are coming back.

Once you have the commands which are taking the most CPU time you can run these commands in the Management Studio and see what the execution plan is. By reviewing the execution plan you can see what additional indexes need to be added to the database which will improve database performance and decrease the CPU load time.

When you start up SQL Profiler don’t run it on the Server’s console. Run it from a workstation and connect to the remote SQL Server. Running SQL Profiler on the servers console will make the problem worse as the SQL Profiler app requires a lot of memory and CPU to run.

Based on your description you may need to contact a local database consultant to have them look into your system. A good database consultant will be a bit pricy, but they can be worth every penny.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following