Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a project where we're receiving data from multiple sources, that needs to be saved into various tables in our database.

Fast.

I've played with various methods, and the fastest I've found so far is using a collection of TableValue parameters, filling them up and periodically sending them to the database via a corresponding collection of stored procedures, inside a transaction.

The results are quite satisfying. However, looking at disk usage (% Idle Time in Perfmon), I can see that the disk is getting periodically 'thrashed' (a 'spike' down to 0% every 13-18 seconds), whilst in between the %Idle time is around 90%. I've tried varying the 'batch' size, but it doesn't have an enormous influence.

  1. Is this 'normal'? Given that the overall average throughput is ok, I could just leave it, but I'm a bit worried about exploding buffers.

  2. Should I be able to get better throughput by (somehow) avoiding the spikes while decreasing the overall idle time

enter image description here

Other details:

  • The database is in Simple recovery mode, and pre-sized to 'big', the log file is not growing
  • The ldf and mdf files are on different disks (the mdf on a striped array, ldf on a 'normal' disk, all I had available)
share|improve this question
4  
Looks like checkpoints occurring. Any particular reasoning behind this focus on disk idle time? The counters described in the Accidental DBA series are more useful. –  Mark Storey-Smith Oct 10 '13 at 9:39
    
@Mark, not reasoning, just lack of experience! –  Benjol Oct 10 '13 at 10:19
1  
Try logging the disk counters in the article I linked and add SQLServer: Buffer Manager: Checkpoint Pages / Sec. Feel free to update the question with the new information. –  Mark Storey-Smith Oct 10 '13 at 10:26
    
That aside, I latched onto the disk idle time because that seemed to correlate with the 'slow downs'. –  Benjol Oct 10 '13 at 11:19
    
@MarkStorey-Smith, I think I've found something. For some reason, it's growing the logfile (even if I'm set to SIMPLE mode). I'm using fairly small transactions, so I was hoping that would avoid too much log file growth. Any thoughts? –  Benjol Oct 10 '13 at 13:23

1 Answer 1

up vote 3 down vote accepted

Indifferent.

SQL Server batches updates - and especially dirty writes with so called checkpoints. This is pretty much normal behavior and only a problem when it turns into a problem (i.e. too large, slowing down the system etc.)

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.