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:
- Create a database and setup change tracking.
-
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
-
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?
Best Answer
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 aUSE 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 fromCHANGETABLE
into a temp table. Queries that need change data can use the temp table populated instead ofCHANGETABLE
directly. Your application code might look like this:Working around scoping issues requires a few tricks. The stored procedure has three parts:
SYS_CHANGE_CREATION_VERSION
,SYS_CHANGE_OPERATION
,SYS_CHANGE_COLUMNS
, andSYS_CHANGE_CONTEXT
sys.columns
and other dmvs for this.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:
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:
I get a scan which you wanted to avoid: