NOLOCK optimizer hint on iterator

An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.

Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?

Dave

Dave,

I am a big fan of using NOLOCK appropriately. Generally, the NOLOCK reads dirty, ignoring locks like an UPDATE lock and would not wait for the lock to release before reading the data (helps against blocking).

However, if the table is static and there are no modification locks on the table, NOLOCK will not likely be of much help to you on this table. It is possible (MS please verify) that the NOLOCK would also skip reading the lock table so that could be something that would help if your SELECT (NOLOCK) is in a large repeating loop or process.

|||

>>His comment was that this optimizer hint was not efficient. <<

Not efficient? While I am not 100% sure that you would get noticable performance improvements by using NOLOCK except in very large query situations, it will save time by not checking or leaving locks. You won't save any contention per ce because you should never leave any exclusive locks that cause contention.

So I don't see that it will ever hurt anything to do this to a read-only table, but it could help, if just a tiny amount.

|||

>> His comment was that this optimizer hint was not efficient.

Did he say how it's not efficient? That statement doesn't make much sense. Your reply should have been: "You keep using that word. I do not think it means what you think it means."

But then, I always welcome an opportunity to quote Inigo Montoya!!!

|||

The reasoning had something to do with "excessive reading of the transaction logs." I've used the NOLOCK hint heavily since the 90's and have never noticed this supposed problem. Moreover, the hint has often been the solution to contention between reports and transaction processes. Back in the 90s I heard a similar objection from a collegue but discounted it. Since This was something I had "heard" before I felt it was better to re-verify.

Dave