Query for top 10 CPU utilization

Question

Query for top 10 CPU utilization

Satish S

Mr or Mrs. 500

Points: 580
More actions
January 22, 2014 at 7:09 am

#287541

Hi All,
Below query is using for find the top 10 query which are utilizing CPU.
select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc
But i have multiple instance and multiple database so my requirement is in particular instance on particular Database top 10 CPU utilization queries are required...
Thanks in Advance..
Satish

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

That query does not get you the top 10 CPU-using queries.

It gets you a (fairly meaningless) set of the top 10 wait types. I say meaningless, because it hasn't filtered out the waits that aren't a problem.

What query are you using to get queries using high CPU?

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: 398886 More actions · Answer 2

You can look at sys.dm_exec_query_stats to get an idea of CPU use on queries. But, that DMV is dependent on what is currently in cache. So queries that have aged out of cache or were never in the cache won't be included. That means it's a less than perfect measure.

"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

Satish S Mr or Mrs. 500 Points: 580 More actions · Answer 3

Hi Gail,

Same query using which i shared, could you current me if i wrong or advice me which query shall i use..

Thanks

Satish

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

satish.saidapur (1/22/2014)
Hi Gail,
Same query using which i shared, could you current me if i wrong or advice me which query shall i use..

The query you shared retrieves wait statistics, not query performance information.

What query are you using to retrieve the top 10 CPU-using queries on the instance?

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) Search web for Glenn Berry SQL Server Diagnostic Query. Learn to use the goodness therein.

2) From his version for 2012 you will find this:

-- Top Cached SPs By Total Worker time (SQL Server 2012). Worker time relates to CPU cost (Query 47) (SP Worker Time)

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Satish S Mr or Mrs. 500 Points: 580 More actions · Answer 6

Satish S

Mr or Mrs. 500

Points: 580

January 23, 2014 at 3:12 am

#1682683

Thanks Kevin... Its working fine...

mike.gallamore Mr or Mrs. 500 Points: 563 More actions · Answer 7

The query Kevin added seems to work for SQL 2008 too. Particularly like the calls per minute break down helps compare things that might have been cached a different amount of time.