Click here to monitor SSC
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
Geoff A
Geoff A
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1798
Comments posted to this topic are about the item How to Monitor for High CPU utilization in SQL Server
vyankarla
vyankarla
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 158
Very good article. How about running the VBS file using windows Task scheduler. ?
satalaj
satalaj
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 25
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
arr.nagaraj
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 1590
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
hrc
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 52
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
Boxer
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 23
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
arr.nagaraj
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 1590

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
Geoff A
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1798
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
Geoff A
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1798
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
satalaj
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 25
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?
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