Analyzing CPU value in profiler.

Question

Analyzing CPU value in profiler.

Joy Smith San

SSC-Insane

Points: 24877
More actions
August 26, 2015 at 10:32 pm

#302283

Hi All,
There are 4 quad core CPUs with hyperthreading enabled (Total 32 processors) in one of the SQL Server.
To investigate one performance issue, I ran a trace for 6 hours and collected the data.
Fund one particular query which executed many times with a total of "487845467" CPU time. I assume this is in milliseconds.
So when I converted it into hours (487845467/1000)/60)/60 it comes to 135 hours.
What exactly is the meaning of it considering the 32 processors available ? Or is my calculation wrong by any chance ?
Kindly advise. Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

You're summing the CPU up over the 6 hours. In total, over 6 hour real period, that query did a total of 135 hours of CPU time, easy enough to do with 32 cores, but does suggest that it's one hell of a heavy CPU-consuming query.

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

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 2

I would definitely take a look at that query as a candidate for performance tuning.

"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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

32 CPUs * 6 hours = 192 hours available CPU time.

135/192 = 70.3125% of all the CPU time was dedicated to that one task.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Hi,

It doesn't return any records, but does a 10798 logical read every second.

Upon checking further, indexes are missing it seems. Especially on some foreign keys.

Currently application team is analyzing that query as per our recommendation.

Thanks a lot for clarifying. I may keep updated the status.

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

It's completely irrelevant how many rows the thing returns. It could well read half the DB to return no rows.

Don't just look at indexes (and indexing foreign key probably won't help unless there are updates or deletes that need to check the relationship). Analyse and tune the query/procedure.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 6

Thanks.

Me too checking the query parallel while app team comes back on this.

Checking with database Engine Tuning Adviser as well.

Thanks again Gila.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Joy Smith San (8/30/2015)
Checking with database Engine Tuning Adviser as well.

Be very careful when using the DTA. It should be taken with a very small grain of salt. It can give you some ideas but it will frequently give you some really, really bad suggestions for indexes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Sure..

Both Auto create statistics and Auto update statistics are ON.

But still it is recommending to create few statistics and indexes.

Won't go blindly by it anyway.

Thanks.

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

Don't use DTA, at best it'll over-index, at worst it could degrade performance.

The code needs to be tuned first, if it's written in a way that the query can't use indexes, no amount of index tuning will help. Look at the query/procedure, look at the execution plan, look at the per-statement statistics and tune it. Unless the app team are very good with SQL, it's unlikely they'll be able to do that.

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

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 10

Since we can't see the query we're only making vague suggestions, but while on that track, another thing to look at is your Cost Threshold for Parallelism. The default value of 5 is way too low. You should adjust that to a higher value like 50. That could also be negatively impacting your CPU use.

"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