Performance benchmarking: Explicit vs Implicit Transactions

imageBenchmarking… The single most important word in Performance Tuning. Without an established benchmark, you really don’t know when to actually call it a day!

If you have ever worked in testing on database projects, you would have probably done testing by trying to narrow down the data set involved which is nearly representative of the actual production data set or used a sub-set of the queries which are representative of the workload which is expected or is active on the production environment.

Now this brings me to the reason of this post. It is quite often during testing that we forget that SQL Server executes all DML operations by default in autocommit mode. This means that all individual statements are committed if they complete successfully. If you want to avoid this behavior, then you either need to set IMPLICIT_TRANSACTIONS setting to ON for your database connection or use a BEGIN TRANSACTION command before you execute your query.

SQL Server follows Write Ahead Logging protocol which means no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction. (when you involve disk-caching, that opens up another can of worms but that discussion is outside the scope of this blog post)

My belief is that if you are comparing execution times between two different environments, then you should be doing that on exactly the same hardware configuration and if that is not feasible, you should at-least match the CPU, physical RAM and disk sub-system on both sides. I had already documented in an earlier blog post why RAM/CPU configurations are important when comparison execution statistics between two different SQL Server environments. Ideally, you should have the same exact environment as your test environment including the workload (i.e. number of users, connections etc.). However, we all know that Utopia is not a place where we live in and hence the toned down test scripts, scaled down/up environments, shared resources and I could keep lamenting!!

In the last month, I dealt with two such issues where in a T-SQL batch performing a large number of inserts on a database table was being used to compare the performance between two different environments. Since I get called in to fix a problem and no-one generally calls CSS to tell us that their server is running as expected… the problem invariably happened to be that a bigger, beefier server was taking a longer time to execute the same batch. I shall demonstrate where not knowing about the WAL protocol can cause such test mechanisms to be skewed and take you down the proverbial rabbit-hole!

The script that I shall be using for demonstrating the pitfalls of this behavior is pretty simple:

declare @cntr int = 1
while @cntr <= 1000000
insert into tblInserts (SNo,RowVal) values(@cntr,’Record ID ‘ +CAST(@cntr as varchar(7)))
set @cntr += 1

The script inserts 1 million rows into a database table (a heap table) using a while loop. During the time of the insert, I shall capture various performance counter values during the execution along with wait statistics for the query.

Modification: September 9th, 2011: Based on Kendra’s (Twitter) feedback, changing the sub-headings. Test 1 makes use of auto-commit mode of SQL Server which is the default and Test 2 can be achieved either by using implicit transaction mode or performing an explicit transaction (BEGIN…COMMIT).

Test 1: With AutoCommit mode

For two iterations the above script on an average took 8 minutes and 30 seconds to complete. When I looked into the wait statistics captured (at 5 second intervals), I don’t see anything standing out other than a few non-zero wait durations for WRITELOGs during certain periods. The non-zero wait times (in milli-seconds) are shown below with the highest value being captured being 10 milli-seconds. The Average Disk Secs/(Read/Write) don’t show me any outstanding values during the data capture to indicate that the disk was a bottleneck, then why does the data below show so many WRITELOG waits. Keep in mind that the total amount of time waited for as indicated by the data below is also not a significantly large amount. So why is it taking over eight minutes to insert the data??image

Runtime Session_id Status Wait_type Wait_time Last_Wait_Type
12:46 AM 51 suspended WRITELOG 10 WRITELOG
12:46 AM 51 suspended WRITELOG 7 WRITELOG
12:47 AM 51 suspended WRITELOG 1 WRITELOG
12:47 AM 51 suspended WRITELOG 2 WRITELOG
12:47 AM 51 suspended WRITELOG 3 WRITELOG
12:47 AM 51 suspended WRITELOG 2 WRITELOG
12:48 AM 51 suspended WRITELOG 7 WRITELOG
12:49 AM 51 suspended WRITELOG 8 WRITELOG
12:49 AM 51 suspended WRITELOG 1 WRITELOG
12:49 AM 51 suspended WRITELOG 10 WRITELOG
12:49 AM 51 suspended WRITELOG 6 WRITELOG
12:50 AM 51 suspended WRITELOG 2 WRITELOG
12:51 AM 51 suspended WRITELOG 3 WRITELOG
12:51 AM 51 suspended WRITELOG 4 WRITELOG
12:51 AM 51 suspended WRITELOG 1 WRITELOG
12:51 AM 51 suspended WRITELOG 2 WRITELOG
12:52 AM 51 suspended WRITELOG 5 WRITELOG
12:52 AM 51 suspended WRITELOG 7 WRITELOG
12:53 AM 51 suspended WRITELOG 1 WRITELOG
12:53 AM 51 suspended WRITELOG 8 WRITELOG

