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 Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 757
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
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 441
Does this work for Virtual Servers? If not any suggestions?
satalaj
satalaj
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 1803
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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3624 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
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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 (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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