A little clarification | SQL Server Performance Forums

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:eek: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?
yi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.