DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 70--------------------------------------------------
       CPU Usage  over specified  Interval 
       By: srinivasma_exceldbp Date: May 29, 2014, 1:58 am
       ---------------------------------------------------------
       USE [master]
       GO
       SET ANSI_NULLS ON
       GO
       SET QUOTED_IDENTIFIER ON
       GO
       USE master
       GO
       IF OBJECT_ID('sp_lib_CPU_Usage') > 0
       BEGIN
       DROP PROC sp_lib_CPU_Usage
       END
       GO
       /*
       USAGE
       
       exec sp_lib_CPU_Usage 10,60,10    -- From last 10 to 60
       minutes at 10 mins interval
       exec sp_lib_CPU_Usage 60,3600,60  -- From last 60 to 3600
       minutes at 60 mins interval
       exec sp_lib_CPU_Usage 30,300,30   -- From last 30 to 300
       minutes at 30 mins interval
       exec sp_lib_CPU_Usage 20,120,30   -- From last 20 to 120
       minutes at 30 mins interval
       exec sp_lib_CPU_Usage 5,60,5      -- From last 05 to  60
       minutes at 05 mins interval
       
       Description 
       trace
       Assumption : Default System Health Trace is running
       
       
       Server 
       Database 
       Input Parameters: @from -- Indicates CPU Usage of   last @from
       mins from current time
       @from -- Indicates CPU Usage upto last @to mins from
       current time
       @interval -- Interval size
       Output Parameter: None
       Return Value 
       Other Outputs 
       Created Date 
       reated_By 
       Modified Date 
       Modified By 
       Modified Version:
       Modifications 
       Called By 
       Calling 
       */
       create procedure sp_lib_CPU_Usage @from int,@to int,@interval
       int
       as
       set nocount on
       declare @ts_now bigint
       create table  #outcpu ( LastIntervalMinutes int,AvgSQLCPU
       int,MinSQLCPU int,MaxSQLCPU int)
       --==============================================================
       ======
       
       SELECT
       @ts_now =  cpu_ticks / ( cpu_ticks / ms_ticks )
       FROM     [sys].[dm_os_sys_info]
       
       --==============================================================
       ======
       SELECT TOP ( @to)
       @@servername AS [Servername] ,
       DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE()) AS
       [Sample Time] ,
       SQLProcessUtilisation
       INTO    #Data
       FROM    (
       SELECT  [R].[sample].[value]('(./Record/@id)[1]', 'int') AS
       [record_id] ,
       
       [R].[sample].[value]('(./Record/SchedulerMonitorEvent/SystemHeal
       th/SystemIdle)[1]','int')
       AS [SystemIdle] ,
       
       [R].[sample].[value]('(./Record/SchedulerMonitorEvent/SystemHeal
       th/ProcessUtilization)[1]','int')
       AS [SQLProcessUtilisation] ,
       [timestamp]
       FROM      (
       SELECT    [timestamp], CONVERT(XML, record) AS [sample]
       FROM      [sys].[dm_os_ring_buffers] AS DORB
       WHERE     [ring_buffer_type] =
       N'RING_BUFFER_SCHEDULER_MONITOR'
       AND [record] LIKE N'%<SystemHealth>%'
       ) AS [R]
       ) AS y
       ORDER BY [record_id] DESC;
       --==============================================================
       ======
       while @from <= @to
       begin
       WITH    datas
       AS (
       SELECT   ROW_NUMBER() OVER ( ORDER BY [Sample Time] ) AS
       r_n ,
       [SQLProcessUtilisation]
       FROM     [#Data] AS D
       )
       insert into #outcpu (
       LastIntervalMinutes,AvgSQLCPU,MinSQLCPU,MaxSQLCPU)
       
       SELECT  @from AS [LastIntervalMinutes] ,
       AVG([SQLProcessUtilisation]) AS [AvgSQLCPU] ,
       MIN([SQLProcessUtilisation]) AS [MinSQLCPU] ,
       MAX([SQLProcessUtilisation]) AS [MaxSQLCPU]
       FROM    [datas]
       WHERE   [r_n] < @from + 1
       set @from = @from + @interval
       end
       --==============================================================
       ======
       select @@servername,
       LastIntervalMinutes,AvgSQLCPU,MinSQLCPU,MaxSQLCPU
       from #outcpu
       order by LastIntervalMinutes
       --==============================================================
       ======
       DROP TABLE [#Data], #outcpu;
       --==============================================================
       ======
       go
       exec sp_ms_marksystemobject 'sp_lib_CPU_Usage'
       go
       grant exec on sp_lib_CPU_Usage to public
       go
       *****************************************************