Home Dashboard Directory Help
Search

sp_MSsetupbelongs Query Slow With Merge Replication by pmccly


Status: 

Active


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

Description

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)
    from
    #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?
Details
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.