Encapsulation of several SQL DML statements like INSERT, UPDATE, DELETE, MERGE in a single transaction can be beneficial for performance compared with approach which doesn’t uses explicit transactions.
To test above hypothesis I have created a small table named dbo.SalesOrder and two batches that insert 100.000 rows (per batch):
First batch uses autocommit transactions (in this context, for every INSERT statement SQL Server will create a transaction that will be committed
or rolled backwhen statement execution completes).
Second batch uses explicit transactions to encapsulate every five INSERT statements in a single transaction.
To measure the performance of every batch I have used two performance counters:
Log Bytes Flushed/sec
Log Flushes/sec (number of I/O operations per second)
As you can see in this simple test, encapsulation of several DML statement in a single transaction increased the volume of data written to disk / second (Log Bytes Flushed/sec) while the number of physical write operations into database log (Log Flushes/sec) and the execution time decreased.
Note #1: the main reasons for using transactions should be the ACID properties but the way we manage the D(durability) can have significant impact on database performance.
Note #2: this test is simple. Wasn’t my goal to do a full performance test. You should do your own tests.