What issue apply when using (NO LOCK)

Posted on 2007-10-09
Last Modified: 2012-08-13
Has anybody worked with SQL 2000 systems where majority of the SELECT statements had been forced to have WITH (NOLOCK) in them? All statements look like Select * from Table1 WITH (nolock). We know that we get the dirty reads but we avoid blocking of the data this way. We suspect, that using (NOLOCK) turns off the SQL LOCK MANAGER and we get the TABLE LOCK level on all our SELECT queries. We do not see a lot of blocking just some, but still does anybody know about any other affects on the SQL performance or have any opinion on the use of the WITH (NOLOCK) in the SELECT statement  when working with the tables which heavily used to read from and write to?
Question by:jeffbrock1968
    LVL 27

    Expert Comment

    Most of the time, you can use NOLOCK with Select statements without major problems, if you understand that you are looking at "dirty" data.

    This article talks about the consistency issue.


    Author Comment

    why are we getting sch-s mode on tables in sql 2000 when using nolock option on our quieries?
    LVL 27

    Accepted Solution

    The sch-s lock is the schema stability lock. It ensures that no one can drop the table in the middle of your query. You would see that if you didn't use the WITH(NOLOCK) hint.


    Author Comment

    With the nolock option in our query we see the mode "sch-s" but when we take the nolock option out of our query the mode is "s".  My understanding is the query is opimtimized once but it looks like with the "nolock "option added to our queries sql 2000 optiimizies the query each time it is used.  Is this normal? does that overhead on our server?
    LVL 27

    Expert Comment

    The s lock is shared.

    That's a different question. The query optimizer estimates the cost of each query and creates an execution plan. That execution plan stays in effect while it remains in cache.

    >>it looks like with the "nolock "option added to our queries sql 2000 optimizies the query each time it is used
    Why do you say that? Are the execution plans changing each time you run the same query?


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    TSQL Update Field Older Than 5 Days 10 36
    MS Access question 11 39
    return month and day from a date 7 24
    best way to upgrade 3 29
    This article describes some very basic things about SQL Server filegroups.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now