I have a SQL Server 2019 database running in compatibility mode 100. We have Read Committed Snapshot Isolation turned on, which I’ve verified as being on. I have a particular SELECT query that is not running under RCSI and am at a loss as to why. I’ve checked individual transaction setting and we dont really change from the default (RCSI). I know NOLOCK can throw a query out of RCSI as well and we have none in our codebase. What else can I look for that would be causing this?
The tables this query is using pretty central to our processing, and the lock waits are all IU locks.
when testing the query, it runs in milliseconds, something is definitely keeping it from starting.