CREATE TABLE ServerList_SSIS([Server] NVARCHAR(512),
[Connect] bit,
[DMZ] bit);
INSERT INTO ServerList_SSIS([Server])
EXEC master..xp_cmdshell 'sqlcmd /Lc';
Delete from ServerList_SSIS where [Server] IS NULL;
Select * from ServerList_SSIS
vrijdag 21 november 2008
donderdag 20 november 2008
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
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
spTraceBuild
--Listing 3. spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
sp_BlockedProcesses
--Listing 2. sp_BlockedProcesses
USE [master]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_BlockedProcesses' and type = 'P')
DROP PROC sp_BlockedProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_BlockedProcesses
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses
exec sp_BlockedProcesses
USE [master]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_BlockedProcesses' and type = 'P')
DROP PROC sp_BlockedProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_BlockedProcesses
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses
exec sp_BlockedProcesses
Sp_ActiveProcesses
--Listing 1. Sp_ActiveProcesses.
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ActiveProcesses' and type = 'P')
DROP PROC sp_ActiveProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_ActiveProcesses
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ActiveProcesses' and type = 'P')
DROP PROC sp_ActiveProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_ActiveProcesses
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver
Abonneren op:
Posts (Atom)