October 17, 2016 at 8:52 am
Could you give me a good example when to use snapshot isolation?
When I wanted to mitigate read-write workload issues then RCSI seemed enough
When I attempted to use snapshot isolation level in update-update scenario it was less efficient than Serializable because of many update conflicts
October 17, 2016 at 11:12 pm
When the same data is modified by many data writers, SQL Server might need to maintain multiple versions of the old data and hence proper envisioning and planning needs to be done for the tempdb database size and storage before utilizing this feature. When you use snapshot isolation levels, any update will be marked with a timestamp and will create a version with old committed data in version store and a pointer is stored with the changed/new data.
Benefit of using snapshot isolation, data readers can get consistent data without being blocked by data writers running at the same time as the versions get stored in version store in the tempdb database before data is changed, whereas the cost of using snapshot isolation is more overheard on SQL Server in creating and maintaining versions and the increased size of tempdb for storage for version data and the increased size of each row for pointers. The overhead even becomes more when you use SI or transaction level snapshot, in which case versions are maintained till the end of the transaction as opposed to completion of the statement, as in case of RCSI.
October 19, 2016 at 7:09 am
October 19, 2016 at 7:25 am
Example: The users who are viewing the data do not need to know that the data they are looking at might be changing or where small changes in data do not affect the result. If I need to see how many items were sold in my large company on a dashboard throughout the day, I know that data is CONSTANTLY changing, but I want a quick snapshot of how many... I don't need 100% accurate data as of this second. So, optimistic may be a good case for a dashboard that is constantly updated.
Example of when not to use it: Concert/event tickets. Imagine your favorite band has tickets that go on sale at 10am. There are a limited amount of tickets available and they are all assigned seats. If you use optimistic when booking these seats, you may present seats to users that are in the process of getting booked. You should only show them "committed" transactions here...
Maybe not the best examples, but its what I can come up with at 8am 🙂
Jared
CE - Microsoft
October 20, 2016 at 4:59 am
suvorav (10/17/2016)
Could you give me a good example when to use snapshot isolation?When I wanted to mitigate read-write workload issues then RCSI seemed enough
When I attempted to use snapshot isolation level in update-update scenario it was less efficient than Serializable because of many update conflicts
It's important to understand the difference between RCSI and SI. This article should help
https://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx[/b]
But in essence, RCSI provides consistency at the statement level.
SI provides consistency at the transaction level (which may have more than one statement ).
Read through the article and come back if you need more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply