Como saber se seu Index está sendo bem utilizado ( foi bem ou mal criado rs.. )

Dale pessoal hoje vou falar um pouco sobre utilização de um índice especifico.
Aquele índice que acabei criei na base, será que está realmente sendo utilizado, como, será que está com as colunas corretas ou precisa de mais algum ajuste !!!

Bom vamos lá,  a consulta abaixo mostra como está sendo a utilização dos índices, quantos Seek e Scans, já é um norte para saber se podemos considerar se um índice foi bem criado ou um mal criado rs....

SELECT
 OBJECT_NAME(U.OBJECT_ID) AS "TABLE",
 I.NAME AS "INDEX", 
 U.USER_SEEKS,
 U.USER_SCANS,
 U.USER_LOOKUPS,
 U.LAST_USER_SEEK,
 U.LAST_USER_SCAN,
 U.LAST_USER_LOOKUP,
 U.LAST_USER_UPDATE
FROM SYS.DM_DB_INDEX_USAGE_STATS U
JOIN SYS.INDEXES I ON ( I."OBJECT_ID" = U."OBJECT_ID" AND I.INDEX_ID = U.INDEX_ID )
WHERE U."OBJECT_ID" = OBJECT_ID('Nome_da_Tabela') -- Nome da Tabela
 AND U.DATABASE_ID = DB_ID()
ORDER BY U.INDEX_ID ASC

Na consulta acima, identificamos se um determinado índice está sendo bem utilizado ou não.

A consulta abaixo, foi retirada do blog do Dirceu Resende (https://www.dirceuresende.com/blog/sql-server-como-identificar-as-consultas-que-utilizam-um-determinado-indice-atraves-do-plan-cache/) e com algumas melhorias, podemos verificar quais consultas estão fazendo uso do índice, e providenciar ajustes quando necessário.

SELECT
    DB_Name(b.dbid) AS DatabaseName,
    SUBSTRING(C.[text], ( A.statement_start_offset / 2 ) + 1, ( CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(C.[text]) ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 + 1) AS sqltext,
    A.execution_count,
    A.total_logical_reads / execution_count AS avg_logical_reads,
    A.total_logical_writes / execution_count AS avg_logical_writes,
    A.total_worker_time / execution_count AS avg_cpu_time,
    A.last_elapsed_time / execution_count AS avg_elapsed_time,
    A.total_rows / execution_count AS avg_rows,
    A.creation_time,
    A.last_execution_time,
    CAST(query_plan AS XML) AS plan_xml,
    B.query_plan,
    C.[text]
FROM
    sys.dm_exec_query_stats AS A
    CROSS APPLY sys.dm_exec_text_query_plan(A.plan_handle, A.statement_start_offset, A.statement_end_offset) AS B
    CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) AS C
WHERE
    B.query_plan LIKE '%Nome_do_Seu_Indice%'
    AND B.query_plan NOT LIKE '%dm_exec_text_query_plan%'
 --AND DB_Name(b.dbid) = 'Nome_da_Base'
ORDER BY
    A.last_execution_time DESC
OPTION(RECOMPILE)

Deixei comentado a condição que filtra as statistics por database, pois algumas execuções podem estar vindo pelo sp_execute e sendo executada direta do master, nesse caso poderíamos identificar a base que rodou validando a coluna Text.

Um exemplo do retorno dessa ultima consulta, com detalhes de quais consultas estão consumindo um determinado índice.

Bom é isso ai, espero que esse post seja util

Reaja com otimismo e positividade a tudo, pois a vida também é a consequência de como encaramos o que nos acontece.