I've got a table with the following DDL:
CREATE TABLE dbo.SubscriberProduct (SubscriberId INT NOT NULL , ProductId INT NOT NULL , PRIMARY KEY (SubscriberId, ProductId)); GO
I've then enabled change tracking on it and initially populated it with roughly 15m rows, later on it's going to be just incremental changes.
However I'm facing an issue that Change Tracking queries are terribly slow and
CHANGETABLE function seems to be the bottleneck. That's the query I'm running locally. It took 40 seconds to return 1000 rows, which is far too long than my expectations.
SELECT TOP (1000) CT.SYS_CHANGE_VERSION , CT.SYS_CHANGE_CREATION_VERSION , CT.SYS_CHANGE_OPERATION , CT.SYS_CHANGE_COLUMNS , CT.SYS_CHANGE_CONTEXT FROM CHANGETABLE(CHANGES dbo.SubscriberProduct, 0) AS CT;
That's the execution plan I've captured: https://www.brentozar.com/pastetheplan/?id=HyIEYPK8z
Normally I'd know where to start, but not this time as I've got no clue how to tune these kind of queries, so I'm asking to help.