Microsoft® SQL Server 2008 R2 Unleashed

Full-Text Search Troubleshooting

The first question you should ask yourself when you have a problem with SQL Full-Text Search is this: “Is the problem with searching or with indexing?” To help you make this determination, Microsoft has included three DMVs in SQL Server 2008:

• sys.dm_fts_index_keywords

• sys.dm_fts_index_keywords_by_document

• sys.dm_fts_parser

The first two DMVs displays the contents of your full-text index. The first DMV returns the following columns:

• Keyword—Each keyword in varbinary form.

• Display_term—The keyword as indexed; all the accents are removed from the word.

• Column_ID—The column ID where the word exists.

• Document_Count—The number of times the word exists in that column.

The second DMV breaks down the keywords ...