Thursday, November 3, 2011

Get CPU usage history

This script helps you to easily check the CPU usage of your SQL Server. You can get SQL CPU usage , Other processes CPU usage(Non-SQL) and the total usage for the previous 2 hours. Helpful to understand the usage patterns as well as to create a baseline.



-- CPU Usage History
SET NOCOUNT ON
declare @ts_now bigint
--SELECT @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info -- (Use this line if server is SQL 2005)
SELECT @ts_now = ms_ticks from sys.dm_os_sys_info -- (Use this line if server is SQL 2008 or above)

select /* top 1 */ record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
--SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) as CPU_Usage
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
GO



2 comments:

  1. Hi Javier,

    First of all thanks for the post. I can see only 256 records for last 4.20 hours .If i want make it every half an hour result ( 8 records only). What change will do the above script.
    Please help me .

    Thanks in advance.
    This is my e-mail id:rjn.msn@gmail.com.

    ReplyDelete
  2. Hi there,
    Thank you so much for the post you do and also I like your post, Are you looking to Buy NEC CPU Scrap For Sale in the whole USA? We are providing Buy 8086 gold cap with gold legs ic ,black fiber chips ,black fiber chips ,intel 8086 gold cap ,junk cpu ,trimmed fingers ,trimmed gold fingers ,scrap cpu buyers ,pentium 4 for sale ,scrap computer processors ,old cpu for sale ,computer scrap for sale in usa ,scrap motherboards for sale ,i386 dx ,i860 for sale ,cpu 486 dx2 ,sell cpu ,computer processor scrap price ,scrap servers for sale ,80487sx ,sell computer scrap ,chip boards for sale ,gold plated scrap for sale ,intel cpu auction ,ceramic cpu scrap price ,central processing unit for sale intel pentium pro for sale ,gold chips for sale ,gold plated pins computer ,gold finger chips ,scrap cpu for sale ,cpu scrap for sale ,cpu marketplace ,cpu scrap ,gold plated pins for sale ,gold fingers for sale ,ceramic cpu scrap for sale ,gold plated pins value ,gold pins for sale ,scrap cpu ,ic chips for sale ,10 pounds gold plated pins ,scrap cpus ,gold chips for sale ,gold scrap for sale ,scrap ic chips for sale ,10 pounds of gold plated pins ,ic chips scrap price ,sell cpu ,intel i486 dx2 ,i486 ,cpu for sale ,scrap backplanes ,scrap connectors ,scrap server boards ,scrap central processing units with the well price and our services are very fast. Click here for MORE DETAILS......
    VISIT US :
    www.cpumarketplace.com/

    ReplyDelete