locked
SQLServer Change Tracking - Internal table in need of index? RRS feed

  • Question

  • Hi

    The performance of my real world deployed SQL Server 2008 Change Tracking/Sync Services based multi-client application is hitting performance issues. Using SQL Profiler I have determined that the standard query type executed against SQL Server to determine changed rows for any given table during the DBServerSyncProvider's sync routine is responsible for the slowdown.

    This query (shortened as much as possible) demos the issue:

    exec sp_executesql N'IF @sync_initialized = 0 SELECT DummyTable.[PK_DummyTableID] FROM
    DummyTable LEFT OUTER JOIN CHANGETABLE(CHANGES DummyTable, @sync_last_received_anchor) CT ON CT.[PK_DummyTableID] = DummyTable.PK_DummyTableID
    WHERE  (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary);'
    ,N'@sync_initialized int,@sync_last_received_anchor bigint,@sync_client_id_binary binary(16),@sync_new_received_anchor bigint,@sync_table_name nvarchar(19),@FilterID varchar(256)',@sync_initialized=0,@sync_last_received_anchor=0,@sync_client_id_binary=0x154807C211FEF8438D0C371C253BD1AF,@sync_new_received_anchor=3091837,@sync_table_name=N'DummyTable',@FilterID='70a3b405-2922-4a48-bff6-1c1d48cd5857'

    The WHERE clause wherein the SYS_CHANGE_CONTEXT column in the internal per table change tracking check is apparently not indexed and as such my syncs against active tables tend to degrade over time. These active tables generate a lot of changes in the internal change tracking table and then queries like the one above take a long time to execute doing the simple comparisons demonstrated in the one line WHERE clause above.

    Can I somehow create an index on the SYS_CHANGE_CONTEXT column on the internal SQL Server change tracking table?? Is there anything else I can do to up performance? That query you see takes 8 seconds to execute on the fastest server/DAS combo I have ever come across.

    Thanks for your time.
    Shane
    • Moved by Hengzhe Li Friday, April 22, 2011 5:24 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, March 10, 2009 11:30 PM

Answers

  • Shane,

    The query above is fired off for initial sync.  Typically, for initial sync the idea is to get all data and we added to join/filter to CHANGETABLE to account for developers that want to change from upload only to bi-di.  Somewhat of an edge case so if you do not feel that the join/filter is important for initial sync I typically recommend that customers remove them altogether.  If your concern is for subsequent sync, there may not be much you can do here assuming your application is syncing changes in a bi-directional fashion.  That SYS_CHANGE_CONTEXT field is used to filter out loopbacks and avoid downloading any changes I made from the server.  That being said, there might be some optimizations we can make in the future within the integrated change tracking feature to speed this up and I will submit your feedback for the next release of sql server.  If you are only interested in a download only scenario you can remove the filter/join.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft
    Thursday, March 12, 2009 6:05 PM
    Moderator