How to Monitor for High CPU utilization in SQL Server

Question

Post reply

How to Monitor for High CPU utilization in SQL Server

Geoff A

SSChampion

Points: 11511
More actions
November 16, 2010 at 9:57 pm

#388245

Comments posted to this topic are about the item How to Monitor for High CPU utilization in SQL Server

Viewing 15 posts - 1 through 15 (of 59 total)

You must be logged in to reply to this topic. Login to reply

vyankarla SSC Enthusiast Points: 159 More actions · Answer 1

Very good article. How about running the VBS file using windows Task scheduler. ?

satalaj SSC Veteran Points: 268 More actions · Answer 2

Hi,

What actions do you perform if it reaches more than 90%. In my scenario, I have multiple SQL jobs which runs in while loop

e.g.

While(1=1)

begin

-- do something

-- delete top 500 records from x table of SERVER A and insert into x table of SERVER B

END

CPU usage of SERVER A reaches more than 90%. I know its because of infinite while loop but

I have to transfer the record from server A to Server B as soon as records gets entered into SERVER A.

Is there any way to deal with it.

Thanks, Satalaj.

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 3

Good article. Handy way to monitor CPU.

But one improvement I would like to see is a way to find which process is causing the CPU hit.

It may not be always SQL Server process. Or atleast it should say SQL usage % and other process usage %. Another improvement can be some information to figure out what internally caused the CPU pressure within SQL Server, if SQL was consuming the most of CPU. For addressing these, I prefer DMVs.

Regards,
Raj

http://Strictlysql.blogspot.com

hrc Old Hand Points: 333 More actions · Answer 4

What about @@CPU_BUSY and @@IO_BUSY

if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

Boxer SSC-Addicted Points: 495 More actions · Answer 5

Satalaj, you have to make sure that while running procedures in infinite loop, you make sure that you identify which procedures running parallely are getting the CPU usage up (by parallel, I mean the tasks running in parallel). Also, check the CXPACKETS running as the wait type.

You can also use SQL Server Profiler and also perfmon correlated together to toggle through the database and traces, to see when CPU usage creeps up.

Rgds!!

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 6

What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

Sys.dm_os_ring_buffer gives CPU usage % directly. Refer http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html

Regards,
Raj

http://Strictlysql.blogspot.com

Geoff A SSChampion Points: 11511 More actions · Answer 7

arr.nagaraj (11/17/2010)
What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.
Sys.dm_os_ring_buffer gives CPU usage % directly. Refer http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html

yes, that DMV will provide CPU info, however, those views are not available in SQL 2000. And unfortunately, like most of us, I still have quite a few SQL Server 2000 systems to look after.

Geoff A SSChampion Points: 11511 More actions · Answer 8

hrc (11/17/2010)
What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.

might be possible, but i never found a good way to do that with either @@CPU_BUSY or sp_monitor.

by using the Win32_PerfRawData_PerfOS_Processor object, the data is coming from the same place that perfmon uses. I find that to be highly accurate.

Thanks

satalaj SSC Veteran Points: 268 More actions · Answer 9

yes, I know that query who leads high CPU utilisation.

I had written that query in while loop. It simply deletes top 500 records with UPDlock,READPAST

and insert them into linked SERVER B.

Any suggestion?

Larry Kruse SSCrazy Points: 2617 More actions · Answer 10

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 Old Hand Points: 333 More actions · Answer 11

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 SSC Enthusiast Points: 163 More actions · Answer 12

Does this work for Virtual Servers? If not any suggestions?

satalaj SSC Veteran Points: 268 More actions · Answer 13

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 SSChampion Points: 11511 More actions · Answer 14

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.