How to Monitor for High CPU utilization in SQL Server

  • BobSaint

    SSC Enthusiast

    Points: 175

    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: 11417

    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: 996430

    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)
    Forum FAQ

  • rew-370421

    Hall of Fame

    Points: 3640

    editted - removed question

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1299

    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: 116

    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: 11417

    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: 116

    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

    Nice and very helpful article

  • Vadim Mordkovich

    SSC Enthusiast

    Points: 137

    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

    SSChampion

    Points: 11417

    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

    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

    I think

    i = 0

    Do While i < 1

    Should be

    i = 0

    Do While i < 10

  • Neha05

    Default port

    Points: 1494

    Nice article!

  • apoovendran

    SSC Rookie

    Points: 27

    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

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

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