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:
Best Answer
A General Distribution Release (GDR) is simply a kind of hotfix, or patch that contains specific fixes, and is intended for installation on all SQL instances. This is in contrast to a Quick Fix Engineering (QFE) hotfix, which is intended to be installed only if you experience a particular issue.
After a hotfix, it will also be included in the next Cumulative Update (CU) or Service Pack (SP). But you can install the hotfix without taking all the changes included in the next CU/SP.
Going forward GDR hotfixes are only going to contain security fixes.
For SQL Server 2017+ a GDR is part of the Modern Servicing Model (MSM), which is replacing the older Incremental Servicing Model (ISM), which will continue to apply to SQL Server 2016 and before. It's explained here.