2

Several days ago, I enabled change tracking on a db and some of its tables. A few days ago, I also SET ALLOW_SNAPSHOT_ISOLATION ON.

(Probably important note: I never SET READ_COMMITTED_SNAPSHOT ON, and the only procedures/queries that actually used transaction isolation where a few manual runs by myself, which I saw them run to completion within a logical time frame).

A few days after that, we got reports of decreased performance. I am aware that both of these things impact performance in a minor(?) way, but i am not sure how to measure this yet. So, for the sake of being on the safe side, I SET ALLOW_SNAPSHOT_ISOLATION OFF, and also disabled change tracking on every table first and on the whole db in the end.

The performance problems remain. So my questions are:

  • Can change tracking and/or ALLOW_SNAPSHOT_ISOLATION cause performance drop in a server/database even AFTER they are disabled? If so, how, and how can I check and fix the problem?

I run a few queries from this site to check the state of tempdb but did not find any interestingly large table.

Improve this question
4
  • When you say "performance problem", you'll need to provide more information. What isn't performing well? Do you have any stats collected to prove the slowdown?
    – George.Palacios
    Commented Oct 31, 2019 at 8:47
  • It is highly unfortunate that I really don't have more info. Instictively, I highly doubt that MS would allow a performance drop caused by a disabled option, so I bet this "performance drop" (same queries take longer times) is caused by something else 99%. I could pose specific questions if you recommend some; although the nature of the question remains intact. If the answer is a simple "no", then I'm fine.
    – George Menoutis
    Commented Oct 31, 2019 at 8:50
  • Do you have Query Store enabled? learn.microsoft.com/en-us/sql/relational-databases/performance/…
    – David Browne - Microsoft
    Commented Oct 31, 2019 at 14:46
  • when you see perf issues - what are the waits associated ?
    – Kin Shah
    Commented Oct 31, 2019 at 17:42

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.