SQL Server Performance Forum – Threads Archive

Locks

Hi,
I need to know how can i configure my sqlserver to avoid locks for concurrency with a general parameter on the all engine.
And how set a lock timeout. Thanks.

You can’t do that in general.
Locks depends, in most cases, on application. From BOL locks Option
Use the locks option to set the maximum number of available locks, thereby limiting the amount of memory MicrosoftÂ® SQL Serverâ„¢ uses for locks. The default setting is 0, which allows SQL Server to allocate and deallocate locks dynamically based on changing system requirements. When the server is started with locks set to 0, the lock manager allocates two percent of the memory allocated to SQL Server to an initial pool of lock structures. As the pool of locks is exhausted, additional locks are allocated. The dynamic lock pool does not allocate more than 40 percent of the memory allocated to SQL Server. Generally, if more memory is required for locks than is available in current memory, and more server memory is available (the max server memory threshold has not been reached), SQL Server allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application was running on the same computer as an instance of SQL Server and using that memory), more lock space is not allocated. Allowing SQL Server to use locks dynamically is the recommended configuration. However, you can set locks and override SQL Server’s 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. locks is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change locks only when show advanced options is set to 1. The setting takes effect after stopping and restarting the se
See sp_configure in BOL too.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for reply. if i change the query_wait can i to reduce the number of deadlocks?

A transaction containing the waiting query may hold locks while the query waits for memory. In rare situations, it is possible for an undetectable deadlock to occur. Decreasing the query wait time lowers the probability of such deadlocks. Eventually, a waiting query will be terminated and the transaction locks released. However, increasing the maximum wait time may increase the amount of time for the query to be terminated. Changes to this option are not recommended. I recommend to run Profiler, find queries with long duration or high reads and try to optimize those queries. See execution plan for each query and run Index Tuning Wizard to find better indexs. Also did you update statistics and/or rebuild indexes as part of maintenance plan?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

As Luis suggests, I dont think you should make server config changes at this point. You should instead concentrate on tuning the queries, decreasing lock time required and improving concurrency within transactions. This site has many articles, try the transaction articles.

What is Profiler?

Thanks, i’ll see.

http://www.sql-server-performance.com/sql_server_profiler_tips.asp
http://sqlteam.com/Item.asp?ItemID=11997 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 tried to follow the trace but didn’t show me anything, please gve me more options. My problem is with locks when some users execute queries on same table in the same time. Sometimes this is a deadlock. I tried with hints (NOLOCK in selects) and (XLOCK) in transacts but didnÂ´t work very well. Please i nead that urgent!!

KBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;251004 – montior SQL 7 blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509 – monitor SQL 2000 blocking To monitor all connections for deadlocks and get deadlock info printed to the sql errorlog, enter:
dbcc traceon (-1, 1204, 1205) To stop this monitoring:
dbcc traceoff (-1, 1204, 1205)
Monitor the errorlog. If there’s a lot of deadlocks it will grow rapidly. Some times deadlocking is confused with normal blocking. When one transaction has a lock on a resource that another transaction wants, the second transaction waits for the lock to be released. In this case the second transaction is blocked, not deadlocked. For more information about deadlocks, see Identifying Deadlocks, and Troubleshooting in BOL.
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.

So, how avoid blocks?

Refer to the KBAs above to monitor the blocking and refer to the below articles to minimize and reduce locking : http://www.sql-server-performance.com/blocking.asp
http://www.sql-server-performance.com/reducing_locks.asp 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.

]]>