SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 High CPU utilisation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranvir_2k
Posting Yak Master

United Kingdom
174 Posts

Posted - 11/27/2013 :  10:10:35  Show Profile  Reply with Quote
Hi all,

I'm seeing high CPU usage on my SQL Server box. It often goes up to 90% and SQL Server is the culprit.

This server is the subscriber node of a merge replication set up.

When I run sp_who2 I can see no active running processes. So why is SQL Server responsible for such high CPU utilization?

What could be the issue? How can I investigate this further?

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 11/27/2013 :  13:01:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
These are the typical issues on my systems with high CPU:

1. Missing indexes
2. Inefficient code
3. Out-of-date statistics
4. Bad execution plan (see #1 and #2)
5. Insufficient memory, causing physical IOs rather than getting them from memory

Regarding sp_who2...run a server-side trace to capture the load and then sort it by Duration DESC and then another query to sort it by Reads DESC. You are likely going to find very high reads. How do I define high reads? Well it depends on the system, but I definitely work on the ones with reads into the hundreds of thousands or millions. When I run a trace, I often will only capture Reads>5000.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Paul J. Brooks
Starting Member

USA
7 Posts

Posted - 12/05/2013 :  06:44:51  Show Profile  Reply with Quote
This is very important information for every one and beneficial.I wanna discuss on this topic so please any one interested me so please reply me.Thanks for sharing this information.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000