Setting isolation level at database level We have a data warehouse that's being dropped and reproduced every night. We have lots of SSRS reports that use the warehouse. Sometimes an SSRS report goes wrong and will never execute, thus preventing our warehouse from being filled up.

I can easily go with dirty reads for that one (data isn't changing anyway) so I figured out we can just go to READ UNCOMMITTED transaction level. And now I've encountered a problem I simply cannot believe:

Is it really not possible to change a database's default isolation level? What would be the reason for that?

It just doesn't make sense. I want my default isolation level to be READ UNCOMMITTED. I really don't want to go to each SSRS report and set it's transaction isolation level separately. We have lots and lots of SSRS reports. 

I could enable RCSI but it will only unnecessarily slow the database down. Handling the reports in such a way is pretty much impossible. We have lots of SSRS reports that are written very poorly (in the graphical designer...) and we don't have time to fix them all.

I'm going through the process when in abundance of free time but that doesn't happen often. Having the warehouse not fill up is a very serious problem to us. For now all our problems could be fixed by changing the default isolation level.


The data warehouse is being filled up at night when nobody is using it
The whole day no data gets into the DW and we work with offline data up to -1 day.


So in our model there's really no concurrent writing and reading unless a query gets stuck and somebody forgets about it and leaves it trying to run.

Any solutions?
There are better ways to handle this than dirty reads.  READ UNCOMMITTED queries will still block DDL, like TRUNCATE TABLE, DROP TABLE and ALTER TABLE … SWITCH, so it might not even help.

First, of course, is RCSI which is the basic correct solution for concurrently loading and reading from a database (even a Data Warehouse).  It does not unnecessarily slow down your loads.  It does what is necessary to enable concurrent readers and writers.  And RCSI only creates row versions for UPDATE and DELETE.  INSERT does not generate row versions unless the table has a trigger.

Second, if you want to reload the data warehouse while reports are running READ UNCOMMITTED, they will get wrong results.  So you might as well just KILL the connected sessions, or have your ETL job alter database current set single_user with rollback immediate.

Third, you can use a Database Snapshot to keep the DW available for reads while you are performing ETL.  You can additionally use the snapshot to roll back (restore) a failed ETL process even if it didn't run in a single transaction.