Are the tables large in terms of Megabytes on disk? Do you have indexes on every field? People starting with databases sometimes tend to over-index a table resulting in a performance penalty.
Can you tell what parts of your forum application are running when the machine takes a dive? Are you using transactions either in SQL or CF that could put things on hold while they complete? Are you forgetting to end a transaction anywhere? Are any timeouts set to high? Maybe there are places you could ease-off on write-locking if you are just reading the records? You should definitely look into using stored procedures for your most intensive or frequent queries. There are some settings in CF administrator you can use to find pages that are taking a long time to execute. Check out those settings then review your CF log files. When you monitor the machine is the memory actually getting maxed out? If not you might try giving SQL server less memory (256 Megs?) so that JAVA/CF can have more. JAVA is kind of a pig if I recall Correctly. Somebody else probably hit the nail on the head when they mentioned running both CF and SQL server on the same box. It's a little bit like playing chess against yourself(!?) Anyone else please feel free to correct, comment. -Nate -----Original Message----- From: Robert Glover [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 12:15 PM To: SQL Subject: Re: MS SQL 2000 / Win Server 2003 Problem Thanks to everybody who has responded so far. To answer some of the questions... The forum is my own creation... the only commercial Cold Fusion-based forum I could find was FuseTalk and they wanted $4000 for it. That's when I chose to write my own. Over the past year, I've optimized it quite a bit. I'm caching every single query that can possibly be cached, and I'm still working on reducing the number of queries used. I've even gone so far as to comment out certain features so that the queries won't run, to see if it helps. So far, it hasn't. The disk system is not RAID. This is basically a single-CPU workstation acting as a server. There are two drives... the first drive is a 60 gig, the second is a 160. Both are UDMA and running on the first controller. A CD-ROM drive is on the second controller. The database and code are all on the second drive, which is not partitioned. Page file... it's set for the first drive and is set to 1524-3000 meg. Physical RAM is 1 gig. From watching the Task Manager's performance meter, it appears that it doesn't spend much time swapping. CF settings. I have the number of simultaneous tasks set to 3. It was at 4 yesterday and I decided to drop it to see if that helped. I want to say it helped slightly, but only slightly. SQL settings. I'll admit I'm NOT very good at administering a SQL Server database; I have a lot to learn, but haven't found anyone or anywhere to learn from. I did turn down the max memory it can take to 750 meg from "however much it wants," which I think was the default setting. CPU usage isn't so much a big deal, as the machine rarely pegs out the CPU. Log files... the transaction log for this database does grow. It was 9 gig yesterday and a ran a script to shrink it down to nothing, and now today it's back up to 1.2 meg. In the past, it would grow very quickly, but lately it's slowed down. Indexing... I have to assume that SQL Server does this automatically? I've never found a way to make it index a table. I used to think if I could make it reindex more often, it'd help. Java... the version on the server is the newest available. All of my queries run from Cold Fusion; I don't use anything stored. I had checked for the number of allowed connections to the database, and it's set to unlimited. I dropped it to 5 and I started getting errors from the site saying connection refused. Upped it to 8 and still got the errors (though not as many). I finally gave up and set it back to unlimited until I could find a way to make CF not error out on that. Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Now there’s a better way to fax. eFax makes it possible to use your existing email account to send and receive faxes. Try eFax free. http://www.houseoffusion.com/banners/view.cfm?bannerid=63 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2278 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
