SQL Clone
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
DBA_Oshvegas
DBA_Oshvegas
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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!
Geoff A
Geoff A
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2356 Visits: 1808
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.
DBA_Oshvegas
DBA_Oshvegas
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 250
Ahhhhhhhhhhhhhh. Sorry, not a VBScript guy and had a bit of a brain freeze this afternoon while stepping through your process.

Thanks!!
bakiaraj_cit
bakiaraj_cit
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 168
Nice and very helpful article
Vadim Mordkovich
Vadim Mordkovich
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 166
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
Geoff A
Geoff A
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2356 Visits: 1808
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.
Vadim Mordkovich
Vadim Mordkovich
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 166
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
krowley
krowley
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 429
I think

i = 0
Do While i < 1


Should be

i = 0
Do While i < 10

Neha05
Neha05
SSChasing Mays
SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)

Group: General Forum Members
Points: 656 Visits: 60
Nice article!
apoovendran
apoovendran
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Thanks for this article. I have doubt after the line "OK, I understand, let's move on." -- where need to use the below code? It is not in CPUpct.vbs.

Sub RaiseError (msg, sev)
Set objShell = CreateObject("WScript.Shell")
query = "raiserror(" + "'" + msg + "'" + "," + sev + "," + "1) with log"
progStr = "sqlcmd -E -q " + """" + query + """"
objShell.exec(progStr)
End Sub

Q2: Can I use this windows 2008 R2 ( I see this is written for 32 bit - it will work for 64 bit also)

Thanks
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