To explain the query, I would need to fall back of SQL Server Performance Monitor counters (sampled at 5 second intervals). On analysis of the performance monitor counters, I found the following:
a. SQLServer:Databases: Log Bytes Flushed/sec showed that on an average 3.1 MB of log bytes were being flushed every 5 seconds for the database on which I was performing the inserts.
b. SQLServer:DatabasesLog Flushes/sec showed that about 6000 log flushes were occurring for this database every 5 seconds on an average.
c. SQLServer:Wait Statistics: Log write waits shows me that on an average there were about 9000+ waits started per second. However, the Cumulative wait time (ms) per second for the same counter shows me negligible values and the Average wait time (ms) value is nearly zero through the data collection period.

So how can I explain where the time is being spent?? Now I decided to run another test using implicit transactions.

imageTest 2: Without AutoCommit mode

I ran two iterations of the above T-SQL batch within BEGIN TRANSACTION…COMMIT block and the average duration was 14 seconds! Whattttt!??!?! Yes.. And all this can be simply explained using the same counters that I looked above. This time around the performance monitor data told me the following story:
a. SQLServer:Databases: Log Bytes Flushed/sec showed that on an average 26 MB of log bytes were being flushed every 5 seconds for the database on which I was performing the inserts.
b. SQLServer:DatabasesLog Flushes/sec showed that about 468 log flushes were occurring for this database every 5 seconds on an average.
c. SQLServer:Wait Statistics: Log write waits shows me that on an average there were about 23(approx.)+ waits started per second.

If you look at the Performance Monitor graphs for the disk statistics that I captured for a single run for Test 1 (screenshot above) and Test 2 (screenshot on the left), you will see that the %Idle Time for the disk, on which the database files resided on (in my case F: drive), shows was busy 50% of the time (see blue squiggly above) during the test and the value climbed back to ~100% after the test completed. On the contrary, the %Idle Time for the same disk has a very zig-zag pattern (see black squiggly on left) during Test 2 which indicates that the disk was sporadically busy and it was not a continuous pattern as observed in Test 1.

The Avg. Disk Sec/Write counter shows absolutely no latency which means that the there is no problem in terms of latency on the disks.

During Test 1, data was being written to the disk at the rate of 907 KB/s where as during Test 2, the write rate was 5MB/s which was determined by monitoring the Disk Write Bytes/sec counter.

The reason for this difference is that for every insert in Test 1 is followed by an autocommit. This means that you need to flush the log buffer for each insert. However in Test 2, the log buffer was being flushed much lesser but at the same time more data was being written to the disk for each log flush. Since SQL Server follows a WAL protocol, till the earlier log flush is completed, it cannot commit/move onto the next transaction.

If you are running a similar system with two different sets of hardware, then you would start having to chase down disk speeds/RPMs between the two servers. For servers which have disks performing optimally, this behavior is quite difficult to gather without the right set of data.

With SQL Server 2008, tracking down the waits for a single session is much, much easier with the help of XEvents. But that shall be a story for another day… errrr.. post!!

Moral of the story: If you are using a test query similar to the one shown above to test/benchmark performance and the query batch is not indicative of your actual workload, then you would probably not be able to gauge or establish an accurate performance benchmark for the queries executing on your system. And REMEMBER that SQL Server runs in auto-commit mode by default!



