SQL Server 2008 Full Text Search Best Practices from the SQL CAT Team

SQLCAT Blogs - Best Practices for Integrated Full Text Search (iFTS) in SQL 2008

“This blog is a result of lessons learned while working with the new Integrated Full Text Search in SQL Server 2008. It is not intended to be an in-depth tutorial on how to implement it. Instead, it will concentrate on best practices. The basics of how the new full text search feature works is found in Books On Line.

If you would like a good introductory tutorial, start with SQL 2008 Books Online, then read this whitepaper: http://msdn.microsoft.com/en-us/library/cc721269(SQL.100).aspx. It is one of the best whitepapers I’ve seen recently on a product feature.

Also for more introductory lessons here is an excellent link: http://sqlblogcasts.com/blogs/simons/archive/2008/02/19/SQL-Server-2008---iFTS-Introduction.aspx

Simon Sabin also has a nice series of posts on the new iFTS features: http://www.sqlskills.com/blogs/simon/2008/02/20/SQLServer2008IFTSNewFeatures.aspx

The new iFTS feature is not rocket science, but you should expect to spend some time learning about the new vocabulary, like Fragments, Master Merge, etc. You will also spend some time learning the new DMVs (like sys.dm_fts_index_keywords and sys.dm_fts_index_keywords_by_document) .

Performance

Here is what we noticed about the performance of iFTS in a production environment.

…

iFilters

…

Limitations

…

Memory Settings

…

Other notes about FDHost

…

Miscellaneous notes:

…

Upgrade

…”

With some of the recent discussions of SQL Server FTS going on, even though I don’t use SQL’s FTS, I thought it made sense to note this posting. While not an uber-deep article, there are a number of interesting points that might help if you’re using, or planning on using, FTS.