Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
More Useful Qualities
In general, Snapshot Isolation is a nice feature if there were no costs involved in implementing it. The feature guarantees that readers do not block writers, and writers do not block readers. Who wouldn't want such a system? Unless two sessions want to update the same piece of data, there is no contention ever! It would even protect the database against a badly written application that keeps transactions open for a long time.
In Oracle, this feature (called UNDO) is enabled by default. In Oracle 9i and 10g, the UNDO data is used for some very nice side effects, such as looking at previous changes to a row at a later time. Now that SQL Server 2005 implements this feature, it joins Oracle—leaving behind other leading relational database servers (that is, DB2, Sybase, and Informix).
Oracle implements Row Versioning (again, called UNDO) as a standard feature, but you have to explicitly enable it in SQL Server 2005. I have seen articles mentioning the option to enable/disable Snapshot Isolation as a feature, wherein you enable it only on certain databases because there is an overhead in doing so. I have not heard of any concrete percentage by which this feature would adversely affect your database. But if your database has a 5–10 percent update and remaining read activity, you should not have any problem. Run some tests on your database with and without Snapshot Isolation turned on to see how much impact it has. Your database is your baby; make sure that it does not cry at night!
The benefits of Snapshot Isolation come at a price. The overhead involved in enabling Snapshot Isolation feature includes the following:
More space for tempdb (you can test it by updating a very large table and see how tempdb grows)
More processing while updating (work is involved in making copies of older versions in tempdb)
More maintenance (the server has to keep track of data copies in tempdb and delete it when it is no longer needed)
More processing while reading (the read process has to look for copies of data in tempdb)
Where can you afford to turn it on? This is something you have to decide on your own, on a case-by-case basis, after performance testing. However, the general rule of thumb is to use snapshotting in these types of applications:
Applications with fewer updates and lot of reads
Applications that need to collect frequent statistics on recent data
Applications with hotspots—where updates and reads are concentrated in a few database pages
A note of caution, however. If you have an application that relies on being blocked when data is being updated, you should be careful. The application will no longer wait for the data to be committed; it will read the previously committed view of the data. But again, if the application really does need such behavior, you should re-examine it and use serializable or repeatable read transactions if necessary.
Also, Snapshot Isolation does not prevent two or more writers who are trying to update the same piece of data from blocking. It is only the readers who benefit from this feature. Multiple writers still have to wait until the writer(s) higher up in the queue finish their task. This is different from a front end-based "optimistic locking;" for example, using the ADO "batch optimistic" locking or an ADO.NET dataset and data adapter-based update.