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. cpu monitor

    CPU Monitor is a lightweight Java app that uses Secure Shell (ssh) to scrape

    information from a server and chart its configuration and current load. It

    communicates directly with the server and as a result doesn’t require an agent to

    be running on the target. It currently supports Linux, Solaris and Mac OS servers

    with more OS’s coming shortly.
    CPU monitor is designed to be easy to configure and install and is sufficiently

    lightweight not to place an unacceptable burden on the server. It uses a simple

    XML file to specify the servers to monitor and is ideal for monitoring clustered

    configurations.

    It also allows the recording of the load to a file for further analysis

    The following features will be added shortly

    Support for AIX and HP-UX
    A Character mode
    A Table Mode
    Network Monitoring
    Further statistics collection
    Source code for information scraping. Allowing improvements on current

    statistic collection or support for new OS's

    more info

    http://egyvedio2015.blogspot.com/
    https://www.youtube.com/watch?v=iCYZPcaPWls

    ReplyDelete