Is My fulltext Index Being Used

Question

Hello,

Have a number of fulltext indexes on a SQL 2008 R2 instance. I know one of these indexes are in use, but unsure about the others. Is there any programatic way of determining which of these indexes are in use?

I had thought the dmv sys.dm.db_index_usage_stats might expose this information - but it doesn't appear to. Welcome any thoughts. Thanks.

Andy

Answer 1

Hi Andy,

SQL Server Engine will decide whether to use full-text index or not based on searching query. I would like to suggest you to run the full-text search related query manually and check the execution plan to see if the index has been used.<o:p></o:p>

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Peja,

Thanks for your suggestion. Unfortunately I probably didn't make myself clear enough. I believe two of my three FTS indexes are no longer used and I was looking for a way to confirm this. I was hoping that SQL Server held some statistics or meta data, which would confirm these FTS indexes have not been accessed in the last 'n' days (weeks/months...)

I have tried to look through our stored procedure logic to try to determine if these indexes are used in the code, but because we use a lot of dynamic SQL - its not the easiest of things to look for.

Any thoughts or ideas are much appreciated. Thanks.

Andy

Answer 3

@Andy

You can query the status using FULLTEXTCATALOGPROPERTY (see here: http://technet.microsoft.com/en-us/library/ms190370.aspx).

SELECT 
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount'), 
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus'), 
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'), 
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus'), 
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') 
FROM sys.fulltext_catalogs AS cat 

You might also like to use SQL Profiler to monitor what commands SQL Server Management Studio issues when you bring up the properties dialog for the catalog.  The dialog includes an indicatin of population status and all the information shown is queried using T-SQL.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.