SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Monitor for High CPU utilization in SQL Server


How to Monitor for High CPU utilization in SQL Server

Author
Message
Larry Kruse
Larry Kruse
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 761
Hey Geoff! Many thanks for the interesting article. I always learn a lot by reading these.

I'd also echo the comments by Raj in that I'd like to know more about what is going on to cause this condition. I have some processes running during the middle of our night which are causing slow-downs of other processing, but I cannot find what is happening during those times. While I'm certain that SQLSERVR.exe that is consuming the CPU, I cannot tell what inside of SQLSERVR might be causing the problem.

Is there a way to discover which process/processes inside of SQLSERVR might be causing this condition?

Many thanks for the informative article!

Larry
hrc
hrc
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 52
Reply to satalaj: How about using a trigger on insert (and there copy the newly inserted records onto server b and delete the old ones). You have the pseudotables "inserted", "deleted" and "updated" (afaIr) to skim.

How do you go about deleting the 500 records? Like this: "delete from table where ref in (select top 500 ref from table)"? This is probably the one draining power. You should check the relevant indexes.
matt.austin
matt.austin
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 441
Does this work for Virtual Servers? If not any suggestions?
satalaj
satalaj
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 25
Hi hrc
The insert operation was performed by Asp.net technology on SERVER A. you suggested that use triggers which means insert on SERVER A should depend on availability of the SERVER B in transaction. We can't afford this.

Below is the way I have implemented the row processing in infinite loop.

Begin tran tran1

declare @x Table(
[ID] [bigint] NOT NULL,
[Mobile] [varchar] (50) NOT NULL,
[Message] [varchar](50) NOT NULL,
[createdate] [datetime] )

SET XACT_ABORT ON

DELETE top(500) xyz with (UPDLOCK,READPAST)

OUTPUT deleted .* INTO @x

Insert into [34].[apiTEST].[dbo].[xyz]

select * from @x

Commit

-- here 34 is linked server B


Thanks, Satalaj.
Geoff A
Geoff A
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4314 Visits: 1825
matt.austin (11/17/2010)
Does this work for Virtual Servers? If not any suggestions?


yes, it will work on VM's as well as Physical servers.
alen teplitsky
alen teplitsky
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11202 Visits: 4674
what about just running perfmon all the time? on our DBA server we monitor a few hundred counters from all our servers
satalaj
satalaj
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 25
Hi Larry,
there is a SQL query that can tell you which query is taking lots of CPU utilization
http://www.revenmerchantservices.com/post/2010/04/23/Sql-server-cpu-utilisation.aspx
Thanks, Satalaj.
Boxer
Boxer
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 23
Does the second call of the SP wait for the first call to end? It should skip the rows locked by the first call and delete the next ones.

Have the proper indexes on your table and make sure your queries always use that index. Are you missing ROWLOCK (which will use more resources in this case)?

Let me know if this helps.
Boxer
Boxer
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 23
Does the second call of the SP wait for the first call to end? It should skip the rows locked by the first call and delete the next ones.

Have the proper indexes on your table and make sure your queries always use that index. Are you missing ROWLOCK (which will use more resources in this case)?

Let me know if this helps.
rance.malcom
rance.malcom
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 6
This is great info and something we encounter quite often.

Is anyone aware of other articles that might describe ways to determine specific queries or processes that might be causing CPU utilization to be high?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search