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 «««23456

How to Monitor for High CPU utilization in SQL Server Expand / Collapse
Author
Message
Posted Wednesday, January 26, 2011 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 12:11 PM
Points: 8, Visits: 250
I am confused about the part "SQLCMD that uses the raiserror command to send messages to the Windows Event Application Log". Is this a job step executed as Operating System (CmdExec) or is this something else?

Thanks!
Post #1054172
Posted Wednesday, January 26, 2011 1:20 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, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
DBA_Oshvegas (1/26/2011)
I am confused about the part "SQLCMD that uses the raiserror command to send messages to the Windows Event Application Log". Is this a job step executed as Operating System (CmdExec) or is this something else?

Thanks!


its executed from within the VB Script. same way it would work from a DOS prompt.

you could open a cmd prompt where SQL had been installed and run SQLCMD.
Post #1054185
Posted Wednesday, January 26, 2011 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 12:11 PM
Points: 8, Visits: 250
Ahhhhhhhhhhhhhh. Sorry, not a VBScript guy and had a bit of a brain freeze this afternoon while stepping through your process.

Thanks!!
Post #1054206
Posted Monday, February 21, 2011 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 7:17 AM
Points: 2, Visits: 168
Nice and very helpful article
Post #1067404
Posted Tuesday, March 1, 2011 2:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:24 PM
Points: 33, Visits: 160
DECLARE @CPU_BUSY int,
@IDLE int,
@seconds int,
@secondsString varchar(50)

SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE
WAITFOR DELAY ''000:00:01''

set @seconds = 0

WHILE (SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *
1.00) *100 AS CPUBusyPct) > 80 AND @seconds < 55
BEGIN
set @seconds = @seconds + 1
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE
WAITFOR DELAY ''000:00:01''
if @seconds % 10 = 0
BEGIN
-- DO Something like send an email
END
END
Post #1071600
Posted Friday, March 11, 2011 11:53 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, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
Hey Vadim,

thanks for the script. however, it presents a bit of a problem when you have multiple instances on a server.
it also does not account for 'other' CPU hogs. i have had to deal with things like anti-virus engines eating up 90% of the CPU. now even though as a DBA, it might not be my job to worry about anti-virus software, i do need to be aware when the SQL server is struggling to process requests because of it.

thanks.
Post #1077116
Posted Friday, March 11, 2011 2:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:24 PM
Points: 33, Visits: 160
Geoff,

I've been using it more as a generic CPU indicator. It let's me know when things are crossing the threshold and for how long. If you want to go beyond that you should probably get a more robust monitoring tool.

Vadim
Post #1077162
Posted Friday, October 19, 2012 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:35 PM
Points: 113, Visits: 423
I think
i = 0
Do While i < 1


Should be
i = 0
Do While i < 10


Post #1374830
Posted Tuesday, December 25, 2012 8:17 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Nice article!
Post #1400060
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse