sql server instance using maximum cpu usage

Question

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.

Answer 1

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.

Answer 2

It's not too hard to write a query that looks at SQL Server's management views and reports back what each busy process is doing. This should get you to a place where you can find the connections that are taking a lot of CPU time. If they're executing a query, you can work on improving that query; or, if they're doing cleanup or system-level work, you can work to address that problem. This query will show you what's running and how much CPU time it's taken, ordering by the most CPU to the lowest.

Note that the query returns live results. If you run it two time sin a row, you'll almost certainly get different results each time because the second run will show you the live state of the server at the moment you run it. On the other hand, that's good: two consecutive runs should make it apparent that certain queries or connections are running for a very long time and causing you trouble.

If it's not apparent what's taking CPU time, you should report back to us with some of the output of this query so we can continue trying to help you diagnose the issue.

SELECT
    cpu_time, total_elapsed_time,
    session_Id, [DatabaseName] = DB_NAME(sp.dbid), Hostname, [User] = nt_username,
    [Status] = ER.status, [WaitType] = wait_type,
    [QueryText] = SUBSTRING (QT.text, ER.statement_start_offset/2, (CASE WHEN ER.statement_end_offset = -1
       THEN LEN(CONVERT(NVARCHAR(MAX), QT.text)) * 2
    ELSE ER.statement_end_offset END - ER.statement_start_offset)/2),
    [Parent Query] = qt.text, [Program] = program_name,  start_time
FROM sys.dm_exec_requests AS ER
INNER JOIN sys.sysprocesses AS SP ON ER.session_id = SP.spid
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS QT
WHERE session_Id <> @@SPID
ORDER BY cpu_time DESC, total_elapsed_time DESC ;

Answer 3

Odds are that your SQL Server's statistics are getting old, or you've got a problem with parameter sniffing.

If the problem is that the statistics are getting old then updating the statistics would do the trick. If the problem is parameter sniffing then flushing the SQL Server's cache to get the bad plan out of there will do the trick.

In either case (or what ever the problem is) you need to figure out what the cause of the problem is through diagnosis and troubleshooting then resolve the issue.