tips for optimizing a read-only sql database

Question

I have a mid-sized SQL Server 2008 database that has actuarial data in it. All of the use cases for it are read-only queries. Are there any special optimizations I should consider given this scenario? Or should I just stick with the normal rules for optimizing a database?

Answer 1

In database:

1. Denormalize it.
2. Use more indexes where needed.
3. Aggregate some data if you need it in your reports.

In program:

1. Use READ UNCOMMITTED isolation level.
2. Use autocommits to escape long-run transactions.

Answer 2

One strategy is to add a readonly filegroup to your DB, and put your readonly tables there. A readonly filegroup allows SQL Server to make a number of optimizations, including things like eliminating all locks.

In addition to standard DB optimization:

1. Make sure all tables and indexes have zero fragmentation
2. Consider adding indexes that you may have otherwise avoided due to excessive update costs