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.