Locks Set To Something Besides 0

Why You Should Leave SQL Server’s LOCKS Setting at 0

SQL Server uses locks to maintain data integrity and concurrency control. By default, the LOCKS configuration option is set to 0, allowing SQL Server to dynamically manage the number of locks based on available memory and workload demands.

Some DBAs may be tempted to manually set a fixed value for LOCKS, but this is almost always a bad idea.

Why You Should Keep LOCKS Set to 0

  1. SQL Server Manages Locks Efficiently
    • When LOCKS = 0, SQL Server automatically allocates and releases locks as needed, optimizing performance based on system resources.
    • Manually setting this value limits SQL Server’s flexibility and may lead to performance issues.
  2. Fixed Lock Limits Can Cause Errors
    • If the LOCKS setting is set too low, SQL Server may run out of available locks, causing queries to fail with errors like: 1204: SQL Server cannot obtain a lock resource at this time.
    • This can lead to unexpected query failures and transaction rollbacks.
  3. Wasted Memory if Set Too High
    • If you set LOCKS too high, SQL Server will pre-allocate memory for locks, even if they are not needed.
    • This reduces available memory for caching and query execution, leading to poor performance.
  4. Microsoft Recommends Keeping It at 0
    • The official Microsoft recommendation is to always leave LOCKS at 0 unless explicitly advised by Microsoft Support.

How to Check the Current LOCKS Setting

Run the following query in SQL Server Management Studio (SSMS):

EXEC sp_configure 'locks';

If the run_value is not 0, it should be reset.

How to Reset LOCKS to Default (0)

If LOCKS has been manually set, reset it with:

EXEC sp_configure 'locks', 0;
RECONFIGURE;

No restart is required—SQL Server will immediately resume dynamic lock management.

Final Thoughts

Manually setting the LOCKS option does more harm than good. By leaving it at 0, you allow SQL Server to dynamically allocate locks based on system needs, ensuring optimal performance and stability.

Need help optimizing your SQL Server settings? Our team at Stedman Solutions specializes in SQL Server performance tuning and can help ensure your database is running at peak efficiency. Contact us today!