I’m bringing two different concepts from editorials this past couple of weeks to demonstrate a whole new topic.
Previously we talked about auditing data in your database. SQL Server, by default, has the ability for you to turn on a feature called RowVersioning. When this feature is enabled, SQL Server maintains a history table of all RowVersioned tables in your database. This allows you to maintain history of what modifications were made to a table, and when the changes were made.
We also talked about a technique to reduce contention on table locks by retrieving data from the tables using a consistent order. In our comments, meilenberger62 writes, “…Maybe that’s why I’ve seen folks turn on snapshot isolation level…”.
One way to reduce lock contention on your tables is to make sure you use the smallest lock mechanism and isolation level possible. Lock rows, when the number of rows are few. Lock Pages or a whole table. Or simply let SQL Server escalate the locks by itself when the locks become too granular.
Second, use the smallest isolation level acceptable:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SNAPSHOT
- SERIALIZABLE
Take a look at https://msdn.microsoft.com/en-us/library/ms173763.aspx for further definitions of each.
Each of these levels, with the exception of READ UNCOMMITTED, have a read lock on the data so that the data doesn’t change while the reads are executing. That means if you are reading LOTS of records, the read lock will remain until all the records have been read. In that scenario, no update, insert, delete commands may be executed that are impacted by the read lock/s.
SQL Server now has a database feature called Snapshot Isolation Level What this does is allow you to perform reads without having to lock the table. Since all changes are maintained in the row versioning, SQL can reliably read from the row version instance of your table, instead of the base table. As a result, locks are not required, and table modifications may co-exist with your read query.
While row versioning does take a reasonable amount of resources, on an active table being written and read concurrently, this option results in better comprehensive performance. Both reads and writes are a little slower. But, they are faster than using locking on the table when supporting both reads and write.
Are you using Row Versioning and Snapshot Isolation? Share your experience or insight in our comments.
Cheers,
Ben