In most environments new data is added to the data warehouse daily, weekly or even monthly.  Even further, these changes typically occur during the ETL process.  This means that most of the activity is read only.  As a result, you may be able to set your warehouse database to READ_ONLY.  When you set your database to read only SQL Server does not bother to acquire any locks, meaning that your SELECT statements should never be in conflict with any other activity.  Therefore, you should get better performance because your queries will produce less overhead.

You can set your database to READ_WRITE just before starting your ETL process. Once the process is complete you can set your database back to READ_ONLY.  These steps could be automated as part of your ETL process using a couple of T-SQL statements.  The process would be similar to the following:

ALTER DATABASE <Database Name> SET READ_WRITE;

ETL Process...

ALTER DATABASE <Database Name> SET READ_ONLY; 

As always, if you have any questions concerning this please email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, founder SQLLunch.com and TSQLScripts.com

Visit www.BIDN.com, Bringing Business Intelligence to your company