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?
+2
A:
If it is read only, one thing that you can do is put indexes on just about anything that might help (space permitting). Normally adding an index is a trade-off between a performance hit to writes and a performance gain for reads. If you get rid of the writes it's no longer a trade-off.
When you load the database you would want to drop all/most of the indexes, perform the load, then put the indexes back on the tables.
Tom H.
2009-12-09 20:21:49
Why would you want to drop all/most of the indexes on load? You may get slightly better performance when loading the data, but you risk the possibility of not putting the indexes back. Not only that, but if you take the indexes off and load bad data, you won't see the issue until you try to put the indexes there (if you remember).Loading data is a one-shot deal, it isn't something that happens all the time. If it takes a little longer to insert, it ain't a big deal. Having meaningful data, priceless.
2009-12-09 20:45:17
Wlater, taking the indexes off and recreating them is a common way to do the loads, it can tremendously speed things up. You don't have to remember to put them back on, it is part of the SSIS package that does the load. I think you are thinking of dropping the pks and FKs which you would not want to do usually.
HLGEM
2009-12-09 21:33:48
As HLGEM said, I wasn't saying that you should remove PKs/FKs (although in some instances even this may be preferred). The difference when loading a lot of data where there may be many indexes involved can be much much more than, "a little longer".
Tom H.
2009-12-09 21:42:53
dropping non-unique indexes is fine. Any (non-redundant) unique indexes or other constraints should remain.
Cade Roux
2009-12-10 01:14:06
Whether they should or shouldn't remain is a judgment call. If I have an accurate data source and dropping FKs will improve the import performance dramatically then I'll weigh that advantage against the risk of having to do a restore because adding them back fails due to bad data. Importing bad data into your database doesn't mean that you have to keep it there. Obviously any ETL process should include checks at the end with a rollback plan in case there is a problem.
Tom H.
2009-12-10 15:16:41
+1
A:
I'm not sure what you consider "normal rules", but here's some suggestions.
If you're 100% certain it's read-only, you can set the transaction isolation level to READ_UNCOMMITTED. This is the fastest possible read setting, but it will lead to phantom reads and dirty reads if you are writing to the tables.
If you have Views, use Indexed Views (create clustered indexes for them). Since they will never have to be updated, the performance penalty is negated.
womp
2009-12-09 20:23:35
+2
A:
In database:
- Denormalize it.
- Use more indexes where needed.
- Aggregate some data if you need it in your reports.
In program:
- Use READ UNCOMMITTED isolation level.
- Use autocommits to escape long-run transactions.
alygin
2009-12-09 20:36:45
+1
A:
- Denormalize the data.
- Apply the appropriate indexes.
- Precalculate aggregations.
- Implement the database atop a striped disk.
- I've never seen this done but if you could somehow load the entire thing into memory (RAM disk???) that would be super fast, right?
Mark Canlas
2009-12-09 20:39:57
+1
A:
For a read-only table, consider altering the indexes to use a fill factor of 100%.
This will increase the amount of data on each data page. More data per page, fewer pages to read, less I/O, thus better performance.
I like this option because it improves performance without code changes or table changes.
Darryl Peterson
2009-12-09 21:28:45
+1
A:
For performance tuning there are several things you can do. Denormailzation works. Proper clustered indexes dependent on how the data will be queried. I don't recommend using a nolock hint. I'd use snapshot isolation level.
It's also important on how your database is laid out on the disks. For read only performance, I'd recommend Raid 10, with separate mdf's and ldf's to isolated spindles. Normally, for a production database it would be Raid 5 for data and Raid 1 for logs. Make sure you have a tempdb file for each cpu, used for sorting, a good starting size is 5gb data and 1 gb log for each cpu. Also make sure you run your queries or procs through showplan to help optimize them as well as possible. Ensure that parallelism is on in the server settings.
Also if you have the time and space for optimal performance, I'd map out exactly where the data lives on the disks, creating file groups and putting them on completely separate volumes that are isolated disks in each volume.
DanceswithLightning
2009-12-09 21:37:00
+2
A:
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:
- Make sure all tables and indexes have zero fragmentation
- Consider adding indexes that you may have otherwise avoided due to excessive update costs
RickNZ
2009-12-10 03:06:21