SQL Server Performance Forum – Threads Archive

A little clarification

Thanks for your reply. I’m still not 100% sure, however, what causes the speed increase. In the other post you say that the two main performance factors concern the obtaining of exclusive locks and the writing to the log – this I understand.

quote:by consolidating write commands, the one time overhead for the write op is reduced.
send one batch of multiple statements instead of multiple batches

I understand this. By vitue of the fact that I’m running in a stored procedure all commands will be operating in a single batch, but I would get this improvement regardless of whether a transaction is used.

quote: each transaction in a sequence gets its own write to the log, even though SQL may combine it with other txs

Are you using "tx" short for transaction? Is being in a transaction causing more log entries to be written or note?

quote

n your comment, this is reducing the locks, not increasing

I assume this is because locks are retained within the transaction, meaning that the cost of obtaining the locks are reduced, causing an increase in speed. Right?

http://www.databasejournal.com/features/mssql/article.php/3289661
http://www.sql-server-performance.com/reducing_locks_hints.asp
http://www.sql-server-performance.com/reducing_locks.asp
http://www.informit.com/articles/article.asp?p=26657
http://www.informit.com/articles/article.asp?p=26949
yi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

]]>