What SQL activities would result in LDF reads?

Question

What SQL activities would result in LDF reads?

G33kKahuna

SSCommitted

Points: 1726
More actions
December 9, 2009 at 12:31 pm

#217555

I am supporting a heavy read relatively low write SQL connected Enterprise application. Recently, we ran into some slowness. Of the many, I captured file stats (dm_io_virtual_file_stats) end of every day on a 24 hour cycle to understand file usage. I see that the LDF has 190K reads with 19G data read. What's perplexing is what would force such a heavy read from the LDF? I can think of log shipping but not configured
any other input is much appreciated..

Viewing 15 posts - 1 through 15 (of 23 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Initial recovery of the database through roll forward/rollback operations would be one thing.

G33kKahuna SSCommitted Points: 1726 More actions · Answer 2

Thanks for the response Jon, yep missed that ... the interesting part is our devs are cowboys dont have any transaction scoping in our code ... definitely a curve ball.

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

Log backups, full backups, differential backups, transaction rollbacks. Restart recovery. Maybe checkpoint.

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

I asked Paul Randal your question because I know there are potentially other things that would do it, but I don't know for sure what exactly those might be beyond Log Backups, if you have them running, and initial recovery.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

GilaMonster (12/9/2009)
Log backups, full backups, differential backups, transaction rollbacks. Restart recovery. Probably checkpoint.

Interesting that you mention checkpoint here. I considered that but couldn't remember enough about it to know if it would need the log to write dirty buffers or not. I wish I understood those mechanisms better.

G33kKahuna SSCommitted Points: 1726 More actions · Answer 6

@Gail: I considered log backup but the monkey wrench is that we have 1 nightly full back up and hourly txn file backup. My DMV would have reported low # of reads; 190K seems a lot for this type of activity. Checkpoint ... not so sure, its just a dirty page push ...

@Jon: thanks for forwarding it along ...

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

Jonathan Kehayias (12/9/2009)
GilaMonster (12/9/2009)
Log backups, full backups, differential backups, transaction rollbacks. Restart recovery. Probably checkpoint.
Interesting that you mention checkpoint here. I considered that but couldn't remember enough about it to know if it would need the log to write dirty buffers or not. I wish I understood those mechanisms better.

I'm not sure if it does. Might do, might not. Should probably have said "Maybe Checkpoint"

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

Here's the list off the top of my head of what will cause log reads:

1) transaction rollback (whether you explicitly start a transaction or do single-statement implicit transactions)

2) crash recovery

3) transaction log backup

4) any kind of data backup (full/differential database/filegroup/file)

5) transactional replication log reader agent

6) change data capture (2008)

7) database mirroring, when there's a SEND queue

8) restoring a log backup using WITH STANDBY

9) DBCC LOG or fn_dblog 🙂

10) a checkpoint, when in the SIMPLE recovery model only, when it checks to see if active VLFs can be marked inactive

Hope this helps.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

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

G33kKahuna (12/9/2009)
@Gail: I considered log backup but the monkey wrench is that we have 1 nightly full back up and hourly txn file backup. My DMV would have reported low # of reads; 190K seems a lot for this type of activity.

How big is the log? Log backup has to read the active portion of the log. That's 24 times in a day. Add that to all the other operations I can see those read numbers getting reached.

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

G33kKahuna SSCommitted Points: 1726 More actions · Answer 10

Thanks Paul. I am confused about #1; doesn't it need an explicit rollback statement to be executed?

@Gail: The log size is about 5G on disk. I apologize, should have given the numbers. The reported number are as below

-------------------------------------------------------
|# of reads | MBs Read | # of writes | MBs Written |
-------------------------------------------------------
| 356,786 | 33,095.23 | 347,893 | 9,365.66 | Data file
-------------------------------------------------------
| 190,691 | 19,002.90 | 703059 | 12,520.35 | Log File
-------------------------------------------------------

Paul Randal One Orange Chip Points: 29544 More actions · Answer 11

Not necessarily - if something goes wrong the transaction will rollback automatically

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

G33kKahuna SSCommitted Points: 1726 More actions · Answer 12

Thanks Paul; interesting ... is there a way to calibrate the # of rollbacks?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 13

I don't understand your question - rollbacks only occur when something goes wrong or you explicitly rollback a transaction - how could you calibrate them?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

G33kKahuna SSCommitted Points: 1726 More actions · Answer 14

I can get the number of explicit rollbacks issued and the #bytes affected/ explicit rollback from sql traces. Is there a way to capture and account for implict rollbacks? same SQL trace approach?