SQL Server Thoughts from the Field

Rob's SQL Server & BI Blog

Performance Dashboard Reports for SQL Server 2008

Performance Dashboard Reports for SQL Server 2008

  • Comments 27
  • Likes

During a recent visit to Seattle for a Microsoft conference, I learned from my colleague Michael Thomassy that it's possible to run the SQL Server 2005 Performance Dashboard reports on SQL Server 2008, with a slight modification. There is a great new feature in SQL Server 2008 called Performance Data Collection, which I have blogged about in the past, and this is excellent for tracking SQL Server performance over time across your 2008 estate. There is also the excellent revamped Activity Monitor in SQL 2008. However, if you want to continue to use the Performance Dashboard reports, which many DBA's have found invaluable, they are not supported in SQL Server 2008. If you try to install the Performance Dashboard reports, you get the following error:

Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6
Invalid column name 'cpu_ticks_in_ms'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_Main_GetCPUHistory', because it does not exist or you do not have permission.

The reason for this is due to a change in the sys.dm_os_sys_info DMV from SQL Server 2005 to 2008 (the cpu_ticks_in_ms column has been removed in 2008 http://msdn.microsoft.com/en-us/library/ms175048.aspx). Download and install the performance dashboard reports as normal (but save the files in the Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard directory) and then modify the setup.sql file as shown below before running it against your SQL Server 2008 instance.

Please note that SQL Server 2008 has introduced new wait types that the Performance Dashboard reports currently don't handle. I would strongly recommend using the new Management Data Warehouse reports in SQL Server 2008 in order to get the best user experience. However, this workaround will help you get the Performance Dashboard Reports up and running on your SQL Server 2008 instances (see the screenshot below).

Click to see larger version

Comments
  • Hello  SQLnoob

    Thank you, it works after i install Report  Viewer 2.0 (www.microsoft.com/.../details.aspx)

    Best regards

    José Júlio Duarte

  • When i try to run SQL Server 2005 Performance Dashboard Reports in SQL Server 2008 R2 x64 i obtain this error:

    Arithmetic overflow error converting expression to data type int.

    Everyone please tell me how can i solve this?

    Thanks

    José Júlio Duarte

  • I believe its hitting a limit converting a bigint to an int. I did the following quick "hack" to the stored procedure "[MS_PerfDashboard].[usp_Main_GetCPUHistory]" to get it working:

    Change the line from:

            dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

    To:

            (-1 * (@ts_now - [timestamp])) as EventTime,

    When you use the "dashboard report" you don't get a nice datetime formatted value for the CPU ..... but everything else seems to be ok.

    Cheers

    Tim

  • I changed the same line to:

             dateadd(ms, -1 * (@ts_now - [timestamp])/1000, GetDate()) as EventTime,

    and the datetime is formatted correctly.

    Trevor.

  • Amending the line to this:

          select @ts_now = cpu_ticks / convert(float, (cpu_ticks/ms_ticks)) from sys.dm_os_sys_info

    Appears to correct the time stamps.

    Trevor.

  • I changed the line from:

        select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

    to:

        select @ts_now = cpu_ticks / convert(float, (cpu_ticks/ms_ticks)) from sys.dm_os_sys_info

    or:

        select @ts_now = ms_ticks from sys.dm_os_sys_info

    and both changes give correctly formatted datetime values.

    Trevor.

  • Has anyone gotten the error: A data source instance has not been supplied for the data source 'CHECK_DEPENDENCIES'?  When I refresh, the error then becomes: The datediff function resulted in an overflow.  The number of dateparts separating two date/time instances is too large.  Try to use datediff with a less precise datepart.

    I get this error when clicking on the System CPU Utilization graph.  

  • I have integrated them to SQL Profiler: sites.google.com/.../sqlprofiler

  • I make use of this in SQL Server 2008 Enterprise Edition (64 bit). It works without any issue. Since it's light weight tool it can used against SQL Profiler to capture the basic monitoring stuffs.

    Thanks,

    Shiv

  • Great info Rob, excatly what I was looking...

  • social.msdn.microsoft.com/.../ac91f5c2-7547-41a6-ab5b-23e17c1c6816

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment