Tracking Merge Agent Performance using Distribution database system tables

Amit Banerjee | Microsoft SQL Server Support

TSQL Code: MS_MergeAgentStatistics.sql script located on SkyDrive MergePerformance or TechNet Code Share

I have sometimes found the need to track the performance of the Merge Agents configured on the server without having to dig through the Replication Monitor or through the Merge Agent job history outputs. So, here goes a small script to help you pull out the TOP N slow performing Merge Agent sessions and their details.

The script below uses a temporary table to pull data from the following tables in the distribution database:

MSmerge_sessions
MSmerge_agents
MSmerge_history
MSmerge_articlehistory
MSrepl_errors

The script below relies on two parameters:

If you set @detail to 1, then the slow performing Merge session details are fetched from the MSmerge_history table. For the default value, the session details are fetched from the MSmerge_articlehistory table.

The detail output will also provide any errors that occurred during that session along with the error message which is obtained from the MSrepl_errors table.

The temporary table used below in the script does a join on the MSmerge_sessions and MSmerge_agents table. The two tables can be linked using the agent_id column in the MSmerge_sessions table and the id column in the MSmerge_agents table. The table below shows how the tables used in the T-SQL script can be co-related with the columns.

How these tables can be used to pull Merge Statistics

The session details can help you identify the following:

1. Which phase of the synch was slow

2. Which article was the problem

3. What changes were being applied that took the most amount of time

/*

Tracking Merge Agent Performance using Distribution database system tables

http://blogs.msdn.com/repltalk

Amit Banerjee

Microsoft SQL Server Support

Script to pull out the TOP N slow performing Merge Agent sessions and their details.

@detail – This parameter decides if a detailed report will be run.

       = 0 (default) Summary Report

       Data fetched from the MSmerge_history table.

       = 1 Detailed Report

       Data fetched from the MSmerge_articlehistory table.

@maxsessions – This parameter decides how many

       worst performing sessions are tracked using the script.

*/

— Run this script against the distributor database

— Move data into a temporary table

SELECT IDENTITY(int,1,1) as RowID,mss.session_id,mss.agent_id,mss.start_time,

mss.end_time,mss.duration,mss.delivery_time,

mss.upload_time,mss.download_time,mss.schema_change_time,mss.prepare_snapshot_time,

mss.delivery_rate,mss.time_remaining,mss.percent_complete,mss.upload_inserts,

mss.upload_updates,mss.upload_deletes,mss.upload_conflicts,mss.upload_rows_retried,

mss.download_inserts,mss.download_updates,mss.download_deletes,mss.download_conflicts,

mss.download_rows_retried,mss.schema_changes,mss.bulk_inserts,mss.metadata_rows_cleanedup,

mss.runstatus,mss.estimated_upload_changes,mss.estimated_download_changes,

mss.connection_type,mss.current_phase_id,mss.spid,mss.spid_login_time,

msa.name,msa.publisher_id,msa.publisher_db,msa.publication,msa.subscriber_id,

msa.subscriber_db,msa.local_job,msa.job_id,msa.profile_id,msa.anonymous_subid,

msa.subscriber_name,msa.creation_date,msa.offload_enabled,msa.offload_server,

msa.subscriber_login,msa.publisher_security_mode,msa.publisher_login

INTO #tmptbl_css_mergeperf

FROM dbo.MSmerge_sessions mss

INNER JOIN dbo.MSmerge_agents msa

ON mss.agent_id = msa.id

ORDER BY duration DESC

— Define local variables to used for generating the report

SET NOCOUNT ON

DECLARE @detail int, @maxsessions int

SET @detail = 0 — Decides whether you want a detailed report

SET @maxsessions = 5 — The number of slow performing sessions that you want reported

— Declare local variables to do the processing

DECLARE @maxval bigint

— Get the number of entries in the temporary table

SELECT @maxval = COUNT(*) FROM #tmptbl_css_mergeperf

