Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 28 '12 at 4:54

This question came from our site for professional and enthusiast programmers.

1  
Let's get a little bit more information. What happens exactly with the CPU? Do you have perfmon counters you could upload that cover this time period for us to see exactly what you're seeing? –  Ali Razeghi Nov 28 '12 at 5:57

3 Answers 3

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.

share|improve this answer

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 ;
share|improve this answer

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.

share|improve this answer
1  
Denny, have you seen instances where stale statistics are taken care of when you reboot a system or parameter sniffing building up? Only reason I ask is I can't find a corrolation on why it would work fine after rebooting. Seems like it can't be stats then. I wasn't paying attention during Grant Fritchey's parameter sniffing sessions. :D –  Ali Razeghi Nov 30 '12 at 19:37
    
Stats aren't going to be updated when the server reboots. That would lead more towards some sort of parameter sniffing. However parameter sniffing could be the result of (slightly) bad stats. –  mrdenny Nov 30 '12 at 20:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.