Como posso saber se um índice de texto completo está sendo usado?

Pelo que sei, não há DMV ou similar para mostrar o uso do índice de texto completo. Eu me perguntei se sys.dm_db_index_usage_stats poderia ser usado (e encontrei um resultado que sugeria que sim), mas não acho que isso funcione (mais sobre isso abaixo).

Um método que deve funcionar é usar o Query Store. Se estiver habilitado (e capturando TODAS as consultas), você poderá pesquisar planos armazenados que usam funções de pesquisa de texto completo e, a partir disso, localizar as tabelas/índices relevantes.

Certifique-se de que o Query Store esteja habilitado e o modo de captura esteja definido como ALL:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON
    (QUERY_CAPTURE_MODE = ALL)

Como os planos no armazenamento de consultas são armazenados como texto, você pode consultar qualquer índice de texto completo que atinja. Algo assim funcionará:

SELECT qsqt.query_sql_text, qsq.object_id, CAST(qsp.query_plan AS XML)  
FROM sys.query_store_query AS qsq      
JOIN sys.query_store_query_text AS qsqt 
    ON qsqt.query_text_id = qsq.query_text_id     
JOIN sys.query_store_plan AS qsp 
    ON qsp.query_id = qsq.query_id   
WHERE qsp.query_plan LIKE '%fulltextmatch%'

(Adaptado de: https://www.scarydba.com/2020/05/11/find-indexes-used-in-query-store/ )

Você também pode aproveitar outros aspectos do armazenamento de consultas, como a sobrecarga de desempenho das consultas.

É claro que existem desvantagens - se o Query Store ainda não estiver ativado, você terá que esperar o tempo suficiente para que as consultas apareçam lá e em um ambiente movimentado (especialmente com o modo de captura definido como ALL), você precisa ter certeza você não perde dados devido a liberações de cache de consulta. Provavelmente o mais importante é que você precisa ter certeza de que o texto completo não seja usado ocasionalmente para algo muito importante (aquele relatório executado anualmente e cheio de pesquisas de texto completo que dependem de índices que você descartou há 11 meses). Talvez você possa exportar periodicamente os resultados do armazenamento de consultas ao longo do tempo e analisá-los tarde (mas isso está além do escopo desta questão).

Você não pode provar o negativo e ter 100% de certeza de que algo nunca será usado. O quão certo você pode ter disso depende muito da complexidade do seu ambiente e de quão bem você o conhece (e do seu apetite por risco). A melhor maneira de ter certeza é perguntar às pessoas da sua organização que usam seu banco de dados. Isso pode não ser possível em todos os casos, mas desenvolvedores/redatores de relatórios, etc. podem ajudar - uma solução não técnica, mas pode ser mais eficaz.

Se você não se importa com conselhos não solicitados, por que deseja remover esses índices? O velho ditado “se não está quebrado, não conserte” pode ter valor. Que benefício você busca obter ao removê-los, versus o possível risco se eles forem necessários, mas removidos. Entendo o desejo de 'arrumar' um sistema herdado, mas sugiro que existem maneiras mais fáceis, seguras e potencialmente mais valiosas de fazer isso. É claro que não conheço suas circunstâncias, mas sugiro que você faça isso apenas se achar que esses índices estão realmente causando problemas.

Usando sys.dm_db_index_usage_stats

Uma postagem antiga do stackoverflow sugeriu que isso poderia ser usado para encontrar as estatísticas de uso com sys.dm_db_index_usage_stats, juntando-se a sys.fulltext_catalogs e sys.fulltext_indexes. Qualquer consulta nessas tabelas que atinja os índices fará com que esses contadores aumentem, e não apenas pesquisas de texto completo.

Testei isso no AdventureWorks2019 com o catálogo Full-Text e índices já configurados como padrão.

Usando a consulta dm_db_index_usage_stats:

SELECT FTSC.name AS CatalogName, 
       T.name AS TableName, 
       C.name AS ColumnName, 
       FTSI.unique_index_id as FTindexID,
       FTSIU.index_id as DMVIndexID,
       FTSIU.user_scans, 
       FTSIU.user_seeks,
       FTSIU.last_user_scan
FROM sys.fulltext_catalogs AS FTSC 
JOIN sys.fulltext_indexes AS FTSI ON FTSC.fulltext_catalog_id = FTSI.fulltext_catalog_id
JOIN sys.dm_db_index_usage_stats AS FTSIU ON FTSI.object_id = FTSIU.object_id AND FTSI.unique_index_id = FTSIU.index_id
JOIN sys.tables AS T ON FTSI.object_id = T.object_id
JOIN sys.columns AS C ON FTSI.object_id = C.object_id 
    AND FTSI.unique_index_id = C.column_id
WHERE FTSIU.database_id = DB_ID()

Resultados do início do teste dm_db_index_usage_stats:

CatalogName TableName   ColumnName  FTindexID   DMVIndexID  user_scans  user_seeks  last_user_scan
AW2016FullTextCatalog   JobCandidate    JobCandidateID  1   1   0   1   NULL
AW2016FullTextCatalog   ProductReview   ProductReviewID 1   1   0   1   NULL

Execute uma consulta de texto completo:

SELECT *  
FROM Production.ProductReview  
WHERE FREETEXT (Comments, 'Maybe do')  
GO

A consulta dm_db_index_usage_stats resulta em aumento de user_scans (conforme esperado):

CatalogName TableName   ColumnName  FTindexID   DMVIndexID  user_scans  user_seeks  last_user_scan
AW2016FullTextCatalog   JobCandidate    JobCandidateID  1   1   0   1   NULL
AW2016FullTextCatalog   ProductReview   ProductReviewID 1   1   1   1   2024-01-15 18:37:44.537

Mas, uma consulta que não seja de texto completo:

SELECT Comments 
FROM Production.ProductReview  where rating >3

Também resulta na consulta dm_db_index_usage_stats retornando um aumento nas varreduras para esse índice aumentando:

CatalogName TableName   ColumnName  FTindexID   DMVIndexID  user_scans  user_seeks  last_user_scan
AW2016FullTextCatalog   JobCandidate    JobCandidateID  1   1   0   1   NULL
AW2016FullTextCatalog   ProductReview   ProductReviewID 1   1   2   1   2024-01-15 18:39:59.407

Brinquei mais com isso, mas não fiz nenhum progresso. É possível que ainda haja algo que possa ser usado, mas não consigo encontrar e como está no post antigo, não acho que funcione.