Effects of Transactions and Batches on Application Performance
A primary goal of using Transact-SQL appropriately is to reduce the amount of data transferred between server and client. Reducing the amount of data transferred will usually reduce the time it takes to accomplish a logical task or transaction. Long-running transactions can be fine for a single user, but they scale poorly to multiple users. To support transactional consistency, the database must hold locks on shared resources from the time they are first acquired within the transaction until the transaction commits. If other users need access to the same resources, they must wait. As individual transactions get longer, the queue and other users waiting for locks gets longer and system throughput decreases. Long transactions also increase the chances of a deadlock, which occurs when two or more users are simultaneously waiting on locks held by each other. For more information, see Deadlocking.
Techniques you can use to reduce transaction duration include:
- Committing transactional changes as soon as possible within the requirements of the application.
Applications often perform large batch jobs, such as month-end summary calculations, as a single unit of work (and thus one transaction). With many of these applications, individual steps of the job can be committed without compromising database consistency. Committing changes as quickly as possible means that locks are released as quickly as possible.
- Taking advantage of Microsoft® SQL Server™ 2000 statement batches.
Statement batches are a way of sending multiple Transact-SQL statements from the client to SQL Server at one time, thereby reducing the number of network roundtrips to the server. If the statement batch contains multiple SELECT statements, the server will return multiple result sets to the client in a single data stream.
- Using parameter arrays for repeated operations.
For example, the Open Database Connectivity (ODBC) SQLParamOptions function allows multiple parameter sets for a single Transact-SQL statement to be sent to the server in a batch, again reducing the number of roundtrips.
SQL Profiler can be used to monitor, filter, and capture all calls sent from client applications to SQL Server. It will often reveal unexpected application overhead due to unnecessary calls to the server. SQL Profiler can also reveal opportunities for placing statements that are currently being sent separately to the server in batches. For more information, see Monitoring with SQL Profiler.