Timeline for Hold exclusive lock on a whole DB
Current License: CC BY-SA 3.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Apr 7, 2016 at 12:56 | comment | added | Hikari | @SQLmojoe what' the difference of setting a filegroup and a database as readonly? | |
| Apr 7, 2016 at 12:55 | comment | added | Hikari | @Moenpad, I'm not saying it's a fact, I'm using it as a reference, it says to not use NOLOCK, to set the whole transaction as READ UNCOMMITED or to set the table RO. Its author may be wrong, then just point that and reference another article teaching an even better solution. | |
| Mar 28, 2016 at 16:03 | comment | added | SQLmojoe | Actually, the article is correct. It can make a positive difference to performance if the DATBASE is set to read_only. That's a world of difference from a table being placed in a RO filegroup. | |
| Mar 28, 2016 at 15:50 | comment | added | Molenpad | @Hikari sorry but an article which alludes to the change as a possible performance improvement does not constitute a fact, especially since you don't seem to have yielded any benefit from it, hence the reason you're asking on here. | |
| Mar 28, 2016 at 15:49 | comment | added | SQLmojoe | Guys, SQL Server will still take locks even if you put the table in a RO filegroup. Don't have to take my word for it, test it yourself. Won't take 10 minutes. | |
| Mar 28, 2016 at 15:09 | comment | added | Hikari | 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. | |
| Mar 28, 2016 at 15:07 | comment | added | Hikari | @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. | |
| Mar 28, 2016 at 15:05 | comment | added | Hikari | @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. | |
| Mar 28, 2016 at 12:48 | history | edited | Hikari | CC BY-SA 3.0 |
added 618 characters in body
|
| Mar 24, 2016 at 22:25 | answer | added | SQLmojoe | timeline score: 4 | |
| Mar 24, 2016 at 20:20 | comment | added | Molenpad | 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. | |
| Mar 24, 2016 at 16:55 | comment | added | Daniel Hutmacher | 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. | |
| Mar 24, 2016 at 16:41 | history | asked | Hikari | CC BY-SA 3.0 |