Server configuration: locks

Applies to: SQL Server

This article describes how to configure the locks server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL. The locks option sets the maximum number of available locks, which limits the amount of memory the SQL Server Database Engine uses for them. The default setting is 0, which allows the Database Engine to allocate and deallocate lock structures dynamically, based on changing system requirements.

Tábhachtach

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Recommendations

This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional.

When the server is started with locks set to 0, the lock manager acquires sufficient memory from the Database Engine for an initial pool of 2,500 lock structures. As the lock pool is exhausted, more memory is acquired for the pool.

Generally, if more memory is required for the lock pool than is available in the Database Engine memory pool, and more computer memory is available (the max server memory threshold hasn't been reached), the Database Engine 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 is running on the same computer as an instance of SQL Server and using that memory), more lock space isn't allocated. The dynamic lock pool doesn't acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool reaches 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error.

Allowing SQL Server to use locks dynamically is the recommended configuration. However, you can set locks and override the ability of SQL Server to allocate lock resources dynamically. When locks is set to a value other than 0, the Database Engine can't allocate more locks than the value specified in locks. Increase this value if SQL Server displays a message that you 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.

The locks option also affects when lock escalation occurs. When locks is set to 0, lock escalation occurs when the memory used by the current lock structures reaches 40 percent of the Database Engine memory pool. When locks isn't set to 0, lock escalation occurs when the number of locks reaches 40 percent of the value specified for locks.

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Use SQL Server Management Studio

1. In Object Explorer, right-click a server and select Properties.

2. Select the Advanced node.

3. Under Parallelism, type the desired value for the locks option.

   Use the locks option to set the maximum number of available locks, which limits the amount of memory SQL Server uses for them.

Use Transact-SQL

1. Connect to the Database Engine.

2. From the Standard bar, select New Query.

3. Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the locks option to set the number of locks available for all users to 20000.

   SQL

   USE master;
   GO
   
   EXECUTE sp_configure 'show advanced options', 1;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'locks', 20000;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'show advanced options', 0;
   GO
   
   RECONFIGURE;
   GO
   

For more information, see Server configuration options.

Follow up: After you configure the locks option

The server must be restarted before the setting can take effect.

Related content

• RECONFIGURE (Transact-SQL)
• Server configuration options
• sp_configure (Transact-SQL)