August 15, 2012 at 3:41 pm
I've inherited some udfs that use NoLock on every table.
The udfs populate a staging database in the middle of the night which means:
1. Very few, if any, blocking locks
2. Very few, if any, uncommited writes
Question: Will NoLock speed up the performance of the query if there are no locks on the table?
I've read how NoLock can speed up the query when blocks exist, at the risk of reading dirty data, but I haven't seen any mention of the impact if there are no locks in place.
Thanks
Wes
(A solid design is always preferable to a creative workaround)
August 15, 2012 at 3:50 pm
The NOLOCK hint is NOT a go faster button. Personally, I'd remove them.
August 15, 2012 at 3:58 pm
Thanks for the reply. I'd prefer to remove them also, but I have to justify changing script that has been working in production without a problem.
How does NoLock affect the performance if there are no locks on a table?
Wes
(A solid design is always preferable to a creative workaround)
August 15, 2012 at 4:03 pm
It neither helps nor hinders. It simply the same as using the READ UNCOMMITTED isolation level. IF there is any activity that could potentially cause a duplicate record to be read or record missed due to updates/inserts/deletes that MAY occur while the code is running, is this acceptable to the users.
August 15, 2012 at 4:04 pm
You could say this is one of those things you won't know is broke until a critical piece of data is duplicated or dropped.
August 15, 2012 at 6:20 pm
Also, if the NOLOCK hint is applied to any kind of data modification queries, SQL Server just ignores it. It has to do locks in order to modify data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply