none
Efficiency of Change Tracking queries

    Question

  • I am trying to use Change Tracking on tables in SQL Server 2012. One of the tables (with about 50 million rows) is updated daily and updates touch about 2 percent of records (about 1 million rows). I noticed that SQL Server analyzer is generating this very resource intensive plan even on a simple SELECT * from the change table function:

    Execution PLan

     If I run the query with the "actual plan" on, I can see that indeed the SQL Server did a scan, returned 191 GB of data. The query takes a long time.

    Is there any way to optimize the retrieval of data from change tracking tables? It appears that the underlying change_tracking table is missing an index, but I don't know if it's possible to add the index to the table since the table is hidden.

    Thank you for your help.

    Sunday, December 30, 2012 9:04 PM

Answers

All replies

  • HI

    Read the Microsoft MSDN "Tuning the Performance of Change Data Capture" article hope it will resolve your issue : 

    http://msdn.microsoft.com/en-us/library/dd266396%28SQL.100%29.aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, December 31, 2012 5:57 AM
  • There is already a fix available for 2008 and 2008 R2, however not sure this is still an issue in 2012:

    FIX: The "CHANGETABLE" function is slow in SQL Server 2008 and in SQL Server 2008 R2

    The following article will also be helpful:

    Tracking Changes in Your Enterprise Database - Paul S. Randal


    Krishnakumar S


    Monday, December 31, 2012 8:55 AM
  • There is already a fix available for 2008 and 2008 R2, however not sure this is still an issue in 2012:

    FIX: The "CHANGETABLE" function is slow in SQL Server 2008 and in SQL Server 2008 R2 <http://support.microsoft.com/kb/2276330>

    I would find it unlikely that this fix is not in SQL 2012. However, observe this passage:

    To apply this hotfix after you installed it, enable trace flag 4199. To enable this trace flag, run the DBCC TRACEON(4199, -1) command in an individual session, or add the -T4199 startup parameter.

    TF4199 is a general trace flag, that enables optimizer fixes since the RTM version. When Microsoft corrects an optimizer issue that only results in a poor choice of execution plan, they only make the fix available when this traceflag is active, to be sure they don't introduced plan regressions for someone else. (If the plan can produce incorrect results, the fix is enabled without the trace flag.)

    My understanding is that when the next major version comes along, you don't need the trace flag anymore, but I am not sure that this is always the case. So I would recommend Popsovy to try the trace flag, and see if this resolves the problem.

    One thing that occurs to me when I look at the query plan is that the estimted number of rows is very high, 95 million, more than the number of rows in the table. You say about 2% of the rows are modified daily. With the default retention period of 2 days, the expected number is rather one million rows. Have you checked what the AUTO_CLEANUP and CHANGE_RETENTION settings are for this database?

    I also note that the estimated row size is high. This indicates that you have TRACK_COLUMNS_UPDATED ON for the table, and you have some wide columns in that table. I assume that you need this option to be on, but if you don't need tracking of the wide columns, maybe you should split the table vertically.

    Finally, I note both Krishnamumar and Ahsan gave links to Change Data Capture - that's a completely different feature with no relation to Change Tracking.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 31, 2012 12:12 PM
  • Finally, I note both Krishnamumar and Ahsan gave links to Change Data Capture - that's a completely different feature with no relation to Change Tracking.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Erland, thanks for pointing this out. I have removed the CDC links from the thread.

    Krishnakumar S

    Monday, December 31, 2012 12:29 PM