SQL Server Performance

NOLOCK hint.

Discussion in 'Performance Tuning for DBAs' started by delmi, Mar 12, 2003.

  1. delmi New Member

    Hi,

    I would like to get somethoughts on how all NOLOCK hint can affect the data of a system. It is hard to test all the possible cases.

    Thank you for your help.
  2. trifunk New Member

    I haven't had any problems using the NOLOCK hint, there's a breakdown of all the hints in this article :

    http://www.sql-server-performance.com/rd_table_hints.asp

    Probably best to check out the Knowledge base article links at the bottom of the article at this link, there seem to be a few problems that can occur but I've never come accross any of them.

    Cheers
    Shaun

    World Domination Through Superior Software
  3. Argyle New Member

    NOLOCK hint is same as setting the transaction isolation level to read uncommitted (the lowest).

    In other words you issue no locks and honor no locks when selecting data. This can increase performance quite a lot if you have read only queries. Never try to use the read uncommitted isolation level in a transaction environment.

    Even if it's a read only query where you add the nolock hint, you can run into issues if other queries are performing transactions in the database.

    Example with read committed (default):
    Connection A is doing DELETE and then INSERT into a table in a transaction. Connection B wants to read from the table. Connection B will only be able to read the new data once it's committed. Until it's committed connection B will read the old data.

    Example with read uncommitted:
    Connection A is doing DELETE and then INSERT into a table in a transaction. Connection B wants to read from the table. Connection B will be able to read from the table during the transaction but the number of returned rows will be different until the INSERT is finished (at first they will be zero). Also the result can not be trusted if you read the data like this during a transaction and connection A does a rollback. You then end up with a so called dirty read.

    In other words, only use the (nolock) hint in a very static data environment or when you are aware of the issues that can occur.

    /Argyle

  4. thomas New Member

    I encourage (i.e. force) my users to use NOLOCK when running reports and ad-hoc queries on our reporting server. This means that their queries, which are often very long-running, don't block each other. This is not a static environment - data is replicated to it - but dirty reads are acceptable for this level of reporting. Querying with NOLOCK has the added advantage of not blocking the continuous replication of transactions from the OLTP system.

    As mentioned above, you should only use NOLOCK when concurrency is not required and/or you don't require granular data accuracy.

    Tom Pullen
    DBA, Oxfam GB
  5. airjrdn New Member

    We use nolock on our warehouse database for all front end (.asp page) queries. It doesn't really matter if they happen to see records we are adding/removing that haven't been committed yet. This is typically a business case call though.
  6. delmi New Member

    Hi All,

    Thank you for all your suggestions.

    Thanks.

Share This Page