SQL Server CPU Usage History Histogram

  • Fatherjack

    SSCommitted

    Points: 1575

    Comments posted to this topic are about the item SQL Server CPU Usage History Histogram

  • fschreuder

    SSC Veteran

    Points: 289

    Super!

    Ad-hoc analysis for the cowboy DBA.

    Might I suggest you extend this with Memory Usage History? 🙂

    Off course, in my opinion, there is no substitute for a well designed, thoroughly tested and verified monitoring system implemented by a big firm.

    But that's just me 😉

    Thank you and keep rocking!

    [/edit]: language difficulties

  • gpatterson-825050

    SSC Enthusiast

    Points: 116

    Thanks for the useful procedure.

    One small correction:

    I believe the "datas" CTE should use "ROW_NUMBER() OVER ( ORDER BY [Sample Time] DESC )". It is currently in ascending order, which is yielding incorrect results in my testing since it is classifying any recent CPU spikes as having occurred exactly 240 minutes ago.

  • Carolyn S. White

    SSC Enthusiast

    Points: 118

    hmmm, I have run this twice an hour apart and the results are always 0 in every cell.

    Last n minutes range Avg SQL CPU Min SQL CPU Max SQL CPU

    10 0 0 0

    30 0 0 0

    60 0 0 0

    120 0 0 0

    240 0 0 0

  • Fatherjack

    SSCommitted

    Points: 1575

    @ gpatterson - Yep, you could be right. Hope it proves useful for you. 🙂

    @ Carolyn - Lots to consider: Does the underlying query produce any values? Is the server in use? etc

  • webrunner

    SSC-Dedicated

    Points: 30303

    FYI I got this error:

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '('.

    Msg 137, Level 15, State 2, Line 8

    Must declare the scalar variable "@ts_now".

    Msg 156, Level 15, State 1, Line 23

    Incorrect syntax near the keyword 'AS'.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Fatherjack

    SSCommitted

    Points: 1575

    You might be using this code against an earlier version of SQL Server so your results will be mixed. It looks like you need to declare the variable and then assign a value to it:

    DECLARE @ts_now BIGINT

    SET @ts_now = ( SELECT cpu_ticks / ( cpu_ticks

    / ms_ticks )

    FROM [sys].[dm_os_sys_info]

    );

    That will fix the syntax problem but whether your version of SQL Server has the same DMVs with the same data will not be fixable if you find further errors, this code is only tested on versions from 2008 and up.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 8 posts - 1 through 8 (of 8 total)

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