Are there any good monitoring tools that would notify us upon CPU,Disk
space, blocking, errors/warnings in event logs,etc..
We use Sitescope and it doesnt seem to be a robust tool
Thanks
Hi,
Have a look into Microsoft operation manager (MOM). See the below URL for
details:-
http://www.microsoft.com/mom/default.mspx
Thanks
Hari
SQL Server MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:OESfJu$qFHA.460@.TK2MSFTNGP15.phx.gbl...
> Are there any good monitoring tools that would notify us upon CPU,Disk
> space, blocking, errors/warnings in event logs,etc..
> We use Sitescope and it doesnt seem to be a robust tool
> Thanks
>
|||There are a lot of different monitoring tools. MOM is one
that Hari mentioned. You may also want to look at some of
the tools from idera:
www.idera.com
-Sue
On Sun, 28 Aug 2005 11:08:46 -0700, "Hassan"
<hassanboy@.hotmail.com> wrote:
>Are there any good monitoring tools that would notify us upon CPU,Disk
>space, blocking, errors/warnings in event logs,etc..
>We use Sitescope and it doesnt seem to be a robust tool
>Thanks
>
|||Take a look at AgileInfoSoftware DataStudio, under its Performance
Monitoring node, it allows user to monitor process, lock, blocking locks
etc.
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:OESfJu$qFHA.460@.TK2MSFTNGP15.phx.gbl...
> Are there any good monitoring tools that would notify us upon CPU,Disk
> space, blocking, errors/warnings in event logs,etc..
> We use Sitescope and it doesnt seem to be a robust tool
> Thanks
>
|||Check out AgileInfoSoftware DataStudio http://www.agileinfollc.com
John King
AgileInfoSoftware
http://www.agileinfollc.com
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:OESfJu$qFHA.460@.TK2MSFTNGP15.phx.gbl...
> Are there any good monitoring tools that would notify us upon CPU,Disk
> space, blocking, errors/warnings in event logs,etc..
> We use Sitescope and it doesnt seem to be a robust tool
> Thanks
>
|||Sorry to bring back an old thread, but this is of great interest to me also.
I'm doing a trial deployment of MOM and finding it a bit more than we may
need. I like the extensibility via the management packs, but it may be too
big, too expensive and to tell the truth, a bit 'finicky' in setting up and
maintaining. Right now I have more alerts about MOM itself to figure out and
fix than the SQL servers we are attempting to monitor.
I'm a network engineer, not a SQL DBA. All I really want is something that
alerts when a job fails, lets us know if the SQL Agent isn't running and a
few other server statistics.
I'm going to look into Idera and Agileinfo.
Does anyone have any other recomendations? I know vendors like to throw in
the kitchen sink when it comes to features. In this case, for me, KISS is
more like my guiding principle.
Regards,
MK
"John King" wrote:
> Check out AgileInfoSoftware DataStudio http://www.agileinfollc.com
> John King
> AgileInfoSoftware
> http://www.agileinfollc.com
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:OESfJu$qFHA.460@.TK2MSFTNGP15.phx.gbl...
>
>
|||I really like Idera's Diagnostic Manager. Great third-party tool!
Lori
"Michael Kenny" <MichaelKenny@.discussions.microsoft.com> wrote in message
news:CA9FCD26-8BB7-4247-930F-2F803688233E@.microsoft.com...[vbcol=seagreen]
> Sorry to bring back an old thread, but this is of great interest to me
> also.
> I'm doing a trial deployment of MOM and finding it a bit more than we may
> need. I like the extensibility via the management packs, but it may be too
> big, too expensive and to tell the truth, a bit 'finicky' in setting up
> and
> maintaining. Right now I have more alerts about MOM itself to figure out
> and
> fix than the SQL servers we are attempting to monitor.
>
> I'm a network engineer, not a SQL DBA. All I really want is something that
> alerts when a job fails, lets us know if the SQL Agent isn't running and a
> few other server statistics.
> I'm going to look into Idera and Agileinfo.
> Does anyone have any other recomendations? I know vendors like to throw in
> the kitchen sink when it comes to features. In this case, for me, KISS is
> more like my guiding principle.
> Regards,
> MK
>
>
> "John King" wrote:
Showing posts with label cpu. Show all posts
Showing posts with label cpu. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Monitor i/o, cpu etc. without traces
Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.
I know about @.@.cpu, @.@.io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.
Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(
I realize that in general this is a ridiculous request, but I thought I would ask anyway.
Hello, Rottengeek.
Are you using SQL Server 2005? If so, you might want to use some of the new DMVs.
sys.dm_exec_query_stats gives you I/O and execution statistics for each plan. You can use the columns in this view to relate the plan handle and statement handle back to the actual SQL text. You'll find that the DMV has lots of neat data; each of the statistics carries a total, min and max value for the plan since it was last compiled.
If you checkout the dm_exec_sql_text() function in books online, it shows how to get the statement text given a sql_handle (one of the columns in dm_exec_query_stats) and has a couple of neat sample queries.
I hope that's enough to get you going in the right direction. If you have follow-up questions, please let me know.
.B ekiM
|||Below are some of the system functions/virtual tables that you can use in SQL Server 2000:
fn_virtualfilestats - To get I/O counters per database/file
sysprocesses
sysperfinfo - Perfmon counters
But whether you can access these or not depends on your permission levels. You can also take a look at the links below for other pointers:
http://support.microsoft.com/kb/298475/
http://support.microsoft.com/kb/243588/
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
|||
I'm on SQL Server 2000.
This was VERY helpful.
Unfortunately, I can't query sysperfinfo, however, I may be able to get a workaround by submitting a script to the DBA...who knows. At any rate, as a consultant (who is normally a DBA) these will come in VERY HANDY. One of the links instructs you how to create sp_blocker_pss80, which I will modify but will be much better than running a trace.
I'm typically pretty familiar with the system tables, but sysperfinfo I did not know about.
Thanks! If anyone else has some more ideas, I'd love to hear them...
Saturday, February 25, 2012
Monitor CPU Usage by Query?
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.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
"quixster@.gmail.com" wrote:
> 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.
>|||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?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> 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
>
> "quixs...@.gmail.com" wrote:
> > 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.- Hide quoted text -- Show quoted text -|||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
quixster@.gmail.com wrote:
> 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.|||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.
quixster@.gmail.com wrote:
> 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.|||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?
"quixster@.gmail.com" wrote:
> 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?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> > > 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.- Hide quoted text -- Show quoted text -
>|||Perfmon 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:
> 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?
> "quixster@.gmail.com" wrote:
> >
> > 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?
> >
> >
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > 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
> > >
> > >
> > >
> > > "quixs...@.gmail.com" wrote:
> > > > 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.- Hide quoted text -- Show quoted text -
> >
> >|||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.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> 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?
>
> "quixs...@.gmail.com" wrote:
> > 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?
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > 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
> > > "quixs...@.gmail.com" wrote:
> > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Yes, 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
quixster@.gmail.com wrote:
> 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.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > 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?
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> >
> > > 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?
> >
> > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > 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
> >
> > > > "quixs...@.gmail.com" wrote:
> > > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Thanks 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, tootsu...@.gmail.com wrote:
> Yes, 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
>
> quixs...@.gmail.com wrote:
> > 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.
> > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > 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?
> > > "quixs...@.gmail.com" wrote:
> > > > 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?
> > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > 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
> > > > > "quixs...@.gmail.com" wrote:
> > > > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks 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, tootsu...@.gmail.com wrote:
> > Yes, 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
> >
> >
> >
> > quixs...@.gmail.com wrote:
> > > 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.
> >
> > > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > 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?
> >
> > > > "quixs...@.gmail.com" wrote:
> >
> > > > > 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?
> >
> > > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > > 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
> >
> > > > > > "quixs...@.gmail.com" wrote:
> > > > > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -
>
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.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
"quixster@.gmail.com" wrote:
> 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.
>|||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?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> 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
>
> "quixs...@.gmail.com" wrote:
> > 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.- Hide quoted text -- Show quoted text -|||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
quixster@.gmail.com wrote:
> 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.|||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.
quixster@.gmail.com wrote:
> 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.|||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?
"quixster@.gmail.com" wrote:
> 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?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> > > 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.- Hide quoted text -- Show quoted text -
>|||Perfmon 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:
> 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?
> "quixster@.gmail.com" wrote:
> >
> > 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?
> >
> >
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > 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
> > >
> > >
> > >
> > > "quixs...@.gmail.com" wrote:
> > > > 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.- Hide quoted text -- Show quoted text -
> >
> >|||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.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> 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?
>
> "quixs...@.gmail.com" wrote:
> > 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?
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > 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
> > > "quixs...@.gmail.com" wrote:
> > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Yes, 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
quixster@.gmail.com wrote:
> 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.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > 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?
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> >
> > > 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?
> >
> > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > 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
> >
> > > > "quixs...@.gmail.com" wrote:
> > > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Thanks 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, tootsu...@.gmail.com wrote:
> Yes, 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
>
> quixs...@.gmail.com wrote:
> > 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.
> > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > 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?
> > > "quixs...@.gmail.com" wrote:
> > > > 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?
> > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > 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
> > > > > "quixs...@.gmail.com" wrote:
> > > > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks 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, tootsu...@.gmail.com wrote:
> > Yes, 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
> >
> >
> >
> > quixs...@.gmail.com wrote:
> > > 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.
> >
> > > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > 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?
> >
> > > > "quixs...@.gmail.com" wrote:
> >
> > > > > 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?
> >
> > > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > > 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
> >
> > > > > > "quixs...@.gmail.com" wrote:
> > > > > > > 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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -
>
Monitor CPU Usage by Query?
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.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
"quixster@.gmail.com" wrote:
> 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.
>|||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?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The Profiler can do this. Create a new trace using the SQLProfilerStandar
d
> trace template and add the CPU column. This will show both the query and t
he
> CPU time, plus some other stuff. If this is a super busy production serve
r
> this can degrade performance so you might want to play with it on a test d
b
> instance first. I typically run it from my desktop or from a remote deskt
op
> 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
>
> "quixs...@.gmail.com" wrote:
>|||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
quixster@.gmail.com wrote:
> 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.|||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.
quixster@.gmail.com wrote:
> 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.|||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 the
n
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?
"quixster@.gmail.com" wrote:
> 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?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>|||Perfmon 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:[vbcol=seagreen]
> 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 i
t
> 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 t
hen
> alert. So anytime the CPU is above your threshold it will alert even if i
t
> 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 lik
e
> ProIT, MOM or Nimbus available you could just monitor the CPU via those an
d
> 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 kil
l
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
>|||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.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> 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 i
t
> 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 t
hen
> alert. So anytime the CPU is above your threshold it will alert even if i
t
> 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 lik
e
> ProIT, MOM or Nimbus available you could just monitor the CPU via those an
d
> 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 kil
l
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
>
>
>
>
>|||Yes, 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
quixster@.gmail.com wrote:[vbcol=seagreen]
> 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.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:|||Thanks 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_s...ver_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, 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
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
>
>
>
>
>
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.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
"quixster@.gmail.com" wrote:
> 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.
>|||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?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The Profiler can do this. Create a new trace using the SQLProfilerStandar
d
> trace template and add the CPU column. This will show both the query and t
he
> CPU time, plus some other stuff. If this is a super busy production serve
r
> this can degrade performance so you might want to play with it on a test d
b
> instance first. I typically run it from my desktop or from a remote deskt
op
> 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
>
> "quixs...@.gmail.com" wrote:
>|||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
quixster@.gmail.com wrote:
> 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.|||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.
quixster@.gmail.com wrote:
> 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.|||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 the
n
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?
"quixster@.gmail.com" wrote:
> 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?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>|||Perfmon 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:[vbcol=seagreen]
> 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 i
t
> 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 t
hen
> alert. So anytime the CPU is above your threshold it will alert even if i
t
> 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 lik
e
> ProIT, MOM or Nimbus available you could just monitor the CPU via those an
d
> 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 kil
l
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
>|||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.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> 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 i
t
> 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 t
hen
> alert. So anytime the CPU is above your threshold it will alert even if i
t
> 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 lik
e
> ProIT, MOM or Nimbus available you could just monitor the CPU via those an
d
> 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 kil
l
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
>
>
>
>
>|||Yes, 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
quixster@.gmail.com wrote:[vbcol=seagreen]
> 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.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:|||Thanks 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_s...ver_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, 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
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
>
>
>
>
>
Monitor CPU Usage by Query?
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
"quixster@.gmail.com" wrote:
> 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.
>
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?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> 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
>
> "quixs...@.gmail.com" wrote:
|||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?
"quixster@.gmail.com" wrote:
> 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?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>
|||Perfmon 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:[vbcol=seagreen]
> 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?
> "quixster@.gmail.com" wrote:
|||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.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> 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?
>
> "quixs...@.gmail.com" wrote:
>
>
|||Yes, 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
quixster@.gmail.com wrote:[vbcol=seagreen]
> 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.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
|||Thanks 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, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, 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
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks 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, tootsu...@.gmail.com wrote:
>
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
"quixster@.gmail.com" wrote:
> 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.
>
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?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> 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
>
> "quixs...@.gmail.com" wrote:
|||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?
"quixster@.gmail.com" wrote:
> 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?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>
|||Perfmon 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:[vbcol=seagreen]
> 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?
> "quixster@.gmail.com" wrote:
|||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.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> 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?
>
> "quixs...@.gmail.com" wrote:
>
>
|||Yes, 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
quixster@.gmail.com wrote:[vbcol=seagreen]
> 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.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
|||Thanks 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, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, 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
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks 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, tootsu...@.gmail.com wrote:
>
Monitor
Is there a way to find running queries and it's details on
a particular db....
I need executin time,CPU time etc...
Try Profiler.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...
|||As Tibor points out Profiler is the correct tool for what you ask but here
are some links that may be of interest:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...
|||Tnaks all...
>--Original Message--
>As Tibor points out Profiler is the correct tool for what
you ask but here
>are some links that may be of interest:
>
>http://www.microsoft.com/sql/techinf...stration/2000/
perftuning.asp
>Performance WP's
>http://www.swynk.com/friends/vandenberg/perfmonitor.asp
Perfmon counters
>http://www.sql-server-
performance.com/sql_server_performance_audit.asp
>Hardware Performance CheckList
>http://www.sql-server-
performance.com/best_sql_server_performance_tips.asp
>SQL 2000 Performance tuning tips
>http://www.support.microsoft.com/?id=q224587
Troubleshooting App
>Performance
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_perfmon_24u1.asp[vbcol=seagreen]
>Disk Monitoring
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
on
>
>.
>
a particular db....
I need executin time,CPU time etc...
Try Profiler.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...
|||As Tibor points out Profiler is the correct tool for what you ask but here
are some links that may be of interest:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...
|||Tnaks all...
>--Original Message--
>As Tibor points out Profiler is the correct tool for what
you ask but here
>are some links that may be of interest:
>
>http://www.microsoft.com/sql/techinf...stration/2000/
perftuning.asp
>Performance WP's
>http://www.swynk.com/friends/vandenberg/perfmonitor.asp
Perfmon counters
>http://www.sql-server-
performance.com/sql_server_performance_audit.asp
>Hardware Performance CheckList
>http://www.sql-server-
performance.com/best_sql_server_performance_tips.asp
>SQL 2000 Performance tuning tips
>http://www.support.microsoft.com/?id=q224587
Troubleshooting App
>Performance
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_perfmon_24u1.asp[vbcol=seagreen]
>Disk Monitoring
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
on
>
>.
>
Monitor
Is there a way to find running queries and it's details on
a particular db....
I need executin time,CPU time etc...Try Profiler.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1080101c43fc0$a0384710$a101280a@.
phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||As Tibor points out Profiler is the correct tool for what you ask but here
are some links that may be of interest:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
on_24u1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1080101c43fc0$a0384710$a101280a@.phx
.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||Tnaks all...
>--Original Message--
>As Tibor points out Profiler is the correct tool for what
you ask but here
>are some links that may be of interest:
>
>http://www.microsoft.com/sql/techin...istration/2000/
perftuning.asp
>Performance WP's
>http://www.swynk.com/friends/vandenberg/perfmonitor.asp
Perfmon counters
>http://www.sql-server-
performance.com/sql_server_performance_audit.asp
>Hardware Performance CheckList
>http://www.sql-server-
performance.com/best_sql_server_performance_tips.asp
>SQL 2000 Performance tuning tips
>http://www.support.microsoft.com/?id=q224587
Troubleshooting App
>Performance
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_perfmon_24u1.asp
>Disk Monitoring
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:1080101c43fc0$a0384710$a101280a@.phx
.gbl...
on[vbcol=seagreen]
>
>.
>
a particular db....
I need executin time,CPU time etc...Try Profiler.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1080101c43fc0$a0384710$a101280a@.
phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||As Tibor points out Profiler is the correct tool for what you ask but here
are some links that may be of interest:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
on_24u1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1080101c43fc0$a0384710$a101280a@.phx
.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||Tnaks all...
>--Original Message--
>As Tibor points out Profiler is the correct tool for what
you ask but here
>are some links that may be of interest:
>
>http://www.microsoft.com/sql/techin...istration/2000/
perftuning.asp
>Performance WP's
>http://www.swynk.com/friends/vandenberg/perfmonitor.asp
Perfmon counters
>http://www.sql-server-
performance.com/sql_server_performance_audit.asp
>Hardware Performance CheckList
>http://www.sql-server-
performance.com/best_sql_server_performance_tips.asp
>SQL 2000 Performance tuning tips
>http://www.support.microsoft.com/?id=q224587
Troubleshooting App
>Performance
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_perfmon_24u1.asp
>Disk Monitoring
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:1080101c43fc0$a0384710$a101280a@.phx
.gbl...
on[vbcol=seagreen]
>
>.
>
Monitor
Is there a way to find running queries and it's details on
a particular db....
I need executin time,CPU time etc...Try Profiler.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||As Tibor points out Profiler is the correct tool for what you ask but here
are some links that may be of interest:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||Tnaks all...
>--Original Message--
>As Tibor points out Profiler is the correct tool for what
you ask but here
>are some links that may be of interest:
>
>http://www.microsoft.com/sql/techinfo/administration/2000/
perftuning.asp
>Performance WP's
>http://www.swynk.com/friends/vandenberg/perfmonitor.asp
Perfmon counters
>http://www.sql-server-
performance.com/sql_server_performance_audit.asp
>Hardware Performance CheckList
>http://www.sql-server-
performance.com/best_sql_server_performance_tips.asp
>SQL 2000 Performance tuning tips
>http://www.support.microsoft.com/?id=q224587
Troubleshooting App
>Performance
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_perfmon_24u1.asp
>Disk Monitoring
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
>> Is there a way to find running queries and it's details
on
>> a particular db....
>> I need executin time,CPU time etc...
>
>.
>
a particular db....
I need executin time,CPU time etc...Try Profiler.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||As Tibor points out Profiler is the correct tool for what you ask but here
are some links that may be of interest:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
> Is there a way to find running queries and it's details on
> a particular db....
> I need executin time,CPU time etc...|||Tnaks all...
>--Original Message--
>As Tibor points out Profiler is the correct tool for what
you ask but here
>are some links that may be of interest:
>
>http://www.microsoft.com/sql/techinfo/administration/2000/
perftuning.asp
>Performance WP's
>http://www.swynk.com/friends/vandenberg/perfmonitor.asp
Perfmon counters
>http://www.sql-server-
performance.com/sql_server_performance_audit.asp
>Hardware Performance CheckList
>http://www.sql-server-
performance.com/best_sql_server_performance_tips.asp
>SQL 2000 Performance tuning tips
>http://www.support.microsoft.com/?id=q224587
Troubleshooting App
>Performance
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_perfmon_24u1.asp
>Disk Monitoring
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1080101c43fc0$a0384710$a101280a@.phx.gbl...
>> Is there a way to find running queries and it's details
on
>> a particular db....
>> I need executin time,CPU time etc...
>
>.
>
Subscribe to:
Posts (Atom)