— Display the TOP slow performing merge sessions

SELECT TOP (@maxsessions) CHAR(13)+‘***** Session ID ‘+CAST(session_id AS varchar(5)) + ‘ *****’,

CHAR(13)+‘Start Time: ‘+ CAST (start_time AS varchar(25))+SPACE(1)+CHAR(9)

       +‘End Time: ‘ +CAST(end_time AS varchar(25)),

CHAR(13)+‘Session Status: ‘+CASE runstatus

       WHEN 1 THEN ‘Start’ 

       WHEN 2 THEN ‘Succeeded’

       WHEN 3 THEN ‘In Progress’ WHEN 4 THEN ‘Idle’

       WHEN 5 THEN ‘Retry’  WHEN 6 THEN ‘Fail’   ELSE ‘Unknown’       END,

CHAR(13)+‘Connection used during upload: ‘ + CASE connection_type

       WHEN 1 THEN ‘Local area network (LAN)’

       WHEN 2 THEN ‘Dial-up network connection’

       WHEN 3 THEN ‘Web synchronization’ ELSE ‘Unknown’ END,

CHAR(13)+‘Merge Agent Name: ‘+ name,

CHAR(13)+‘Publisher Database: ‘+ publisher_db,

CHAR(13)+‘Subscriber Database: ‘+ subscriber_db,

CHAR(13)+‘Publication Name: ‘+ publication,

CHAR(13)+‘Subscriber Name: ‘+ subscriber_name,

CHAR(13)+‘Job running on Distributor: ‘+ CASE local_job WHEN 1 THEN ‘YES’ ELSE ‘NO’ END,

CHAR(13)+‘*** Session Statistics ***’+CHAR(13)+CHAR(9)+‘Delivery Time (in seconds): ‘

       +CAST(delivery_time AS varchar(10)),

CHAR(13)+CHAR(9)+‘Time taken to upload changes to Publisher (in seconds): ‘

       +CAST(upload_time AS varchar(10)),

CHAR(13)+CHAR(9)+‘Time taken to download changes from Publisher (in seconds): ‘

       +CAST(download_time AS varchar(10)),

CHAR(13)+CHAR(9)+‘Average number of delivered commands per second: ‘

       +CAST(delivery_rate AS varchar(10)),

CHAR(13)+CHAR(9)+‘INSERTS applied at the Publisher: ‘

       +CAST(upload_inserts AS varchar(10)),

CHAR(13)+CHAR(9)+‘UPDATES applied at the Publisher: ‘

       +CAST(upload_updates AS varchar(10)),

CHAR(13)+CHAR(9)+‘DELETES applied at the Publisher: ‘

       +CAST(upload_deletes AS varchar(10)),

CHAR(13)+CHAR(9)+‘Rows being uploaded to the Publisher that were retried: ‘

       +CAST(upload_rows_retried AS varchar(10)),

CHAR(13)+CHAR(9)+‘INSERTS applied at the Subscriber: ‘

       +CAST(download_inserts AS varchar(10)),

CHAR(13)+CHAR(9)+‘UPDATES applied at the Subscriber: ‘

       +CAST(download_updates AS varchar(10)),

CHAR(13)+CHAR(9)+‘DELETES applied at the Subscriber: ‘

       +CAST(download_deletes AS varchar(10)),

CHAR(13)+CHAR(9)+‘Rows being downloaded to the Subscriber that were retried: ‘

       +CAST(download_rows_retried AS varchar(10)),

CHAR(13)+CHAR(9)+‘Conflicts that occurred while applying changes at the Publisher: ‘

       +CAST(upload_conflicts AS varchar(10)),

CHAR(13)+CHAR(9)+‘Conflicts that occurred while applying changes at the Subscriber: ‘

       +CAST(download_conflicts AS varchar(10)),

CHAR(13)+CHAR(9)+‘Schema changes applied during the session: ‘

       +CAST(schema_changes AS varchar(10)),

