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

Question

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

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

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.

You didn't answer Grant's question. Which are you looking for a 2000 script for?

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

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

Grant Fritchey (10/5/2010)
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?

Yes, a similar listing of events inside of the 2000 trace events that would replicate the default trace for SQL Server 2000.

Thank you

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

The trace events haven't changed names. To semi-replicate the default trace on SQL 20p00, create a server-side trace with the same events (hint, get profiler to create the script) then create a startup procedure to start the trace whenever SQL starts. You'll also need a job or similar to delete older files.

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: 398890 More actions · Answer 4

Exactly what Gail says. You can use the list she provided earlier as the basis for your server side trace.

"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