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).
Best Answer
Setting the filegroup to read only doesn't actually improve performance. It might appear that way if there are users/jobs that update the data while most users are just reading data (blocking) but that's a misinterpretation of your observation.
A common misconception is that it saves on locking however that would require the database to be read only not just the specific file group (Source1, Source2).
From your description, it looks like nobody else apart from the ETL job should have write access to the table. That means, all users are read only by definition. So what makes you think further making the table read only will help performance? Have you observed other users writing to the database hence causing blocking? If so why not just secure the table and allow only the account used for ETL to write data while all other accounts can only read? Much easier to do, far less intrusive plus it enforces your rule of only ETL can write, all others can only read.