SQL Server Performance Forum – Threads Archive

How much memory does a lock take?

Is there a way to tell how much memory does a lock take? So far, I can only find that SQL Server 2000 will not allocate more than 40% of the memory for locks.

Allowing SQL Server to use locks dynamically is the recommended configuration. However, you can set locks and override SQL Server#%92s ability to allocate lock resources dynamically. Increase this value if SQL Server displays a message that you have exceeded the number of available locks. Because each lock consumes memory (96 bytes per lock), increasing this value can require increasing the amount of memory dedicated to the server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, So, if I want to calculate how much memory all the locks consumed, I can just multiply # of locks (RID, KEY, PAG, TAB, DB) by 96 bytes? Is there a formula to calculate when SQL Server will use PAG or TAB instead of RID or KEY? I wonder whether using WITH (PAGLOCK) in SQL Server 2000 will cause SQL Server to use PAGLOCK even though the lock should be RID or KEY.
Thank you, Peter

I don’t have straight answer to this but you would be better of by referring to Inside SQL Server book by Kalen Delaney. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I think looking at the no. of locks should give you a fair idea of the memory being used for handling locks. I don’t think I ahve a forrmula for the same. Yes using PAGLOCK will use Page lock even when row lock would have been sufficient. Ideally, we should leave the locking mechanism for the engine to figure out. But occasionally when the need comes, locking hints may need to be provided. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

From one of the MS article:
Databases place locks at all levels of their physical hierarchies: rows, pages (typically a few KB of rows), extents (typically a few pages), entire tables, and entire databases. Some databases (Oracle, others?) only use fine-grained row locks, others don’t do row locks at all and only allow rough-grained page, extent, table, and database locks. Most databases – SQL Server included – support row locking, but often use rough-grained locks. This is because lock management is a royal pain. Locks aren’t small or simple entities, so if you only do row-level locking, you can get yourself into a world of pain: a million-row update can easily swamp memory and be a bear to manage.
And I believe third party tools may be available to count the memory, if not you may try with the PERFORMANCE MONITOR counters using MEMORY, SQL Server Locks objects. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>