Monitor CPU Usage by Query?



  • 1. SQL to satisfy conditions on multiple rows
    Hi, I'm using ASP.NET which allows you to put users in multiple roles. Table: aspnet_UsersInRoles with two columns (UserId and RoleId). I know how to generate a list of all users belonging to RoleA or RoleB. What would be the query to return users that in RoleA and RoleB? Thanks Maz
  • 2. connection error after upgrade sql2005 sp2
    hi, We've upgrade to sql2005 SP2, now we have VB and .net app connection error, [Microsoft][SQL Native Client][SQL Server]Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server. I added a registry key see below and still the same error. any idea? HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\MSDASQL\ and right click in the right pane create DisallowAdhocAccess and set value to 0
  • 3. Cachestore flush
    Hi, On a third party application that runs with a BD on SQL Express 2005, sometimes I receive those messages: 2009-06-02 14:46:40.07 spid16s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 2009-06-02 14:46:42.34 spid16s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 2009-06-02 14:46:42.56 spid16s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. If I go in SQL Server Configuration Manager, I can't see my BD. Where and how can I edit my BD properties like I can do in SQL 2000 under Entreprise manager ? Thanks for your help.
  • 4. Does sp_helpindex give me all the info I need to understand a table's indices?
    Confused a little about these indices: I typed the command: sp_helpindex Mandate_Activity It responded with something like: index_name index_description index_keys ----------------------------------------------------------------------- _dta_i_Ma_5_101955_K2_3_6 nonclustered MandateID _dta_i_Ma_5_101955_K2_K1_3_6 nonclustered MandateID,ActivityID _dta_i_Ma_5_101955_K3_1_2_4 nonclustered ActivityType _dta_i_Ma_5_101955_K3_1_2_6_7 nonclustered ActivityType INX_MandateActivityID nonclustered ActivityID IX_Mandate_Activity nonclustered MandateID PK_Activity clustered, ActivityID unique, primary key (index_name of first 4 has been abbreviated) It seems to me that there is a fair amount of duplication here! So that these could go: INX_MandateActivityID _dta_i_Ma_5_101955_K2_3_6 _dta_i_Ma_5_101955_K3_1_2_6_7 Q: Is this command giving me all the information I need to distinguish these indices? PS: This table once had many more columns than it currently has but these extra columns were dropped when a new table was created to turn the column data into row data. 41 Bit columns were dropped.
  • 5. What's the best way to setup a SQL Server 2000 for failover
    Hi all, I want to setup a second Server (with SQL 2000) as a backup for my first one. I case of failure I want to be able to manually switch to the backup server (which must have as much actual data as possible < 5 sec latency) Which is the best way to achieve this? The whole process should be easy to perform, because non IT people should do it. I tried with transcational replication but when the primary server failed I was not able to use the second one (because this Server was logged because of replication). I recently read something about Log Shipping. Is this the solution for me? Thank you very much Best regard immes

Monitor CPU Usage by Query?

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

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

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

RE: Monitor CPU Usage by Query?

Postby TmV0bW9u » Thu, 02 Nov 2006 04:21:02 GMT

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


Re: Monitor CPU Usage by Query?

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

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

Re: Monitor CPU Usage by Query?

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

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

SELECT * FROM master..sysprocesses ORDER BY cpu DESC

Then, run this to see the actual query:

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


Re: Monitor CPU Usage by Query?

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

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

Re: Monitor CPU Usage by Query?

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

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

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?

Re: Monitor CPU Usage by Query?

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

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

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:

Re: Monitor CPU Usage by Query?

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

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

Re: Monitor CPU Usage by Query?

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

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

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



Re: Monitor CPU Usage by Query?

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

hanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server

This has been immensely helpful!

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

Re: Monitor CPU Usage by Query?

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

nother product that you might want to consider is Idera Diagnostic Manager


" XXXX@XXXXX.COM " wrote:

Similar Threads:

1.cpu & memory usage on large, long-running query

Hi there

I'm not a SQL Server developer or power-user, mostly I do statistical and 
similar analysis on large datasets and use SQL Server to manage the 
underlying data.

I notice that, on my IBM workstation, my CPU utilization doesn't rise much 
above 50% and my PF usage doesn't rise above 40% on large, long-running 
queries. I have a 3.4GHz Intel P4 processor with 1.25GB of RAM.

Can I whip up the speed a bit, or if not what's the limiting factor?

Best regards

2.High CPU usage on FullText query

Hello FullText experts. I'm having some performance issues with my
forum database and searching with fulltext. I'm generally getting CPU
spikes to 100% for a few seconds at a time when a user performs a
search. I suppose the problem is in my SQL or technique.

SELECT Topics.TopicID, Topics.ForumID, Topics.Name, Topics.Title,
Topics.LastPostTime, Topics.Replies, Topics.LastPostName, Forums.Title
AS Forum, Topics.Views, Max([FreeText].Rank) AS RankMax
FROM Topics INNER JOIN Forums ON Topics.ForumID = Forums.ForumID
LEFT OUTER JOIN Posts ON Topics.TopicID = Posts.TopicID
INNER JOIN FREETEXTTABLE(Posts,*,@TextISearchFor) AS [FreeText] ON
GROUP BY Topics.ForumID, Topics.TopicID, Topics.Name, Topics.Title,
Topics.LastPostTime, Topics.Replies, Forums.Title,
Topics.LastPostName, Topics.Views
ORDER BY Max([FreeText].Rank) DESC

The intent here is to search a combination of Topics.Title and
Posts.FullText (the two fields indexed) but only return one
Topics.TopicID (records in Posts are constrained to Topic.TopicID, as
you might suspect). I'm also looking to get the parent forum's title
with each record.

I'm obviously doing something here that is too much, and I've not been
successful at making it faster. Any suggestions?


3.VS.Net 2003 CPU Usage 100% if the Dynamic Query Contains More para

Hi Group,

VS.Net 2003 CPU Usage 100% if the Dynamic Query Contains More parameters.In 
my case i got 14 Reportparameters it is taking long time to refresh the 
fields and run the query. Why is this happening.

4.High CPU -- identify which query ties up cpu


I'm running SQL2K enterprise, recently faced constant high cpu usage by 
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more 
precise and comprehensible than cpu column in sp_who2.

thanks for any help,

5.100% cpu usage

Access 2002 adp connected to SQL server, running through 
terminal services. When I execute any command through ADO
(sample below), the cpu usage immediately jumps to 100% 
and stays there.  Needless to say all other users (3-4) 
are dead in the water until the command is done.  It 
occurs whether I am executing a stored procedure or 
passing a sql string.  

Any ideas?

           Dim cmd6 As ADODB.Command
6380       Set cmd6 = New ADODB.Command
6390       cmd6.ActiveConnection = cnnConnection
6400       cmd6.CommandText = "sp_WABillPrintUpdate"
6410       cmd6.CommandType = adCmdStoredProc
6420       cmd6.Parameters.Refresh
6430       cmd6.Parameters(1).value = Me!cboBillingGroup
6440       cmd6.Execute , , adAsyncExecute
6450       Do Until cmd6.state <> adStateExecuting
6460       Loop
6470       Set cmd6 = Nothing

6. SqlServer CPU Usage hits 100% lang is C#

7. MS SQL 7 CPU Usage Problem

8. CPU usage comparison for MS SQL Server 2000



Who is online

Users browsing this forum: No registered users and 71 guest