none
error when trying to install SQL Server 2005 Performance Dashboard Reports on sql 2008

    Question

  • when trying to install SQL Server 2005 Performance Dashboard Reports on sql 2008,by executing setup.sql script, the following error is fired:
    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 column 'cpu_ticks_in_ms'is not available in sys.dm_os_sys_info in sql 2008.
    how to resolve that problem.
    Wednesday, August 27, 2008 6:17 AM

Answers

All replies

  • I dont think you can use SQL 2005 performance dashboard with SQL Server 2008. The RDL files in dashboard is designed using for SQL 2005 (i.e queries inside the file)

    Wednesday, August 27, 2008 9:37 AM
    Moderator
  • i can run the RDL files in dashboard , the only problem is the change of  sys.dm_os_sys_info in sql 2008 , and the diappearance the column cpu_ticks_in_ms (Number of CPU ticks in milliseconds) , so that statement give the error:

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

     

    Is there a replacement for performance dashboard reports in SQL Server 2008.?

    Is there a new column for cpu_ticks_in_ms  in sql 2008?

    Wednesday, August 27, 2008 11:27 AM

  • While googling, i found that there was a feedback for a bug issue in microsoft connect about the accuracy of that column.
    details are :

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291877&wa=wsignin1.0
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=292638&wa=wsignin1.0

    So , that is why it is excluded in sql 2008.

    so all queries that use that column must be modified in sql 2008.
    i will try to workaround that column to use dashboard in sql 2008



    Thursday, August 28, 2008 6:34 PM
  • Did you or anyone find an answer.  Looks like a big hole when trying to report on CPU level over a time window!

    Friday, September 12, 2008 8:06 PM
  • what is the suggestion from MSFT team to workaround the queries that use cpu_ticks_in_ms  ?
    Friday, September 12, 2008 9:21 PM
  • moh,

    Have you looked at the Activity Monitor (right click on server in OE) in SQL Server 2008 SSMS? Server Standard Reports? Database Standard Reports?

    They appear to be along the Performance Dashboard theme.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Proposed as answer by ftpex Tuesday, October 06, 2009 9:29 PM
    • Marked as answer by Kalman TothEditor Sunday, May 06, 2012 12:08 AM
    • Edited by Kalman TothEditor Sunday, September 30, 2012 12:05 AM
    Thursday, September 18, 2008 7:43 AM
    Answerer
  • This will fix the query enough to make it run.  I have the reports running on two different servers.
    set nocount on declare @ts_now bigint

     select @ts_now = cpu_ticks /( cpu_ticks / ms_ticks )

     

     /*cpu_ticks / convert(float, cpu_ticks_in_ms)*/ from sys.dm_os_sys_info

     

     /*cpu_ticks / convert(float, cpu_ticks_in_ms)*/ from sys.dm_os_sys_info

     /*cpu_ticks / convert(float, cpu_ticks_in_ms)*/ from sys.dm_os_sys_info

     

     

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

     SQLProcessUtilization,SystemIdle,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

                from (select record.value('(./Record/@id)[1]', 'int') as record_id,

        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

                timestamp from (select timestamp, convert(xml, record) as record

                from sys.dm_os_ring_buffers

                where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%<SystemHealth>%') as x

                                  ) as y order by record_id desc

    • Proposed as answer by ftpex Tuesday, October 06, 2009 9:29 PM
    Tuesday, October 06, 2009 9:29 PM
  • SQL Server 2008 provides the option of Data Collection using Management Data Warehouse. Read more at the link below on how to use it:
    http://blogs.msdn.com/mssqlisv/archive/2009/01/29/using-sql-server-2008-management-data-warehouse-for-database-monitoring-in-my-application.aspx

    MDW can be leveraged to monitor performance on the server.

    The standard reports (Right Click on the Instance in SSMS-> Reports -> Standard Reports) provide another option at looking at Transaction Statistics on the server.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: Troubleshooting SQL
    Wednesday, October 07, 2009 7:25 AM
  • Doesn't seem to be working. I kept htiing the execute button. On task manager when it was showing 60% or even more the query output never reached to that value. Most of time it showed a constnat value which was way below the actual task managers CPU usage.
    Thursday, December 10, 2009 2:33 AM
  • Microsoft recently released SQL Server 2012 Performance Dashboard which resolves the issue that the original poster asked about. It is compatible with SQL 2008, 2008R2 and 2012:
    http://www.microsoft.com/en-us/download/details.aspx?id=29063

    If you are interested, the reason for the error (and a fix to get the 2005 reports to work with SQL 2008) is at:

    http://blogs.msdn.com/b/vascov/archive/2008/09/30/using-performance-dashboard-with-sql-server-2008.aspx


    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you.

    Saturday, May 05, 2012 12:12 PM