2

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).

Improve this question
10
  • 1
    The fairly small performance advantage of read-only databases (single-digit percentage, for certain types of transactions) is probably offset by the risk and the extra work involved in setting up such a solution. Good index design and tuning queries is going to be much better for performance, and a more stable solution.
    – Daniel Hutmacher
    Commented Mar 24, 2016 at 16:55
  • You say: I wanna set some tables readonly, to have better performance. Personally I would reconsider this statement and look at some of the research that has been done on readonly vs. readwrite, as you seem to assume this is factual. As Daniel says, you need to fully optimise your database in terms of indexes and query design. You will have a cleaner database at the end of it and performance will improve at least as much as setting up a readonly environment.
    – Molenpad
    Commented Mar 24, 2016 at 20:20
  • @Daniel I updated the question adding the reason for setting these tables as readonly. I agree on the importance of having optimized data/index and query design, but that doesn't exclude the importance of optimizing the infastructure. These tables aren't written during the day, so there's no need for query engine to set shared locks on them. And that article suggests setting them readonly instead of using read uncommited isolation level.
    – Hikari
    Commented Mar 28, 2016 at 15:05
  • @Molenpad I don't just assume it's factual, I'm basing on that article. I don't believe setting tables to readonly will be as important in performance as good data/index and query design. But, if it would be as much improve, as you say, then it's even more interesting to do.
    – Hikari
    Commented Mar 28, 2016 at 15:07
  • The point is, query engine sets shared locks as default for these tables, and that's some overhead. These locks aren't needed (while ETL isn't running), so I'd like to configure MSSQL to not use them. That article suggest as the best solution to set them readonly, but doesn't explain the best way to do it. I'm looking for a way to make it happen.
    – Hikari
    Commented Mar 28, 2016 at 15:09

1 Answer 1

Reset to default
4

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.

Improve this answer
4
  • Thanks Martin, I'm gonna read these articles. I'm still looking for the least intrusive way of setting them to readonly.
    – Hikari
    Commented Mar 28, 2016 at 15:11
  • @SQLmojoe sorry maybe I didn't make it clear. Take a look on the update. My objective in setting them readonly isn't a security measure to avoid undesired writes on these tables, it's to make query engine not set shared locks on them.
    – Hikari
    Commented Mar 28, 2016 at 15:12
  • That's a misconception and that test is misleading. The delta is almost certainly due to contention rather than RO filegroups. Also, SQL Server does still issue locks on RO filegroups (you can argue it shouldn't but for now, it does). The links below clearly show what happens. Suggest you stop linking to the other blog. Correlation != causation. insidesql.org/blogs/andreaswolter/2013/02/… and blogs.msdn.microsoft.com/sqlserverstorageengine/2007/01/08/… (Sunil is a SQL engine PM)
    – SQLmojoe
    Commented Mar 28, 2016 at 15:24
  • @hikari, I understand your goal. Taking write permissions away prevents them from placing non-compatible locks on the table. If you want, you can take read permissions also which effectively blocks them completely: no access = no locks. Putting the tables in RO filegroups still allows them to take locks. You can easily prove this yourself, just check out the sample code in the links I provided above.
    – SQLmojoe
    Commented Mar 28, 2016 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.