Chatty or Chunky? What do you mean? Running code block with a transaction can ensure the atomicity of the code, all done or all undone. In defult programming mode, select statement will never start a transaction automatically. Data modification language, such insert, delete, update, merge, send, receive, etc, will automatically start a transaction as the command starts if there isn’t any transactions and will commit
automatically (if there isn’t any transactions). I call this kind of strategy Chatty.
I call it chunky when more data modification within a transaction and complete (commit/rollback) all of them at the end. It’s important to know the differences between those 2 strategies. Why? Let’s have a test here
use test set nocount on if object_id('table1') is not null drop table table1 go create table table1 (id int) go truncate table table1 go declare @i int = 0, @StartTime datetime2(3) = sysdatetime() while @i< 3000 begin insert into table1 values (CHECKSUM(NEWID())) select @i = @i +1 end select DATEDIFF(millisecond, @StartTime, sysdatetime()) [Chatty] --529 go truncate table table1 declare @i int = 0, @StartTime datetime2(3) = sysdatetime() begin transaction while @i< 3000 begin insert into table1 values (CHECKSUM(NEWID())) select @i = @i +1 end commit select DATEDIFF(millisecond, @StartTime, sysdatetime()) [Chunky] --50 go
Chatty ----------- 1019 Chunky ----------- 35
Inserting 3000 records to a table with chatty strategy, my laptop needs to take 1.019 seconds to finish. However, by using chunky strategy, it only takes 0.035 seconds to complete, which is almost 30 times faster than chatty strategy.
One of the items in SQL Server “Best Practice Guide” is keep transaction small. This has been followed as truth by many developers neglecting the real truth in db world is “It depends”. Dealing with the size of a transaction is always an art. Bigger chunk of the transaction migh prevent records from being accessed by other sessions. This is blocking issue. Using chunky stategy might also lead transaction logs grow rapidly, which migh affect the other components of SQL Server such as transactional replication, backup/recovery, Change data captures, etc. But it provides performance!
SQL Server does natively support “chunky” strategy by using
use set implicit_transactions on. By default, implicit transacions is set to off. Once this is set, the first SQL statement of a code block which also refereneces a table will cause a transaction start if there isn’t any transacitons.
set implicit_transactions on select @@TRANCOUNT TansactionCountBeforeSelect select top 0 * from table1 select @@TRANCOUNT TansactionCountAfterSelect commit
TansactionCountBeforeSelect --------------------------- 0 id ----------- TansactionCountAfterSelect -------------------------- 1
From the example above, a transaction is started automatically when “select top 0…” command started. Back old days, it’s almost impossible to set this flag on in a busy system due to the blocking. Starting from version SQL Server 2005, SQL Server supports read committed snapshot isolation level. It effectively reduces blockings in which makes this kind of strategy possible. Shall you change your current SQL programming style? It depends again. At least, this is the best alternative when you want to make big amount of changes to a table without employing bulk insert. What if you are in a distributed transaction environment? A chatty stagegy over such environment will kill the performance.
4 thoughts on “Transactions, Chatty or Chunky?”
Thanks for providing such nice explanation.
Nice post. I’m curious, have you checked the amount of log flushes vs. log flush bytes during both tests? What about the number of status 2 VLFs as the transactions progress?
Nope, I did not. The main cause of the the differences for the performance of those 2 approaches is the overhead of the transaction log. the Log Flushes in chatty mode should be way higher than the script running under chunky mode. same to log flush buyes.
Nice blog post. I’ve run a similar that and had similar results: chatty is slow.