memory used by Locks

Question

I am kind of curious, one of SQL 2012 enterprise edition with 128 GB of RAM size of database is 370 GB and growing, amount of memory used by locks (OBJECTSTORE_LOCK_Manager) memory clerk showing 7466016 KB. I can also confirm that by looking at perf counter select * from sys.dm_os_performance_counters where counter_name = 'Lock Memory (KB)'

However, when I run query

select count(*) from sys.dm_tran_locks

it shows only 16 locks. So what is using over 7 GB of locks. Is there a way to find out?

Does that mean if once memory for locks has been allocated SQL has not yet deallocated it? In past 1 hour I do not see lock count exceeding 500 but lock memory stays the same.

Max Server Memory is 106 GB, We do not use lock pages in memory and I do not see any memory pressure or any errors in the error log in past 12 hours. Available MBytes counter shows more than 15 GB of available memory.

Activity monitor consistently shows 0 waiting tasks so obviously no blocking.

Considering SQL server lock take about 100 bytes of memory 7 GB is lots of memory and trying to find out who is using it.

I run a server dash board report top transaction by lock count it says "currently no locking transactions are running on the system. However, lock memory still shows as stated above. DB is most busy during overnight hours.

Answer 1

The lock manager is a such super-hot critical code path (probably the most hot critical code path) that it if it would have to wait on a memory allocation for each lock performance would tank. It probably allocates large memory blocks and manages them on it's own. I wouldn't be surprised if it also reserves memory so that it does not run out of memory in some critical code paths.

Answer 2

Addendum to @RemusRusanu's answer (wouldn't fit in a comment)...

Given that the database engine will permit up to 5000 locks per object before escalating and taking into account Remus’s answer regarding the critical nature of the lock manager, the high reservation starts to look plausible:

5000 (locks) * 10 (tables or indexes) * 96 (bytes per lock) * 1000 (concurrent queries) = 4.47GB

I would speculate the reservation is derived from a combination of the available RAM and the current workload but haven’t seen it documented or blogged about anywhere. Could also speculate that your 128GB memory would have been considered generous in 2008 and the 7GB reservation is indicative of expecting a heavy OLTP workload at that size.

Answer 3

sys.dm_tran_lock shows locked resources and requests for locks on resources, not individual rows, that locked. Each locked resource will hold many rows and, possibly, other objects, locked.

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.