Final update on this issue for future generations.... Our SQL expert helped pinpoint some query issues and updated a few indexes for us... resolving the way my database driver was configured helped clear up a lot of problems by reducing the # of reads we were doing. HOWEVER, the server slowdowns still occurred for the same duration but they were less noticable because more of the queries were runnign without reading from disk during these times.
There are, apparently, two solutions to performance issues caused by checkpointing. #1 - add more disks to your drive array. Apparently, a 6 disk array is less likely to have these kind of read/write blockages than a 3 disk array. This isn't an option for us right now though. #2 - force checkpointing more often. This was our solution. The system was checkpointing every 10-20 minutes depending on activity, and we are forcing it now to do checkpoints every minute now through a SQL Agent task. The spikes are still there, but they won't really affect our users anymore. Apparently, there is option #3 - which is "Upgrade to SQL Server 2008" which in and of itself would improve checkpointing performance, but also apparently SQL 2000 can only use 1.5gb of RAM, so we're underutilizing our server in terms of physical memory (it has 4gb total) Rick On Wed, Feb 11, 2009 at 9:13 AM, Rick Root <rick.r...@webworksllc.com> wrote: > Well, using perfmon I was able to see significant amounts of disk read > and write on the SQL Box during these times... but I still didn't > really know what that meant. > > We've contracted a 4 hour block of time from a sql server tuning > expoert and in conversations with him, just based on that information > about, he suggested that we might have a problem with checkpoints, and > it certainly seems to be that. I finally figured out how to enable > the sql server counters in perfmon (we're running 32bit sql server > 2000 on 64 bit windows server 2003 so the counters didn't appear in > the 64 bit perfmon)... it does look like a checkpoint problem. I'll > let the expert help us figure out the best way to resolve it but the > solution may involve changing the server's recovery interval. > > Turns out that the physical hardware was ordered based on > recommendations from seefusion. I'm surprised they'd recommend having > the OS and Data on the same physical set of disks (maybe they didn't > and the hosting company just misinterpreted their recommendations) but > at least we have the transaction logs on their own set of disks. > > Anyway, as I get a complete answer from our consultant I'll report > back here in case anyone googles similar issues or if anyone is just > curious. > > Rick > -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319231 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
