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.

  • I'll do it tomorrow first thing in the morning. Thanks for suggesting! – Evaldas Buinauskas Feb 7 '18 at 16:07
  • Does the plan change under COMPAT 110? Possibly related: dba.stackexchange.com/questions/188967/… – Joe Obbish Feb 8 '18 at 1:10
  • @sp_BlitzErik I've added actual execution plan. – Evaldas Buinauskas Feb 8 '18 at 6:20
  • @JoeObbish Changing compatibility level to 110 changes to seek, doesn't seem to be much faster, but plan actually seeks.\ – Evaldas Buinauskas Feb 8 '18 at 6:29
  • @JoeObbish I'm having exactly same problem in azure (so no COMPAT). Any idea how to tackle this? – Botis Aug 20 '19 at 14:06

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.