- A Full text catalog containing a single full text index
- The index is of a single table containing a uniqueidentifier column and five varchar(max) columns, all indexed
- About 300,000 rows in the table
If I create the index and immediately run a query like this:
SELECT COUNT(*) FROM FREETEXTTABLE(tablename, columnname, 'term1 term2 term3', 5000)
I get the expected result: 5000 (there are actually about 185,000 matching rows if that same query is run without the top_n_by_rank parameter).
But then if I run the same query repeatedly over the next 10-15 minutes, the returned value decreases...4616, 3505...ending up around 507. It's like the full text index rots away before my eyes.
The database is purely read-only. We do run stored procedures against it that construct temporary tables, but nothing puts new data into this particular table. Doesn't matter whether the full-text index is set to manual or automatic repopulation. The timestamps on the index files on disk don't change. Nothing odd shows up in the Windows event logs or SQL Server logs.
I'm stumped. Any ideas?
http://www.sql-server-performance.com/full_text_search.asp
http://www.sql-server-performance.com/tb_search_optimization.asp
没有评论:
发表评论