Managing iFTS - SQL Server 2008

SQL Server 2008 provides new features to make configuration and administration of iFTS easier than ever. So far in this chapter, we’ve covered several of the new features in iFTS, including the tighter integration with the SQL query engine, the new T-SQL keywords for full-text catalog and fulltext index creation and management, and the flexible new stopwords and stoplist management. In addition to the stored procedures and catalog views and functions, SQL Server 2008 provides some iFTS-specific data management functions and views.

While there are several stored procedures available for backward compatibility with previous versions of SQL Server, most of them are deprecated and should not be used for future development. Instead, you’ll want to use the equivalent T-SQL statements, catalog views, and dynamic management views. These deprecated features are well-documented in SQL Server Books Online, so we’ll focus instead on the new functionality in SQL Server 2008.

SQL Server 2008 introduces two new stored procedures to aid in iFTS management:

  • The sys.sp_fulltext_load_thesaurus_file procedure allows you to reload thesaurus files without needing to stop and restart services.
  • The sys.sp_fulltext_resetfdhostaccount procedure updates the Windows username and password that SQL Server uses to start the new filter daemon service.

A big issue for developers who used FTS in prior versions of SQL Server was the lack of transparency. Basically, everything that FTS did was well hidden from view, and developers and administrators had to troubleshoot FTS issues in the dark. SQL Server 2008 introduces some new catalog views and dynamic management functions that make iFTS more transparent (good news for SQL Server iFTS developers and administrators):

  • If you’re experiencing iFTS query performance issues, the sys.fulltext_index_fragments catalog view might provide some insight. With this catalog view, SQL Server reports full-text index fragments and their status. You can use the information in this catalog view to decide if it’s time to reorganize your full-text index.
  • The sys.fulltext_stoplists and sys.fulltext_stopwords catalog views let you see the userdefined stopwords and stoplists defined in the current database. The information returned by these catalog views is useful for troubleshooting issues with certain words being ignored (or not being ignored) in full-text queries.
  • The sys.fulltext_system_stopwords catalog view returns a row for every stopword in the system stoplist, which is useful information if you want to use the system stoplist as the basis for your own stoplists.
  • The sys.dm_fts_parser function accepts a full-text query string, an LCID, a stoplist ID, and an accent-sensitivity setting. It returns the results produced by the word-breaker and stemmer for any given full-text query. This information is very useful if you need to troubleshoot, or if you just want to better understand exactly how the word-breaker and stemmer affect your queries.
  • The following is a simple demonstration of the sys.dm_fts_parser function, which produces the results.

    SELECT *
    FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, go)', 1033, NULL, 0);

    Results of word-breaking and stemming a full-text query for “go”

    Results of word-breaking and stemming a full-text query for “go”

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics