In SQLServer, there is a theoretical limits of maximun locks:
Configuration value Minimun Maximun
------------------- ------- -------
locks 5000 2,147,483,647
Microsoft SQL Server version 7.0 contains improved algorithms for controlling computer resources. LOCKS option that had to be configured manually in earlier versions of SQL Server is managed dynamically in SQL Server 7.0.
Instead of specifying the size of a static allocation, the LOCKS option now specify the lower or upper limit for the number of locks that are allocated dynamically as needed.
In comp.databases.sybase DNP <High.Flight_at_btinternet.com> wrote:
> After that essay on the lock performace of SQL Server (i.e. it can run
> out of locks) versus Oracle which can assign unlimited locks (subject to
> memory AND disk limits) well then I think I'll give SQL Server 7 a
> miss, thankyou very much.
> I bet MS LOVES people like you telling the world of the inadequacies of
> their products!!
> Or taking another point of view; if the only people advocating SQL
> Server 7 are seen to be a bit lost when it comes to databases, then this
> sort of negates their own advice to try / use SQL Server doesn't it.
> David P.
> Oracle Certified DBA.
> ====================================================================
> Norris wrote:
>>
>> In comp.databases.sybase DNP <High.Flight_at_btinternet.com> wrote:
>>
>> > From recent discussions about their writer-blocks-reader nonsense and
>> > 'dynamic row level locking' i.e.
>> > 'row-level-locking-until-our-server-can't-handle-it-and-then-the-locks-are-escalted'
>> > it's clear that SQL Server, while technically able to store data /
>> > answer queries etc. is not much more than a pretty, wizard driven
>> > database.
>>
>> Have you used SQL7 ?
>>
>> You can use the LOCKS option to set the maximum number of available locks, limiting the amount of memory Microsoft SQL Server uses for locks. The default setting is 0, which allows SQL Server to allocate and deallocate locks dynamically based on changing system requirements.
>>
>> When the server is started with LOCKS set to 0, the lock manager allocates two percent of the memory allocated to SQL Server to an initial pool of lock structures. As the pool of locks is exhausted, additional locks are allocated. The dynamic lock pool does not allocate more than 40 percent of the memory allocated to SQL Server.
>>
>> Generally, if more memory is required for locks than is available in current memory, and more server memory is available (the MAX SERVER MEMORY threshold has not been reached), SQL Server allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application was running on the same computer as SQL Server and using that memory), more lock space is not allocated.
>>
>> Allowing SQL Server to use locks dynamically is the recommended configuration. However, you can set LOCKS and override SQL Servers ability to allocate lock resources dynamically. Increase this value if SQL Server displays a message that you have exceeded the number of available locks. Because each lock consumes memory (96 bytes per lock), increasing this value can require increasing the amount of memory dedicated to the server.
--
http://www.cooper.com.hk
Received on Mon Apr 10 2000 - 00:00:00 CDT
