I'm developing a site, which currently gets about 6000+ visits per day, and is hosted on a single W2K SQL Server machine, 768 RAM and 1 1GHz CPU.
It's a fairly standard "directory browsing" and "searching" site.
What I want to find out is the best method to free up SQL CPU processing (cut down on it),
solely on the "browsing" aspect, as this page is accessed the most.
There is one main table (which has/requires full-text indexing) containing the content, which contains, only 40,000 records, which is split into about 50 categories (via a seperate table contains id's etc.).
The site uses stored procedures and the datatypes (fields) within the table are optimised, including the SP's.
There are options within the category / directory pages to allow a visitor to "sort" (order by) the search results by 3 different column headings, (name, price, date etc.).
I'm looking for a way, database wise (as opposed to code), to organise the tables to increase performance.
I'm looking to make browsing the categories faster, ideally, in terms of CPU usage.
Currently, there is the option of splitting the main content table (but still have the main content table for other parts of the site) into individual category tables containing the main content (not ideal, hence why I'm posting this).
Keeping in mind, there are no options for getting other servers.