TABLOCK hint for SELECT statements when blocking writers is not an issue

Question

Since SELECT statements take a shared lock in contrast X and U it seems to me that using WITH(TABLOCK) in datawarehouse style queries used for reporting is the correct thing to do in order to avoid lock management and lock escalation.

I am talking about cases where data is not live production data and concurrency is not a problem. E.g. I don't care about blocking writers by taking a shared table lock. Of course I do not want to block readers, but S locks does not block other readers.

So I guess I can formulate my question as: In a data warehouse environment should all queries not be hinted with WITH (TABLOCK)? And theoretically this should improve performance. If not, why?

Thanks

Answer 1

If you're concerned about taking locks, consider using read committed snapshot isolation or snapshot isolation instead. These use row-versioning instead of shared locks when reading. If there are few changes to the database, there is little reason to prefer your solution.

Ultimately, if the database is completely unchanging, it could be set to read-only. In that case, SQL Server will not take any locks at all, regardless of requested isolation level. There are other things to consider, like creating statistics, before setting a database to read-only. See the documentation for more details.

Adding table-lock hints to every query is technically plausible, but I doubt it is the optimal solution in many cases. You'd have to make a lot of changes if you ever wanted to change your arrangements.

Documentation: Transaction Locking and Row Versioning Guide

Answer 2

If your sole purpose is to minimize resource usage, then yes - resource usage will be less by acquiring table locks upfront.

One option could be to configure select indexes whether you allow row and page locks in them. Disallow both, and you get table locks upfront. But that will of course be for all users through that index - perhaps not what you want.

Now, much do you gain? You tell me. What you are asking for it how much 5000 locks costs. (Lock escalation might happen at some other number, 5000 is not cast in stone, but it is a pretty frequent number and can serve as example.) Run a trace and capture the cost where you in one case get 1 lock and the other get 5000 locks. Keep the table as small as possible, else the difference will probably get lost in the noise.

You might argue that escalation might fail and it will keep acquiring row locks (so we should use a much higher number than 5000). But in that case you have a conflict and if you don't allow smaller granularity locks then outcome will be a blocking situation, which is far worse than the few CPU cycles and memory usage that 5000 locks cost you.

Answer 3

I think query hints are worth testing in different situations, but are rarely a guarantee to always be the right choice for a given situation (otherwise Microsoft would hopefully make that the default behavior of such situation).

If you believe you may see improvement from using the TABLOCK hint on some of your data warehouse queries, try it on a few, measure the results, and compare them to your queries without it. You may surprisingly find almost no difference, and in which case I'd personally defer to the default behavior of not using it then.

To answer your questions, yes if you know a query is always going to escalate to a table lock, then it's probably better to use the TABLOCK hint upfront to avoid the escalation, and at least minimally improve performance. But not all data warehouse queries necessarily result in escalation to a table lock, so you could actually hurt performance by prematurely using the TABLOCK hint in such a case.

Like with most things database performance related, the best way to find out for your specific use cases is to test.