Benefits of making a sql server database as read-only?

  1. You cannot make schema changes when the database is read only but you could put all your user tables on a new file group and mark that as read only.
  2. You can expect a modest performance benefit from absence of locking.
  3. On versions of SQL Server prior to 2012 statistics can't be auto created or updated on read only databases.
  4. Before making it read only you might as well remove all logical fragmentation and make page density as high as possible. Any non default FILL_FACTOR settings will not be of benefit in a read only environment. Additionally create/update any statistics anticipated to be of use for SELECT queries if on version < 2012.

  1. The entire database is read only so you cannot add indexes or views.
  2. No locking required so no locks are taken for any query other than a shared database lock. Obviously means that no blocking will occur either.
  3. Not as such no.
  4. None that I can suggest.

Tags:

Sql Server

Recent Posts