SQL Server Performance Forum – Threads Archive

Effects of using Transaction in SP on Performance?

Hi All; I have a web application which is being used at large scale and there are upto 10,000 entries per day. Currently, there is no Begin Tran/Commit Tran/Rollback Tran and b/c of that we found some data corruption. Now, I changed all the transactional Stored Procedure and used Transaction into them. Now, I am thinking about the performance of the application when I used Transaction into Stored Procedures which are being used by each user very frequently. Transaction Lock the objects which might hurt the application performance. Please, comments on it and should I use Transaction now or not? Thanks. Essa, M. Mughal
Software Engineer

I don;t see any note of performance issues in using Transactions, and I feel it is better to use them in SP which provided easy data integrity. In the generic term transactions are handled in SQL Server, where it first writes to the log file what it’s going to do. Then it does the actual update statement and finally it writes to the log that it completed the update statement. The writes to the log file are written directly to disk but the update itself is probably done to a copy of the data that resides in memory. At some future point that database will be written to disk. If the server fails after a transaction has been committed and written to the log, SQL Server will use the transaction log to "roll forward" that transaction when it starts up next. Wherever possible, break large transactions into smaller transactions. Make sure each transaction is well-defined within a single batch. To minimize possible concurrency conflicts, transactions should not span multiple batches nor wait for user input. Grouping many Transact-SQL statements into one long-running transaction can negatively affect recovery time and cause concurrency problems. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

You may have issues for queries waiting for locks to be released. You may consider using nolock hints in selects that are just reading data without intention to update them.

]]>