SQL Server Performance Tuning
|
Tips On How to
Minimize SQL Server Blocking |
|
Blocking occurs when one connection to SQL Server locks
one or more records, and a second connection to SQL Server requires a
conflicting lock type on the record or records locked by the first connection.
This causes the second connection to wait until the first connection releases
its locks. By default, a connection will wait an unlimited amount of time for
the blocking lock to go away. Blocking is not the same thing as a deadlock. A certain amount of blocking is normal and unavoidable.
But too much blocking can cause connections (representing applications and
user) to wait extensive periods of time, hurting overall SQL Server performance.
In the worst cases, blocking can escalate as more and more connections are
waiting for locks to be released, creating extreme slowdowns. The goal should
be to reduce blocking as much as possible. Locks held by SELECT statements
are only held as long as it takes to read the data, not the entire length of
the transaction. On the other hand, locks held by INSERT, UPDATE, and DELETE
statements are held until the entire transaction is complete. This is done in
order to allow easy rollback of a transaction, if necessary. Some causes of excessive blocking, and ways
to help avoid blocking, include:
In
many ways, the best way to avoid blocking is to write well-tuned applications
that follow the tuning advice found on this website. [7.0, 2000] Updated 1-18-2004 ***** By
default, blocking locks don't time out. The waiting connection waits
until the lock is released, and the block is over. If you like, you can set a
lock time-out so that a connection does not wait indefinitely for the
blocking lock to be released. This is accomplished using the LOCK_TIMEOUT
setting. When
the LOCK_TIMEOUT setting is used to set a maximum amount of time that a
connection can wait for a blocking lock to go away. This means that the connection
that has the lock and is causing the blocking problem is not affected, but
that the connection waiting for the block is halted, and receives an error
message. When this happens, then error message 1222, "Lock request
time-out period exceeded" is sent to the application. This
means that the application needs to include the appropriate error-handling
code to deal with this situation and take the appropriate action, which
includes rolling back the transaction. If the application does not know how
to deal with this error message, and the transaction is not rolled back, it
is possible that the application can continue as if the transaction was not
automatically cancelled. Because of this, you should not use the LOCK-TIMEOUT
setting unless your application(s) that will be affected by it know what to
do when they receive this message from SQL Server. The
syntax for the SET LOCK_TIMEOUT is: SET LOCK_TIMEOUT timeout_period where
timeout_period is the number of milliseconds that a connection waits
for a blocking lock to go away before an error is returned from SQL Server to
the application. A value of -1 is the default, which means to wait
indefinitely. A value of 0 tells SQL Server not to wait at all, and to return
the error immediately. This
command is based on a per connection basis, and stays with the connection
until the connection is broken, or a new SET LOCK_TIMEOUT command is issued.
Updated [7.0, 2000] 1-18-2004 ***** Avoid
INSERTing, UPDATEing, or DELETEing large numbers of records in a single
transaction. If you do, all the
records affected by your action will be locked until the transaction is done.
If you find that you need to perform mass data changes, it is better to batch
them into smaller, shorter transactions to prevent unnecessary locking. In
addition, changing the backup method from full to simple will also reduce the
overhead incurred by long running transactions. Once you are done with the
long running activity, you can switch back to the full backup method. [6.5,
7.0, 2000] Updated 1-18-2004 ***** One
way to help identify blocking locks is to use Enterprise Manager. If you expand "Process Info" Under
"Current Activity," for the appropriate server, and then scroll to
the right of the screen, you will see if there are currently any blocking
locks. If you do, you will see which SPID is blocking what other SPIDs.
Unfortunately, this screen is not dynamically updated, so if you will want to
refresh this screen often if you are looking for blocking locks. To refresh
the screen, right-click on "Current Activity," not "Process
Info," and then select "Refresh." Most
blocking locks go away soon. But if a blocking lock does not go away, and it
is prevent one or more users from performing necessary tasks, you can ask the
user, whose SPID is causing the blocking, to exit their program that is
causing the block. Or, you can KILL the blocking SPID from Enterprise
Manager. KILLing the blocking SPID will cause the current transaction to
rollback and allow the blocked SPIDs to continue. [7.0, 2000] Updated
1-18-2004 ***** To
help identify and correct queries that cause blocking locks, you need to find
out what the query looks like that is causing the blocking lock. Before you can do this, you must first identity
the SPID that is causing the blocking. Once you have done that, there are two
ways to view the query that is causing the blocking lock. First,
from Enterprise Manager, in the "Process Info" window located under
"Current Activity," right-click on the SPID causing the blocking
and choose "Properties." This will display the query. Second,
from Query Analyzer, enter this code to reveal the query causing the blocking
lock: DBCC INPUTBUFFER (<spid>) Once
you have identified the query that is causing the blocking lock, you can
begin researching it to see if there is anything you can do to modify the
query to avoid blocking in the future. [7.0, 2000] Updated 1-31-2004 ***** To
help identify the type of lock that a blocking lock is holding, you must first identify the SPID that is causing
the blocking, Once you have that, there are two ways to view the type of lock
being held by the blocking lock: First,
from Enterprise Manager, in the "Locks/Process ID" window located
under "Current Activity," locate the SPID causing the blocking
lock, and click on it. The type of lock will be displayed in the right-hand
window. Second,
run the command: sp_lock in
Query Analyzer. You will then have to match the SPID of the block lock to the
SPID listed in the results of this command. Knowing
the type of lock held by the blocking lock can help you figure out why the
query in question is causing a blocking lock. [7.0, 2000] Updated
1-31-2004 ***** One
way to help identify blocking locks is to use the SQL Server Profiler. The Profiler is useful for capturing blocking
locks because it can capture blocking locks over time, unlike the Enterprise
Manager, which only shows blocking locks as of the current instant. In
addition, the query that is available from the INPUTBUFFER may not be enough
information to diagnose a blocking problem. Sometimes, queries that run just
before the query that is causing the blocking is related to the blocking
problem. By performing a Profiler Trace, you can see all the queries and
other activity that precede a blocking lock. In order to use the trace data,
you will have to know the SPID that caused the blocking lock, and then look
up the data from the trace for this one particular SPID. Below
are two different Profiler configurations, one for SQL Server 7.0 and one for
SQL Server 2000. SQL
Server 7.0 Profiler Configuration Events Error
and Warnings: Exception Data
Columns Group
By: SPID Filters Trace
Event Criteria: Severity (type 24 in the Maximum box) SQL
Server 2000 Profiler Configuration Events Error
and Warnings: Exception Group:
SPID Filters Trace
Event Criteria: Severity (type 24 in "Less than or equal" box) ***** To
help identify blocking in your databases, Microsoft has two separate stored procedures listed on their
website (one each for SQL Server 7.0 and 2000) you can use to help identify
blocking problems on your SQL Servers. On these pages are scripts you can use
to create stored procedures that you can run anytime to help you identify
blocking issues. INF: How to Monitor SQL Server
7.0 Blocking (Q251004) INF: How to Monitor SQL Server 2000 Blocking (Q271509) [7.0,
2000] Added 8-9-2004 ***** One
way to help avoid blocking locks is
to ensure that your tables have appropriate indexes. This is because the quicker
that SQL Server can find the data it is looking for, the less time locks have
to be in place, reducing the potential for blocking locks to occur in the
first place. For
example, if SQL Server needs to perform an UPDATE to a row, and there is not
an appropriate index to quickly find the row that needs to be UPDATED, then a
table scan must be performed. While SQL Server is examining every row in the
table (which can take a long time if there are many rows), SQL Server has to
maintain an UPDATE lock on the entire table during the duration of the table
scan, potentially causing much blocking. Table
scans are not the only performance problem. Poorly designed indexes, while
perhaps faster than a table scan, can be much slower than a well-designed
index, potentially contributing to blocking. It is always in your best
interest to optimize indexes so they perform at their maximum. [6.5, 7.0,
2000] Added 8-9-2004 |