> I'm caching every single query that can possibly be cached, > and I'm still working on reducing the number of queries used.
That's a good thing, but keep in mind that you're trading less memory usage in SQL Server for more memory usage in ColdFusion. At some point, you'll hit the wall. In fact, you may be there already, since you say it isn't helping. > The disk system is not RAID. This is basically a single-CPU > workstation acting as a server. RAID isn't absolutely necessary, but it will give you better performance. I'll reiterate what someone else already said, which is that running your web, ColdFusion, and SQL servers all on the same machine is a pretty bad idea. Personally, aside from the indexing advice that Chris gave you earlier, this is the first thing I'd change. You don't have to go buy 8-way Itanium servers with 32GB of RAM, but SQL Server will be happier with more RAM, even if you have to run a slightly slower CPU to get the price right. Web servers, database servers, and application servers (including CF) are all harder on your RAM than on your CPU. > Page file... it's set for the first drive and is set to 1524-3000 meg. Reset that page file so it doesn't change size. It needs to be at least 1024MB, since that's how much RAM you have, but it doesn't really matter so long as Windows isn't constantly trying to resize the sucker. > 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. It only helped slightly because it only reduced the resource consumption slightly. My gut tells me that ColdFusion server isn't going to be the culprit here. > 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. Your transaction log grows every time you have an INSERT or an UPDATE query. A 9GB transaction log tells me that you either have LOTS of data inserts and updates, or you're not backing up your database and transaction log. The transaction log will automatically truncate itself on every full backup or transaction log backup, but the file size will remain the same so the server doesn't have to grow the file as much. Use the maintenance plan wizard to set up a backup schedule. Personally, I do NOT suggest you go to the Simple recovery model as Chris suggested. The Simple recovery model does not maintain a transaction log AT ALL, which means that you have no opportunity to back up the transaction log. This guarantees that in event of a crash, you will lose all data changes made since the last full backup. > Indexing... I have to assume that SQL Server does this automatically? No. And even if it did, you wouldn't want it to. I've yet to see automated indexes that were any good. > All of my queries run from Cold Fusion; I don't use anything stored. This isn't as big a deal as some people make it seem. Stored procedures are more useful for security purposes than anything else. > I had checked for the number of allowed connections to the database Keep it set to unlimited, and pool your connections as Chris suggested. The top two suspects other than the resource issues are your database design and your CFML code itself. Be sure you have your database normalized and look for opportunities to index. Chris mentioned "clustered" indexes; a clustered index is one in which the rows are organized physically in the order determined by the index. Therefore, you can only ever have one clustered index. I have found that as the traffic picks up on busy databases, the clustered indexes actually hurt performance. I use these rules for indexes: 1. Declare PRIMARY KEY constraints. 2. Declare unique indexes on alternate keys. 3. Declare clustered indexes tables that will have a large number of rows and where you can clearly define the columns that will have a wide range of queries. Avoid having a clustered index on an IDENTITY or other auto-increment column, as everyone then contends for the same data page when multiple users are trying to add data to the table. 4. Declare non-clustered indexes on tables where you have non-key columns that will be part of ad-hoc joins. o The primary key of a table is automatically indexed, and is usually the preferred column to use when referring to a row. o The alternate key is a unique index that usually is the preferred column for human interaction and selection. -- Eric ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2279 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
