• SQL Server mostly uses unordered clustered index scans when NOLOCK is specified,

    this can cause duplicate records to be returned when page splits occur while your query is scanning the index.

    Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system.

    With or without nolock, SQL will use the most appropriate index for the query. It won't use the cluster just because a nolock is specified.

    Nolock is not considered a general good practice. It's overused and is often used to hide the fact that there are other issues on the server. If you have blocking issues, the best thing to do about them is to fix the root cause, not hide the symptoms

    Better option, than using locking hints, would be to make sure that your indexes are appropriate to the queries, that the queries are written as optimally as possible and that your transactions are as short as possible. Those three will ensure that locks are taken at the lowest level of granularity possible and held for the shortest possible time.

    If you've done all that and still have blocking problems, consider using snapshot or read-committed snapshot isolation. Just be aware of the impact on TempDB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass