Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Slow Merge Replication

Unanswered Slow Merge Replication

  • Friday, November 30, 2012 3:29 AM
     
     
    Sign In to Vote
    0

    We are using merge replication with SQL 2012. The first sync we do is slow, and often times out. We are using precomputed partitions.

    We have traced it back to this SQL statement being called in sp_MSsetupbelongs,

    select distinct b.tablenick, b.rowguid, c.colv1, c.generation, c.lineage, 
    sys.fn_MSgeneration_downloadonly(c.generation, c.tablenick), 
    sys.fn_MSvector_downloadonly(c.lineage, c.tablenick), 
    sys.fn_MSvector_downloadonly(c.colv1, c.tablenick) from
                    #belong b left outer join dbo.MSmerge_contents c
                    on  c.tablenick = b.tablenick and c.rowguid = b.rowguid

    I set it up in management studio with the temporary table, and it ran very slow.

    If I remove the functions from the select list the query is very fast. Why are the functions there like that? Couldn't this have been done with table joins to the sysmergearticles table? This is very inefficient and seems like a mistake?


    • Edited by pmccly Friday, November 30, 2012 3:44 AM
    • Edited by pmccly Friday, November 30, 2012 7:24 AM
    • Edited by pmccly Friday, November 30, 2012 7:41 AM
    • Edited by pmccly Friday, November 30, 2012 7:42 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:31 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:31 AM
    •  
     

All Replies

  • Friday, November 30, 2012 7:48 AM
    Moderator
     
     
    Sign In to Vote
    0
    How many articles do you have in your publication?

    Brandon Williams (blog | linkedin)

     
  • Friday, November 30, 2012 7:56 AM
     
     
    Sign In to Vote
    0

    We have over 700, which is more than officially supported, but that doesn't explain why the query was written like that in the first place. It still seems strange to me.

    For instance if I profile the query,

    select distinct b.tablenick, b.rowguid, c.colv1, c.generation, c.lineage, 
    sys.fn_MSgeneration_downloadonly(c.generation, c.tablenick), 
    sys.fn_MSvector_downloadonly(c.lineage, c.tablenick), 
    sys.fn_MSvector_downloadonly(c.colv1, c.tablenick) from
                    #belong b left outer join dbo.MSmerge_contents c
                    on  c.tablenick = b.tablenick and c.rowguid = b.rowguid

    I can see this query being run thousands and thousands of times,

    if exists (select top 1 artid from dbo.sysmergearticles where 
                                        nickname = @tablenick and
                                        (upload_options = 1 or upload_options = 2))

    Which is what is inside the function sys.fn_MSgeneration_downloadonly and sys.fn_MSvector_downloadonly

    It increases the number of reads from 4000 -> 230000, and the number of CPU from 500 -> 7500. This could have been done with a table join couldn't it?

    I guess the answer is that in the functions fn_MSgeneration_downloadonly and fn_MSvector_downloadonly is some other logic, but it still seems like it could be improved.


    • Edited by pmccly Friday, November 30, 2012 7:57 AM
    • Edited by pmccly Friday, November 30, 2012 7:58 AM
    • Edited by pmccly Friday, November 30, 2012 8:01 AM
    • Edited by pmccly Friday, November 30, 2012 8:01 AM
    • Edited by pmccly Friday, November 30, 2012 8:03 AM
    • Edited by pmccly Friday, November 30, 2012 8:03 AM
    • Edited by pmccly Friday, November 30, 2012 8:04 AM
    • Edited by pmccly Friday, November 30, 2012 8:07 AM
    • Edited by pmccly Friday, November 30, 2012 8:13 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:32 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:34 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:36 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:37 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:37 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:40 AM
    • Edited by pmccly Tuesday, December 04, 2012 3:42 AM
    •