Encontrando índices não utilizados

Olá pessoal,

Neste post irei compartilhar um script que nos auxilia a melhorar a performance das consultas que são executadas em nossa instância, mostrando índices que poderiam ser criados para melhorar a performance.

Demonstração:

CREATE TABLE t1 (
    c1 INT IDENTITY,
    c2 AS c1 * 2,
    c3 AS c1 + c1,
    c4 CHAR (3000) DEFAULT 'a');
GO
CREATE UNIQUE CLUSTERED INDEX t1_clus ON t1 (c1);
GO

--Insert 100000 linhas

SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 100000

--Select com a opção (include Actual Execution Plan Ctrl + M)

SELECT * FROM t1
    WHERE c2 BETWEEN 10 AND 1000
    AND c3 > 1000;

Ao executar a query acima teremos o seguinte plano de execução.

No plano de execução o SQLServer nos sugere a criação de um índice na tabela T1, usando as colunas C2 e C3, incluindo as colunas C1 e C4.

Depois que você executa a query, você consegue recuperar essas sugestões de índice utilizando algumas DVM (dynamic management views).

No script abaixo teremos informações sobre qual impacto do índice, quais campos a ser adicionado no include, contagem de UserSeek e o script para criação do índice.

No script vamos usar as DMVs:

sys.dm_db_missing_index_details – Retorna informações detalhadas sobre os índices ausentes, incluindo tabelas e colunas, a DMV exclui sugestão de índices espaciais.

sys.dm_db_missing_index_group_stats – Retorna informações sobre um grupo de índices ausentes, que com as atualizações a cada consulta.

sys.dm_db_missing_index_groups – Retorna informações de um índices contido em um grupo de índices.

SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
    ,db.[database_id] AS [DatabaseID]
    ,db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
    ,gs.[avg_user_impact] AS [AvgUserImpact]
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
    ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
    ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
    ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4
--and gs.[last_user_seek] >= '20150803 13:00'
--and OBJECT_NAME(id.OBJECT_ID,id.database_id) = 't1'
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

Para visualizar as informações das DMVs, você deverá ser Sysadmin ou ter permissão VIEW SERVER STATE.

Esses índices ausentes são os que o otimizador de consulta do SQL Server identifica durante as execuções de consultas e ficam armazenados nos planos de execução, lembrando que o cache é limpo sempre que o SQL Server for reiniciado, antes de criar os índices é recomendado verificar se realmente o índice é necessário..

Até a próxima.