AWR like jobs in SQL Server

This morning when I arrived at the office, there were several emails related to a high CPU utilization issue on a SQL Server that occurred around 4:45AM this morning. Since SQL Server 2005 does not have the anticipated performance repository that SQL Server 2008 R2 has, I decided to begin implementing something similar to AWR that Oracle has. I am starting with what is below…

create procedure [dbo].[dbaspOSWaitStats_p] as
  declare @t varchar(8);
  if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) < 5
      set @t=convert(char, getdate(), 112);
      exec ('select current_timestamp as sample_time,wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into osws_' + @t + ' from sys.dm_os_wait_stats where 1 = 0');
      set @t=convert(char, getdate() - 14, 112);
      begin try
        exec ('drop table osws_' + @t);
      end try
      begin catch
        print 'table did not exist'
      end catch
  set @t=convert(char, getdate(), 112);
  exec ('insert into osws_' + @t + ' select current_timestamp as sample_time,wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms from sys.dm_os_wait_stats');

This is scheduled to run as a SQL Agent job every ten minutes. At midnight, it creates a new table for the day, and drops the one from 14 days ago. My hope is this will be self managing, especially as we add other dmv performance views to our "repository".

1 comment for “AWR like jobs in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.