| 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 |
|
|
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. |
 |
|
|
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 |
|
|
|
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%. |
|
|
|
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. |
|
|
|
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 |
|
|
|
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. |
|
|
|
|
High CPU