{"id":2034,"date":"2012-03-22T08:54:14","date_gmt":"2012-03-22T13:54:14","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=2034"},"modified":"2012-03-22T09:19:08","modified_gmt":"2012-03-22T14:19:08","slug":"recording-sql-server-cpu-utilization-for-later-analysis","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2012\/03\/22\/recording-sql-server-cpu-utilization-for-later-analysis\/","title":{"rendered":"Recording SQL Server CPU utilization for later analysis"},"content":{"rendered":"<p>Since we are still on SQL Server 2005 (going to 2008 R2 in the next six months), we don&#8217;t currently have the performance repository that 2008 does.  As such, we recently added a job to record CPU utilization for later analysis.<\/p>\n<p>We have a job that runs every five minutes and simply runs the following.  There is a sample_time column that defaults to getdate().<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\nuse dbaDB\r\ngo\r\ninsert into swr_cpu_hist(cpu_busy,cpu_idle) SELECT @@CPU_BUSY, @@IDLE \r\n<\/pre>\n<p>We report on it with something similar to the following (no LAG function in 2005 :()<\/p>\n<pre lang=\"tsql\" line=\"1\">\r\nuse dbaDB\r\ngo\r\ndeclare cur cursor for\r\n  select * from swr_cpu_hist order by sample_time;\r\ndeclare @sample_time datetime;\r\ndeclare @sample_time_previous datetime;\r\ndeclare @cpu_busy bigint;\r\ndeclare @cpu_idle bigint;\r\ndeclare @cpu_busy_previous bigint;\r\ndeclare @cpu_idle_previous bigint;\r\nbegin\r\n  open cur\r\n  fetch next from cur into @sample_time, @cpu_busy, @cpu_idle;\r\n  while @@fetch_status = 0\r\n  begin\r\n    set @cpu_busy_previous = @cpu_busy;\r\n    set @cpu_idle_previous = @cpu_idle;\r\n    fetch next from cur into @sample_time, @cpu_busy, @cpu_idle;\r\n    if @cpu_busy > @cpu_busy_previous\r\n      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));\r\n  end\r\n  close cur\r\n  deallocate cur\r\nend\r\n<\/pre>\n<p>This provides output similar to the following&#8230;<\/p>\n<pre lang=\"text\">\r\nMar 22 2012  9:10AM 29.4742\r\nMar 22 2012  9:15AM 38.8602\r\nMar 22 2012  9:20AM 44.9948\r\nMar 22 2012  9:25AM 31.1189\r\nMar 22 2012  9:30AM 35.1379\r\nMar 22 2012  9:35AM 34.6393\r\nMar 22 2012  9:40AM 20.1499\r\nMar 22 2012  9:45AM 28.1512\r\nMar 22 2012  9:50AM 22.4059\r\nMar 22 2012  9:55AM 17.4686\r\nMar 22 2012 10:00AM 19.3572\r\nMar 22 2012 10:05AM 16.8845\r\nMar 22 2012 10:10AM 15.9102\r\nMar 22 2012 10:15AM 15.0026\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Since we are still on SQL Server 2005 (going to 2008 R2 in the next six months), we don&#8217;t currently have the performance repository that 2008 does. As such, we recently added a job to record CPU utilization for later&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2012\/03\/22\/recording-sql-server-cpu-utilization-for-later-analysis\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,34],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2034"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=2034"}],"version-history":[{"count":9,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2034\/revisions"}],"predecessor-version":[{"id":2043,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2034\/revisions\/2043"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=2034"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=2034"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=2034"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}