Top SQL objects consuming CPU

Question

Top SQL objects consuming CPU

nagakula

Newbie

Points: 3
More actions
September 19, 2015 at 7:19 pm

#300763

hi Team,
We are trouble shooting a SQL Server high CPU issue and want to know what statements are the expensive ones in terms of CPU usage (basically OpenText is issuing auto generated queries to SQL server). I have used DMV queries to find out the total_worker_time, last_worker_time, logical_reads etc.. but it is giving the percentage since it is last compiled which is not so good estimate.
I am looking for the last one hour or so, is there a way that we can calculate the CPU usage in terms of percentage and pin point the SQL object consuming highest CPU?
Also I have used sql profiler and saw CPU column which is showing values similar to 23,500 and based on this I am not sure how we can calculate the percentage based on this.

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

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

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

nagakula (9/19/2015)
hi Team,
We are trouble shooting a SQL Server high CPU issue and want to know what statements are the expensive ones in terms of CPU usage (basically OpenText is issuing auto generated queries to SQL server). I have used DMV queries to find out the total_worker_time, last_worker_time, logical_reads etc.. but it is giving the percentage since it is last compiled which is not so good estimate.
I am looking for the last one hour or so, is there a way that we can calculate the CPU usage in terms of percentage and pin point the SQL object consuming highest CPU?
Also I have used sql profiler and saw CPU column which is showing values similar to 23,500 and based on this I am not sure how we can calculate the percentage based on this.

Open SSMS, press the {f8} key to open the Object Explorer, right click on the instance name, select REPORTS, and follow your nose.

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

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

You'll have to modify the query to get %, but it's not that hard. Probably should also be using extende events, not profiler in SQL 2012, but profiler does still work.

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

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

Jeff Moden (9/19/2015)
nagakula (9/19/2015)
hi Team,
We are trouble shooting a SQL Server high CPU issue and want to know what statements are the expensive ones in terms of CPU usage (basically OpenText is issuing auto generated queries to SQL server). I have used DMV queries to find out the total_worker_time, last_worker_time, logical_reads etc.. but it is giving the percentage since it is last compiled which is not so good estimate.
I am looking for the last one hour or so, is there a way that we can calculate the CPU usage in terms of percentage and pin point the SQL object consuming highest CPU?
Also I have used sql profiler and saw CPU column which is showing values similar to 23,500 and based on this I am not sure how we can calculate the percentage based on this.
Open SSMS, press the {f8} key to open the Object Explorer, right click on the instance name, select REPORTS, and follow your nose.

The reports use the same DMVs that the OP doesn't want to use because they're since the last compile, not a time frame.

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

Jeff Moden SSC Guru Points: 1004686 More actions · Answer 4

GilaMonster (9/20/2015)
The reports use the same DMVs that the OP doesn't want to use because they're since the last compile, not a time frame.

Damn... I keep forgetting that because the systems that I'm working on have a bit limited memory and there's a batch job that drives everything out of cache at about 4:30AM every morning. Thanks for the correction, Gail.

--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: 1004514 More actions · Answer 5

Jeff Moden (9/20/2015)
GilaMonster (9/20/2015)
The reports use the same DMVs that the OP doesn't want to use because they're since the last compile, not a time frame.
Damn... I keep forgetting that because the systems that I'm working on have a bit limited memory and there's a batch job that drives everything out of cache at about 4:30AM every morning. Thanks for the correction, Gail.

Beats a system I was working on recently where nothing lived in cache for longer than 25 minutes due to the sheer volume of unparameterised ad-hoc queries.

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