1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.