SQL Server Performance Forum – Threads Archive
(nolock) speed gain
Hi all, Following statement performs very differently with an without (nolock) hint. Can someone explain why the difference is so huge, or if I can get the perf without (nolock)? Table contains approximately 3 million rows.select top 100 * from table (nolock) where Date > ‘1 Dec 2005’
–0 seconds select top 100 * from table where Date > ‘1 Dec 2005’
–1min 39 "Date" is part of a nonclustered composite index. Index has 6 columns in total. I have tried reindexing already. Thanks, Xku
The nolock hint ignores any locks that other transactions might have placed on the table in question (or rows/pages within the table). The reason that using nolock is faster is probably because there is another transaction running against that table that has some locks on it. That’s the logical assumption anyway. When you run the statement without the nolock hint, from another connection, run sp_who2 to see if you have any blocking going on. That should help you identify what other spid is causing the blocking (if any). Hope that helps,
quote:Originally posted by akus
Hi all,
Following statement performs very differently with an without (nolock) hint. Can someone explain why the difference is so huge, or if I can get the perf without (nolock)? Table contains approximately 3 million rows.
select top 100 * from table (nolock) where Date > ‘1 Dec 2005’
–0 seconds select top 100 * from table where Date > ‘1 Dec 2005’
–1min 39 "Date" is part of a nonclustered composite index. Index has 6 columns in total. I have tried reindexing already. Thanks, Xku
select top 100 * from table (nolock) where Date > ‘1 Dec 2005’
–0 seconds select top 100 * from table where Date > ‘1 Dec 2005’
–1min 39 "Date" is part of a nonclustered composite index. Index has 6 columns in total. I have tried reindexing already. Thanks, Xku
Karl Grambow www.sqldbcontrol.com
Hi, There are no other users using the database as this is being run on a specially setup test db. And can be repeatable any time I like.
under normal circumstances, nolock should not have more than 2X gain,
there are special circumstances, queries than trigger the lock escalation, scans, and parallel execution plans that could show much > 2X, which are really "bugs",
what do you mean by part of a composite index?, is date the lead field or not? if not, the plan is a scan, followed by a bookmark lookup,
which could explain the difference, page lock and table lock should show similar perf as with nolock
also, is a parallel execution plan involved? is HT enabled?
]]>