Database Reference
In-Depth Information
The transaction of the blocking process need not acquire a lock on the covering index since it accesses only the
C3
column of the table. The covering index will allow the
SELECT
statement to get the values for the
C1
and
C2
columns
without accessing the base table. Thus, the
SELECT
statement of the blocked process can acquire an (S) lock on the
covering-index row without being blocked by the (X) lock on the data row acquired by the blocking process. This
allows both transactions to execute concurrently without any blocking.
Consider a covering index as a mechanism to “duplicate” part of the table data in which consistency is
automatically maintained by SQL Server. This covering index, if mostly read-only, can allow some transactions to be
served from the “duplicate” data while the base table (and other indexes) can continue to serve other transactions.
The trade-offs to this approach are the need for additional storage and the potential for additional overhead during
data modification.
Recommendations to Reduce Blocking
Single-user performance and the ability to scale with multiple users are both important for a database application.
In a multiuser environment, it is important to ensure that the database operations don't hold database resources for a
long time. This allows the database to support a large number of operations (or database users) concurrently without
serious performance degradation. The following is a list of tips to reduce/avoid database blocking:
•
Keep transactions short.
•
Perform the minimum steps/logic within a transaction.
•
Do not perform costly external activity within a transaction, such as sending an
acknowledgment e-mail or performing activities driven by the end user.
•
Optimize queries.
•
Create indexes as required to ensure optimal performance of the queries within the
system.
•
Avoid a clustered index on frequently updated columns. Updates to clustered index key
columns require locks on the clustered index and all nonclustered indexes (since their
row locator contains the clustered index key).
•
Consider using a covering index to serve the blocked
SELECT
statements.
•
Consider partitioning a contended table.
•
Use query timeouts or a resource governor to control runaway queries. For more on the
Avoid losing control over the scope of the transactions because of poor error-handling
•
routines or application logic.
•
Use
SET XACTABORT ON
to avoid a transaction being left open on an error condition
within the transaction.
•
Execute the following SQL statement from a client error handler (
TRY/CATCH
) after
executing a SQL batch or stored procedure containing a transaction.
IF @@TRANCOUNT > 0 ROLLBACK
Use the lowest isolation level required.
•
•
Consider using row versioning, one of the
SNAPSHOT
isolation levels, to help reduce
contention.
