Locks Set To Something Besides 0

What this check looks for

sys.configurations where locks has a value_in_use other than 0.

0 is the default and it means dynamic: SQL Server allocates lock structures as it needs them and frees them when it does not, within the memory available. Any other value is a hard ceiling.

Why it matters

A fixed lock limit converts a memory question into a failure.

With the setting at 0, an instance that needs a lot of locks allocates them, and if memory genuinely runs short it escalates row locks to table locks, which is a graceful degradation. With a fixed value:

  • Hitting the limit raises error 1204: “The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time.” The statement fails.
  • It fails abruptly and under load, because that is when lock counts peak: a large delete, an index rebuild, a month end batch.
  • It escalates earlier and more aggressively, because SQL Server tries to stay under the ceiling, so row locks become table locks sooner and blocking gets worse before the error arrives.

The setting is deprecated, and Microsoft’s documentation has recommended leaving it at 0 for many versions. It exists because in SQL Server 6.5 and earlier, lock structures were allocated in a fixed pool at startup and the size had to be chosen. That has not been true since SQL Server 7.0, released in 1998.

So a non-zero value on a current instance is one of two things: a setting carried forward through twenty years of upgrades, or somebody responding to a lock memory error by setting a limit, which is the opposite of the fix.

It usually travels with other settings from the same era. priority boost, lightweight pooling, affinity mask and set working set size all have the same provenance and all have checks on this report.

How to confirm it yourself

SELECT [name], [value], [value_in_use], [is_dynamic], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'locks';

The settings that usually accompany it:

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('locks', 'priority boost', 'lightweight pooling',
                  'affinity mask', 'set working set size')
 ORDER BY [name];

How many locks the instance is actually using, so you can see how close to the ceiling it runs:

SELECT [object_name], [counter_name], [cntr_value]
  FROM sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE [object_name] LIKE '%Locks%'
   AND [counter_name] IN ('Lock Requests/sec', 'Lock Timeouts/sec',
                          'Number of Deadlocks/sec', 'Lock Waits/sec')
   AND [instance_name] = '_Total';

SELECT COUNT(*) AS [locks_held_now]
  FROM sys.dm_tran_locks WITH (NOLOCK);

How much memory lock structures are consuming, which is the question the setting was ever about:

SELECT [type], [pages_kb] / 1024 AS [mb]
  FROM sys.dm_os_memory_clerks WITH (NOLOCK)
 WHERE [type] = 'OBJECTSTORE_LOCK_MANAGER';

And whether anything has already hit the limit:

EXEC sp_readerrorlog 0, 1, N'1204';
EXEC sp_readerrorlog 0, 1, N'LOCK resource';

How to fix it

Set it back to 0:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'locks', 0;
RECONFIGURE;

It requires a service restart to take effect. Until then value reads 0 and value_in_use still shows the old ceiling.

Check the neighbours while arranging the restart, because they need one too:

EXEC sp_configure 'priority boost', 0;
EXEC sp_configure 'lightweight pooling', 0;
EXEC sp_configure 'affinity mask', 0;
RECONFIGURE;

If error 1204 was the original motivation, the real answers are upstream and none of them is this setting:

  • Batch large modifications. A delete of ten million rows in one statement takes ten million locks. In batches of a few thousand it takes a few thousand at a time, and it is kinder in every other way too.
  • Check max server memory. Lock structures come from the same memory the instance is allowed, so an instance with no limit set, or one set too low, makes lock memory harder to come by. That has its own check.
  • Look at lock escalation on the specific tables involved. ALTER TABLE ... SET (LOCK_ESCALATION = AUTO) is worth understanding before changing, and partition level escalation is sometimes the right answer on a partitioned table.
  • Look at the transactions themselves. One very long transaction accumulating locks is a different problem with its own check.

How long it takes

About an hour, nearly all of it waiting for a window to restart the service.

Report Why you would go there
Configuration Values Every setting against its default, where the rest of these live.
Memory The memory lock structures come from.
Blocking Tree The blocking that earlier escalation produces.
Open Transactions Long transactions accumulating locks.
Waits LCK_ waits and what they say about contention.
Error Log Whether error 1204 has already occurred.
Check
Priority Boost enabled The setting this is nearly always found beside.
Lightweight Pooling enabled Likewise.
Not using all cores Affinity, the third of the group.
Max server memory Where lock memory actually comes from.
A transaction has been open far too long A common source of very high lock counts.

Frequently asked questions

We set it after getting error 1204. That is the common story, and it makes the error more likely rather than less. A fixed ceiling is what produces 1204; dynamic allocation is what avoids it.

Is there any case for a non-zero value? Not on any supported version. Microsoft’s guidance is to leave it at 0, and the setting is deprecated.

Does it need a restart? Yes. value_in_use tells you what is actually running.

How much memory will locks use if I let it manage itself? As much as the workload needs, bounded by max server memory. On a normal instance it is a small fraction, and sys.dm_os_memory_clerks above shows the current figure.