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?