Database Reference
In-Depth Information
•
Prevent the possibility of transactions being left open because of poor error-handling routines
or application logic. Do so using the following techniques:
•
Use
SET XACTABORT ON
to ensure that a transaction is aborted or rolled back on an error
condition within the transaction.
•
After executing a stored procedure or a batch of queries containing a transaction
from a client code, always check for an open transaction and then roll back any open
transactions using the following SQL statement:
IF @@TRANC0UNT > 0 ROLLBACK
Use the lowest level of transaction isolation required to maintain data consistency as
•
determined by your application requirements. The amount of isolation provided by the Read
Committed isolation level, the default isolation level, is sufficient most of the time. If excessive
locking is occurring, consider using the Read Committed Snapshot isolation level.
The impact of transactions on database performance is explained in detail in Chapter 19.
Eliminate or Reduce the Overhead of Database Cursors
Since SQL Server is designed to work with sets of data, processing multiple rows using DML statements is generally
much faster than processing the rows one by one using database cursors. If you find yourself using lots of cursors,
reexamine the logic to see whether there are ways you can eliminate the cursors. If you must use a database cursor,
then use the database cursor with the least overhead: the
FASTFORWARD
cursor type (generally referred to as the
fast-forward-only cursor).
You can also use the equivalent
DataReader
object in ADO.NET.
The performance overhead of database cursors is explained in detail in Chapter 22.
Natively Compile Stored Procedures
In situations where you're accessing only in-memory tables, you have one additional performance enhancement
open to you, which is to compile your stored procedures into a DLL that runs within the SQL Server executable. As
was shown in Chapter 23, this has fairly radical performance implications. Just be sure that you call the procedures
in the correct fashion passing parameters by ordinal position rather than by parameter name. Although this feels like
you're breaking a best practice, it leads to better performance of the compiled procedure.
Summary
Performance optimization is an ongoing process. It requires continual attention to database and query characteristics
that affect performance. The goal of this chapter was to provide you with a checklist of these characteristics to serve as
a quick and easy reference during the development and maintenance phases of your database applications.