RCSI isolation mode on SQL Server DB to improve performance

Robert Anscomb
Robert Anscomb
Name Dropper First Anniversary Facilitator 1
in Developer Discussions


Our DBA's have asked us if we can enable RCSI isolation mode on the BlackBaud DB as we are getting performance issues it appears that they are holding transactions open longer than necessary, causing deadlocks.
I asked support but they cannot provide any support or advice so asking the wider community if anybody has enabled RCSI isolation mode and did it help with performance.
Thank You

Comments

  • Mitchell Gibbs
    Mitchell Gibbs
    Fifth Anniversary Kudos 1 Name Dropper Participant

    @Robert Anscomb We tested RCSI far more than it really needed before implementing it. For context, we're running 14 instances of CRM ranging from less than a hundred GB to multiple terabytes. It has been very helpful in eliminating deadlocks and has improved performance for some operations, slightly. It chews up a little extra disk space and CPU, but that's been insubstantial. It's also allowed us to stamp out WITH (NOLOCK) hints in our reports and code as well, which is always a good thing.

  • Robert Anscomb
    Robert Anscomb
    Name Dropper First Anniversary Facilitator 1

    @Mitchell Gibbs Thank you for your response that has been very helpful.

Categories