I got a question from one of our UG member. He asked whether we can use SQL Server 2005 performance dashboard with SQL Server 2008. When we tried at the session we failed to implement it in SQL2K8. I was just thinking this today and planned to try this. I found out the reason why we failed to implement and what has changed in the query.
I have downloaded SQL 2005 performance dashboard and installed successfully. The next step is to run setup.sql file from performance dashboard folder. When I tried to execute it I got the failure message as cpu_ticks_in_ms column is missing on sys.dm_os_sys_info DMV. The second error is due the dependency on the first error so I have neglected the second error and concentrated on the first error.
I have done a quick study on the DMV and found that SQL team has removed that column in SQL Server 2008. So to overcome this I have rewritten the query to manipulate the column value, now Ive used that query and it got executed successfully. This means that you are now able to use SQL 2005 performance dashboard in SQL Server 2008. Please note that Ive not fully tested all the functionalities in SQL 2005 performance dashboard on SQL 2008 so just verify your output. Ive uploaded the modified script here so just download it and make use of it.
Existing query
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
Modified query
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info
(Works with SQL 2008 & SQL 2008 R2)
The next question hits my mind is Why SQL team is not included this dashboard feature in SQL 2008 release. I then found out that they have added a feature Data Collection in SQL 2008 to do this. In case of SQL 2008 R2 they have added one more feature called Utility Explorer, so these things will take care of capturing all those stuffs. Some of them will still try to use the performance dashboard to capture the details, so this post will be helpful for them.
Leave a Reply