Use master;
Go
If Exists (Select 1 from information_schema.routines
Where routine_name = 'dba_RunCPUUtilizationTrace'
And routine_schema = 'dbo')
Drop procedure dbo.dba_RunCPUUtilizationTrace
Go
Set ANSI_Nulls On
Set Quoted_Identifier On
Go
Create procedure dbo.dba_RunCPUUtilizationTrace
@Threshold int = 75 -- % of CPU utilization to use as a toggle point for turning the trace on and off
As
Declare @TraceID int,
@StartDate nvarchar(35),
@maxfilesize bigint,
@on bit,
@options int,
@Path nvarchar(200),
@TraceFile nvarchar(245),
@TraceFileBaseName nvarchar(25),
@TraceRunning bit,
@CPU_Busy int,
@Idle int,
@CPUPercent decimal(5, 2)
Set NoCount On
Set @on = 1
Set @options = 2
Set @StartDate = Convert(nvarchar, getdate(), 112) + Replace(Convert(nvarchar, getdate(), 108), ':', '')
Set @maxfilesize = 5
Set @TraceRunning = 0
Set @TraceFileBaseName = 'CPUSpikeTrace'
-- Get current CPU counts
Select @CPU_Busy = @@CPU_BUSY,
@Idle = @@IDLE
-- Delay for 1 second
WaitFor Delay '000:00:01'
-- Get current CPU counts and calculate % CPU utilization over the last 1 second
Set @CPUPercent = Cast(Round((@@CPU_BUSY - @CPU_Busy)/((@@IDLE - @Idle + @@CPU_BUSY - @CPU_Busy) * 1.0) * 100, 2) As decimal(5, 2))
-- If CPU > Threshold, wait 1 minute and retest.
-- This is to avoid tracing for a momentary spike.
-- Suggested by Sajesh Gopalakrishnan
If @CPUPercent >= @Threshold
Begin
-- Delay for 1 minute
WaitFor Delay '000:01:00'
-- Get new current CPU counts
Select @CPU_Busy = @@CPU_BUSY,
@Idle = @@IDLE
-- Delay for 1 second
WaitFor Delay '000:00:01'
-- Get new current CPU counts and calculate % CPU utilization over the last 1 second
Set @CPUPercent = Cast(Round((@@CPU_BUSY - @CPU_Busy)/((@@IDLE - @Idle + @@CPU_BUSY - @CPU_Busy) * 1.0) * 100, 2) As decimal(5, 2))
End
-- Get file path of system trace file to use as target path for custom trace
Select @Path = Cast(value as nvarchar(255))
From fn_trace_getinfo(1) -- 1 = default trace
Where property = 2 -- 2 = value is path\name of trace file
-- Remove name of trace file from @Path
Set @Path = Reverse(Right(Reverse(@Path), Len(@Path) - CharIndex('\', Reverse(@Path))))
If Right(@Path, 1) <> '\'
Set @Path = @Path + '\'
-- Check to see if custom trace already exists
Select @TraceID = traceid
From fn_trace_getinfo(null) -- null = all current traces
Where property = 2 -- 2 = value is path\name of trace file
And Cast(value as nvarchar(245)) Like @Path + @TraceFileBaseName + '%'
-- Check to see if custom trace is running (if exists)
If @TraceID Is Not Null
Begin
If Exists (Select 1 From fn_trace_getinfo(null) -- null = all current traces
Where property = 5 -- 5 = status of trace
And Cast(value as int) = 1 -- 1 = trace is active
And traceid = @TraceID)
Begin
Set @TraceRunning = 1
End
Else
Begin
-- Close trace and delete trace definition
Exec sys.sp_trace_setstatus @TraceID, 2
End
End
-- If CPU below threshold and trace is running, stop the trace
If @CPUPercent < @Threshold And @TraceRunning = 1
Begin
Exec sys.sp_trace_setstatus @TraceID, 0 -- Turn trace off
Exec sys.sp_trace_setstatus @TraceID, 2 -- Close trace and delete trace definition
End
-- If CPU >= threshold and trace is not running, start the trace
If @CPUPercent >= @Threshold And @TraceRunning = 0
Begin
Set @TraceID = Null
Set @TraceFile = @Path + @TraceFileBaseName + @StartDate
-- Create trace definition
Exec sys.sp_trace_create @traceid = @TraceID OUTPUT,
@options = @options, -- rollover files
@tracefile = @TraceFile,
@maxfilesize = @maxfilesize
-- Set the trace events
-- These events were generated by SQL Profiler
-- syntax: Exec sys.sp_trace_setevent @traceid, @eventid, @columnid, @on
-- @eventid = 10 = RPC:Completed
-- @columnid = 1 = TextData
-- @columnid = 12 = SPID
-- @columnid = 13 = Duration
-- @columnid = 14 = StartTime
-- @columnid = 15 = EndTime
-- @columnid = 16 = Reads
-- @columnid = 17 = Writes
-- @columnid = 18 = CPU
-- @columnid = 34 = ObjectName
Exec sys.sp_trace_setevent @TraceID, 10, 15, @on
Exec sys.sp_trace_setevent @TraceID, 10, 16, @on
Exec sys.sp_trace_setevent @TraceID, 10, 1, @on
Exec sys.sp_trace_setevent @TraceID, 10, 17, @on
Exec sys.sp_trace_setevent @TraceID, 10, 18, @on
Exec sys.sp_trace_setevent @TraceID, 10, 34, @on
Exec sys.sp_trace_setevent @TraceID, 10, 12, @on
Exec sys.sp_trace_setevent @TraceID, 10, 13, @on
Exec sys.sp_trace_setevent @TraceID, 10, 14, @on
-- Set the trace filters
-- Don't capture sp_reset_connection
Exec sys.sp_trace_setfilter @TraceID, 1, 0, 7, N'Exec sp_reset_connection'
-- Set the trace status to start
Exec sys.sp_trace_setstatus @TraceID, 1
If object_id('dbo.TopQueriesByCPU') Is Null
Begin
Create Table dbo.TopQueriesByCPU (
TrackID int identity(1, 1) not null primary key,
TrackDate datetime not null default (getdate()),
iTrackDate As (convert(int,convert(varchar,TrackDate,(112)),(0))),
iTrackTime As (convert(int,replace(convert(varchar,TrackDate,(108)),':',''),(0))),
CPU_Time int not null,
DBName sysname null,
ObjectName sysname null,
QueryText nvarchar(max) null,
Reads int null,
LogicalReads int null,
Writes int null)
End
-- Capture Top 25 queries by CPU time
Insert Into dbo.TopQueriesByCPU (
CPU_Time,
DBName,
ObjectName,
QueryText,
Reads,
LogicalReads,
Writes)
SELECT TOP 25
CPU_Time
, DBName = db_name(qs.database_id)
, ObjectName = object_name(qt.objectid, qs.database_id)
, QueryText = SUBSTRING(qt.text, qs.statement_start_offset/2,
(Case When qs.statement_end_offset = -1
Then len(convert(nvarchar(max), qt.text)) * 2
Else qs.statement_end_offset
End - qs.statement_start_offset)/2)
,qs.reads
,qs.logical_reads
,qs.writes
FROM sys.dm_exec_requests qs
Cross Apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY CPU_Time Desc, qs.logical_reads Desc;
End
Set NoCount Off
Go
