SQL 2005 Performance Dashboards

Getting Techi With It!

Ok, yes I like the song Getting Jiggy With It so what. Not what this post is about but it’s my first technical post so it was catchy. Being a manager of DBA’s I don’t get in the weeds that much but I had that opportunity to do so today and I wanted to share my findings.

We have been using the SQL 2005 Performance Dashboards where I work for some time. They are a great way to troubleshoot issues, share information with development teams, and quickly see what’s going on your SQL 2005 server.

Will it work in SQL 2008?
Recently we have rolled out SQL 2008 instances and have had some issues with these reports. I have found a few blogs that mention that these are no longer needed due to the new Management Data Warehouse. However if you have an existing system and your users are used to the 2005 reports you may not be ready to move there just yet.

In our pushing out new SQL 2008 servers I have run into one problem that I was able to fix. I have not fully tested all the features and reports yet but this one took up most of today so I thought I would share.

Changing DMV’s

Essentially it boils down to a change in the DMV that this blog details very nicely so I won’t discuss that. I tried this change and it worked on 2008 but I later noticed that it broke reports against my 2005 servers. My unique situation is that we have a central code base that runs against all new server setups both 2005 & 2008 so I wanted a fix for this that would work for both.

This is what I came up with, with the help of Dave Levy

Replace this line of code in the proc usp_Main_GetCPUHistory.

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

With this….

if (@@MicrosoftVersion / 0x01000000) < 10
SET @sql_String = 'select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info'
SET @sql_String = 'select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info'

exec sp_executesql @sql_String, N'@ts_now bigint OUTPUT', @ts_now = @ts_now OUTPUT

I have found many blogs out there with the simple change to the one line of code. In my case I needed to modify the proc create script in such a way that I could run the script on both SQL Server 2005 & 2008 servers and have one code base that would work on both.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s