Changing Isolation Level after retiring database from active/production use

Question

We have a product that uses SQL Server (currently 2019). We're moving to a different product, but will keep the database from the old product around for several years for reporting purposes.

Part of this project involves reviewing account permissions for who still needs access and revoking write permissions for most accounts that remain, since historical reporting data should not change. That is, we'll have very confidence data can no longer change at all in this DB.

As another part of this process, I am also considering changing the default isolation level for this database from the default READ COMMITTED to instead be READ UNCOMITTED. Since the data should no longer change, things like dirty reads, etc are no longer a concern, and in theory we can (at least marginally) improve performance by going to the less restrictive level.

The question is whether this is a good idea, and what drawbacks to this plan I might have missed.

Answer 1

I am also considering changing the default isolation level for this database from the default READ COMMITTED to instead be READ UNCOMITTED. Since the data should no longer change, things like dirty reads, etc are no longer a concern, and in theory we can (at least marginally) improve performance by going to the less restrictive level.

To me this sounds like using a hammer to nail in a screw. Since there will be no blocking, since reads don't block other reads, I don't believe this would be necessary to do.

Alternatively, I think you'd be better off explicitly setting the database to READ_ONLY which will prevent shared locks from being taken out against the data. It also has the bonus of ensuring no one accidentally changes the data, should you inadvertently miss revoking someone's write permissions.

This alternative option is briefly mentioned by Paul White at the end of The Read Uncommitted Isolation Level:

For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).

Answer 2

First off, you can't set a default isolation level. It needs to be set on every connection.

But it makes no sense to do so anyway. READ UNCOMMITTED is a ridiculous isolation level to use, unless you are in a debugging environment.

If you are that concerned about the performance of a few locks when you have no contention (unclear why), then you have two main options:

• Either set the whole database to READ_ONLY, in which case no locks are taken.
• Or set all indexes in the database to have ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF. This will force all locks to be at the table level, which is obviously much faster, at the expense of blocking the whole table if it's an exclusive lock.