Ajuste índices não clusterizados com sugestões de índice ausentes

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de dados SQL no Microsoft Fabric

O recurso de índices ausentes é uma ferramenta leve para localizar índices ausentes que podem melhorar significativamente o desempenho da consulta. Este artigo descreve como usar sugestões de índice ausentes para ajustar índices de forma eficaz e melhorar o desempenho da consulta.

Limitações do recurso de índice ausente

Quando o otimizador de consulta gera um plano de consulta, ele analisa quais são os melhores índices para uma condição de filtro específica. Se os melhores índices não existirem, o otimizador de consulta ainda gerará um plano de consulta usando os métodos de acesso menos dispendiosos disponíveis, mas também armazenará informações sobre esses índices. O recurso de índices ausentes permite que você acesse essas informações sobre os melhores índices possíveis para que você possa decidir se eles devem ser implementados.

A otimização de consultas é um processo sensível ao tempo, portanto, há limitações para o recurso de índice ausente. As limitações incluem:

As sugestões de índice ausentes são baseadas em estimativas feitas durante a otimização de uma única consulta, antes da execução da consulta. As sugestões de índice ausentes não são testadas ou atualizadas após a execução da consulta.
O recurso de índice ausente sugere apenas índices de armazenamento de linha baseados em disco não clusterizados. Índices exclusivos e filtrados não são sugeridos.
As colunas principais são sugeridas, mas a sugestão não especifica uma ordem para essas colunas. Para obter informações sobre como ordenar colunas, consulte a seção Aplicar sugestões de índice ausente deste artigo.
As colunas incluídas são sugeridas, mas o SQL Server não realiza nenhuma análise de custo-benefício em relação ao tamanho do índice resultante quando um grande número de colunas incluídas é sugerido.
As solicitações de indexação ausentes podem oferecer variações semelhantes de índices na mesma tabela e coluna(s) em várias consultas. É importante rever as sugestões de índice e combiná-las sempre que possível.
Não são feitas sugestões para planos de consulta triviais.
As informações de custos são menos precisas para consultas que envolvem apenas predicados de desigualdade.
São reunidas sugestões para um máximo de 600 grupos de índice em falta. Depois que esse limite é atingido, não são coletados mais dados de grupo de índice ausentes.

Devido a essas limitações, as sugestões de índice ausentes são melhor tratadas como uma das várias fontes de informação ao realizar análise, design, ajuste e teste de índice. Sugestões de índice ausentes não são prescrições para criar índices exatamente como sugerido.

Note

O Banco de Dados SQL do Azure oferece ajuste automático de índice. O ajuste automático de índice usa o aprendizado de máquina para aprender horizontalmente com todos os bancos de dados no Banco de Dados SQL do Azure por meio da IA e melhorar dinamicamente suas ações de ajuste. O ajuste automático de índices inclui um processo de verificação para garantir que haja uma melhoria positiva no desempenho da carga de trabalho a partir dos índices criados.

Ver recomendações de índice em falta

O recurso de índices ausentes consiste em dois componentes:

O elemento MissingIndexes presente no XML dos planos de execução. Isso permite correlacionar índices que o otimizador de consulta considera ausentes com as consultas para as quais eles estão faltando.
Um conjunto de exibições de gerenciamento dinâmico (DMVs) que podem ser consultadas para retornar informações sobre índices ausentes. Isso permite que você visualize todas as recomendações de índice ausentes para um banco de dados.

Visualizar sugestões de índices ausentes nos planos de execução

A visão geral do plano de execução pode ser gerada ou obtida de várias maneiras:

Ao escrever ou ajustar uma consulta, pode usar o SQL Server Management Studio (SSMS) para mostrar o plano de execução estimado sem executar a consulta, ou executar a consulta e mostrar um plano de execução real.
Monitorar o desempenho usando o Repositório de Consultas, quando habilitado, coleta planos de execução.
Você pode identificar planos de execução armazenados em cache consultando DMVs como sys.dm_exec_text_query_plan.

Por exemplo, você pode usar a consulta a seguir para gerar solicitações de índice ausentes nos bancos de dados de exemplo AdventureWorks.

SELECT City, StateProvinceID, PostalCode
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;
GO

Para gerar e visualizar as solicitações de índice ausentes:

Abra o SSMS e conecte uma sessão à sua cópia dos bancos de dados de exemplo do AdventureWorks.
Cole a consulta na sessão e gere um plano de execução estimado no SSMS para a consulta selecionando o botão da barra de ferramentas Exibir Plano de Execução Estimado . O plano de execução será exibido em um painel na sessão atual. Uma instrução, Índice ausente verde, aparecerá perto da parte superior do plano gráfico.

Um único plano de execução pode conter várias solicitações de índice ausentes, mas apenas uma solicitação de índice ausente pode ser exibida no plano de execução gráfico. Uma opção para exibir uma lista completa de índices ausentes para um plano de execução é exibir o XML do plano de execução.
Clique com o botão direito do mouse no plano de execução e selecione Mostrar XML do Plano de Execução... no menu.

O XML do plano de execução será aberto como uma nova guia dentro do SSMS.

Note

Apenas uma única sugestão de índice ausente será mostrada na opção de menu Missing Index Details... , mesmo que várias sugestões estejam presentes no XML do plano de execução. A sugestão de índice ausente exibida pode não ser a com a maior melhoria estimada para a consulta.
Exiba a caixa de diálogo Localizar usando o atalho CTRL+f .
Procurar MissingIndex.

Neste exemplo, há dois MissingIndex elementos.
- O primeiro índice ausente sugere que a consulta pode beneficiar de um índice na tabela Person.Address que ofereça suporte a uma pesquisa de igualdade na coluna StateProvinceID, que inclui duas outras colunas, City e PostalCode. No momento da otimização, o otimizador de consulta acreditava que esse índice poderia reduzir o custo estimado da consulta em 34,2737%.
- O segundo índice ausente sugere que a consulta pode usar um índice na Person.Person tabela que ofereça suporte a uma pesquisa de desigualdade na coluna FirstName. No momento da otimização, o otimizador de consulta acreditava que esse índice poderia reduzir o custo estimado da consulta em 18,1102%.

Cada índice não clusterizado baseado em disco em seu banco de dados ocupa espaço, adiciona sobrecarga para inserções, atualizações e exclusões e pode exigir manutenção. Por esses motivos, é uma prática recomendada revisar todas as solicitações de índice ausentes para uma tabela e os índices existentes em uma tabela antes de adicionar um índice com base em um plano de execução de consulta.

Ver sugestões de índice ausentes em DMVs

Você pode recuperar informações sobre índices ausentes consultando os objetos de gerenciamento dinâmico listados na tabela a seguir.

Visualização de gerenciamento dinâmico	Informações devolvidas
sys.dm_db_missing_index_group_stats	Retorna informações resumidas sobre grupos de índices ausentes, por exemplo, as melhorias de desempenho que poderiam ser obtidas com a implementação de um grupo específico de índices ausentes.
sys.dm_db_missing_index_groups	Retorna informações sobre um grupo específico de índices ausentes, como o identificador de grupo e os identificadores de todos os índices ausentes contidos nesse grupo.
sys.dm_db_missing_index_details	Devolve informação detalhada sobre um índice em falta; Por exemplo, ele retorna o nome e o identificador da tabela onde o índice está ausente e as colunas e os tipos de coluna que devem compor o índice ausente.
sys.dm_db_missing_index_columns	Retorna informações sobre as colunas da tabela da base de dados que estão sem um índice.

A consulta a seguir usa os DMVs de índice ausentes para gerar CREATE INDEX instruções. As instruções de criação de índice aqui destinam-se a ajudá-lo a criar sua própria DDL depois de examinar todas as solicitações para a tabela, juntamente com os índices existentes na tabela.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Esta consulta ordena as sugestões por uma coluna chamada estimated_improvement. A melhoria estimada baseia-se numa combinação de:

O custo estimado das consultas associadas à solicitação de índice ausente.
O impacto estimado da adição do índice. Esta é uma estimativa de quanto o índice não clusterizado reduziria o custo da consulta.
A soma das execuções de operadores de consulta (buscas e verificações) que foram executadas para consultas associadas à solicitação de índice ausente. Como discutimos em persistir índices ausentes com o Query Store, essas informações são periodicamente limpas.

Note

O script de criação de índice no Tiger Toolbox da Microsoft examina DMVs de índice ausentes e remove automaticamente todos os índices sugeridos redundantes, analisa índices de baixo impacto e gera scripts de criação de índice para sua revisão. Como na consulta acima, ele executa NOT comandos de criação de índice. O script de Criação de Índice é adequado para o SQL Server e a Instância Gerenciada SQL do Azure. Para o Banco de Dados SQL do Azure, considere implementar o ajuste automático de índice.

Analise Limitações do recurso de índice ausente e como aplicar sugestões de índice ausentes antes de criar índices e modifique o nome do índice para corresponder à convenção de nomenclatura do banco de dados.

Guardar índices ausentes com o Query Store

As sugestões de índice ausentes em DMVs são limpas por eventos como reinicializações de instância, failovers e definição de um banco de dados offline. Além disso, quando os metadados de uma tabela são alterados, todas as informações de índice ausentes sobre essa tabela são excluídas desses objetos de gerenciamento dinâmico. As alterações nos metadados da tabela podem ocorrer quando colunas são adicionadas ou descartadas de uma tabela, por exemplo, ou quando um índice é criado em uma coluna de uma tabela. A execução de uma operação ALTER INDEX em um índice em uma tabela também limpa as solicitações de índice ausentes para essa tabela.

Da mesma forma, os planos de execução armazenados no cache de planos são limpos por eventos como reinicializações de instância, failovers e definição de um banco de dados offline. Os planos de execução podem ser removidos do cache devido à pressão da memória e recompilações.

As sugestões de índice ausentes nos planos de execução podem ser persistidas nesses eventos, habilitando o desempenho do Monitor usando o Repositório de Consultas.

A consulta a seguir recupera os 20 principais planos de consulta que contêm solicitações de índice ausentes do Repositório de Consultas com base em uma estimativa aproximada do total de leituras lógicas para a consulta. Os dados são limitados a execuções de consulta nas últimas 48 horas.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Aplicar sugestões de índice ausentes

Para usar efetivamente as sugestões de índice ausentes, siga as diretrizes de design de índice não agrupado. Ao ajustar índices não agrupados com sugestões de índice ausentes, revise a estrutura da tabela base, combine cuidadosamente os índices, considere a ordem das colunas principais e revise as sugestões de colunas incluídas.

Rever a estrutura da tabela base

Antes de criar índices não agrupados em uma tabela com base em sugestões de índice ausentes, revise o índice clusterizado da tabela.

Uma maneira de verificar se há um índice clusterizado é usando o procedimento armazenado do sistema sp_helpindex. Por exemplo, podemos exibir um resumo dos índices na tabela Person.Address executando a seguinte instrução:

exec sp_helpindex 'Person.Address';
GO

Analise a coluna index_description. Uma tabela pode ter apenas um índice clusterizado. Se um índice clusterizado tiver sido implementado para a tabela, o index_description conterá a palavra 'clustered'.

Se nenhum índice clusterizado estiver presente, a tabela será um heap. Nesse caso, analise se a tabela foi criada intencionalmente como um heap para resolver um problema de desempenho específico. Geralmente, as tabelas beneficiam-se de índices clusterizados: frequentemente, as tabelas são implementadas como heaps por acidente. Considere a implementação de um índice agrupado com base nas diretrizes de design de índice agrupado .

Revisar índices ausentes e índices existentes para sobreposição

Os índices ausentes podem oferecer variações semelhantes de índices não agrupados na mesma tabela e coluna(s) nas consultas. Os índices ausentes também podem ser semelhantes aos índices existentes em uma tabela. Para obter um desempenho ideal, é melhor examinar os índices ausentes e os índices existentes quanto à sobreposição e evitar a criação de índices duplicados.

Gerar script dos índices existentes numa tabela

Uma maneira de examinar a definição de índices existentes em uma tabela é criar scripts para os índices com os Detalhes do Pesquisador de Objetos:

Conecte o Pesquisador de Objetos à sua instância ou banco de dados.
Expanda o nó do banco de dados em questão no Explorador de Objetos.
Expanda a pasta Tabelas.
Expanda a tabela para a qual você gostaria de criar scripts de índices.
Selecione a pasta Índices .
Se o painel Detalhes do Pesquisador de Objetos ainda não estiver aberto, no menu Exibir , selecione Detalhes do Pesquisador de Objetos ou pressione F7.
Selecione todos os índices listados no painel Detalhes do Pesquisador de Objetos com o atalho CTRL+a.
Clique com o botão direito do mouse em qualquer lugar na região selecionada e selecione a opção de menu Índice de script como, depois CREATE Para** e Nova janela do Editor de Consultas.

Rever índices e combinar sempre que possível

Analise as recomendações de índice ausentes para uma tabela como um grupo, juntamente com as definições de índices existentes na tabela. Lembre-se de que, ao definir índices, geralmente as colunas de igualdade devem ser colocadas antes das colunas de desigualdade e, juntas, elas devem formar a chave do índice. Para determinar uma ordem efetiva para as colunas de igualdade, ordene-as com base em sua seletividade: liste as colunas mais seletivas primeiro (mais à esquerda na lista de colunas). As colunas exclusivas são mais seletivas, enquanto as colunas com muitos valores repetitivos são menos seletivas.

As colunas incluídas devem ser adicionadas CREATE INDEX à instrução usando a INCLUDE cláusula. A ordem das colunas incluídas não afeta o desempenho da consulta. Portanto, ao combinar índices, as colunas incluídas podem ser combinadas sem se preocupar com a ordem. Saiba mais em Diretrizes para colunas incluídas.

Por exemplo, você pode ter uma tabela, Person.Address, com um índice existente na coluna chave StateProvinceID. Você pode ver recomendações de índice ausentes para a Person.Address tabela para as seguintes colunas:

Filtros IGUALDADE para StateProvinceID e City
IGUALDADE filtra para StateProvinceID e City, INCLUDEPostalCode

Modificar o índice existente para corresponder à segunda recomendação, um índice com chaves ligadas StateProvinceID e City incluindo PostalCode, provavelmente satisfaria as consultas que geraram ambas as sugestões de índice.

As compensações são comuns no ajuste do índice. É provável que, para muitos conjuntos de dados, a City coluna seja mais seletiva do que a StateProvinceID coluna. No entanto, se nosso índice existente em StateProvinceID for muito usado, e outras solicitações pesquisarem em grande parte em ambos StateProvinceID e City, é menor sobrecarga para o banco de dados em geral ter um único índice com ambas as colunas na chave, levando em StateProvinceID, embora não seja a coluna mais seletiva.

Os índices podem ser modificados de várias maneiras:

Pode utilizar a instrução CREATE INDEX com a cláusula DROP_EXISTING. Talvez você queira renomear os índices após a modificação para que o nome ainda descreva com precisão a definição do índice, dependendo da sua convenção de nomenclatura.
Você pode usar a instrução DROP INDEX (Transact-SQL) seguida por uma instrução CREATE INDEX.

A ordem das chaves de índice é importante ao combinar as sugestões de índice: City como coluna principal é diferente de StateProvinceID como coluna principal. Saiba mais em Diretrizes de design de índice não clusterizado.

Ao criar índices, considere o uso de operações de índice online quando estiverem disponíveis.

Embora os índices possam melhorar drasticamente o desempenho da consulta em alguns casos, os índices também têm custos gerais e de gerenciamento. Revise as diretrizes gerais de design de índice para ajudar a avaliar o benefício dos índices antes de criá-los.

Verificar se a alteração do índice foi bem-sucedida

É importante confirmar se as alterações de índice foram bem-sucedidas: o otimizador de consulta está usando seus índices?

Uma maneira de validar suas alterações de índice é usar o Repositório de Consultas para identificar consultas com solicitações de índice ausentes. Anote o query_id das consultas. Use a vista Consultas Controladas no Query Store para verificar se os planos de execução de uma consulta foram alterados e se o otimizador está a utilizar o seu novo ou modificado índice. Saiba mais sobre Consultas Controladas em comece com a resolução de problemas de desempenho de consultas.

Comentários

Esta página foi útil?

Last updated on 2025-11-18