Yet another SQL Server enthusiast

2015-04-23

SQL Server: autocommit transactions vs. explicit transactions. A simple test.

Hypothesis

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.

Test

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 back when statement execution completes).
  • Second batch uses explicit transactions to encapsulate every five INSERT statements in a single transaction.
Code Snippet
CREATE TABLE dbo.SalesOrder (
    SalesOrderID    INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate        DATE NOT NULL,
    FillerColumn    VARCHAR(100) NOT NULL
);
GO

TRUNCATE TABLE dbo.SalesOrder
GO

— First batch
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT    dbo.SalesOrder (OrderDate, FillerColumn)
    VALUES    (GETDATE(), REPLICATE('*', 100));

    SET @i += 1;
END;
GO

— Second batch
DECLARE @i INT = 0;
BEGIN TRANSACTION;

WHILE @i < 100000
BEGIN
    INSERT    dbo.SalesOrder (OrderDate, FillerColumn)
    VALUES    (GETDATE(), REPLICATE('*', 100));

    SET @i += 1;
    IF @i % 5 = 0
    BEGIN
        COMMIT TRANSACTION;
        BEGIN TRANSACTION;
    END
END;
IF @@TRANCOUNT > 0
    COMMIT
GO

Test’s results

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)

TX.performance.AutoCommit.vs.Explicit.Transactions. 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.

Blog at WordPress.com.