SQL Fool Adventures in SQL Tuning – a blog for the rest of us

IF OBJECT_ID('dbo.dba_replicationMonitor') IS Null
BEGIN
    CREATE TABLE dbo.dba_replicationMonitor
    ( 
          monitor_id            INT IDENTITY(1,1)   Not Null
        , monitorDate           SMALLDATETIME       Not Null 
        , publicationName       sysname             Not Null
        , publicationDB         sysname             Not Null
        , iteration             INT                 Null
        , tracer_id             INT                 Null
        , distributor_latency   INT                 Null
        , subscriber            VARCHAR(1000)       Null
        , subscriber_db         VARCHAR(1000)       Null
        , subscriber_latency    INT                 Null
        , overall_latency       INT                 Null 
    );
END;
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_replicationLatencyMonitor_sp'), N'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.dba_replicationLatencyMonitor_sp;
    PRINT 'Procedure dba_replicationLatencyMonitor_sp dropped';
END;
Go
 
SET Quoted_Identifier ON
Go
SET ANSI_Nulls ON
Go
 
CREATE PROCEDURE dbo.dba_replicationLatencyMonitor_sp
 
        /* Declare Parameters */
          @publicationToTest    sysname        = N'yourPublicationName'
        , @publicationDB        sysname        = N'yourPublicationDB'
        , @replicationDelay     VARCHAR(10)    = N'00:00:30'
        , @iterations           INT            = 5
        , @iterationDelay       VARCHAR(10)    = N'00:00:30'
        , @displayResults       BIT            = 0
        , @deleteTokens         BIT            = 1
AS
/*********************************************************************************
    Name:       dba_replicationLatencyMonitor_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves the amount of replication latency in seconds
 
    Notes:      Default settings will run 1 test every minute for 5 minutes.
 
                @publicationToTest = defaults to yourPublicationName publication
 
                @publicationDB = the database that is the source for the publication.
				    The tracer procs are found in the publishing DB.
 
                @replicationDelay = how long to wait for the token to replicate;
                    probably should not set to anything less than 10 (in seconds)
 
                @iterations = how many tokens you want to test
 
                @iterationDelay = how long to wait between sending test tokens
                    (in seconds)
 
                @displayResults = print results to screen when complete
 
                @deleteTokens = whether you want to retain tokens when done
 
    Called by:  DBA
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2008-11-20   MFU        Initial Release
    2008-11-24	 ILK        Tweaked to allow for centralized execution 
                            Replaced temp table with permanent table.
    2008-11-25   MFU        More tweaking, added publication data to 
                            dba_replicationMonitor, fixed NULL latency data,
                            moved dba_replicationMonitor creation out of proc
*********************************************************************************
    Exec dbo.dba_replicationLatencyMonitor_sp
          @publicationToTest    = N'myTestPublication'
        , @publicationDB        = N'sandbox_publisher'
        , @replicationDelay     = N'00:00:05'
        , @iterations           = 1
        , @iterationDelay       = N'00:00:05'
        , @displayResults       = 1
        , @deleteTokens         = 1;
*********************************************************************************/
 
SET NOCOUNT ON;
SET XACT_Abort ON;
 
BEGIN
 
    /* Declare Variables */
    DECLARE @currentIteration   INT
          , @tokenID            BIGINT
          , @currentDateTime    SMALLDATETIME
          , @sqlStatement       NVARCHAR(200)
          , @parmDefinition		NVARCHAR(500);
 
    DECLARE @tokenResults TABLE
    ( 
          iteration             INT             Null
        , tracer_id             INT             Null
        , distributor_latency   INT             Null
        , subscriber            VARCHAR(1000)   Null
        , subscriber_db         VARCHAR(1000)   Null
        , subscriber_latency    INT             Null
        , overall_latency       INT             Null 
    );
 
    /* Initialize our variables */
    SELECT @currentIteration = 0
         , @currentDateTime  = GETDATE();
 
    WHILE @currentIteration < @iterations
    BEGIN
 
		/* Prepare the stored procedure execution string */
		SET @sqlStatement = N'Execute ' + @publicationDB + N'.sys.sp_postTracerToken ' + 
							N'@publication = @VARpublicationToTest , ' +
							N'@tracer_token_id = @VARtokenID OutPut;'
 
		/* Define the parameters used by the sp_ExecuteSQL later */
		SET @parmDefinition = N'@VARpublicationToTest sysname, ' +
			N'@VARtokenID bigint OutPut';
 
        /* Insert a new tracer token in the publication database */
        EXECUTE SP_EXECUTESQL 
              @sqlStatement
            , @parmDefinition
            , @VARpublicationToTest = @publicationToTest
            , @VARtokenID = @TokenID OUTPUT;
 
        /* Give a few seconds to allow the record to reach the subscriber */
        WAITFOR Delay @replicationDelay;
 
        /* Prepare our statement to retrieve tracer token data */
        SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_helpTracerTokenHistory ' +
                    N'@publication = @VARpublicationToTest , ' +
                    N'@tracer_id = @VARtokenID'
            , @parmDefinition = N'@VARpublicationToTest sysname, ' +
                    N'@VARtokenID bigint';
 
        /* Store our results for retrieval later */
        INSERT INTO @tokenResults
        (
            distributor_latency
          , subscriber
          , subscriber_db
          , subscriber_latency
          , overall_latency
        )
        EXECUTE SP_EXECUTESQL 
              @sqlStatement
            , @parmDefinition
            , @VARpublicationToTest = @publicationToTest
            , @VARtokenID = @TokenID;
 
        /* Assign the iteration and token id to the results for easier investigation */
        UPDATE @tokenResults
        SET iteration = @currentIteration + 1
          , tracer_id = @tokenID
        WHERE iteration IS Null;
 
        /* Wait for the specified time period before creating another token */
        WAITFOR Delay @iterationDelay;
 
        /* Avoid endless looping... :) */
        SET @currentIteration = @currentIteration + 1;
 
    END;
 
    /* Display our results */
    IF @displayResults = 1
    BEGIN
        SELECT 
              iteration
            , tracer_id
            , IsNull(distributor_latency, 0) AS 'distributor_latency'
            , subscriber
            , subscriber_db
            , IsNull(subscriber_latency, 0) AS 'subscriber_latency'
            , IsNull(overall_latency, 
                IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
                AS 'overall_latency'
        FROM @tokenResults;
    END;
 
    /* Store our results */
    INSERT INTO dbo.dba_replicationMonitor
    (
          monitorDate
        , publicationName
        , publicationDB
        , iteration
        , tracer_id
        , distributor_latency
        , subscriber
        , subscriber_db
        , subscriber_latency
        , overall_latency
    )
    SELECT 
          @currentDateTime
        , @publicationToTest
        , @publicationDB
        , iteration
        , tracer_id
        , IsNull(distributor_latency, 0)
        , subscriber
        , subscriber_db
        , IsNull(subscriber_latency, 0)
        , IsNull(overall_latency, 
            IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
    FROM @tokenResults;
 
    /* Delete the tracer tokens if requested */
    IF @deleteTokens = 1
    BEGIN
 
        SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_deleteTracerTokenHistory ' +
                    N'@publication = @VARpublicationToTest , ' +
                    N'@cutoff_date = @VARcurrentDateTime'
            , @parmDefinition = N'@VARpublicationToTest sysname, ' +
                    N'@VARcurrentDateTime datetime';
 
        EXECUTE SP_EXECUTESQL 
              @sqlStatement
            , @parmDefinition
            , @VARpublicationToTest = @publicationToTest
            , @VARcurrentDateTime = @currentDateTime;
 
    END;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go
 
SET Quoted_Identifier OFF;
Go
SET ANSI_Nulls ON;
Go

CREATE PROCEDURE dbo.dba_replicationLatencyGet_sp
 
        /* Declare Parameters */
          @publicationToTest sysname        = N'yourPublicationName'
        , @replicationDelay  VARCHAR(10)    = N'00:00:30'
        , @iterations        INT            = 5
        , @iterationDelay    VARCHAR(10)    = N'00:00:30'
        , @deleteTokens      BIT            = 1
        , @deleteTempTable   BIT            = 1
AS
/*********************************************************************************
    Name:       dba_replicationLatencyGet_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves the amount of replication latency in seconds
 
    Notes:      Default settings will run 1 test every minute for 5 minutes.
 
                @publicationToTest = change the default to your publication
 
                @replicationDelay = how long to wait for the token to replicate;
                    probably should not set to anything less than 10 (in seconds)
 
                @iterations = how many tokens you want to test
 
                @iterationDelay = how long to wait between sending test tokens
                    (in seconds)
 
                @deleteTokens = whether you want to retain tokens when done
 
                @deleteTempTable = whether or not to retain the temporary table
                    when done.  Data stored to ##tokenResults; set @deleteTempTable 
                    flag to 0 if you do not want to delete when done.
 
    Called by:  DBA
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2008-11-20   MFU        Initial Release
*********************************************************************************
    Exec dbo.dba_replicationLatencyGet_sp
          @publicationToTest    = N'yourPublicationName'
        , @replicationDelay     = N'00:00:05'
        , @iterations           = 1
        , @iterationDelay       = N'00:00:05'
        , @deleteTokens         = 1
        , @deleteTempTable      = 1;
*********************************************************************************/
 
SET NOCOUNT ON;
SET XACT_Abort ON;
 
BEGIN
 
    /* Declare Variables */
    DECLARE @currentIteration   INT
          , @tokenID            BIGINT
          , @currentDateTime    SMALLDATETIME;
 
    IF OBJECT_ID('tempdb.dbo.##tokenResults') IS Null
    BEGIN
        CREATE TABLE ##tokenResults
                        ( iteration           INT             Null
                        , tracer_id           INT             Null
                        , distributor_latency INT             Null
                        , subscriber          VARCHAR(1000)   Null
                        , subscriber_db       VARCHAR(1000)   Null
                        , subscriber_latency  INT             Null
                        , overall_latency     INT             Null );
    END;
 
    /* Initialize our variables */
    SELECT @currentIteration = 0
         , @currentDateTime  = GETDATE();
 
    WHILE @currentIteration < @iterations
    BEGIN
 
        /* Insert a new tracer token in the publication database */
        EXECUTE sys.sp_postTracerToken 
          @publication = @publicationToTest,
          @tracer_token_id = @tokenID OUTPUT;
 
        /* Give a few seconds to allow the record to reach the subscriber */
        WAITFOR Delay @replicationDelay;
 
        /* Store our results in a temp table for retrieval later */
        INSERT INTO ##tokenResults
        (
            distributor_latency
          , subscriber
          , subscriber_db
          , subscriber_latency
          , overall_latency
        )
        EXECUTE sys.sp_helpTracerTokenHistory @publicationToTest, @tokenID;
 
        /* Assign the iteration and token id to the results for easier investigation */
        UPDATE ##tokenResults
        SET iteration = @currentIteration + 1
          , tracer_id = @tokenID
        WHERE iteration IS Null;
 
        /* Wait for the specified time period before creating another token */
        WAITFOR Delay @iterationDelay;
 
        /* Avoid endless looping... :) */
        SET @currentIteration = @currentIteration + 1;
 
    END;
 
    SELECT * FROM ##tokenResults;
 
    IF @deleteTempTable = 1
    BEGIN
        DROP TABLE ##tokenResults;
    END;
 
    IF @deleteTokens = 1
    BEGIN
       EXECUTE sp_deleteTracerTokenHistory @publication = @publicationToTest, @cutoff_date = @currentDateTime;
    END;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go