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.

Answer 1

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)

Answer 2

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?

Answer 3

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

Answer 4

Did you or anyone find an answer.  Looks like a big hole when trying to report on CPU level over a time window!

Answer 5

what is the suggestion from MSFT team to workaround the queries that use cpu_ticks_in_ms  ?

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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.

Answer 10

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.