Using Performance Dashboard with SQL Server 2008

Today, by the force of habit i was trying to spawn SQL Server Performance Dashboard to diagnose a SQL perf issue and didn't even realize the DB was SQL 2k8.
I just went ahead to execute setup.sql to setup the sprocs that Perf Dashboard requires for that server and then to my great surprise ran into an error

Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6
Invalid column name 'cpu_ticks_in_ms'.

Here's a peek at the code (from setup.sql that ships with the Performance Dashboard):

create procedure MS_PerfDashboard.usp_Main_GetCPUHistory

as

begin

      declare @ts_now bigint

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

Hmm, what is going on !? Ah, this is Sql 2k8!

At this point i was about to switch to using the new super cool Activity Monitor in SQL2k8, but i was still intrigued, why wasn't this working?
I'm so used to Performance Dashboard that I wanted to know whether this was not going to work against Sql 2k8.

After some live searching i found that the column had been removed (well, i already knew that :)) and that it was due to some imprecision with its value.
The next question was how to fix the query in Sql 2k8, and it turned out to be a very trivial fix. 
Looking at sys.dm_os_sys_info 2k8 schema and at the original query using cpu_ticks_in_ms i saw a very interesting column (ms_ticks):

Column name	Data type	Description
cpu_ticks	bigint	Current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number.
ms_ticks	bigint	Number of milliseconds since the computer was started.

The reason that column was interesting is because cpu_ticks / convert(float, cpu_ticks_in_ms) looks awefuly similar (ticks/(ticks/ms) == ms) to what ms_ticks already provides!
I ran some tests to make sure this column was good in both sql 2k5 and 2k8:

select cpu_ticks / ms_ticks as ratio_ticks_in_ms, cpu_ticks, ms_ticks from sys.dm_os_sys_info

The first column (ratio_ticks_in_ms) remained (mostly - within 0.000001% variance which i believe is due to rounding errors in ms_ticks) constant (and surprise, it was roughly my CPU frequency in ms - number of CPU ticks per millisecond). Note this happened in both SQL 2k5 and 2k8 (and i verified this across 3 different boxes). In SQL 2k5 i also added cpu_ticks_in_ms to the output and again it was within 0.000001%.

So, it seemed weird that in SQL 2k5 the query was written to divide cpu_ticks / cpu_ticks_in_ms given that ms_ticks is also present in sys.dm_os_sys_info 2k5. I can't think of any good reason (other than minimizing the rounding error of ms_ticks) and since ms_ticks was not dropped in 2k8 it was probably as reliable in 2k5 so i'll assume that was just some copy/paste, historical reasons...

On any case, i then changed the original stmt to

create procedure MS_PerfDashboard.usp_Main_GetCPUHistory

as

begin

      declare @ts_now bigint

      select @ts_now = ms_ticks from sys.dm_os_sys_info

and ran setup.sql again.

I haven't tested all the reports, but all i've tested (main, costliest queries and some others) all seem to be working fine!

So, there you go. If you are running into errors trying to deploy SQL Server Performance Dashboard into 2k8 just change setup.sql as shown above and you should be good.

That said i should say that Activity Monitor + Performance Data Collector and reports in Sql 2k8 are SIMPLY AWESOME and you should definetely look at them too.

PS: It has been a long time since i've published anything and very commonly I wish I had posted something that I (or somebody else) end up needing again. So, i'm trying to keep up to date again.