MS SQL - Professional Assistance required concerning Capacity.

Hi there,

We are currently developing a E-Com research site where subscribed members can conduct market research. The web site is developed and ready to go, however we are undecided what is required on the Database side and could do with some of your expert help.

Users will be conducting research on our MS SQL database. The database holds every product sale made by a select number of consumer good companies. The database holds 12 million records at any one time, however overall the database is very static and is not updated online. We intend to only store 1 months data, therefore on a weekly basis we delete old & add new records.

We are offering three categories of searches on the DB.
1. By Seller
2. By Category (i.e. Hair Care, Laundry etc)
3. By Sale.

The first two searches are not performance intensive as they are compiled and stored in the database - therefore no need for calculation online. Our main concern is the 3rd report. This report is where we allow the user to search for a specified search string. Therefore in the main table that holds all the sales, we have a full-text index for the field "Product Description".

What we are unclear on is the Capacity requirements regarding providing such functionality. I.e What server do we require?

We have researched many articles and have absorbed all the information regarding multiple disk controllers, lots of memory etc.. etc.. However we still have not been able to obtain an idea on what Server would serve our purposes. For example: would the following server do the job?

2 x 2.4Ghz Hyper-threading Xeon CPUs with 512Kb Cache and 400MHz System Bus
2GB Samsung RAMBUS 800-45 Memory (RIMMS / RDRAM)

A lot is determined by the usage, i.e. how many queries are executed per minute etc. At present we do not have a clear idea on the number of users. However we are not truely concerned about this as the 3rd report needs to execute and return within a short period of time. That is our main concern.

Therefore our question is as follows: If you were going to host a database that consisted of 12 million records & full-text indexing which had to be queried in a few seconds, what would you purchase?

We ask you to consider that funds are extremely tight and although the new latest server may do the job, it is most likely out of our budget. We are looking for the most cost effective solution but still statisfying the purpose.

Kind regards
AngusPerformance tuning isn't an exact science. Especially when there isn't a good test base where you can make and test your proposed changes, the whole thing is subject to a lot of opinion rather than fact.

In my opinion, your search will almost certainly be disk bound rather than CPU bound. Fast disk is important, but redundancy gets expensive fast... I'd suggest all of the RAM you can and the fastest drive subsystem that you can afford.

-PatP