CHAR(13)+CHAR(9)+‘Rows of metadata cleaned-up during the session: ‘

       +CAST(metadata_rows_cleanedup AS varchar(10))

FROM #tmptbl_css_mergeperf

WHERE runstatus NOT IN (3,6)

ORDER BY duration DESC

PRINT CHAR(13)

PRINT ‘***** TOP 3 articles that took the longest in the synch for each of the above Sessions *****’

— Declare local variables for running WHILE loop

DECLARE @loopctr int,@agent_id int,@session_id int, @RowID int

SET @loopctr = 1

SET @RowID = 1

— Start WHILE loop

WHILE (@loopctr <= @maxsessions)

BEGIN

       REDO:

       — Get the session and agent id

       IF EXISTS (SELECT *

       FROM #tmptbl_css_mergeperf

       WHERE runstatus NOT IN (3,6) AND RowID = @RowID)

       BEGIN

              SELECT @agent_id = agent_id, @session_id = session_id

              FROM #tmptbl_css_mergeperf

              WHERE runstatus NOT IN (3,6) AND RowID = @RowID

              — Show detailed report if it’s been asked for

              IF (@detail = 1)

              BEGIN

                     SELECT mshis.session_id,mshis.agent_id,mshis.comments,

                           mshis.error_id,mshis.updateable_row,mshis.[time],

                           mserr.error_code,mserr.error_text,mserr.source_name

                     FROM dbo.MSmerge_history mshis

                     LEFT OUTER JOIN MSrepl_errors mserr

                     ON mshis.error_id = mserr.id

                     WHERE mshis.agent_id = @agent_id and mshis.session_id = @session_id

              END

              ELSE

              BEGIN

                     SELECT TOP (3) CHAR(13)+‘*** Session ID: ‘ + CAST(session_id AS varchar(5)),

                     CHAR(13)+CHAR(9)+‘Phase: ‘ + CASE phase_id

                     WHEN 1 THEN ‘Upload’

                     WHEN 2 THEN ‘Download’

                     WHEN 4 THEN ‘Cleanup’

                     WHEN 5 THEN ‘Shutdown’

                     WHEN 6 THEN ‘Schema Changes’

                     WHEN 7 THEN ‘BCP’

                     ELSE ‘Unknown’ END,

                     CHAR(13)+CHAR(9)+‘Article name: ‘ + article_name,

                     CHAR(13)+CHAR(9)+‘Start Time: ‘

                           + CAST (start_time AS varchar(25)),

                     CHAR(13)+CHAR(9)+‘End Time: ‘

                           + CAST (DATEADD(s,duration,start_time) AS varchar(25)),

                     CHAR(13)+CHAR(9)+‘Duration (in seconds): ‘

                           + CAST (duration AS varchar(10)),

                     CHAR(13)+CHAR(9)+‘Inserts: ‘ + CAST (inserts AS varchar(10)),

                     CHAR(13)+CHAR(9)+‘Deletes: ‘ + CAST (deletes AS varchar(10)),

                     CHAR(13)+CHAR(9)+‘Updates: ‘ + CAST (updates AS varchar(10)),

                     CHAR(13)+CHAR(9)+‘Conflicts: ‘ + CAST (conflicts AS varchar(10)),

                     CHAR(13)+CHAR(9)+‘Rows retried: ‘

                           + CAST (rows_retried AS varchar(10)),

                     CHAR(13)+CHAR(9)+‘Relative cost: ‘

                           + CAST (relative_cost AS varchar(10))

                     FROM dbo.MSmerge_articlehistory

                     WHERE session_id = @session_id

                     ORDER BY duration DESC

              END

       END

       ELSE

       BEGIN

              SET @RowID = @RowID + 1

              GOTO REDO

       END

       — Increment loop counter

       SET @RowID = @RowID + 1

       SET @loopctr = @loopctr + 1

END

— Drop the temporary table

DROP TABLE #tmptbl_css_mergeperf