SQL Server 2008 FTS problem, best alternative

Question

I develop news search engine with C# and SQL Server 2008
i using SQL Full text search for search and it's ok until some week ago.

currently i have more than 100 million record, that daily added about 1 million article.
but i see sometimes when user search, one of 8 CPU become 100% and take some minutes. after repopulate catalog, it's ok for some days, What's really problem ?

Note: I set automatic track changes and schedule it for incremental population when CPU become Idle

Also I need to know what's best alternative ?

• Lucene.Net

• Xapian

• Sphinx

• Solar

• Or any other best solution ?

Could you please help me ?

Answer 1

I recommend you go over this white paper: SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned, which explains some techniques used by the SQL CAT (Customer Advisory Team) to manage a 420Gb database with a FT catalog of 10Gb.

Answer 2

Also, if you are adding 1 million articles per day, SQL needs to merge the indexes, which would cause a spike for a while until the indexes are merged.

This sounds like normal behaviour and you will experience this type of resource contention with the scale you are pushing with any alternative full-text indexing engine.