How to even out spikes in IO | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to even out spikes in IO

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 />
go to SQL 2005, this matter was addressed,
otherwise configure the storage to handle these spikes,
i have sent this into [email protected]
but i suspect they do not want to change the code