configuring locks in awe system

[aswinee]

I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition
on Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPU
and 8GB of RAM. I have AWE enabled, /pae /3gb switch is on in
boot.ini, In my errorlog I have noticed few times error messages as
"The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration..
" I haven't modified anything in sp_configure 'locks', so i am
assuming SQL server is dynamically managing the lock memory. Running
perfmon for few days I observed that Lock Requests/sec are usually few
100,000 most of the times and sometimes goes to 2+ million. However I
haven't seen Lock Memory (KB) going more than 40MB. So i wonder why am
I seeing the Error: 1204 in my sql log ? And any advise on how to
prevent it.
Thanks
Aswinee

[Erland Sommarskog]

aswinee (as*****@yahoo.com) writes:

I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition
on Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPU
and 8GB of RAM. I have AWE enabled, /pae /3gb switch is on in
boot.ini, In my errorlog I have noticed few times error messages as
"The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration..
" I haven't modified anything in sp_configure 'locks', so i am
assuming SQL server is dynamically managing the lock memory. Running
perfmon for few days I observed that Lock Requests/sec are usually few
100,000 most of the times and sometimes goes to 2+ million. However I
haven't seen Lock Memory (KB) going more than 40MB. So i wonder why am
I seeing the Error: 1204 in my sql log ? And any advise on how to
prevent it.

I guess there are two questions here:
1) is that locking frequency natural, or is there is a need to investigate
this?
2) are you really hitting hard limits of number of available locks.

I don't know what the maximum number of locks, but I expect it to be more
than two million. Then again, two million lock requests in one second,
does not tell us how many locks thar actually are in use. A lock can be
held for a short moment, or several hours (which would be due to poor
design).

But two million lock requests, or even 100000 sounds a lot to me. But if
that is a busy system it could be normal.

Since this is not really my field of expertise, so I asked in our private
MVP forum, and I got these suggestion:

few interesting points to check are:
- make sure your statistics are up to date
- you have sufficient indexes to run your query efficiently

which is pretty standard. But this is more interesting:

I'd also ask if trace flag 1211 has been set, see
http://support.microsoft.com/?kbid=323630

With 1211 set, you turn off lock escalation, and this can lead to a lot of
locks being requested.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp