SQL Server Performance

CPU Usage high on SQL 2008

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by nexlogic, Aug 24, 2009.

  1. nexlogic New Member

    Hi there,
    We are in search for someone who can help optimize our SQL 2008 database on Windows 2008 server. CPU usage (sqlservr.exe) spikes up between 25% - 70% on a weekly basis, thus causing our website to run "sluggish".
    We have powerful hardware so we don't think that's the root.
    Can someone help? Thank you
  2. imSQrLy New Member

    the key to your problem is trying to find out what is happening when it spikes. I would suggest running a trace for long running queries during the peak time. Find out whats taking a long time and back track from there. How have you resolved the spikes to date?
  3. nexlogic New Member

    Hi there. It magically stops and works "normally" again. It's very bizzare. How would one run a trace? Thank you
  4. MohammedU New Member

    If there is a pattern in the spike check it out what is happenning exactly at that time by running the sql trace as mentioned and also check if there are any maintenance/backup and/or batch jobs running at that time...
  5. vikasrajput New Member

    Its very difficult to understand (and hence cure) the system by the problem statement that its working slow at times. What I would recommend:
    1. Establish if the issue is really because of the SQL Server itself or something else. I would start with setting up a light weight perfmon onto the system which will gather the stats throughout the day. Setup for disk utilization & queue length, CPU & memory utilization, users logged on, memory used by SQL Server, network throughput and some other basic perfmon counters. Might well be the case that underlying Disks are developing large queues at some point. Server / Application logs are also a good place to look at, watch out for exceptions which are related to SQL Server service. Also, when the issue is popping, have a look within task manager to see what all components are taking highest amount of memory and CPU - keep a track of these if possible.
    2. If you cant make out anything from above, then have SQL Server profiler run at different point of times. Its not that having SQL Server profile run doesnt make sense if server is working fine. You can use this run trace to establish how server and different components are working when its working fine. Good chances are that you will capture an instance when server is sluggish. And once you eye mark specific area (SP, table, index, disk.. . etc) then bring those hawk's eyes to break it.
    In built util's for SQL Server are more than enough to make out the real performance issue. Perfmon and SQL Server profile, use this combination and am sure you will be able to crack it. Also, I would definitely suggest trying what MohammedU has suggested when your server is in midst of the trouble.
  6. gurucb New Member

  7. Ajeet Rai New Member

    Hi All,
    I used below quarry it is really amazing,It summaries the all sql statement along with cpu usages.
    SELECTTOP50[Average CPU used]= total_worker_time / qs.execution_count,[Total CPU used]= total_worker_time,[Execution count]= qs.execution_count,[Individual Query]= SUBSTRING (qt.text,qs.statement_start_offset/2,(CASEWHEN qs.statement_end_offset =-1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))*2ELSE qs.statement_end_offset END- qs.statement_start_offset)/2),[Parent Query]= qt.text ,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qs CROSSAPPLY sys.dm_exec_sql_text(qs.sql_handle)as qt ORDERBY[Average CPU used]DESC;

Share This Page