Auto-create/auto-update statistics and read-only databases

This post is a short follow on to my last post about read-only data and locking in SQL Server. To prevent the lock-manager from taking out shared-locks on infrequently updated databases (particularly databases updated via some kind of batch process) such databases are often set in a read-only state immediately following update.

Be aware, that if you set a database read-only, one consequence is that the auto-create and auto-update statistics functionality becomes disabled (for obvious reasons). At the very least, you may want to invoke sp_updatestats prior to setting the DB read-only. Ideally, ensure you have all the required statistics / indexes predefined so that the optimizer is unlikely to require additional column statistics. Of course, you may generously index your read-only DB, since the usual warnings about over-indexing do not apply for read-only data…

Comments are closed.