SQL 2005 Reports using the DMV {dm_os_sys_info} tweaks for 2008
Having recently started @ new employer, I'm still in the process of ("Setting up Shop") in regards to the various reports I want to be able to run on the instances
One of these items I had finally got around to do was installing was "SQL Server 2005 Performance Dashboard Reports"
This Company has SQL 2005 instances as well as SQL 2008 / 2008 R2 instances.
Well for SQL 2005 it was easy as pie, just run SETUP.sql and hey presto some great reports @ the drop of a hat, I had almost (well OK I had forgotten)
That a small change to one of the DMV's in occurred in SQL Server 2008 this DMV is one used by reports, and it meant the script was not going
to work "as is" on the SQL 2008 Server without a small adjustment, so after I made a quick coffee I made the required change to the statement so it would work, specifically I'm talking about MS_PerfDashboard.usp_Main_GetCPUHistory stored procedure and I have shown them both below
Original (2005) which uses the cpu_ticks_in_ms and MY revised version for SQL 2008.. This uses the cpu_ticks column (but it is not in ms) so needs to be multiple * 1000 as you can see in the scripts.. hope this can be of some benefit.
/* SQL SERVER 2005 Version (Original Script) */
if object_id('MS_PerfDashboard.usp_Main_GetCPUHistory', 'P') is not null
drop procedure MS_PerfDashboard.usp_Main_GetCPUHistory
go
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
select top 15 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
end
go
grant execute on MS_PerfDashboard.usp_Main_GetCPUHistory to public
go
/* SQL SERVER 2008+ Version (Modified Script) */
if object_id('MS_PerfDashboard.usp_Main_GetCPUHistory', 'P') is not null
drop procedure MS_PerfDashboard.usp_Main_GetCPUHistory
go
create procedure MS_PerfDashboard.usp_Main_GetCPUHistory
as
begin
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, ms_ticks)*1000 from sys.dm_os_sys_info
SELECT TOP 15 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
end
go
grant execute on dbo.usp_Main_GetCPUHistory to public
go