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.