August 24, 2013 at 11:54 am
One of our developers was trying to help me troubleshoot a query timeout problem and he ran into a problem like this...
He was testing a small SP that updates a sequence table and returns the next value. The test was done on a lab server that was not running anything else so there was no possibility of interference from other processes at all. He ran the following set of queries, 1000 lines in total.
update SeqTable set NextValue = 748661 where (SeqID = 142) and NextValue = 748660;
update SeqTable set NextValue = 748662 where (SeqID = 142) and NextValue = 748661;
update SeqTable set NextValue = 748663 where (SeqID = 142) and NextValue = 748662;
update SeqTable set NextValue = 748664 where (SeqID = 142) and NextValue = 748663;
update SeqTable set NextValue = 748665 where (SeqID = 142) and NextValue = 748664;
...
The whole bunch of queries lasted 88 seconds, which translates to about 90ms per update.
However, if the whole 1000 lines were enclosed in a 'Begin Transaction' and 'commit', each update averages less than 1ms. The SeqTable itself has three columns, 200+ rows and a primary key on SeqID.
My guess is that the whole table was on a one or two memory pages. When 1000 queries needs to update the single page, each query will have to acquire a lock and it adds to the overhead. But wouldn't the same set of queries in a transaction have to do the same thing?
Thanks.
August 24, 2013 at 1:36 pm
Someone could explain this better than I but, at a 100,000ft view, when you put all of these queries in a single transaction, the data isn't actually written as you would think until the transaction is committed. When that happens, it's done as a single transaction from the Log file to the table. It's a tried and true method to make WHILE loops run a bit faster although it does extend the time for locks taken. Generally, such single-ton updates should be avoided.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2013 at 2:09 pm
Hi Jeff,
We did this test to check the overall response time on high number of consecutively updates, which is similar to the pattern the table is used in production.
We thought that the semicolon at the end of each line will cause an implicit commit. Are we wrong in such an assumption?
What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?
BY the way I checked the Implicit Transaction for both SQL server and my query sessions and it is OFF.
Thanks.
August 24, 2013 at 4:15 pm
Jan Arnoldus (8/24/2013)What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?
In the first case, each statement commits separately, and the log record must be flushed to the transaction log on disk before execution can continue. In the second case, the log records do not have to be flushed until the entire transaction commits.
By the way, the semicolons has nothing to with it. They are just syntax.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 24, 2013 at 4:26 pm
Jeff Moden (8/24/2013)
the data isn't actually written as you would think until the transaction is committed.
In both cases the data changes are made to pages in memory and those changes will be written to disk later. Later is not at the point the transaction is committed, it's any time before or after that. There's no difference here between individual statements and an explicit transaction.
When that happens, it's done as a single transaction from the Log file to the table.
Nothing moves from the log file to the table when a transaction commits.
What happens when a transaction commits is that the log records describing that change are written to disk. For individual statements, that's one log write after each one. For a single explicit transaction, that's a log write at the commit with maybe a couple more during the operation, depending on the size of the log records and other factors.
The explicit transaction will have much fewer log writes, so much less time in writing to the log file on disk (viewable as writelog waits). That's why it's faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2013 at 4:30 pm
Hi Gail,
That makes lot of sense now. Thanks.
I'm a big fan of your blogs. I'm up to Chap 7 of SQL Server Transaction Log Management as we speak.
Have a great weekend.
August 24, 2013 at 8:18 pm
GilaMonster (8/24/2013)
Jeff Moden (8/24/2013)
the data isn't actually written as you would think until the transaction is committed.In both cases the data changes are made to pages in memory and those changes will be written to disk later. Later is not at the point the transaction is committed, it's any time before or after that. There's no difference here between individual statements and an explicit transaction.
When that happens, it's done as a single transaction from the Log file to the table.
Nothing moves from the log file to the table when a transaction commits.
What happens when a transaction commits is that the log records describing that change are written to disk. For individual statements, that's one log write after each one. For a single explicit transaction, that's a log write at the commit with maybe a couple more during the operation, depending on the size of the log records and other factors.
The explicit transaction will have much fewer log writes, so much less time in writing to the log file on disk (viewable as writelog waits). That's why it's faster.
And that, ladies and gentlemen, is the "someone" I was talking about. Thanks, Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2013 at 11:27 am
Jan Arnoldus (8/24/2013)
Hi Jeff,We did this test to check the overall response time on high number of consecutively updates, which is similar to the pattern the table is used in production.
We thought that the semicolon at the end of each line will cause an implicit commit. Are we wrong in such an assumption?
What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?
BY the way I checked the Implicit Transaction for both SQL server and my query sessions and it is OFF.
Thanks.
Implicit Transactions have to committed explicitly, so the first DML (or DDL, with some exceptions) after implicit transactions is switched on or a previous implicit transaction is committed or rolled back starts a new implicit transaction and subsequent statements are part of that same transaction until it is ended either by an explicit commit or rollback or by an error that forces rollback.
When implicit transactions is off and there is no current transaction, a statement that would otherwise have begun an implicit transaction becomes a transaction on its own - it starts the transaction and also either commits it or rolls it back.
Rotten terminology, really - both the single statement transaction when implicit transactions is set off and the multi-statement when it is on are actually begun implicitly; the single statement one is also terminated implicitly, so it actually seems more entitled to the designation "implicit transaction" than what you get when implicit transactions is switched off. It's not at all surprising that people sometimes are confused about what "SET IMPLICIT_TRANSACTIONS" means.
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply