SQL Server Performance

How to even out spikes in IO

Discussion in 'Performance Tuning for Hardware Configurations' started by sankha, Mar 6, 2006.

  1. sankha New Member

    I am testing an OLTP workload on a system the specs of which are-<br />1)SQL Server 2000 EE SP4<br />2)Windows Server 2003 EE SP1<br />3)Storage Type - iSCSI on NAS.<br />4)Raid - Raid 5.<br />5)Number of physical spindles - 15<br />6)Data/Log breakup - 10/5<br />7)Server CPU - Dell 1850 (Dual processor 3.0 GHz Intel Xeon)<br /><img src='/community/emoticons/emotion-11.gif' alt='8)' />Server Memory - 4 GB (with 3GB switch thrown on)<br />9)Gigabit ethernet<br /><br />I know it sucks to have log on R5 but that is the way the test requirements are.<br />I am using single files for database and the log and am testing in a non-clustered environment. <br />My problem is this. I see sharp spikes in the file write operations on the data file during which the file write operations go as high as 2000/sec. But between two spikes there is a trickle of writes happening at a rate of 60-100/sec. due to these spikes I get very high disk latencies (&gt;10 msec) and my tests fail.<br />I figured this had something to do with how SQL server was checkpointing. I increased the number of checkpoints so that SQL was doing checkpoints every 30 seconds (to spread out the accumulated writes). But still the spikes were as high as 1200/sec. I plotted my userload and found that my userload is bursty and that was what was causing the spikes when i let SQL decide when to checkpoint (depending on the default recovery interval). I then decided to try changing the recovery interval to make SQL checkpoint more frequently. I changed the recovery interval to 1 minute. But still SQL was checkpointing with about 1200 sized peaks.<br />I have run out of options. I would be grateful if someone could point me on to how I can spread my writes so that i dont do more than 500 writes/sec.<br /><br />
  2. joechang New Member

    go to SQL 2005, this matter was addressed,
    otherwise configure the storage to handle these spikes,
    i have sent this into SQLWish@microsoft.com
    but i suspect they do not want to change the code

Share This Page