4

Has anyone else experience bad plans when calling CHANGETABLE in SQL Server 2016?

I have an application that uses change tracking to update a cache. Application servers get the changes for multiple tables every second. The CHANGETABLE is called so often that usually returns just a few rows. This works great in 2008 R2 and 2012. When I tested 2016 I found that the CPU spiked and found that the plan is scanning change tracking internal tables where it used to do a seek.

If you want to see this behavior here are the steps to replicate it:

  1. Create a database and setup change tracking.
  2. Create a Table:

    CREATE TABLE [dbo].[ChangeTable_Test](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Test_ChangeTable_Id] PRIMARY KEY CLUSTERED 
    (
         [Id] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[ChangeTable_Test] ENABLE CHANGE_TRACKING 
    GO    
    INSERT INTO ChangeTable_Test (Name) VALUES ('Test')
    GO 1000
    
  3. Include the actual execution plan and run the following.

    SET STATISTICS IO ON;
    SET NOCOUNT OFF;
    ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
    GO
    ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 110
    GO
    DECLARE @CurrentVersion INT;
    SELECT  @CurrentVersion=CHANGE_TRACKING_CURRENT_VERSION ()-1;
    SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
    GO
    ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 130
    GO
    DECLARE @CurrentVersion INT;
    SELECT  @CurrentVersion=CHANGE_TRACKING_CURRENT_VERSION ()-1;
    SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
    

The above demo works for me on SQL Server 2016 SP1-CU4. I uploaded two plans to Paste the Plan. The first one is under COMPAT 110 and the second is under 130.

Microsoft support acknowledged the problem but do not plan to fix it. They suggested workarounds using a USE PLAN hint or Plan Guides. But it would make a maintenance nightmare for this product.

The funny thing is that 2014 and 2017 do not pick the bad plans and they use the new Cardinality Estimator.

Do you have any suggestions beside of the ones I mentioned?

  • 1
    I'm seeing the bad plans in 2017 also. If I set compatibility back down to 110 it brings in nice seeks, but at 140 it still shows scans.Any thoughts on why that might be? – Paul Lemke Nov 3 '17 at 21:06
  • @PaulLemke Are you running a test in a developer edition 2017 in Windows? I tested it in a Linux Docker version and it always picked the right plan. – Jose Chama Nov 6 '17 at 21:50
  • Yea, dev edition on windows. I'll deploying it to our development environment this week and will get a better idea on if it was something specific to my little tests. – Paul Lemke Nov 7 '17 at 14:53
  • Yea, this is still an issue in 2017. The execution plans just won't seek against the change tracking tables. – Paul Lemke Mar 23 '18 at 18:38
  • @PaulLemke I have a case open with Microsoft and they are working on it. I have been meeting with their support once a month but still could not find exactly what in the optimizer is causing these plans. – Jose Chama Apr 5 at 19:44
4

Based on your question it sounds like you have a highly concurrent application that calls CHANGETABLE on a variety of tables in many different queries. Forcing each query plan to have a specific shape with a USE PLAN hint or a plan guide would be a Herculean effort. Changing your active database to have a compatibility level of 110 without query fixes may be a bad option for other reasons.

The suggestion in this answer could be difficult to implement but it feels like a better option than the ones listed above. The idea is to replace the call to CHANGETABLE with a stored procedure that inserts the data from CHANGETABLE into a temp table. Queries that need change data can use the temp table populated instead of CHANGETABLE directly. Your application code might look like this:

DECLARE @CurrentVersion INT = CHANGE_TRACKING_CURRENT_VERSION () - 1;

CREATE TABLE #changes (SYS_CHANGE_VERSION bigint NULL);

EXEC dbo.CHANGETABLE_API '#changes', 'TEST', 'dbo', 'ChangeTable_Test', @CurrentVersion;

-- run queries that need #changes temp table

DROP TABLE #changes;

Working around scoping issues requires a few tricks. The stored procedure has three parts:

  1. Add static columns to the temp table: SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, and SYS_CHANGE_CONTEXT
  2. Add dynamic primary key columns to the temp table. You can use sys.columns and other dmvs for this.
  3. Insert into the temp table from CHANGETABLE under the context of an empty database with 2012 compatibility level and whatever other settings you need to get good performance.

Below is a quick and dirty implementation of most of the above. It doesn't have any kind of error checking or protection against SQL injection attacks. Feel free to do whatever you want with the code:

CREATE OR ALTER PROCEDURE dbo.CHANGETABLE_API (
    @temp_table_name SYSNAME,
    @changes_database_name SYSNAME,
    @changes_schema_name SYSNAME,
    @changes_table_name SYSNAME,
    @last_sync_version BIGINT
)
AS
BEGIN
    DECLARE @sql_to_add_static_cols NVARCHAR(4000),
    @sql_to_add_table_cols NVARCHAR(4000),
    @sql_to_insert_rows NVARCHAR(4000);

    -- okay to hardcode these depending on how you call CHANGETABLE
    SET @sql_to_add_static_cols = N'ALTER TABLE '
    + QUOTENAME(@temp_table_name)
    + N' ADD '
    + N'SYS_CHANGE_CREATION_VERSION bigint NULL, '
    + N'SYS_CHANGE_OPERATION nchar(1) NULL, '
    + N'SYS_CHANGE_COLUMNS varbinary(4100) NULL, '
    + N'SYS_CHANGE_CONTEXT varbinary(128) NULL';

    EXEC (@sql_to_add_static_cols);

    -- this should be dynamic based on sys.columns and other dmvs
    SET @sql_to_add_table_cols = N'ALTER TABLE '
    + QUOTENAME(@temp_table_name)
    + N' ADD id BIGINT NOT NULL';

    EXEC (@sql_to_add_table_cols);

    -- key is to run the insert in a database with the settings that you need for a good query plan
    SET @sql_to_insert_rows = N'USE DB_2012_COMPAT; '
    + N'INSERT INTO '
    + QUOTENAME(@temp_table_name)
    + N' SELECT * FROM CHANGETABLE(CHANGES '
    + QUOTENAME(@changes_database_name)
    + N'.' + QUOTENAME(@changes_schema_name) 
    + N'.' + QUOTENAME(@changes_table_name)
    + N', ' + CAST(@last_sync_version AS NVARCHAR(30))
    + N') AS C';

    EXEC (@sql_to_insert_rows);
END;

When I call the stored procedure in a database with compatibility level 130 I get a query plan that only does seeks as desired:

good plan

In the same session if I run the following code:

DECLARE @CurrentVersion INT = CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
OPTION (RECOMPILE);

I get a scan which you wanted to avoid:

bad plan

  • I will keep my databases in 110 compatibility level. I will upgrade straight to 2017. I am worry that these bad cost calculations are affecting more than just change tracking. – Jose Chama Oct 23 '17 at 15:27
  • 1
    You may want to consider using sp_executesql to avoid potential injection attacks 😉 – Erik Darling Oct 23 '17 at 15:59
  • 1
    @sp_BlitzErik Real men use QUOTENAME() – Joe Obbish Oct 23 '17 at 16:28
  • @JoeObbish not all of your variables have that testicle. – Erik Darling Oct 23 '17 at 16:29
  • Plus testing this with our data sets, using option (recompile) gets away from the generalised statistics query, as does the exec (). Using sp_executesql without option (recompile) results in the query plan using general statistics assumptions and getting it wrong, but the EXEC option puts the value of the variable into the dynamic SQL and gets back to good performance. – Toby Jun 3 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.