Is it possible to disable all locks for a specific table in any version of SQL Server?

I have a handful number of tables that are populated with data at deployment time. For example, one of the smaller tables, [items].[Colour], holds 'Blue', 'Green', 'Red', 'White'. After deployment, the application(s) only ever select from these tables (never insert, update, or delete). I got to thinking, if these tables are only ever read from, then what is the point of having locks? I assume it takes some amount of time to get a lock. Even if that amount of time turns out to be negligible, part of me won't be satisfied until I test the difference myself.

As I searched for the answer on my own, I discovered that I can mark an entire database as read-only, and that there may be some benefits. I want those benefits, but on a table-by-table basis. I also read about disabling lock-escalation, but if I understood the documentation correctly, that would only lead to more (albeit finer-grained) locks, which is the opposite what I want to achieve.

I know that I can update every single query that uses the table to have a NOLOCK hint, but that introduces a secondary problem. In the future when I teach my application how to learn new Colours at runtime, do I want to go update every query that uses the table to remove the NOLOCK hint? That sounds like a hassle.

It seems the question is receiving a few downvotes, and without any explanation provided, I'm going to assume that it's because this seems like what I'm asking for is premature optmization. Premature optimization is, as pointed out in a now deleted comment, the root of all evil. I know that, and I'm not prematurely optimizing. Here's why.

I'm allowed to poll this API once per second. It's not stated on the linked page, but I saw the rule somewhere (trust me). Anyway, it takes my application anywhere from 100ms to 800ms to make the HTTP request, download the gzip'd text file, and unzip it. Most of this time is spent waiting on the API for a response. That leaves me with hardly any time at all for processing all of the data into nice, neat, normalized database tables. If I'm to be competitive, I have to process all of the data in 200ms or less. I'm so close to achieving my goal that I'm cutting corners by discarding data I might not need, sending all of the data to the database at once in one big ugly bundle, etc. So yes, I am optimizing, but not prematurely (in my opinion).

I also recognize that what I'm asking for may have no performance benefits. In fact, the one solution that has been provided so so far didn't seem to affect performance at all. I couldn't have known that before I asked the question though, and now any future visitors who are wondering if this will help them can see the test I performed and know that it isn't worth it. Isn't that useful?

If this doesn't address whatever the downvoters' concerns are, please consider leaving a comment with what you think I could improve about the question.

  • If you only read from those tables, then you should have any locks anyway. – a_horse_with_no_name Dec 3 '17 at 11:10
  • @a_horse_with_no_name No exclusive locks, yes. But reading from a table takes shared locks. You can read more about that in this article on TechNet (in the section "Escalating Mixed Locks Types"). – Rainbolt Dec 4 '17 at 1:12
  • 2
    So what's the problem with the shared lock? As the data is read-only it won't block anything, right? (I hardly ever use SQL Server, so maybe I am missing something) – a_horse_with_no_name Dec 4 '17 at 6:49
  • 2
    I assume the Rainbolt is trying to avoid some cost of managing the locks, which will be non-zero. Strikes me as an "optimisation too far" for the most part but could perhaps make a measurable difference in some contexts. Though I think my cross-database solution might add enough overhead to undo anything it gains in that respect. – David Spillett Dec 4 '17 at 8:32

You can set individual filegroups to be read-only, but unless recent SQL server versions have changed masters this will not confer the reduced locking benefit found when setting a whole database to be read-only.

You could try keeping the read-only configuration in a separate database and use cross-database queries to access it. Use synonyms so that you can refer to these objects as if they were still local tables. Off the top of my head, I can't say if this would behave as desired (reduced locking) or if the query planner/runner would end up treating the arrangement the same as if it just had a read-only filegroup but it might be worth testing as an option. If it does work then to migrate object back to the normal database simply copy the data over to a new table and update the synonym, or do the synonym completely and give the table that name directly (I suggest the latter if this is the only instance of that database, the former if you have many that may be in different versions at a given time, such as if your product is sold to clients with their own on-premises instances).

NOTE: (as pointed out by Rainbolt in the comments below) using a cross-database access like this will stop you being able to define foreign key constraints, so even if this did confer a performance benefit from locking differences it still wouldn't be recommended. So take the above answer as "technically you could" rather than good advice! You could implement the constraint one way with a check constraint or trigger everywhere you would normally have the FK constraint, but that is extra work to maintain/debug and enforcing the other way (not letting a row be deleted from the referred object if it is referenced by rows elsewhere) is not possible.

  • I tested this. In one database, I created two "parent" tables with 10,000 records each. In another database, I created a "child" table with 100,000,000 records each with keys to the parent tables (unenforced, because you can't have foreign keys across databases apparently). I joined them all together in a query, and recorded how long it took for the query to run. Then I set the parent database to READ_ONLY and repeated the test. The results were the same. Interestingly, when I set both databases to READ_ONLY, the results were consistently 15% worse. (cont.) – Rainbolt Dec 4 '17 at 2:25
  • 2
    Here's a link to the script I was testing with: anotepad.com/notes/dc67pk. Just comment out the "read write" or "read only" bit depending on which one you want to test. Anyway, my conclusion is that this is a way to do what I was asking for (so I should accept the answer) but it doesn't improve performance whatsoever, and you lose referential integrity in the process, so it's a bad idea. – Rainbolt Dec 4 '17 at 2:25
  • @Rainbolt - +1 for RI concerns, I definitely went for answering the letter of the question rather than thinking of consequences. You could manage this yourself with triggers, but that would get very messy. I'll add a note about that so any passing beginners don't assume the answer is good advice rather than "technically correct, but...". – David Spillett Dec 5 '17 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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