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:

SQL Server 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 Server 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 Server Profiler.

See Also
Batches Coding Efficient Transactions

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.