Início / dba / Perguntas / 18943
Accepted
mehdi lotfi
Asked: 2012-06-08 04:59:37 +0800 CST

Ferramentas para Identificar Índices Necessários

  • 772

Eu quero criar os melhores índices para cada tabela em meu banco de dados. Existe alguma consulta ou ferramenta no SQL Server para ajudar nesse processo?

sql-server database-design

3 respostas

  1. Best Answer

    Sim, existem ferramentas, como o orientador de ajuste do mecanismo de banco de dados. Ele vem com o SQL Server e faz algumas coisas bem desagradáveis:

    • Ele permite que você considere apenas uma carga de trabalho muito pequena (mesmo uma única consulta). O que irá então sugerir índices que podem ajudar apenas aquela pequena carga de trabalho, sem levar em conta o restante da carga de trabalho (índices ajudam a acelerar algumas consultas, mas podem desacelerar outras, especialmente inserir/atualizar/excluir), não importa o resto do ciclo de negócios.
    • Frequentemente, ele recomendará índices redundantes que diferem apenas por uma coluna à direita ou por uma coluna incluída.
    • Ele realmente gosta muito de incluir colunas - acho que pode ser um fetiche.

    A abordagem adequada para o ajuste do índice é considerar toda a carga de trabalho em um ciclo de negócios completo. Às vezes, é melhor recorrer a ferramentas de terceiros do que reinventar a roda. DBSophic faz um produto gratuito chamado Qure Workload Analyzer que eu acho que faz um trabalho muito melhor do que o DTA - mas sua ferramenta licenciada (Qure Workload Optimizer) é absolutamente fenomenal no que faz. No ano passado, escrevi em um blog sobre como a ferramenta funciona quando integrada ao SQL Sentry ).

    Também existem algumas outras opções, por exemplo, índices ausentes são relatados nos DMVs e você também pode descobrir em quais índices estão sendo gravados (para que estejam ocupando espaço e afetando as operações de inserção/atualização/exclusão), mas nunca (ou raramente ) usado por consultas do usuário:

    Mas eles também precisam da ressalva de que os DMVs são válidos apenas desde a última reinicialização do SQL Server e que não cobrirão necessariamente um ciclo de negócios adequado (e, portanto, podem estar faltando informações vitais

    • 15

  2. A ferramenta da qual você está falando é chamada de Database Engine Tuning Advisor (DTA para abreviar). Ele pode receber várias entradas e fornecer recomendações sobre coisas como índices ausentes. Vale a pena notar, porém, que estas são apenas sugestões e uma consideração cuidadosa deve ser tomada em vez de seguir cegamente as recomendações do DTA.

    Link para o Tutorial DTA

    Também é importante notar que existem DMVs integrados para coletar esse tipo de informação:

    • 6

  3. Pode parecer bom demais para ser verdade, mas encontrei uma consulta que faz exatamente isso:

    USE YourDatabase
GO

SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,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]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,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]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,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  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);
    • 0

relate perguntas