nolock hint is causing a table lock

[kprogrammer]

We are running SQL Server 2000 on a windows 200 platform. Recently we started having a problem with our nolock hints, even directly out of query maker. The associated select queries are causing table level locks. The table we wroking against has approximatly 7 million rows. Has anyone had this problem and found a solution to it or know what causes it?

Kirk

 

[SQLDenis]

what is query maker?

Can you post the query here?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[kprogrammer]

Query maker is my shortcut name for SQL Query Analyzer (sorry for not being specific)

This happens with any querry, even basic ones such as:
Select * from tbltimesheet with (nolock) where cl_code='xyz'

Thanks
Kirk

 

[SQLSister]

How specifically did you identify that it was table locking that was causing the problem?



"NOTHING is more important in a database than integrity." ESquared

 

[evilDBA]

Hint: if you see some table-level locks of your table, it can be just harmless schema-stability lock, not data lock.

 

[kprogrammer]

It is a table sch-s lock. We are using enterprise manager to identify the locks. The queries we have been testing are just simple select statments, but of our selects come out of stored procedures. So these locks are not a problem that we should be worrying about? What casues them?

Kirk

 

[SQLSister]

from BOL
Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.

"NOTHING is more important in a database than integrity." ESquared

 

[dkillilea]

How do you set the Sch-M lock? I tried using this to no avail:
@LockMode = 'Sch-M'

I get the following message:
Procedure or function 'sp_getapplock' expects parameter '@LockMode', which was not supplied.