Is the forum a commercial offering? I'm not an expert, and I don't play one on TV, but I start by looking for a long-running query, especially a transactional one. Are the queries running from CF, or are they stored queries?
I would also check the SQL server settings to see home much SQL server is allowed to monopolize the CPU (Is it a multiprocessor machine?). A few other more obvious places to start - is the size of the Windows page file consistent with available RAM? Is the page file set to the same size for min and max size? Could the page file be fragmented? What kind of a disk subsystem is in use? If it is RAID5 is it recovering or showing any errors? If it is IDE, is DMA enabled? Some things to check if you haven't already. -Nate -----Original Message----- From: Robert Glover [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 12:38 PM To: SQL Subject: MS SQL 2000 / Win Server 2003 Problem Hi everybody... I hope this is the best place to post this. I'm running out of options because nobody seems to know what's causing this. I have a webserver... it's running Windows Server 2003 with all updates except SP2. It's running MS SQL Server 2000 with SP3a. It's also running CF MX 7. This server is running a series of personal websites and message forums, on forum software that I wrote myself about two years ago. There are 6-8 forums running off of the same codebase and database (the URL being the determining factor as to what content is selected). Ever since the forums started getting a lot of use, the server itself has slowed to a crawl. It's a P4-3.0 GHz machine with 1 gig of RAM. I'm 99.9% sure that the slowness problem is being caused my SQL Server. When I run PerfMon, I watch CPU utilization, Avg Disk Queue Length, and a few others. The one to note is Avg Disk Queue Length. Whenever this item spikes and maxes out, the system stops responding until the item drops off again. It seems unrelated to CPU usage. These spikes last anywhere from 5-30 seconds, and again while it's spiked, nothing else on the server can take place. In some instances, a remote session with Terminal Services will freeze up; otherwise it just gets really sluggish. These spikes go away if I pause or stop the SQL Server service.. but that's kinda pointless since the sites won't run without it. There's only one database still in use on SQL Server now -- I've moved the rest to mySQL, on the same box. The one left is the big one -- the one the forums depend on. BUT, my users have said that the system feels less sluggish now. I've noticed it's not as bad either, but it still happens. I would go ahead and move this last database, but the migration tools for SQL Server to mySQL are poor at best, at least the ones I've found. It takes several hours to export the data, and then several more to manually modify the SQL code it exports because of bugs in the export script that I've been unable to correct. So I'm kinda stuck and unsure what to do next... Any ideas? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how to get a fax number that sends and receives faxes using your current email address http://www.houseoffusion.com/banners/view.cfm?bannerid=64 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2271 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
