One of the things we have done here is to make some larger queries into stored procedures. This helped some.
Another thing is if while viewing the debug of the site, grab the queries that you feel take long to process. Copy that query into SQL Analyzer. Under the main menu, choose Query - Index Tuning Wizard. Follow the wizard and it will recommend some indexes and the percentages that the index will improve performance. (The above is under the assumption that you are running SQL 2000). This is also available in SQL 7 but I don't know the steps for that. I know that this may be a long, tedious process but it has helped us tremendously. We also have budget issues so we have had to tune as much code and as many queries as we can. Thanks, Ruben -----Original Message----- From: Robert Glover [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 1: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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2280 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
