Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 yet not yet deallocated it? In past 1 hour I do not see lock count exceeding 500 but lock memory stays the same.

EDIT: 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. Avialble MBytes couter shows more than 15 GB of available memory.

EDIT 2 Activity monitor consistenly 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.

EDIT 3: 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.

share|improve this question
    
What is the Max memory setting on the server ? Any error message relating to locks in the error log ? msdn.microsoft.com/en-us/library/aa337440.aspx –  Kin May 30 '13 at 15:33
    
question has been updated –  SQL Learner May 30 '13 at 15:35
    
I would suggest looking at system_health as well as RING_BUFFERS to see whats going on –  Kin May 30 '13 at 16:03
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
Remus, I do not know who else in this forum knows C++ side of SQL Server as good as you. So giving you a benefit of doubt. :-) –  SQL Learner May 30 '13 at 17:36
add comment

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.

share|improve this answer
1  
Database size expected to be 1.5 TB by end of the year It has been in service just couple of weeks. Your calculation kind of make sense. –  SQL Learner May 31 '13 at 2:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.