How to Monitor for High CPU utilization in SQL Server

Question

How to Monitor for High CPU utilization in SQL Server

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

BobSaint SSC Enthusiast Points: 175 More actions · Answer 1

Geoff - that is awesome!! Very nice presentation - and it covers all the prime touch points near and dear to the DBA at a glance. My curiosity is bristled - data cached in a local database? for how long? User-settable thresholds on all counters? User-settable sample rates? Alerts when thresholds exceeded (SQL or UI)? All hooks through WMI? Can you track other stuff like auto-grows, errors, failed authentications?

To be honest, I have yet to dabble in PS, although it seems like all the rage for scripting these days.

Again - very nice job.

Geoff A SSChampion Points: 11511 More actions · Answer 2

BobSaint (11/19/2010)
Geoff - that is awesome!! Very nice presentation - and it covers all the prime touch points near and dear to the DBA at a glance. My curiosity is bristled - data cached in a local database? for how long? User-settable thresholds on all counters? User-settable sample rates? Alerts when thresholds exceeded (SQL or UI)? All hooks through WMI? Can you track other stuff like auto-grows, errors, failed authentications?
To be honest, I have yet to dabble in PS, although it seems like all the rage for scripting these days.
Again - very nice job.

its a small local DB.

its all configurable.

alerts are not a part of this project.

almost all is through WMI except for user connections.

alerting I handle differently, which is another article i still owe to Steve..... (part two of this article... http://www.sqlservercentral.com/articles/Monitoring/71390/

Jeff Moden SSC Guru Points: 1004616 More actions · Answer 3

I can't wait for an article on the dashboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

rew-370421

Hall of Fame

Points: 3640

January 4, 2011 at 9:20 am

#1269054

editted - removed question

Kim Killian-SiteDataView Ten Centuries Points: 1309 More actions · Answer 5

Great article Geoff, very useful thanks for sharing!

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook

DBA_Oshvegas SSC Enthusiast Points: 126 More actions · Answer 6

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 SSChampion Points: 11511 More actions · Answer 7

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 SSC Enthusiast Points: 126 More actions · Answer 8

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

Thanks!!

bakiaraj_cit SSC Rookie Points: 28 More actions · Answer 9

bakiaraj_cit

SSC Rookie

Points: 28

February 21, 2011 at 10:44 pm

#1289686

Nice and very helpful article

Vadim Mordkovich SSC Enthusiast Points: 137 More actions · Answer 10

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

Geoff A SSChampion Points: 11511 More actions · Answer 11

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 SSC Enthusiast Points: 137 More actions · Answer 12

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 Ten Centuries Points: 1344 More actions · Answer 13

I think

i = 0

Do While i < 1

Should be

i = 0

Do While i < 10

Neha05 Default port Points: 1494 More actions · Answer 14

Neha05

Default port

Points: 1494

December 25, 2012 at 8:17 am

#1571422

Nice article!

apoovendran SSC Rookie Points: 27 More actions · Answer 15

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

How to Monitor for High CPU utilization in SQL Server

Cookies on SQLServerCentral