Microsoft SQL Server 2008 R2 Unleashed- P161:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1544 CHAPTER 41 A Performance and Tuning Methodology Avoid transaction nesting issues in your stored procedures by developing a consistent error-handling strategy for failed transactions or other errors that occur in transactions within your stored procedures. Implement that strategy consistently across all procedures and applications. Within stored procedures that might be nested you need to check whether the procedure is already being called from within a transaction before issuing another BEGIN TRAN statement. If a transaction is already active you can issue a SAVE TRAN statement so that the procedure can roll back only the work that it has performed and allow the calling procedure that initiated the transaction to determine whether to continue or abort the overall transaction. Break up large complex stored procedures into smaller more manageable stored procedures. Try to create very modular pieces of code that are easily reused and or nested. For more information on using and optimizing stored procedures see Chapter 28 Creating and Managing Stored Procedures. Coding Efficient Transactions and Minimizing Locking Contention Poorly written or inefficient transactions can have a detrimental effect on concurrency of access to data and overall application performance. To reduce locking contention for resources you should keep transactions as short and efficient as possible. During development you might not even notice that a problem exists the problem might become noticeable only after the system load is increased and multiple users are executing transactions simultaneously. Following are some guidelines to consider when coding transactions to minimize locking contention and improve application performance Do not return result sets within a transaction. Doing so prolongs the transaction unnecessarily. Perform all data retrieval and analysis outside the transaction. Never prompt for user input during a transaction. If you do you lose all control over the duration of .
