Toad World® Forums

How can I get SQL Optimizer to use snapshot isolation


#1

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?


#2

Hi Mark,

I am sorry to tell you that there is currently no way to do so in the product.

I initially thought that it could be possible to have a logon trigger to identify your machine and then set the transaction level accordingly. However, it doesn’t work as the SET option will be reverted after the trigger. For now, I couldn’t think of any workaround to support this.

I think what you are trying to do is a valid request to add this functionality to the product. I have added this into our backlog to consider in the next release. It could be a specific setting for the Test Run or a Login Script to run after connecting. We will give it more thought when implementing it.

Thanks,

Alex


#3

Alex,

If that could get into Toad 6.7 in January, that would be a big help to us.

Thanks,

Mark