I have researched into this and I understand the general differences between the two isolation levels. I would like to explain our scenario and need help in deciding the correct isolation level to use.
We were using the default behavior of SQL and started experiencing some locking behavior especially when the volumes of data increased and some of the queries started taking a bit of time to load. There were other factors that made the problem worse which we have addressed already.
In the meantime we enabled SNAPSHOT ISOLATION LEVEL on our database and changed ONLY the
select queries to use this ISOLATION LEVEL i.e. when a query is run we just want to get the last committed snapshot of the data. These are mainly queries that are used to display the data in grids.
I have a few questions that I would like some clarification on please:
- To achieve what I am trying to achieve is enabling isolation snapshot the best option or are there other options worth considering?
- Since I have enables isolation snapshot but I do not run this in the queries where I update / insert / delete records my assumption here is that in these queries it will use the default behavior of SQL (Read Committed). Is my understanding correct?
- I have read that snapshot isolation level utilities the
tempdbto store versions. Would this be the case even when I am running selects?
- What can we do from a performance point of view to optimize our databases that have isolation snapshot enabled?