How to set the maximum number of available locks?

  • Post author:
  • Post category:
  • Post comments:0 Comments
  • Post last modified:January 7, 2005

Use the locks option to set the maximum number of available locks, thereby 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 an instance of 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 Server’s 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.

locks is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change locks only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

To set the locks option

A. List the advanced configuration options

USE master
EXEC sp_configure ‘show advanced option’, ‘1’
—Here is the message:
Configuration option ‘show advanced options’ changed from 0 to 1.
Run the RECONFIGURE command to install.

EXEC sp_configure

B. Change a configuration option

USE master
EXEC sp_configure ‘locks’, ‘0’

Author Details
Kubernetes, DevSecOps, AWS, 클라우드 보안, 클라우드 비용관리, SaaS 의 활용과 내재화 등 소프트웨어 개발 전반에 도움이 필요하다면 도움을 요청하세요. 지인이라면 가볍게 도와드리겠습니다. 전문적인 도움이 필요하다면 저의 현업에 방해가 되지 않는 선에서 협의가능합니다.
0 0 votes
Article Rating
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments