Prevent Sql Server Locks

Most of the time we write query in different databases, but we are not sure whether it affects any performance of the database. Lets see how to prevent SQL server table locks.

Tables that change little, if at all, such as lookup tables consider altering the default lock level for the table.

You can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to tell SQL Server to use page locking, not row locks, for a specific table:

SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO

This code turns off both row and page locking for the table, thus only table locking is available.

One way to help reduce locking issues is to identify those transactions that are taking a long time to run. The longer they take to run, the longer their locks will block other processes, causing contention and reduce performance. The following script can be run to identify current, long-running transactions. This will provide you with a clue as to what transactions are taking a long time, allowing you to investigate and resolve the cause.

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]FROM master..sysprocesses pWHERE open_tran > 0AND spid > 50AND datediff (s, last_batch, getdate ()) > 30ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)

This query provides results based on the instant is runs, and will vary each time you run it. The goal is to review the results and look for transactions that have been open a long time. This generally indicates some problem that should be investigated.

In order to reduce blocking locks in an application, you must first identify them.

Once they are identified, you can then evaluate what is going on, and perhaps be able to take the necessary action to prevent them. The following script can be run to identify processes that have blocking locks that occur longer than a time you specify. You can set the value used to identify blocking locks to any value you want. In the example below, it is for 10 seconds.

SELECT spid, waittime, lastwaittype, waitresourceFROM master..sysprocessesWHERE waittime > 10000 -- The wait time is measured in millisecondsAND spid > 50 -- Use > 50 for SQL Server 2000, use > 12 for SQL Server 7.0

This query measures blocking locks in real-time, which means that only if there is a blocking lock fitting your time criteria when you this query will you get any results. If you like, you can add some additional code that will loop through the above code periodically in order to more easily identify locking issues. Or, you can just run the above code during times when you think that locking is a problem.

The original link is http://www.sql-server-performance.com/reducing_locks.asp