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 Tuesday, November 16, 2010 9:57 PM


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, June 24, 2014 12:24 PM
Points: 514, Visits: 1,717
Comments posted to this topic are about the item How to Monitor for High CPU utilization in SQL Server
Post #1021910
Posted Tuesday, November 16, 2010 10:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:26 AM
Points: 6, Visits: 104
Very good article. How about running the VBS file using windows Task scheduler. ?
Post #1021911
Posted Wednesday, November 17, 2010 1:09 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,
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.

Post #1021952
Posted Wednesday, November 17, 2010 2:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:48 AM
Points: 488, Visits: 1,328
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

Strictlysql.blogspot.com
Post #1021985
Posted Wednesday, November 17, 2010 3:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:17 AM
Points: 11, 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!!
Post #1022016
Posted Wednesday, November 17, 2010 3:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:48 AM
Points: 488, Visits: 1,328

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

Strictlysql.blogspot.com
Post #1022018
Posted Wednesday, November 17, 2010 3:52 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, June 24, 2014 12:24 PM
Points: 514, Visits: 1,717
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.
Post #1022032
Posted Wednesday, November 17, 2010 3:59 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, June 24, 2014 12:24 PM
Points: 514, Visits: 1,717
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
Post #1022033
Posted Wednesday, November 17, 2010 4:39 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
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?
Post #1022062
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse