How to: Check which query causing the most cpu usage?

Question

How to: Check which query causing the most cpu usage?

monkeyDBA

Default port

Points: 1457
More actions
September 29, 2010 at 2:49 am

#157228

I encountered SQL Server time-out issue, and I could not even login to the server using MSSMS. I checked the even log, it shows idle hit 95%. The issue resolved after I restart the SQL Server services. I am currently doing RCA (root cause analysis) for this issue.
I wish to find out which SQL queries run and causing the performance issue.
Can any one advice me on this?
Your help is highly appreciated. Thanks.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

Viewing 15 posts - 1 through 15 (of 18 total)

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

NickDBA Hall of Fame Points: 3792 More actions · Answer 1

If this was an AdHoc query it may be difficult to spot. If this is caused from "normal" routine usage then I suggest you run a SQL Profiler trace and try use it to identify statements / procedures with high CPU time values, then you'll have a good idea of which stored procedures / statements need looking at.

monkeyDBA Default port Points: 1457 More actions · Answer 2

Is that possible if we could execute any t-sql to show a list of quries/transaction that caused high cpu usage?

Just an idea pop up from my mind may be we could get it from sql log. 😀

Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

NickDBA Hall of Fame Points: 3792 More actions · Answer 3

SELECT TOP 50

SUM(QS.TOTAL_WORKER_TIME) AS TOTAL_CPU_TIME,

SUM(QS.EXECUTION_COUNT) AS TOTAL_EXECUTION_COUNT,

COUNT(*) AS NUMBER_OF_STATEMENTS,

SQL_TEXT.TEXT,

QS.PLAN_HANDLE

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT

GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER

BY SUM(QS.TOTAL_WORKER_TIME) DESC

This query will show you top 50 CPU consuming statements. Its not my own work, I aquired from the web some time ago. It may help you diagnose / spot the offending statement

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

That query is dependent on the queries' plans still being in cache, which means no restarts of the SQL process.

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

NickDBA Hall of Fame Points: 3792 More actions · Answer 5

Excellent point. I think he did restart the service.

I would still advise running a trace to try and identify timeout issues, may not necessarily be a soley CPU issue.

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 6

Please check the default trace file stored in errorlog folder.

To find your default trace

SELECT *

FROM fn_trace_getinfo(default);

GO

USe the following function to dump the trace details into a table

SELECT * FROM ::fn_trace_gettable('traceFileName', DEFAULT)

Probably can be of somehelp your root cause analysis..

Regards,
Raj

http://Strictlysql.blogspot.com

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

arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful information
like CPU, starttime etc. .. Probably can help your root cause analysis

The default trace does not contain any information on queries run. It contains only the following events:

Most of the events in the Audit category

Data File Auto Grow

Data File Auto Shrink

Database Mirroring State Change

ErrorLog

Full text crawl related events

Hash Warning

Log File Auto Grow

Log File Auto Shrink

Missing Column Statistics

Missing Join Predicate

Object:Altered

Object:Created

Object:Deleted

Plan Guide Unsuccessful

Server Memory Change

Sort Warnings

There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.

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

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 8

my mistake. Thanks for correcting

Regards,
Raj

http://Strictlysql.blogspot.com

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

Just to reiterate, since you bounced the server, there's no real recourse for identifying an issue other than the error logs. That is, unless you put some type of monitoring in place ahead of time. At this point, you're probably going to have to write this one down as unknown.

"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

monkeyDBA Default port Points: 1457 More actions · Answer 10

Hi all,

I really appreciated with all your comments, advice and help. It helps me a lot. I do not realize that it can be done. It really impressive. Now I know anything if one can think of, it can be done. The future is no limits.

Again, thank you all very much! 😀

Have a nice day.

Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

tt-615680 SSCertifiable Points: 7835 More actions · Answer 11

GilaMonster (9/29/2010)
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful information
like CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.

I was wondering if there is a script for sql 2000 that would do the same job please.

Thank you

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 12

tt-615680 (10/5/2010)
GilaMonster (9/29/2010)
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful information
like CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.
I was wondering if there is a script for sql 2000 that would do the same job please.
Thank you

Which, do the same as the default trace or capture RPC:Complete & SQL:BatchComplete events?

"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

tt-615680 SSCertifiable Points: 7835 More actions · Answer 13

Grant Fritchey (10/5/2010)
tt-615680 (10/5/2010)
GilaMonster (9/29/2010)
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful information
like CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.
I was wondering if there is a script for sql 2000 that would do the same job please.
Thank you
Which, do the same as the default trace or capture RPC:Complete & SQL:BatchComplete events?

Yes, I know there are many queries or sysyem sp's that cannot be used in sql 2000.

Thank you

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 14

tt-615680 (10/5/2010)
Grant Fritchey (10/5/2010)
Which, do the same as the default trace or capture RPC:Complete & SQL:BatchComplete events?
Yes, I know there are many queries or sysyem sp's that cannot be used in sql 2000.
Thank you

Sorry, I'm still confused what you're looking for. Do you want A) a similar listing of events inside of the 2000 trace events that would replicate the default trace for SQL Server 2000, or B) A method of capturing query trace events within SQL Server 2000?

"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