SQL Server Performance Forum – Threads Archive
how to set LOCK ?
How to set dynamic allocation of locks by setting theconfiguration value to 0 ? whts the procedure.Please advice
Check BOL for SP_CONFIGURE and I’m sure this option will effect only if you restart SQL after you set it. 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 issued sp_configure ‘Locks’ value now is min-5000……..Max-2147483647 . This is the max value u can go for(ie 2147483647) Then why people are bothered about configuring LOCK no, if SQL server manage it automatically within this limit . Rajiv
SQL-DBA
Hi I really did some R&d with configuring locks.Here are my findings. sp_configure we can use to configure static or dynamic settings.
*Static – we need to restart the server
eg: awe enabling
*Dyamic – we can use RECONFIGURE WITH OVERRIDE option after making the configuration.
ther by we can avoid restart of server.
.Some time people use fixed value for locks.But leaveing it to default value(the one exist after fresh installation) is recomended.If you are using a fixed value for locks you can bring itto default by sp_configure ‘Locks’ ,’0′ . By this lock escalation can aquire max 40% memory. Thanks to all . Rajiv
SQL-DBA
Check this linkhttp://www.sql-server-performance.com/sql_server_performance_audit5b.asp under Locks section. 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.
]]>