Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
What recovery model was in use for the database? –  Nick Kavadias Sep 17 '09 at 7:41
    
It's fully logged. –  SuperCoolMoss Sep 17 '09 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 '09 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 '09 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 '09 at 3:17

4 Answers 4

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

share|improve this answer

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? :-)

share|improve this answer
    
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 '09 at 8:16

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)

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.