Sql-server – sql server instance using maximum cpu usage

performancesql server

I have a SQL server 2005 instance installed on the server machine with two vm

Server Specification are
*Processor:* Xeon
Ram : 6GB
OS: window server on vm ware

It is running asp.net application deployed on the IIS and sql server database instance . This application is used by around 500 users daily. after every 22 days the processors CPU Usage Reaches to 100% to 95% . and the most used process (in task manager) is the instance of the sql server that is taking the maximum(all) the processing of the processor. Ram is only used up to 3 – 4 GB

I executed the below task but of no use

  1. Recreated all the table indexes but of no use
  2. Restarted the sql server database engine instance but of no use

But when the server(machine) was restarted. The sql server was working fine and CPU Usage was back to normal.

WE DO NOT WANT TO RESTART THE SERVER MACHINE. is this possible to performance tune the sql server so that after 22 days it does not choke up the server processing or what can i do to avoid it.

Best Answer

Try the CPU related DMV queries from Glenn Berry's SQL Diagnostic Queries. In fact, I suggest you run all the diagnostic queries because the high CPU utilization can be a result of some problem with the way the instance is configured, the way queries were written etc.,

Here is another article on SQL Server Performance site, that might help you troubleshoot.

Related Question