Monitor CPU Usage by Query?

by **quixster** » Thu, 02 Nov 2006 00:57:43 GMT

We are currently running MS SQL 8.0 on Windows 2003.  I've been tasked
setting up a monitor that can identify queries that are causing CPU
spikes.

Does anyone have any suggestions about going about this?  I'm at a
complete loss.

by **TmV0bW9u** » Thu, 02 Nov 2006 04:21:02 GMT

The Profiler can do this.  Create a new trace using the SQLProfilerStandard 
trace template and add the CPU column. This will show both the query and the 
CPU time, plus some other stuff.  If this is a super busy production server 
this can degrade performance so you might want to play with it on a test db 
instance first.  I typically run it from my desktop or from a remote desktop 
and save the results to a local file cause if you write the result to the db 
you are monitoring it too can impact your findings.

Netmon

by **quixster** » Thu, 02 Nov 2006 05:10:53 GMT

Thanks for the tip!  Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold?  I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert.  Any thoughts?

by **tootsuite** » Thu, 02 Nov 2006 05:32:44 GMT

You can also use this query to see what's using the CPU - highest to
lowest usage:

SELECT * FROM master..sysprocesses ORDER BY cpu DESC

Then, run this to see the actual query:

DBCC INPUTBUFFER (spid) -- spid being the # in the spid column from the
above query

hth

by **tootsuite** » Thu, 02 Nov 2006 05:41:49 GMT

You can also use the windows tool PerfMon on the server and set up your
own custom traces which can viewed realtime or logged to files and
replayed.

by **TmV0bW9u** » Thu, 02 Nov 2006 06:30:01 GMT

No the trace is not going to do that.  I'v heard that NetIQ has a product 
that monitors for query execution but I'm not sure of the details on how it 
works.

Like the other person (tootsuite) posted you could use perfmon however it 
does not take sustained thresholds, that is if CPU > 90% over 60 seconds then 
alert.  So anytime the CPU is above your threshold it will alert even if it 
is a short lived spike you get alerted.

I'm not sure the context within which you want to filter the trace to 
generate an alert.  If you have any enterprise server monitoring tools like 
ProIT, MOM or Nimbus available you could just monitor the CPU via those and 
have it email your admin.

Are you hoping to find the rogue/cpu intense queries in action? Or are you 
desiring to know when the CPU gets hammered so an admin can log in and kill 
the offending query or find out the user running it?

by **tootsuite** » Thu, 02 Nov 2006 07:32:19 GMT

erfmon is just a general monitoring tool.... you cannot see individual
queries.

Usually it works like this:

1. end users complain

2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)

3. isolate queries using Quest based on various factors

4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory

so it's not enough to just isolate cpu performance, as many factors are
usually involved

so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors

Netmon wrote:

by **quixster** » Thu, 02 Nov 2006 23:19:42 GMT

The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation.  We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.

by **tootsuite** » Fri, 03 Nov 2006 01:20:08 GMT

es, you can do this easily using the query I provided in an earlier
post.

SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query

it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt

Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution

hth

XXXX@XXXXX.COM wrote:

by **quixster** » Fri, 03 Nov 2006 05:34:40 GMT

hanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/

This has been immensely helpful!

On Nov 1, 11:20 am, XXXX@XXXXX.COM wrote:

by **THVjYXMgS2FydGF3aWRqYWph** » Wed, 22 Nov 2006 03:10:01 GMT

nother product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx

Lucas

" XXXX@XXXXX.COM " wrote:

Monitor CPU Usage by Query?

Monitor CPU Usage by Query?

Jump to:

Next

Monitor CPU Usage by Query?

RE: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Re: Monitor CPU Usage by Query?

Similar Threads:

Who is online