Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 High CPU

Author  Topic 

poratips
Posting Yak Master

105 Posts

Posted - 2013-05-05 : 00:06:43
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-05 : 08:33:30
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

105 Posts

Posted - 2013-05-05 : 15:56:53
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-06 : 05:45:13
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

105 Posts

Posted - 2013-05-08 : 13:19:38
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

5072 Posts

Posted - 2013-05-08 : 14:08:52
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

1974 Posts

Posted - 2013-05-08 : 14:17:59
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

105 Posts

Posted - 2013-05-08 : 14:25:00
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
   

- Advertisement -