90% CPU utilization on SQL Server 2008

Question

Hi,

I am using SQL Server 2008 at the backend.

The machine on which SQL Server is running has 24 cores with 64 GB RAM.

I see 90% + CPU utilization for SQL Server when in running state.

I generated some reports from Management Studio.

There are no blockers and the CPU utilization of the queries is not that much.

For Server dashboard it shows me that most of the CPU is used by Ad-hoc queries.

I tried the same application with Oracle and max CPU utilization for Oracle is 30% (on same machine configuration)

Can you please let me know what the tuning parameters?

Answer 1

First you have to identify the heaviest statements and start the troubleshooting from there. Start with below script to identify them. That will help you to see where to start your troubleshooting.

http://sql-javier-villegas.blogspot.com.ar/2011/12/get-top-queries-by-cpu-time_27.html

http://sql-javier-villegas.blogspot.com.ar/2011/11/get-cpu-usage-history.html

Are you doing maintenance (Index Rebuild , Update Statistics)

http://sql-javier-villegas.blogspot.com.ar/2012/01/handling-indexes-fragmentation.html

http://sql-javier-villegas.blogspot.com.ar/2012/01/check-statistics.html

Also check if you are having a bottleneck on the I/O system. You can user Perfmon

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Hi Javier,

with your stuff is showing some scripts...it really nice.

But i have some Quries..

What we have to take 1st step to Re-Build the index & Update the statiscs ?

In which situvation we have to re- build the index. & Update the staticis

In case i have found the one quiery OR Sp is taking long time and long IO at this situvation what actions we have to take.. can you pleas guide with good stuff.

Normally we found those things we will kill that ID's right, i hope this did any body but as DBA what actions we have to chekc..

Long running jobs and SP every body kill that ID's right. any more good suggestions really appriciate to you

Thanks in advance..

Tx

---

subu

Answer 3

Hello,

You could start by rebuilding the indexes.

To troubleshoot the long running statements, you can use Adam Machanic's SP_WhoIsActive. That will provide you all the necessary info to identify the heaviest statements while they are running

http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx

Try to identify the statements and run them on a testing environment so you can check for missing indexes, execution plan, etc. 

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

Hi Javier,

 Its really good information.

Thanks you very much for your reply..

---

subu

Answer 5

Hi Chetan Punekar,

>> For Server dashboard it shows me that most of the CPU is used by Ad-hoc queries.
If there are a large number of ad-hoc queries, which are not included into stored procedures or forced to prepared statements, in the application, it will lead to not only high CPU but a high memory, since SQL Server is attempt to compile and generate a new execution plan every time while you invoke the ad-hoc queries, instead of reuse.  For more information: Stolen Pages, Ad-hoc queries and the sins of dynamic SQL in the application.

---

Stephanie Lv

TechNet Community Support

Answer 6

---This first thing to check if CPU is at 100% is to look for parallel queries:

-- Tasks running in parallel (filtering out MARS requests below):
select * from sys.dm_os_tasks as t
 where t.session_id in (
   select t1.session_id
    from sys.dm_os_tasks as t1
   group by t1.session_id
  having count(*) > 1
  and min(t1.request_id) = max(t1.request_id));

-- Requests running in parallel:
 select *
   from sys.dm_exec_requests as r
   join (
           select t1.session_id, min(t1.request_id)
          from sys.dm_os_tasks as t1
         group by t1.session_id
        having count(*) > 1
           and min(t1.request_id) = max(t1.request_id)
      ) as t(session_id, request_id)
     on r.session_id = t.session_id
    and r.request_id = t.request_id;

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 7

Thanks a lot guys for your answers.

I was using SQL Server 2008 RTM.

I used sqldiag manager to collect data for SQL Server and later used one tool called SQL Nexus  (http://sqlnexus.codeplex.com/) to view the collected data.

The reports generated show that there are major bottlenecks at the SQL Server side.

For eg: in my case they were:

• XProc
• PREEMPTIVE_OS_GETPROCADDRESS

I switched to SQL Server 2008 R2 and the CPU utilization is now lower than Oracle. :)

Thanks again for your help, really appreciate.

Answer 8

Hi folks, I am back :)

The CPU utilization was low for my 1st run with SQL Server 2008 R2.

But for the second run, it is again shooting too much. 

I executed all the scripts mentioned above, but it is difficult to come to any conclusion.

I still see the max cpu utilization due to Ad-hoc queries.

I referred this link - http://www.simple-talk.com/community/blogs/philfactor/archive/2009/08/03/74227.aspx

Will I need to tweak the database?

If yes, what are those?

Thanks in advance.

Answer 9

Hi,

One thing you could do if you have mostly ad hoc queries is to Something quick you could try is to enable 'Optimise for ad hoc workloads':

EXEC sp_configure 'Show Advanced Options', 1

RECONFIGURE

EXEC sp_configure 'Optimize for ad hoc workloads', 1

RECONFIGURE

This means that ad hoc queries don't get compiled the first time they are run, which means SQL should spend less time overall compiling.

Really, you should look at wait stats before going down this route - but there aren't really any drawbacks to this setting in my opinion and it's easy to turn on or off to do a quick test.

Ben

Answer 10

HI Javier,

you have posted wonderful scripts and very helpful..I am looking on your script to get CPU Usage history.and it gives me past 2 hr history..can you help me in script to get like past 1 day or 2 day or past week history?

http://sql-javier-villegas.blogspot.com.ar/2011/11/get-cpu-usage-history.html

Thanks

---

Ankit Shah SQL Server DBA

Answer 11

Hi Ben,

Sorry for the late reply.

I tried the mentioned settings, dint work for me :(

Is there any other thing?

Thanks,

Chetan

Answer 12

One quick thing you could try is executing

sp_updatestats

on the database in question. If you have inefficient parallel plans due to out of date statistics this should help.

Ben

Answer 13

Hi Ben,

I executed the sp_updates

All the results were same (no updates)

Sample - "0 index(es)/statistic(s) have been updated, 3 did not require update"

Answer 14

Hi Ben,

I found some horrible wait stats:

wait_type	waiting_tasks_count	wait_time_ms	max_wait_time_ms	signal_wait_time_ms
MSQL_XP	6330872	263054558	1536	0
PREEMPTIVE_OS_GETPROCADDRESS	6330872	262985110	1536	0
WRITELOG	4334886	59146088	589	4684646
LAZYWRITER_SLEEP	119467	23591518	3727550	106210

Are the highlighted waits causing the high CPU utilization?

-Chetan

Answer 15

Hi Javier,

can you please give a step to reduse the CPU utilization. my production server CPU went almost 95 to 100% .

Thanks,

Pinakin