Because of dramatic differences between my test and production environments (amount of RAM and number of cores), I want to optimize a SELECT statement by connecting to my production server. But to avoid causing blocking, I want SQL Optimizer to do all its work in snapshot isolation. I have the database set to allow it, but that is not the default.
SQL Optimizer won’t let me precede the SELECT statement with “SET TRANSACTION ISOLATION LEVEL SNAPSHOT;”. How can I configure SQL Optimizer to use snapshot isolation when executing my very long running SELECT the many dozens of times that it will need to test the optimization permutations?