read only databases and index fragmentation in SQL Server

Question

I have a number of read only databases alter database [ReferenceData] set READ_ONLY that is used by our application and was wondering what type of things I can tweak for maximum performance?

So far, I came up with the following goals:

• Force padding in indexes to 100% fill factor
• index fragementation be less than 10%
• If space isn't a problem look at DMVs and add as many INCLUDED columns as recommended

Do these make sense? Are there more things I should be tweaking for max efficiencies?

I'm running SQL Server 2008 on Windows 2008

Answer 1

This might sound kind of obvious but you don't outright say you've done it in your question so I'll mention it:

1. Are you sure all the proper indexes are in place? You might run profiler/DTA against the DB to see if there are any areas that could be potentially improved by creating new indexes.
2. Have you performance tuned the queries that use the read only DB? All the indexes in the world don't matter if you have poorly written queries.

Answer 2

ALTER DATABASE MyDatabase
SET READ_ONLY

will set a database into a non-modifiable state. Presumably there are some optimizations there, but I'm not sure.

You can also set the default transaction level to be READ UNCOMMITTED for maximum read performance for each session. Presumably if you set your database to read-only mode, this is already happening though.

EDIT: Ran across this article about what you should to to prep a database for being read-only.