Log File writing algorithm

Question

Log File writing algorithm

burtsev

SSC-Addicted

Points: 400
More actions
January 8, 2012 at 2:04 pm

#248615

Hello,
Why SQL Server writes so MUCH data to transaction log and why it uses so small pieces and big number of IO operations when writing log file? Can I change the way It writes transaction log?
I have a very simple query that copies data (one column actually) from one table to another:
INSERT txt2 SELECT * FROM txt
I looked at performance monitor (N: - data, M: - log) and found out that transaction log is quite overloaded. MS SQL Server writer more data to the log file and with much bigger number of io operations rather than to data file.
So, my questions are
1. Why SQL Server writes so much data to log file (recovery model is SIMPLE)? How can I reduse volume of data that must be writtent to the log?
2. Why log data is written with so HUGE number of operations? It generates 530 IO operations per second and I have only 32 MB/sec instead of 200MB/sec that M: can achieve. Actually M: and N: are 4-disks RAID10 blocks and stable throughput is about 200MB/sec each.
Anton Burtsev

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

burtsev (1/8/2012)
1. Why SQL Server writes so much data to log file (recovery model is SIMPLE)? How can I reduse volume of data that must be writtent to the log?

It logs enough to undo or redo the operation. Since this is an insert, it logs the entire of each inserted row, along with the log header and metadata. This is so that the insert is guaranteed to be durable once the transaction completes. You can't change how SQL writes to its log (and to be honest, unless you know a lot more about what and why the log behaves as it does, why do you think your alternatives would be better)

2. Why log data is written with so HUGE number of operations?

The log buffer is max 60kb in size, so when 60kb of log records have been generated (or a transaction commits), the log buffer is flushed. The large number of outstanding operations is not a concern, SQL's designed to use async IO, so an IO (or many IOs in some cases) is issued and SQL carries on doing other stuff while the IO completes. Doesn't look like it's anywhere close to maxing out the drive.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

To add to what Gail has already identified, it's an INSERT. That means that any and all indexes for the table will also be logged and updated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

burtsev (1/8/2012)
How can I reduse volume of data that must be writtent to the log? Why log data is written with so HUGE number of operations?

One thing you could do, assuming your version of SQL Server is at least 2008, is to ensure you are getting a minimally-logged INSERT.

You haven't said whether the destination table is a heap or clustered table, whether it contains any rows before the INSERT starts, whether any non-clustered indexes exit, or whether you are using a TABLOCKX hint...so you might need trace flag 610 to achieve minimal logging. See http://msdn.microsoft.com/en-us/library/dd425070.aspx for the full details.

By the way, minimal logging does not mean a smaller volume will be logged (it can be larger!) - with minimal logging, SQL Server does not log individual rows, it logs only extent allocations and the new pages when the transaction commits.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

burtsev SSC-Addicted Points: 400 More actions · Answer 4

Thank you very much guys, all your advices are very informative, thanks again.

Anton Burtsev

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

burtsev (1/9/2012)
Thank you very much guys, all your advices are very informative, thanks again.

Just bear in mind that the comment about minimally logged operations, while it applies to your example, doesn't apply to every operation in the DB. The list of operations that can be minimally logged is small.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass