Post reply

SQL Server CPU Usage History Histogram

  • May 15, 2014 at 5:36 pm

    #288920

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

  • May 23, 2014 at 3:38 am

    #1715996

    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

  • July 7, 2014 at 6:57 am

    #1727333

    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.

  • July 7, 2014 at 12:16 pm

    #1727521

    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

  • July 7, 2014 at 1:27 pm

    #1727547

    @ 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

  • July 22, 2014 at 2:38 pm

    #1731587

    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

  • July 23, 2014 at 2:06 am

    #1731660

    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.

  • September 21, 2015 at 11:35 am

    #1828177

    Thanks for the script.

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

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