(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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 *****************************************************