Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

How to Monitor for High CPU utilization in SQL Server Expand / Collapse
Author
Message
Posted Wednesday, November 17, 2010 6:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 94, Visits: 677
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
Post #1022115
Posted Wednesday, November 17, 2010 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:49 AM
Points: 12, Visits: 45
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.
Post #1022116
Posted Wednesday, November 17, 2010 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:07 PM
Points: 6, Visits: 290
Does this work for Virtual Servers? If not any suggestions?
Post #1022118
Posted Wednesday, November 17, 2010 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 3, 2010 8:07 AM
Points: 8, 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.
Post #1022142
Posted Wednesday, November 17, 2010 7:22 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:08 AM
Points: 514, Visits: 1,735
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.
Post #1022144
Posted Wednesday, November 17, 2010 7:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
what about just running perfmon all the time? on our DBA server we monitor a few hundred counters from all our servers

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1022148
Posted Wednesday, November 17, 2010 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 3, 2010 8:07 AM
Points: 8, 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.
Post #1022160
Posted Wednesday, November 17, 2010 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:17 AM
Points: 11, 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.
Post #1022163
Posted Wednesday, November 17, 2010 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:17 AM
Points: 11, 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.
Post #1022167
Posted Wednesday, November 17, 2010 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 18, 2011 5:30 PM
Points: 1, Visits: 5
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?
Post #1022169
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse