Home Dashboard Directory Help

sp_MSsetupbelongs Query Slow With Merge Replication by pmccly



Sign in
to vote
Type: Bug
ID: 773374
Opened: 12/3/2012 8:09:41 PM
Access Restriction: Public
User(s) can reproduce this bug


In SQL Server 2012 with merge replication inside sp_MSsetupbelongs is this query,

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

This causes a performance problem for us because the functions sys.fn_MSgeneration_downloadonly and sys.fn_MSvector_downloadonly are called for every row in the query.

When profiling the above query I can see this query (which is inside the functions),

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

Being called thousands of times. When I remove the functions from the select list it reduces the number of reads from 227454 down to 3764.

Is this query intended to be like this?
Sign in to post a comment.
Posted by aconsolati on 3/28/2014 at 3:13 PM
Was this issue ever followed up? This query causes huge performance issues in production
Posted by Microsoft on 12/7/2012 at 2:36 PM
Hi pmccly,
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant 
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking
Sign in to post a workaround.