Recommended Hardware to Support Full Text Search in SQL 2005

Question

What is the recommended hardware specifications to support full text search in SQL 2005?

We are looking to go in production and I want to make sure the current hardware can accommodate it.

To provide the context,

We are expecting 2 - 3 million records to be searched by small subset of our users(probably in 100s).The user has the option of doing a keyword search to look into 10-15 columns in a particular view/table.

The Full Text Search Implementation in the development environment seems to be promising but I would like to understand the hardware requirements before taking it up with the team for approval.

Answer 1

This certainly needs more context:

• how big are your data sets?
• how many users will be searching?
• how fast does it need to be?

The easy answer is: as much RAM, CPU, and fast storage as you can afford.

Answer 2

The full text search itself is not computationally intensive. It's the full text indexing that is, but it can be run overnight.

Answer 3

I have a similar setup, searching tables with millions of rows. The FTS data size on disk, and the search itself to find the keys that match are not a problem at all. I haven't noticed that updating the index is causing any problems, and I have it updating constantly, not at a set time at night.

The performance issue for me has been the other data that I select based on those keys. If a user searches for something very common, and you get a few thousand (or hundred thousand) matches, and then you do another query to grab the data you will actually return from the query, that's the part you will want to plan for and optimize.

I would recommend at least a quad-core processor, 16Gb of RAM, a separate disk array for the indexes, and don't run anything else on the Sql Server machine other than the database. But that's just based on your data size, I wouldn't change the recommendation based on using FTS. FTS should actually make it so you need less processing power, since it's much more efficient at finding data.

Answer 4

If your joining data from multiple tables there is many solutions other that Hardware. You can use an Indexed View, OLAP