Optimizing Transactional Code

There are a number of general guidelines for writing efficient transaction-oriented T-SQL. Here are a few of them.

• Keep transactions as short as possible. Once you've determined what data modifications need to be made, initiate your transaction, perform those modifications, and then end the transaction as soon as possible. Try not to initiate transactions prematurely.

• Limit transactions to data modification statements when practical. Don't initiate a transaction while scanning data if you can avoid it. Though transactions certainly impact reading data as well as writing it (e.g., dirty and nonrepeatable reads, phantom rows, and so on), it's often possible to limit them to just those statements that modify data, especially if you do not need to reread data within a transaction.

• Don't require user input during a transaction. Doing so could allow a slow user to tie up server resources indefinitely. It could also cause the transaction log to fill prematurely since active transactions cannot be cleared out of it.

• Try to use optimistic concurrency control when possible. That is, rather than explicitly locking every object your application may change, allow the server to determine when a row has been changed by another user. You may find that this occurs so little in practice (perhaps the app is naturally partitioned, or, once entered, rows are rarely updated, and so on) as to be worth the risk in order to improve concurrency.

• Use nonlogged operations intelligently. As I've pointed out, nonlogged operations impact the transaction log backup and recovery process. This may or may not be a showstopper, but when allowable, nonlogged operations can turbocharge an application. They can often reduce processing time for large amounts of data by orders of magnitude and virtually eliminate a number of common transaction management headaches. Just keep in mind that this increased performance sometimes comes at a cost.

• Try to use lower (less restrictive) TILs when possible. READ COMMITTED, the default, is suitable for most applications and will provide better concurrency than REPEATABLE READ or SERIALIZABLE.

• Attempt to keep the amount of data you change within a transaction to a minimum. Don't indiscriminately attempt to change millions of rows in a table and expect concurrency and resource utilization to magically take care of themselves. Database modifications require resources and locks, and these locks by definition impact other users. Unless your app is a single-user app, it pays to be mindful of operations that could negatively impact concurrency.

• Don't use implicit transactions unless you really need them, and even then watch them very closely. Because implicit transactions are initiated by nearly any primary Transact-SQL command (including SELECT), they can be started when you least expect them, potentially lowering concurrency and causing transaction log problems. It's nearly always better to manage transactions explicitly with BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN than to use implicit transactions. When you manage transactions yourself, you know exactly when they're started and stopped—you have full control over what happens.