To index a SQL table: >From SQL Enterprise Manager: Right click the table in SQL, choose all tasks, choose 'Manage Indexes'
You will want to create a 'Clustered Index' of the most often used field (say, PostID, or UserID, etc), then create secondary indexed (non-clustered) on any field that is used in a SQL query. Name them whatever you want, I usually use CL_<field name>, and NC_<field name>. If you don't have indexes, this will help TONS! This should definitely be the very first thing you do, you will see an immediate improvement if indexes do not exist. Basically, if you have a fiend in your SQL 'WHERE' clause, make an index on it. For transaction logs: If you do regular backups of the DB itself, I do this (which basically turns transactions logs off): From enterprise manager, right click on the Database itself, choose properties. Go to the 'options' tab, and change the 'Recovery model' to 'Simple'. Now you wont have the gigantic transaction logs! =) Let me know if I can help more! Chris -----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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2276 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=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
