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- 1)SQL Server 2000 EE SP4 2)Windows Server 2003 EE SP1 3)Storage Type – iSCSI on NAS. 4)Raid – Raid 5. 5)Number of physical spindles – 15 6)Data/Log breakup – 10/5 7)Server CPU – Dell 1850 (Dual processor 3.0 GHz Intel Xeon) <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />Server Memory – 4 GB (with 3GB switch thrown on) 9)Gigabit ethernet I know it sucks to have log on R5 but that is the way the test requirements are. I am using single files for database and the log and am testing in a non-clustered environment. 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 (>10 msec) and my tests fail. 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. 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.

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

]]>