Type: Advanced, self-configuring
In versions of SQL Server prior to 2005, the DBA had to specify the number of locks available to SQL Server. If this parameter was set to a low value, a query requiring a large number of locks would fail at runtime. Setting it too high would result in wasting memory that otherwise could be used to cache data. SQL Server 2012 can handle locks dynamically if this parameter is set to the default value (
0). SQL Server initially allocates 2% of memory available to SQL Server (or usually about an initial pool of 2,500 lock structures). As lock resource structures are consumed, the lock manager allocates more lock resources to the pool, to a maximum of 60% of the memory available on SQL Server. ...