David's Blog

If you have read-only data, placing it in a read-only database will prevent shared-locks from being taken on that data (for all transaction isolation levels) reducing some locking overhead.

However, there are many disadvantages to placing data in a separate database, for example, you cannot enforce foreign key constraints across databases and auto create/update statistics functionality is effectively disabled in a read-only database.

A common misconception is that shared-locks are not taken on data stored on read-only filegroups. This is (unfortunately) not the case – SQL Server will take shared-locks on data backed by a read-only filegroup – you can verify this easily enough.

A few workarounds come to mind.

1. Specify the WITH (NOLOCK) hint wherever you access your read-only tables. Or alternatively, create views on your read-only tables that specify the WITH (NOLOCK) hint and only access read-only data through these views. None of the usual warnings around “read uncommitted” apply providing the underlying data is not being modified.

2. Specify table-locking only (by disabling row/page locks via the ALTER INDEX statement or sp_indexoption stored procedure) for your read-only tables/indexes. This way, you’ll eliminate much of the locking overhead associated with accessing these read-only tables.

Of the two options above, I prefer option 2; it’s likely the most maintainable approach and option 1 involves intermingling logical and physical constructs which is never a good idea.