spProcessTrace-fnExtractSPNameFromTextData-SpActivityGraph-spShowActivityGraphByChunks

--Listing 4. spProcessTrace
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spProcessTraceFile' and type = 'P')
DROP PROC spProcessTraceFile
GO
-------------------------------------------------------------------------------------------------------
-- Name spProcessTraceFile
-- Description Aggregates data from a SQL Server trace file and sends reports with top consumers and
-- long-runners by email
-- Input @ServerName - server name a trace file was created for
-- @ReportDate (optional) - date of the trace file (default - current)
-- @TraceFilePath - path to the trace file
-- @recipients - list of email recipient divided by [;]
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spProcessTraceFile
@ServerName sysname,
@ReportDate varchar(20) = null,
@TraceFilePath varchar(1000) = '\\Process02\D$\Program Files\Microsoft SQL Server\MSSQL\LOG\',
@recipients varchar(4000) = 'Manager1@company.com;Manager2@company.com'
AS
SET NOCOUNT ON
DECLARE @TabName sysname
DECLARE @Sqlstr varchar(8000)
declare @m varchar(8000)
declare @s varchar(8000)
declare @q varchar(8000)
declare @ReportDateShort varchar(20)
SET @ReportDate = CASE WHEN @ReportDate is null
THEN CONVERT(varchar(20),getdate(),107)
ELSE CONVERT(varchar(20),CONVERT(datetime,@ReportDate),107)
END
SET @ReportDateShort = REPLACE(LEFT(@ReportDate,6),' ','')
SET @TabName = 'Trace_' + @ServerName + @ReportDateShort
SET @Sqlstr = '
SELECT EventClass, TextData, DatabaseId, SPID, Duration, StartTime, Reads, Writes, CPU
INTO '+@TabName+' FROM ::fn_trace_gettable('''+@TraceFilePath+@TabName+'.trc'', default)'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Loading the trace file into a table succeeded'
-- CPU consumers
SET @Sqlstr = '
SELECT *
into '+@TabName+'_CPU
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(CPU) ''TotalCPU'', MIN(CPU) ''MinCPU'', MAX(CPU) ''MaxCPU''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Aggregation for CPU has been succeeded'
-- Sending top CPU consumers by e-mail
set @m = 'Top Overlord.dev CPU consumers for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev CPU consumers for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalCPU,
TotalCPU/TimesExecuted ''AverageExecTime'',
MinCPU,
MaxCPU
from dbo.'+@TabName+'_CPU
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalCPU DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
-- Long-runners
SET @Sqlstr = '
SELECT *
into '+@TabName+'_Duration
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(Duration) ''TotalDuration'', MIN(Duration) ''MinDuration'', MAX(Duration) ''MaxDuration''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
-- Sending top Long-Runners result by e-mail
set @m = 'Top Overlord.dev Long-Runners for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev Long-Runners for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalDuration,
TotalDuration/TimesExecuted ''AverageExecTime'',
MinDuration,
MaxDuration
from dbo.'+@TabName+'_Duration
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalDuration DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
GO


--Listing 5. fnExtractSPNameFromTextData
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnExtractSPNameFromTextData' and type = 'FN')
DROP FUNCTION fnExtractSPNameFromTextData
GO
---------------------------------------------------------------------------------
-- Name fnExtractSPNameFromTextData
-- Description Extracts SP name from profiler's textdata
-- Input @TextData
-- Output SP name
-- Created By Viktor Gorodnichenko
---------------------------------------------------------------------------------
CREATE FUNCTION fnExtractSPNameFromTextData
(@TextData nvarchar(4000))
RETURNS varchar(128)
AS
BEGIN
DECLARE @Name varchar(128)
IF CHARINDEX('sp_execute',@TextData) > 0
BEGIN
SET @Name = 'sp_execute'
GOTO exit_fn
END
IF CHARINDEX('exec',@TextData) > 0
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('exec',@TextData)+1)
SET @TextData = LTRIM(REPLACE(@TextData,'execute ',''))
SET @TextData = LTRIM(REPLACE(@TextData,'exec ',''))
IF CHARINDEX(' ',@TextData) > 0
BEGIN
IF LEFT(@TextData,1) = '@'
BEGIN
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('=',@TextData)-1)
END
SET @Name = CASE WHEN CHARINDEX(' ',@TextData) > 0
THEN LEFT(@TextData,CHARINDEX(' ',@TextData)-1)
ELSE @TextData
END
END
ELSE
SET @Name = @TextData
SET @Name = CASE WHEN CHARINDEX(CHAR(9),@Name) > 0
THEN LEFT(@Name,CHARINDEX(CHAR(9),@Name)-1)
ELSE @Name
END
IF CHARINDEX('.',@Name)>0
SET @Name = REVERSE(LEFT(REVERSE(@Name),CHARINDEX('.',REVERSE(@Name))-1))
exit_fn:
RETURN @Name
END


--Listing 6. SpActivityGraph
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spActivityGraph' and type = 'P')
DROP PROC spActivityGraph
GO
-------------------------------------------------------------------------------------------------------
-- Name spActivityGraph
-- Description Builds a graph of code activities. Uses data from a trace table
-- Input @TraceTable - name of the trace table
-- @TraceStart/@TraceEnd - trace period to cover in the graph
-- @SPNameLayoutLen = 20 - size of the column "name" in the report
-- @DurationLayoutLen = 6 - size of the column "duration" in the report
-- @LayoutWidth int = 115 - width of the report
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spActivityGraph
@TraceTable varchar(128),
@TraceStart varchar(40),
@TraceEnd varchar(40),
@SPNameLayoutLen int = 20,
@DurationLayoutLen int = 6,
@LayoutWidth int = 115
AS
DECLARE @GraphStart datetime
DECLARE @GraphEnd datetime
DECLARE @TimeToMark datetime
DECLARE @Coeff numeric(7,3)
DECLARE @strCoeff varchar(20)
DECLARE @StartTime datetime
DECLARE @Duration int
DECLARE @TextData varchar(8000)
DECLARE @sqlstr nvarchar(4000)
SET @sqlstr = N'SELECT @GraphStart = MIN(StartTime), @GraphEnd = MAX(StartTime) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and Duration > 5000'
EXEC sp_executesql @sqlstr, N'@GraphStart datetime OUTPUT, @GraphEnd datetime OUTPUT',
@GraphStart = @GraphStart OUTPUT, @GraphEnd = @GraphEnd OUTPUT
IF @GraphStart is null
BEGIN
PRINT 'No data for the period'
RETURN
END
SET @Coeff = CONVERT(numeric(10,2),@LayoutWidth) / CASE WHEN DATEDIFF(ss, @GraphStart, @GraphEnd)>0
THEN CONVERT(numeric(10,2),DATEDIFF(ss, @GraphStart, @GraphEnd)) ELSE 1800 END -- 1800 sec in 30 min
-- The smaller @Coeff the less number of dashes will be used to represent a long-runner.
Fo ex, 0.2 means that a 5 sec process will get 1 dash.
SET @strCoeff = STR(@Coeff,7,3)
EXEC ('DECLARE SPIDs CURSOR FOR
select StartTime, Duration, CONVERT(varchar(8000),TextData) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and (Duration/1000)*'+@strCoeff+' > 1
order by StartTime')
-- Printing time scale
PRINT 'StartTime Duration Text'+
SPACE(12)+LEFT(CONVERT(varchar(10),@GraphStart,108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)*2/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),@GraphEnd,108),5)
IF @TimeToMark is not null
PRINT REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@TimeToMark)*@Coeff,0))+'*'
OPEN SPIDs
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT RIGHT(CONVERT(varchar(10),@StartTime,108),8) + ' ' + STR(@Duration,@DurationLayoutLen) + ' ' +
LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)+
REPLICATE(' ',@SPNameLayoutLen-LEN(LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)))+
REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@StartTime)*@Coeff,0))+
REPLICATE('-',ROUND((@Duration/1000)*@Coeff,0))
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
END
DEALLOCATE SPIDs
GO


--Listing 7. spShowActivityGraphByChunks
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spShowActivityGraphByChunks' and type = 'P')
DROP PROC spShowActivityGraphByChunks
GO
-------------------------------------------------------------------------------------------------------
-- Name spShowActivityGraphByChunks
-- Description Shows activity graphs by small portions
-- Input @ServerName - name of the production server the trace was collected for
-- @ReportDate - date of the trace
-- @StartTime/@EndTime - trace period to cover
-- @ChunkSize - size in minutes of a single graph
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spShowActivityGraphByChunks
@ServerName sysname,
@ReportDate datetime,
@StartTime varchar(7) = '8:30AM',
@EndTime varchar(7) = '5:30PM',
@ChunkSize int = 30
AS
DECLARE @TraceStart datetime
DECLARE @TraceEnd datetime
DECLARE @ReportDateShort varchar(20)
DECLARE @SqlStr varchar(2000)
SET @ReportDateShort = REPLACE(LEFT(CONVERT(varchar(20),@ReportDate,107),6),' ','')
SET @TraceStart = CONVERT(varchar(20),@ReportDate,107)+' '+@StartTime
WHILE @TraceStart <= CONVERT(varchar(20),@ReportDate,107)+' '+@EndTime
BEGIN
SET @TraceEnd = DATEADD(mi,@ChunkSize,@TraceStart)
SET @SqlStr = 'spActivityGraph Trace_'+@ServerName+@ReportDateShort+ ', '''+
CONVERT(varchar(40),@TraceStart)+''', '''+CONVERT(varchar(30),@TraceEnd)+''''
EXEC (@SqlStr)
SET @TraceStart = DATEADD(mi,60,@TraceStart)
END
--------------------
http://www.sqlservercentral.com/columnists/vgorod/monitoringperformance_scripts.txt