1

I have three databases (one publisher, two subscribers) running merge replication and this morning noticed very slow performance. They sync on 10 second intervals but this morning it was taking much longer to see changes get pushed.

I checked to see which queries were running using this:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

and got this as a result:

    (@rowguid UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000')

DECLARE @gen1 BIGINT
DECLARE @gen2 BIGINT
DECLARE @gen3 BIGINT
DECLARE @gen4 BIGINT
DECLARE @gen5 BIGINT
DECLARE @gen6 BIGINT
DECLARE @gen7 BIGINT
DECLARE @gen8 BIGINT
DECLARE @gen9 BIGINT
DECLARE @gen10 BIGINT
DECLARE @gen11 BIGINT
DECLARE @gen12 BIGINT
DECLARE @gen13 BIGINT
DECLARE @gen14 BIGINT
DECLARE @gen15 BIGINT
DECLARE @gen16 BIGINT
DECLARE @gen17 BIGINT
DECLARE @gen18 BIGINT
DECLARE @gen19 BIGINT
DECLARE @gen20 BIGINT
DECLARE @gen21 BIGINT
DECLARE @gen22 BIGINT
DECLARE @gen23 BIGINT
DECLARE @gen24 BIGINT
DECLARE @gen25 BIGINT

SELECT @gen1 = 1262662

SELECT @gen2 = 1262669

SELECT @gen3 = 1262675

SELECT @gen4 = 1262680

SELECT @gen5 = 1262687

SELECT @gen6 = 1263078

SELECT @gen7 = 1263131

SELECT @gen8 = 1263140

SELECT @gen9 = 1263169

SELECT @gen10 = 1263171

SELECT @gen11 = 1263175

SELECT @gen12 = 1263233

SELECT @gen13 = 1263245

SELECT @gen14 = 1263252

SELECT @gen15 = 1263255

SELECT @gen16 = 1263270

SELECT @gen17 = NULL

SELECT @gen18 = NULL

SELECT @gen19 = NULL

SELECT @gen20 = NULL

SELECT @gen21 = NULL

SELECT @gen22 = NULL

SELECT @gen23 = NULL

SELECT @gen24 = NULL

SELECT @gen25 = NULL

SELECT TOP 100 mc.tablenick
    ,mc.rowguid
    ,mc.generation
    ,mc.lineage
    ,mc.colv1
    ,t.*
FROM [DATABASE_NAME].[dbo].[MSmerge_contents] mc
INNER JOIN [DATABASE_NAME].[dbo].[TABLE_NAME] t ON mc.rowguid = t.ROWGUIDCOL
INNER JOIN (
    SELECT @gen1 AS gen

    UNION ALL

    SELECT @gen2 AS gen

    UNION ALL

    SELECT @gen3 AS gen

    UNION ALL

    SELECT @gen4 AS gen

    UNION ALL

    SELECT @gen5 AS gen

    UNION ALL

    SELECT @gen6 AS gen

    UNION ALL

    SELECT @gen7 AS gen

    UNION ALL

    SELECT @gen8 AS gen

    UNION ALL

    SELECT @gen9 AS gen

    UNION ALL

    SELECT @gen10 AS gen

    UNION ALL

    SELECT @gen11 AS gen

    UNION ALL

    SELECT @gen12 AS gen

    UNION ALL

    SELECT @gen13 AS gen

    UNION ALL

    SELECT @gen14 AS gen

    UNION ALL

    SELECT @gen15 AS gen

    UNION ALL

    SELECT @gen16 AS gen

    UNION ALL

    SELECT @gen17 AS gen

    UNION ALL

    SELECT @gen18 AS gen

    UNION ALL

    SELECT @gen19 AS gen

    UNION ALL

    SELECT @gen20 AS gen

    UNION ALL

    SELECT @gen21 AS gen

    UNION ALL

    SELECT @gen22 AS gen

    UNION ALL

    SELECT @gen23 AS gen

    UNION ALL

    SELECT @gen24 AS gen

    UNION ALL

    SELECT @gen25 AS gen
    ) AS genlist ON mc.generation = genlist.gen
    AND genlist.gen IS NOT NULL
WHERE mc.generation >= 1262662
    AND mc.generation <= 1263270
    AND mc.tablenick = 7094001
ORDER BY mc.tablenick
    ,mc.rowguid

which I don't understand very well (note that I changed the database name and table name). The query stays in a running state but after about 500,000ms it seems like the query disappears for a few seconds then reappears.

When I check my sync status I get the following: Sync status

Anyone know what is causing this? Can I kill it?

  • I've found that this usually happens when a large number of changes have been made and need to be replicated and the Merge Agent encounters a query timeout. The default Merge Agent -QueryTimeout is 300 seconds, or 5 minutes. Try bumping up the -QueryTimeout parameter value to 600 or 900 seconds, restart the Merge Agent, and see if that works. – Brandon Williams Mar 3 '16 at 20:51
  • I actually just came back from lunch and the problem seems to have taken care of itself! I'll leave this question as is for now as I'll be repeating many of the same procedures as I have in the past 12 hours. It might recur. – tmwoods Mar 3 '16 at 21:10
  • That is great news – Brandon Williams Mar 3 '16 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.