CPU question

Question

CPU question

mxy

SSCarpal Tunnel

Points: 4034
More actions
January 8, 2016 at 12:33 pm

#399540

I have few questions about CPU details displayed in SET statistics TIME ON. Query runs fast, what does CPU time represent. Can we convert that value to a percentage used in total CPU on SQL instance?
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 444 ms.
Is there any threshold value that we can CPU usage by query is normal?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

mxy (1/8/2016)
I have few questions about CPU details displayed in SET statistics TIME ON. Query runs fast, what does CPU time represent. Can we convert that value to a percentage used in total CPU on SQL instance?
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 444 ms.
Is there any threshold value that we can CPU usage by query is normal?
Thanks

No. You can't really convert the output of STATISTICS IO to Percent of CPU especially since it's not likely that your query used all the CPU's on the box due to parallelism.

As for your second question, there is not threshold to determine if the CPU usage by a query is "normal". The best tool is your brain and a very good understanding of the execution plan. For existing queries, you might also try one of the performance related reports that you can get to by right clicking on the instance name in SSMS and following your nose or using something like Adam Machanic's sp_WhoIsActive.

--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)

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

mxy (1/8/2016)
Query runs fast, what does CPU time represent.

It's the number of milliseconds that the query spent actually executing (as opposed to

Can we convert that value to a percentage used in total CPU on SQL instance?

Not without an insane amount of work and not without knowing on average how often the query runs in a normal workload. It's not really worth doing tbh.

Is there any threshold value that we can CPU usage by query is normal?

No. A single table query will probably be in the tens of ms, while a complex multi-table query with aggregations may take seconds.

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

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 3

If your query is still in the cache then you might be lucky enough to find it in this DMV sys.dm_exec_query_stats

And then by looking at creation_time, execution_count and total_worker_time columns you can get some ideas of how much CPU you query is consuming, but there is no easy way to translate this info into % of CPU used in total by the query.

Alex Suprun