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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poratips
Posting Yak Master

104 Posts

Posted - 05/05/2013 :  00:06:43  Show Profile  Reply with Quote
Hi,
We are experiencing a high CPU consumption issue, it's almost 80 - 90% and sometimes close to 100%.
I am thinking to try to Rebuild the indexes as lots of indexes having fragmentation issue, I will be also updating the statistics and Recompiling the objects to improve Stored Procedure.
Do you suggest anything which can help to identify the CPU issue and resolve it?

Thanks,

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2032 Posts

Posted - 05/05/2013 :  08:33:30  Show Profile  Visit jackv's Homepage  Reply with Quote
Witout doing an in depth root cause analysis - checking for parallel queries is a good way forward if CPU constantky high.
use these scripts to identify high cou usage tasks - http://www.sqlserver-dba.com/2013/04/cpu-100-check-for-parallel-queries.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 05/05/2013 :  15:56:53  Show Profile  Reply with Quote
Thanks Jack. I see your query and try to implement that query and see the results. We have 8 cpus, I am little concern to use CXPACKET.
Do you think any other tips?
I am watching also Performance Monitor and mostly it shows SqlServer.exe causing high cpu.
I am also running sql to check any sql is causing that or not.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2032 Posts

Posted - 05/06/2013 :  05:45:13  Show Profile  Visit jackv's Homepage  Reply with Quote
CXPACKET can be normal if multiple CPUs exist. It depends also on the type of queries the system is running, for example if you're running reporting queries , CXPACLETS are caused by parallel queries , waiting to finish.

If you're running high volume , short transactions than CXPACKET can be an issue .



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 05/08/2013 :  13:19:38  Show Profile  Reply with Quote
Thanks Jack. We are running more reporting queries but as well as some high volume transaction queries also. High CPU is almost 95% - 100% during morning as we have ETL job going but through out the day we having also seen most of the times CPU is more than 75%.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/08/2013 :  14:08:52  Show Profile  Visit russell's Homepage  Reply with Quote
Most common cause of high CPU is bad queries and/or missing indexes. You need to examine the queries and fix the most expensive ones.

Can start with the built in system reports, but probably you need to run a trace to identify the poorly performing queries. Look for those with the highest CPU and I/O consumption.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/08/2013 :  14:17:59  Show Profile  Visit chadmat's Homepage  Reply with Quote
Don't need a trace, just query sys.dm_exec_query_stats, and order by worker time (Last, Max or total) DESC. Use sys.dm_exec_sql_text to get the query, and sys.dm_exec_query_plan to get the Execution Plan of the highest CPU queries.

-Chad
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 05/08/2013 :  14:25:00  Show Profile  Reply with Quote
Thanks Chad and Russell.
I have ran the query to find out the longest running query and which has most worker time also.
I found that one of the DB is consuming most CPU and some of the queries are coming from this application/DB while running TOP SQL for IO and CPU costly script.
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.08 seconds. Powered By: Snitz Forums 2000