SQL Server Performance

DTS package/step tracking script

Discussion in 'Contribute Your SQL Server Scripts' started by Spyridon, Jan 27, 2005.

  1. Spyridon New Member

    I originaly placed this in the DTS forum, but being new here I reconsidered and thought that this might be the appropriate place.<br /><br /><br />I've created two sproc's to measure DTS package and DTS Step run times. It gives either an exact time or an average. Logging needs to be turned on for whatever package you want to look at. It also sets up a system function that converts time in seconds to a regular time format. I use it to tell what packages are currently running, which ones have errors, how long they ran, etc. It's basically the logging info that you get when you right click on a DTS package, but it's formatted and in one place. The two sp's are set up as system sp's so you can call them from any database. <br /><br />Hope it's of use!<br /><br />Michael<br /><br />Copy below this line and run in QA<br />________________________________________________________________________________________<br /><br /><br /><br /><br />USE Master<br />SET QUOTED_IDENTIFIER ON<br />SET ANSI_NULLS ON<br /><br />EXEC sp_configure 'allow updates', 1<br />EXEC sp_MS_upd_sysobj_category 1<br />RECONFIGURE WITH OVERRIDE<br />GO<br /><br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br /><br />IF EXISTS (SELECT id<br />FROM dbo.sysobjects<br />WHERE uid = USER_ID('system_function_schema')<br />AND name = 'fn_timeformat')<br />DROP FUNCTION system_function_schema.fn_timeformat<br />GO<br /><br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br /><br /><br /><br />CREATE FUNCTION system_function_schema.fn_timeformat(@ElapsedTime int)<br />RETURNS varchar(10)<br />AS<br />BEGIN<br /><br />/*This Function accepts a time value in seconds within the<br />bounds of an integer value and returns it in 00:00:00 format */<br /><br />DECLARE @Sec int, @Min int, @Hour int,<br />@PrintSec varchar(2), @PrintMin varchar(2), @PrintHour varchar(2),<br />@Time varchar(10)<br /><br />SET @Sec = @ElapsedTime<br />SET @Min = 0<br />SET @Hour = 0<br /><br />WHILE @Sec &gt;= 60<br />BEGIN <br />SET @Sec = @Sec - 60<br />SET @Min = @Min + 1<br />END<br /><br />WHILE @Min &gt;= 60 <br />BEGIN<br />SET @Min = @Min - 60<br />SET @Hour = @Hour + 1<br />END<br /><br />IF Len(@Sec) &lt; 2<br />BEGIN<br />SET @PrintSec = '0' + CAST(@Sec AS varchar) <br />END<br />ELSE<br />BEGIN<br />SET @PrintSec = CAST(@Sec AS varchar)<br />END<br /><br />IF Len(@Min) &lt; 2<br />BEGIN<br />SET @PrintMin = '0' + CAST(@Min AS varchar) <br />END<br />ELSE<br />BEGIN<br />SET @PrintMin = CAST(@Min AS varchar)<br />END<br /><br />IF Len(@Hour) &lt; 2<br />BEGIN<br />SET @PrintHour = '0' + CAST(@Hour AS varchar) <br />END<br />ELSE<br />BEGIN<br />SET @PrintHour = CAST(@Hour AS varchar)<br />END<br /><br />SET @Time = @PrintHour + ':' + @PrintMin + ':' + @PrintSec<br /><br />RETURN @Time<br /><br />END<br /><br />GO<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br /><br />IF OBJECT_ID(N'dbo.sp_PackageRunTime') IS NOT NULL<br />DROP PROC dbo.sp_PackageRunTime<br />GO<br /><br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br /><br />CREATE PROCEDURE dbo.sp_PackageRunTime<br /><br />@RunMode varchar(7) = 'Detail',<br />@StartDate datetime = NULL,<br />@EndDate datetime = NULL,<br />@Order nvarchar(100) = NULL<br /><br /><br />AS<br /><br />SET NOCOUNT ON<br />DECLARE @SQL nvarchar(4000)<br /><br />IF @StartDate IS NULL<br />SET @StartDate = CONVERT(varchar(15), GETDATE(), 106)<br /><br />IF @EndDate IS NULL<br />SET @EndDate = @StartDate<br /><br /><br /><br />IF @RunMode IN('Average','Avg')<br />BEGIN<br />IF @Order IS NULL<br />SET @Order = 'ETime desc'<br /><br />SET @SQL = <br />'SELECT Name [Package], fn_timeformat(CAST(AVG(ElapsedTime)AS int)) [ElapsedTime], <br />AVG(ElapsedTime) [ETime], MIN(LogDate) [MinLogDate], MAX(LogDate) [MaxLogDate]<br />FROM Msdb.dbo.SysDtsPackageLog<br />WHERE LogDate &gt;= ''' + CONVERT(varchar, @StartDate ,101) + '''<br />AND LogDate &lt; ''' + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) + '''<br />GROUP BY Name<br />ORDER BY ' + @Order<br />EXEC(@SQL)<br /><br />END<br /><br />IF @RunMode = 'Detail' <br /><br />BEGIN<br />IF @Order IS NULL<br />SET @Order = 'Etime desc,Package'<br /><br />SET @SQL =<br />'SELECT Name [Package], Description, fn_timeformat(ElapsedTime) [ElapsedTime],<br />StartTime, EndTime, Computer, Operator, ElapsedTime [ETime]<br />FROM Msdb.dbo.SysDtsPackageLog<br />WHERE LogDate &gt;= ''' + CONVERT(varchar, @StartDate, 101) + '''<br />AND LogDate &lt; ''' + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) + '''<br />ORDER BY ' + @Order<br />EXEC(@SQL)<br /><br />END<br /><br />IF @RunMode IN('?', 'Help')<br />PRINT' <br />Object: sp_PackageRunTime<br />Author: Michael West<br />Email: St_Spyridon@hotmail.com<br /><br />Execution: sp_PackageRunTime ''Detail'', ''01/01/05'' , etc<br />or<br />sp_PackageRunTime @RunMode = ''Average'', @StartDate = <br /><br />''01/01/05'', @EndDate = ''01/20/05''<br />or<br />exec(sp_PackageRunTime)<br /><br />Parameters: @RunMode varchar(7), @StartDate datetime, @EndDate datetime, @Order <br /><br />nvarchar(100)<br /><br />Dependencies: fn_timeformat, logging enabled within the DTS package you want to <br /><br />track.<br /><br />Description:<br /><br />This procedure checks the DTS logs and provides overview info on DTS packages. It <br /><br />can show either a detail for <br />each day selected within a range, or an average of those days. The default sort <br /><br />order is set to show either <br />the amount of time in desc order or the package and the amount of time in desc <br /><br />order, but that can be changed<br />to whatever is needed. The ElapsedTime column is varchar so sorting by it will not <br /><br />produce the desired<br />results. Instead sort by ETime, which is ElapsedTime in seconds.<br /><br />Arguments:<br /><br />@RunMode (optional) - The default is ''Detail''; Also possible are ''Average'' , <br /><br />''Avg'' , ''Help'', and ''?''.<br />@StartDate (optional) - GETDATE() if not provided.<br />@EndDate (optional) - @StartDate + 1 if not provided.<br />@Order (optional) - Any sql compliant order statement. <br /><br />'<br /><br /><br />GO<br /><br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br /><br />IF OBJECT_ID(N'dbo.sp_StepRunTime') IS NOT NULL<br />DROP PROC dbo.sp_StepRunTime<br />GO<br /><br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br />------------------------------------------------------------------------------------------<br /><br />-------------<br /><br /><br /><br />CREATE PROCEDURE dbo.sp_StepRunTime<br /><br />@RunMode varchar(7) = 'Detail',<br />@Step nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> = NULL,<br />@StartDate datetime = NULL,<br />@EndDate datetime = NULL,<br />@Order nvarchar(100) = NULL<br />AS<br />SET NOCOUNT ON<br /><br />DECLARE @SQL nvarchar(4000)<br /><br /><br />IF @StartDate IS NULL<br />SET @StartDate = CONVERT(varchar(15), GETDATE(), 106)<br /><br />IF @EndDate IS NULL<br />SET @EndDate = @StartDate<br /><br />IF @Step IS NULL<br />SET @Step = '%%'<br /><br /><br />IF @RunMode = 'Detail'<br />BEGIN<br /><br />IF @Order IS NULL<br />SET @Order = 'ErrorCode asc, Package, StartTime, ETime'<br />SET @SQL = <br />'SELECT Package, Step, ElapsedTime, StartTime, EndTime, ErrorCode, <br /><br />ErrorDescription, ProgressCount, ETime<br />FROM<br />(SELECT Pkg.Name [Package], Step.StepName [Step], fn_timeformat<br /><br />(Step.ElapsedTime) [ElapsedTime], <br />Step.StartTime, ISNULL(Step.EndTime, '''') [EndTime], Step.ErrorCode, <br />SUBSTRING(Step.ErrorDescription,24, LEN(Step.ErrorDescription)) <br /><br />[ErrorDescription], Step.ProgressCount, Step.ElapsedTime [ETime]<br />FROM Msdb.dbo.SysDtsStepLog Step<br />INNER JOIN Msdb.dbo.SysDtsPackageLog Pkg<br />ON Step.LineageFull = Pkg.LineageFull<br />WHERE Step.StartTime &gt; ''' + CONVERT(varchar, @StartDate, 101) + '''<br />AND Step.StartTime &lt;= ''' + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) <br /><br />+ '''<br />AND Step.StepName LIKE ''' + @Step + '''<br />)PackageSteps<br />ORDER BY ' + @Order<br /><br />EXEC(@SQL)<br /><br />END<br />IF @RunMode IN('Average', 'Avg')<br />BEGIN<br /><br />IF @Order IS NULL<br />SET @Order = 'ETime desc, Package, Step'<br />SET @SQL = <br />'SELECT Package, Step, ElapsedTime, MinLogDate, MaxLogDate, ETime<br />FROM<br />(SELECT Pkg.Name [Package], Step.StepName [Step], fn_timeformat(AVG<br /><br />(Step.ElapsedTime)) [ElapsedTime],<br />MIN(LogDate) [MinLogDate], MAX(LogDate) [MaxLogDate], AVG(Step.ElapsedTime) <br /><br />[ETime]<br />FROM Msdb.dbo.SysDtsStepLog Step<br />INNER JOIN Msdb.dbo.SysDtsPackageLog Pkg<br />ON Step.LineageFull = Pkg.LineageFull<br />WHERE Step.StartTime &gt; ''' + CONVERT(varchar, @StartDate, 101) + '''<br />AND Step.StartTime &lt;= ''' + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) <br /><br />+ '''<br />GROUP BY Pkg.Name, Step.StepName<br />)PackageSteps<br />ORDER BY ' + @Order<br /><br />EXEC(@SQL)<br /><br />END<br /><br />IF @RunMode IN('?', 'Help')<br />PRINT' <br />Object: sp_StepRunTime<br />Author: Michael West<br />Email: St_Spyridon@hotmail.com<br /><br />Execution: sp_StepRunTime ''Detail'', ''01/01/05'' , etc<br />or<br />sp_StepRunTime @RunMode = ''Average'', @StartDate = ''01/01/05'', <br /><br />@EndDate = ''01/20/05''<br />or<br />exec(sp_StepRunTime)<br /><br />Parameters: @RunMode varchar(7), @Step nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @StartDate datetime, <br /><br />@EndDate datetime, @Order nvarchar(100)<br /><br />Dependencies: fn_timeformat, logging enabled within the DTS package you want to <br /><br />track.<br /><br />Description:<br /><br />This procedure checks the DTS logs and returns that info in a readable format. The <br /><br />information returned helps in knowing<br />which steps within a DTS package has errored out, what the error message was, and <br /><br />whether or not it is currently running.<br />If it successfully ran then the total time it took for each individual step to <br /><br />complete. You also have the ability to<br />select an average time of each step. The default sort order is set to show the <br /><br />packages that are running and those that <br />have errored out in the Detail RunMode, i.e, ErrorCode asc, Package, StartTime, <br /><br />ETime. The default for the Average<br />RunMode is ETime desc, Package, Step. The ElapsedTime column is varchar so sorting <br /><br />by it will not produce the desired<br />results. Instead sort by ETime, which is ElapsedTime in seconds.<br /><br />Arguments:<br /><br />@RunMode (optional) - The default is ''Detail''; Also possible are ''Average'' , <br /><br />''Avg'' , ''Help'', and ''?''.<br />@Step (optional) - Any step name. If one is not provided then it becomes a wildcard <br /><br />selecting all.<br />@StartDate (optional) - GETDATE() if not provided.<br />@EndDate (optional) - @StartDate + 1 if not provided.<br />@Order (optional) - Any sql compliant order statement. <br /><br />'<br /><br />GO<br /><br /><br />EXEC sp_configure 'allow updates', 0<br />EXEC sp_MS_upd_sysobj_category 2<br />RECONFIGURE WITH OVERRIDE<br />GO<br />

Share This Page