• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 843
  • Last Modified:

What issue apply when using (NO LOCK)

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?
0
jeffbrock1968
Asked:
jeffbrock1968
  • 3
  • 2
1 Solution
 
ptjcbCommented:
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. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

0
 
jeffbrock1968Author Commented:
why are we getting sch-s mode on tables in sql 2000 when using nolock option on our quieries?
0
 
ptjcbCommented:
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.


ΓΏ
0
 
jeffbrock1968Author Commented:
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?
0
 
ptjcbCommented:
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?

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now