4

I was using perfmon to monitor writes per second on a LUN containing a single SQL2005 database data file. I was under the assumption that database files are only written to during checkpoints or by the lazy writer process, is this assumption correct? as something seemed to be writing to the LUN when the checkpoint and lazy writer processes were inactive.

[EDIT]

The writes seem to happen every 3 seconds or so and is 510 bytes long? I have my suspicions that it's not SQL writing to the LUN but some OS process.

[EDIT]

This write is actually hitting all my LUNs simultaneously - so can't be SQL.

Improve this question
5
  • What recovery model was in use for the database?
    – Nick Kavadias
    Sep 17, 2009 at 7:41
  • It's fully logged.
    – SuperCoolMoss
    Sep 17, 2009 at 8:17
  • I'm wondering if it is the lazy writer process but Perfmon doesn't have enough precision to show it.
    – SuperCoolMoss
    Sep 17, 2009 at 10:51
  • 1
    Recovery model doesn't affect when the DB file gets written to. It affects how long log records are retained, how much gets logged in bulk-operations and what operations truncate the log.
    – GilaMonster
    Sep 17, 2009 at 11:43
  • you wouldn't think so, but it does. Saw a session where David Lean looked at how read/writes to disk performed with different recovery models. Will ask him if he can post his slides
    – Nick Kavadias
    Sep 19, 2009 at 3:17

4 Answers 4

Reset to default
2

My guess is it's the OS updating the atime on the DB files.

Improve this answer
1

Are you sure this LUN only contain data files? Where are your transaction log files located? Perhaps a log flush was occurring when writing to the transaction log? This usually happens within milliseconds of a write being committed (depending on load of course).

What is your recovery interval set to? This has a bearing on frequency of checkpoints.

[EDIT]

Perhaps you could use one of the sysinternals tools to diagnose what exactly is writing to your disk? Maybe perfmon is lying to you? :-)

Improve this answer
1
  • The database file is isolated on this one LUN, the transaction log is on another. I also measured checkpoint pages / sec on Perfmon and the writes to the LUN don't correlate.
    – SuperCoolMoss
    Sep 17, 2009 at 8:16
1

http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/

The lazy writer can be writing quite a lot if SQL is under memory pressure. If both checkpoint pages/sec and lazy writer pages/sec are 0, there should be no writing occuring. Definitely see if one of the sysinternals tools can diagnose what process is writing.

Edit: There's also something known as the eager writer. From what I recall it has to do with bulk operations (bcp, BULK INSERT)

Improve this answer
1

Do you have any paging files setup on this LUN or and backup software which makes use of this LUN for shadow copying? We had a case where the backup software's would come in and make use of LUNs for shadow copying filling up the drive, so we had to explicitly exclude certain LUN's to prevent this scenario. You might wish to check.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .