Change Tracking Bad Plans in SQL Server 2016
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:


Add static columns to the temp table: SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, and SYS_CHANGE_CONTEXT
Add dynamic primary key columns to the temp table. You can use sys.columns and other dmvs for this.
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:



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: