How to Monitor for High CPU utilization in SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Geoff Albin » How to Monitor for High CPU utilization in...

59 posts, Page 6 of 6

«««2 3 4 5 6

How to Monitor for High CPU utilization in SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

DBA_Oshvegas

Posted Wednesday, January 26, 2011 1:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 2:37 PM
Points: 8, Visits: 247

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

Geoff A

Posted Wednesday, January 26, 2011 1:20 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:10 AM
Points: 485, Visits: 1,568

DBA_Oshvegas (1/26/2011)

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

DBA_Oshvegas

Posted Wednesday, January 26, 2011 1:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 2:37 PM
Points: 8, Visits: 247

Ahhhhhhhhhhhhhh. Sorry, not a VBScript guy and had a bit of a brain freeze this afternoon while stepping through your process.

Thanks!!

Post #1054206

bakiaraj_cit

Posted Monday, February 21, 2011 10:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:10 AM
Points: 2, Visits: 154

Nice and very helpful article

Post #1067404

Vadim Mordkovich

Posted Tuesday, March 01, 2011 2:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:40 PM
Points: 33, Visits: 145

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

Geoff A

Posted Friday, March 11, 2011 11:53 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:10 AM
Points: 485, Visits: 1,568

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

Vadim Mordkovich

Posted Friday, March 11, 2011 2:08 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:40 PM
Points: 33, Visits: 145

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