As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance.
These tables are only written by an ETL that runs daily, then they are only read. Therefore, I'll move these tables' indexes to a separate filegroup and keep this filegroup as readonly. When my ETL runs, I must set the filegroup to read-write, run the ETL, then set it back to readonly.
The issue is that, for doing so, I must run
ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, and for this operation to run there can't be any other transaction running on the whole DB.
ALTER DATABASE MyDB SET SINGLE_USER isn't an adequate solution, because I also can't let my ETL kick/rollback other jobs. It must wait them to finish, keep them blocked while it runs, then let them finish transparently. Also, with that operation any transaction could grab a lock on the database while ETL is running, and block my ETL.
I really don't know the best solution for this, I'm open to any suggestion. My current idea is to request an exclusive lock on the DB, so that I can run
ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY without breaking any other transaction. In the worst case scenario, I'd keep a loop asking for the lock until I find the DB free and grab it.
I don't need the lock/single_user during the whole ETL. Only for setting the filegroup to readonly and read-write.
UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:
For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).