SQL Server CPU Usage History Histogram

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

  • 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

  • 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.

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

    Last n minutes rangeAvg SQL CPUMin SQL CPUMax SQL CPU

    10000

    30000

    60000

    120000

    240000

  • @ 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

  • 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

  • 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.

  • 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