Recording SQL Server CPU utilization for later analysis

Since we are still on SQL Server 2005 (going to 2008 R2 in the next six months), we don’t currently have the performance repository that 2008 does. As such, we recently added a job to record CPU utilization for later analysis.

We have a job that runs every five minutes and simply runs the following. There is a sample_time column that defaults to getdate().

use dbaDB
go
insert into swr_cpu_hist(cpu_busy,cpu_idle) SELECT @@CPU_BUSY, @@IDLE 

We report on it with something similar to the following (no LAG function in 2005 :()

use dbaDB
go
declare cur cursor for
  select * from swr_cpu_hist order by sample_time;
declare @sample_time datetime;
declare @sample_time_previous datetime;
declare @cpu_busy bigint;
declare @cpu_idle bigint;
declare @cpu_busy_previous bigint;
declare @cpu_idle_previous bigint;
begin
  open cur
  fetch next from cur into @sample_time, @cpu_busy, @cpu_idle;
  while @@fetch_status = 0
  begin
    set @cpu_busy_previous = @cpu_busy;
    set @cpu_idle_previous = @cpu_idle;
    fetch next from cur into @sample_time, @cpu_busy, @cpu_idle;
    if @cpu_busy > @cpu_busy_previous
      print cast(@sample_time as varchar(100)) + ' ' + cast(((@cpu_busy - @cpu_busy_previous) * cast(@@timeticks as float))/((@cpu_idle - @cpu_idle_previous + @cpu_busy - @cpu_busy_previous) * cast(@@timeticks as float)) * 100 as varchar(100));
  end
  close cur
  deallocate cur
end

This provides output similar to the following…

Mar 22 2012  9:10AM 29.4742
Mar 22 2012  9:15AM 38.8602
Mar 22 2012  9:20AM 44.9948
Mar 22 2012  9:25AM 31.1189
Mar 22 2012  9:30AM 35.1379
Mar 22 2012  9:35AM 34.6393
Mar 22 2012  9:40AM 20.1499
Mar 22 2012  9:45AM 28.1512
Mar 22 2012  9:50AM 22.4059
Mar 22 2012  9:55AM 17.4686
Mar 22 2012 10:00AM 19.3572
Mar 22 2012 10:05AM 16.8845
Mar 22 2012 10:10AM 15.9102
Mar 22 2012 10:15AM 15.0026