15 thoughts on “Performance benchmarking: Explicit vs Implicit Transactions

  1. Pingback: Wednesday Weekly #sqlserver Links for 2011-30 | sqlmashup

  2. Hello!

    Let me first say that I very much enjoyed this post and I also thank you for blogging extensively. You really help show people that Microsoft SQL Server customer support is friendly, super smart, and so hard working that you go the extra mile to share information! That’s fantastic.

    I have a question about the post.

    In the headings, Test 1 says “Explicit Transactions” and Test 2 says “Implicit Transactions”. I believe Test 2 should actually be labeled ‘Explicit Transaction’ (singular) since you are using the ‘BEGIN TRANSACTION… COMMIT’ block to wrap all the inserts into a single transaction. Ref:

    Reading books online on these sections makes me realize that I think I’ve been using ‘Implicit’ incorrectly. I have used that term before to refer to single statements that are auto-committed– but now I think it may actually only be appropriate when referring to transactions begin implicitly by using SET IMPLICIT_TRANSACTION_ON

    So here’s the interesting question to me– what are the statements in Test 1 appropriately called? According to BOL, they don’t seem to be either implicit or explicit transactions. Perhaps the best way to describe them is simply auto-committed statements (and nothing else)?

    I don’t mean this at all to be nit-picking, but this is one of those things where I think the right terms help make it more clear what’s happening on the back end.

    Cheers for this post– it makes a great point, and also was very thought provoking in terms of how I think and write about transactions.

    Liked by 1 person

  3. Thanks Kendra for the words of encouragement. 🙂

    Actually this was an oversight on my part. From a pure definition standpoint, Test 1 would be SQL’s default which is “auto commit transaction mode”. From a definition standpoint, they are not implicit transactions but I was using the grammatical meanings of the words and wasn’t referring to the technical implication w.r.t. the phrases in paranthesis! I guess without me specifying that in the paragraph below makes this quite unobvious which I realize in retrospect (after reading your comments). As you already noted in your comment, implicit transactions are transactions which are executed with implicit transaction property set to ON as per BOL definition.

    Feedback like this is always appreciated as you tend to overlook the third person’s perspective when you are proof-reading your posts. 🙂

    Thanks for your feedback and the due changes have been made to the post with a shout out to you!


  4. Thanks Amit for this post. If I have to summarize. For Data Migration Process, Loading records, Updating records it is a good practice to enclose transactions with BEGIN TRAN and Commit TRAN. This would help in faster data loading.


  5. Performing operations within a begin and commit tran wouldn’t still help if you are performing a single update for a workload which performs multiple thousand such updates within a minute or lesser.


  6. – Thanks Amit. Since Data need to be committed for thousands of impacted records. Implicit transaction would need to wait to commit the transaction.


  7. Pingback: Webcast Recording – The Developer and the SQL Server Transaction Log | Brent Ozar PLF | Brent Ozar PLF

  8. Pingback: DB NewsFeed - In Case You Really Have To Use Cursors

  9. Hi Amit, for your second test, where exactly did you put the BEGIN TRANS / COMMIT? Inside of the while loop or outside?


  10. I appreciated this post since I am trying to identify invalid uses of BEGIN TRAN. I see now that depending on what we are trying to accomplish, it can be faster. I am thinking of so many ways I can use this.

    I added a third test where I added all the values to a temp table and then did a batch insert to the table and thus eliminating the need to do a BEGIN TRAN at all. Of course there are many other factors that will determine if this method is viable. Do you have any reasons as to why I would not use this approach?


  11. The idea of the post was to show that singleton transactions are not as efficient for performance if you have the option of batching. Any set based insert operation helps like the example that you are pointing out using temp tables.


  12. Does this same apply for UPDATEs?
    I know I applied batch commit for INSERTs and gain huge performance boost in my previous project, now I have a situation having millions of dynamic updates running – will there we any boost batching them in transaction-commit using a while loop for every 10000 updates?


  13. Pingback: In Case You Really Have To Use Cursors - Madeira Data Solutions

Comments are closed.