Setting isolation level at database level

Question

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?

Answer 1

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.

Answer 2

There is a difference between Transaction Isolation Levels and (Database) Snapshot Isolation in SQL Server. (Microsoft Docs)

And there is a big difference between reading dirty data and no data at all (as already pointed out by David Browne in his answer).

You might have to redesign your whole DW solution to ensure you can load/unload during a specific time frame and still maintain a nearly seamless availability of your Reporting Services. As your load increase (users & data) so will the need for a new solution.

You might have to adapt your Business Requirements to the capabilities of your environment. If your environment doesn't meet your (Business) Requirements then you might have to invest in a better environment:

• Availability Groups
  • Sync
  • Async
  • Read-Only

You might even have to rethink your ETL strategy. Why not do a ELT instead?

• Building an Effective Data Warehouse Architecture - PASS (Pass.org)

I would have a talk with your business to determine their requirements and then possibly with a consultant you trust, to allow you to implement a viable solution.

In the end it all depends on your business requirements and the available resources.

"You can't build a Sojus launchpad out of matchsticks and expect it to reach its orbit."
                                                                                                                                - unknown