Locks Option - Need advanced help

I am having a problem with lock resources on my server. I am getting
Error: 1204 Cannot obtain LOCK resource at this time.
Now, before you jump to any quick answers, please keep reading.
SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
Server memory = 7168 MB
When I had the server set to dynamically configure the locks, I would
get error 1204 after my Lock memory increased to 985,728 KB. It would
increase steadily, but once it got to 985,728 KB - it would hit a wall
and not increase any further. It is my understanding that lock memory
should allocate up to 40% of the total server memory. This number
represents only about 13% of the total server memory allocated at the
time.
Considering I could not get enough resources allocated, I tried to do
the math myself and manually configure the locks option on the server
to a value of 34,000,000. Considering each lock represents 96 bytes,
this number should be about 40% of my total server memory.
Problem is that when my server starts, I get the following error:
Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
of committed memory)
Where is that coming from? Why is it allocating only 25% and 25% of
what? Certainly not 7168 MB. It is my understanding that when AWE is
enabled, it reserves the maximum server memory as soon as SQL starts.
Also, when I configure the lock option manually, I thought I was just
configuring the maximum it could get to, not the value that it would
reserve all the time, so it should not try to allocate that much at
startup.
I would love to use the dynamic configuration of locks if it would
wouldn't get stuck at a maximum of 13% of server memory.
Please help. Thanks.I could be wrong but I believe that locks are one of the many things in
memory that can not live in the AWE memory space. That would explain why
you can't go higher. That's a lot of memory for locks. I would look into
why you are taking so many locks as that is the real root of the problem.
--
Andrew J. Kelly
SQL Server MVP
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Andrew is right. Locks can not be allocated from the AWE space. The lock
manager constrains the amount of memory it consumes based upon the committed
size of the buffer pool. There is a perfmon counter that will give you the
buffer manager's committed size.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> I could be wrong but I believe that locks are one of the many things in
> memory that can not live in the AWE memory space. That would explain why
> you can't go higher. That's a lot of memory for locks. I would look into
> why you are taking so many locks as that is the real root of the problem.
>|||what do you mean by committed size of the buffer pool ?
"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vpuekj1v7dot38@.corp.supernews.com...
> Andrew is right. Locks can not be allocated from the AWE space. The lock
> manager constrains the amount of memory it consumes based upon the
committed
> size of the buffer pool. There is a perfmon counter that will give you the
> buffer manager's committed size.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > I could be wrong but I believe that locks are one of the many things in
> > memory that can not live in the AWE memory space. That would explain
why
> > you can't go higher. That's a lot of memory for locks. I would look
into
> > why you are taking so many locks as that is the real root of the
problem.
> >
>|||Jeff, Still struggling eh? You asked yourself 25% of what. Isn't it simply
25% of min server memory? I think that comes very lcose to 64 bytes per lock
(64*4309162*4(=25%)) is about 1024MB, your 'min server mem'.
Locks are 64 byte structures, a lock owner adds 32 bytes, and there is also
a lock hash slot structure with 8 byte per lock(as far as I could see). So
maybe by combining some of these other 32 and 8 byte numbers, sql comes up
with the 4309162 locks
I would think that increasing the 'min server memory' could solve your
problem, but that's (an educated) guess.
As the other authors say, SQLserver doesn't allocate lock structures from
AWE memory. I think because of the implementation of AWE, it is to 'clumsy'
to use it for other things than database page buffers.
regards,
Mario
http://www.sqlinternals.com
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Certainly don't want to put words in David's mouth but I believe he is
referring to the amount of memory in the buffer pool that is actually being
used. Just because you set the max memory to a certain size doesn't mean it
is actually holding data in all those buffer slots.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vpuekj1v7dot38@.corp.supernews.com...
> > Andrew is right. Locks can not be allocated from the AWE space. The lock
> > manager constrains the amount of memory it consumes based upon the
> committed
> > size of the buffer pool. There is a perfmon counter that will give you
the
> > buffer manager's committed size.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > > I could be wrong but I believe that locks are one of the many things
in
> > > memory that can not live in the AWE memory space. That would explain
> why
> > > you can't go higher. That's a lot of memory for locks. I would look
> into
> > > why you are taking so many locks as that is the real root of the
> problem.
> > >
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
SQL Server's buffer manager allocates the bulk of a processes *VIRTUAL*
address space on startup and then controls the amount of committed memory it
consumes by using the VirtualAlloc API with the MEM_COMMIT flag. This is how
SQL Server dynamically grows and shrinks its memory in response to system
demand.
The "committed" size of the buffer pool is the amount of memory the buffer
pool has committed at that point.
I agree with Andrew in thinking the interesting question is why the process
is consuming so many locks.