Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Script

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2008-11-24 : 19:42:00

I have created a script to capture top 25 high CPU utilizing SPIDS.
Pls review this script and let me know if you find anything incorrect

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),
@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

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

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)
Where property = 2

-- 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)
Where property = 2
And Cast(value as nvarchar(245)) Like @Path + 'ProdTrace%'

-- 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)
Where property = 5
And Cast(value as int) = 1
And traceid = @TraceID)
Begin
Set @TraceRunning = 1
End
Else
Begin
Exec sys.sp_trace_setstatus @TraceID, 2 -- Close trace and delete trace definition
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 + 'ProdTrace' + @StartDate

-- Create trace definition
Exec sys.sp_trace_create @traceid = @TraceID OUTPUT,
@options = @options, -- rollover files
@tracefile = @TraceFile,
@maxfilesize = @maxfilesize

-- Set the trace events
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
Exec sys.sp_trace_setfilter @TraceID, 1, 0, 7, N'Exec sp_reset_connection'
Exec sys.sp_trace_setfilter @TraceID, 1, 0, 7, N'exec spds+GetReplicatorData%'

-- Set the trace status to start
Exec sys.sp_trace_setstatus @TraceID, 1

-- 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 SupportDB.sys.DM_EXEC_REQUESTS qs
Cross Apply SupportDB.sys.dm_exec_sql_text(qs.sql_handle) as qt
Where qs.database_id = db_id('SupportDB')
ORDER BY CPU_Time Desc, qs.logical_reads Desc;
End
Set NoCount Off
Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources