CPU always utilize 60% constantly?

Question

CPU always utilize 60% constantly?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
February 4, 2014 at 11:59 pm

#289200

Hi,
CPU always utilize 60 % constantly, attached here execution plan.
I checked execution plan handle CompileTime="9" CompileCPU="9" = 81 Entries for that query, is it 81 times new entry and create new plan? But plan shows total number of 33 entries. I have doubt Please confirm.
I checked that index stats everything good indicate but index scan cost is more than 90%, pls. guide me how to reduce that cost?
Total fragmentation – less than 2 %
Update stats – up to date
Thanks
ananda

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is this query really a problem?

9ms of CPU time to compile isn't large and the cost % alone is pretty meaningless, the costs have to add to 100% over the entire plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Query is not a problem also facing any performance issues from user side but CPU always utilize 60 % constantly.

why index scan cost always 90% at every exectuion? please suggestion me, how to reduce the index scan cost? even frag., stats are under limited precentage and up to date.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ananda.murugesan (2/5/2014)
Query is not a problem also facing any performance issues from user side but CPU always utilize 60 % constantly.

If the query isn't a problem and you don't have performance problems, why are you wasting time looking at this query?

why index scan cost always 90% at every exectuion? please suggestion me, how to reduce the index scan cost?

Again, why? Costs must add to 100%, the cost has to be somewhere and you've already stated the query isn't a problem. Why are you then spending time investigating something which isn't a problem?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Resolved CPU issues after changing few steps, now CPU usages 12 %

1. Adding missing index some tables which are not created before nonclustered index

2. Changed the default setting cost threshold parallelism and Maxtop after that CXPACKET wiat time reduced as well as CPU is normal not more than 12%.

Thanks

ananda

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

On any query while looking at the execution plan, the costs will add up to about 100% (sometimes a little less, occasionally a lot more, it's a bug). So seeing a high cost in the plan, in an of itself, is not an indication of anything. You need to understand, within that single plan, why the cost is high. It may or may not be an issue.

You say you changed MAXDOP, what did you change it to? Not 1 hopefully. That completely eliminates all parallel queries. A general suggestion would be to set it to one less than the number of cores on the system (general guideline, not a precise recommendation). But good job on raising the cost threshold for parallelism. The default value is way to low for any system.

I would suggest going about your performance tuning in a more methodical manner. Just based on your posts you were jumping from idea to idea without really seemingly connecting them to the problem at hand.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning