Hi all,

SQL server doesnt provide any kind of configuration to
control granularity of locking on tables. As per books
online it is managed dynamically.

I have a major problem with my SQL Server, during
execution of one SP, on one table with 400+ rows it
acquires 600,000 locks. My profiler shows
whooping 'Lock:Acquired' on this particular table.

Can somebody please throw some light on this? Why SQL
server doesn't promote it to single table level lock? Is
there any bug in SQL?

thanks very much
Himanshu Jani

You probably want to keep working on why this happens in the first place (although there's not much
to go on for us), but here are a couple of options for immediate fix:

Specify lock hint in the query (TABLOCK, for instance).
Configure the index used to not allow row locks (sp_indexoption).

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


"Himanshu Jani" <anonymous (AT) discussions (DOT) microsoft.com> wrote