SQL Server Performance

(nolock) speed gain

Discussion in 'T-SQL Performance Tuning for Developers' started by akus, Dec 22, 2005.

  1. akus New Member

    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
  2. SQLDBcontrol New Member

    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

    Karl Grambow

    www.sqldbcontrol.com
  3. akus New Member

    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.
  4. joechang New Member

    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?

Share This Page