sys.dm_db_missing_index_details (Transact-SQL)

  • Artigo

Aplica-se a: SQL ServerBanco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Retorna informações detalhadas sobre índices ausentes.

No Banco de Dados SQL do Azure, as exibições de gerenciamento dinâmico não podem expor informações que afetariam a contenção do banco de dados ou expor informações sobre outros bancos de dados aos quais o usuário tem acesso. Para evitar a exposição dessas informações, todas as linhas que contêm dados que não pertencem ao locatário conectado são filtradas.

Nome da coluna Tipo de dados Descrição
index_handle int Identifica um determinado índice ausente. O identificador é exclusivo no servidor. index_handle é a chave desta tabela.
database_id smallint Identifica o banco de dados onde reside a tabela com o índice ausente.

No Banco de Dados SQL do Azure, os valores são exclusivos em um único banco de dados ou em um pool elástico, mas não em um servidor lógico.
object_id int Identifica a tabela onde o índice está ausente.
equality_columns nvarchar(4000) Lista separada por vírgulas de colunas que contribuem para os predicados de igualdade do formulário:

table.column = constant_value
inequality_columns nvarchar(4000) Lista separada por vírgulas de colunas que contribuem para predicados de desigualdade, por exemplo, predicados do formulário:

table.column>constant_value

Qualquer operador de comparação diferente de "=" expressa desigualdade.
included_columns nvarchar(4000) Lista separada por vírgulas de colunas necessárias como colunas de cobertura para a consulta. Para obter mais informações sobre colunas de cobertura ou incluídas, consulte Criar índices com colunas incluídas.

Para índices com otimização de memória (hash e não clusterizados com otimização de memória), ignore included_columnso . Todas as colunas da tabela são incluídas em cada índice com otimização de memória.
instrução nvarchar(4000) Nome da tabela onde o índice está ausente.

Comentários

As informações retornadas por sys.dm_db_missing_index_details são atualizadas quando uma consulta é otimizada pelo otimizador de consulta e não são persistentes. As informações de índice ausentes são mantidas somente até que o mecanismo de banco de dados seja reiniciado. Os administradores de banco de dados devem periodicamente gerar cópias de backup de informações de índice ausente se quiserem mantê-las após o desligamento e a reinicialização do servidor. Use a coluna sqlserver_start_time em sys.dm_os_sys_info para localizar a última hora de inicialização do mecanismo de banco de dados.

Para determinar de quais grupos de índices ausentes um determinado índice ausente faz parte, você pode consultar a sys.dm_db_missing_index_groups exibição de gerenciamento dinâmico associando-a sys.dm_db_missing_index_details com base index_handle na coluna.

Observação

O conjunto de resultados para essa DMV é limitado a 600 linhas. Cada linha contém um índice ausente. Se você tiver mais de 600 índices ausentes, deverá abordar os índices ausentes existentes para poder exibir os mais recentes.

Usando informações de índice ausente em instruções CREATE INDEX

Para converter as informações retornadas por sys.dm_db_missing_index_details em uma instrução CREATE INDEX para índices com otimização de memória e baseados em disco, as colunas de igualdade devem ser colocadas antes das colunas de desigualdade e, juntas, devem formar a chave do índice. As colunas incluídas devem ser adicionadas à instrução CREATE INDEX com a cláusula INCLUDE. Para determinar uma ordem efetiva para as colunas desiguais, ordene-as com base em sua seletividade: liste as colunas mais seletivas primeiro (a mais à esquerda na lista de colunas). Saiba mais em Ajustar índices não clusterizados com sugestões de índice ausente, incluindo Limitações do recurso de índice ausente.

Para obter mais informações sobre índices com otimização de memória, consulte Índices para tabelas com otimização de memória.

Consistência da transação

Se uma transação criar ou descartar uma tabela, as linhas contendo as informações de índice ausente sobre os objetos descartados serão removidas do objeto de gerenciamento dinâmico, preservando a consistência da transação. Saiba mais sobre as limitações do recurso de índice ausente.

Permissões

No SQL Server e na Instância Gerenciada de SQL, requer a permissão VIEW SERVER STATE.

Nos objetivos de serviço Básico, S0 e S1 do Banco de Dados SQL e para bancos de dados em pools elásticos, a conta de administrador do servidor, a conta de administrador do Microsoft Entra ou a ##MS_ServerStateReader## associação na função de servidor são necessárias. Em todos os outros objetivos de serviço do Banco de Dados SQL, a permissão VIEW DATABASE STATE no banco de dados ou a associação à função de servidor ##MS_ServerStateReader## são necessárias.

Permissões do SQL Server 2022 e posteriores

É necessária a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Exemplos

O exemplo a seguir retorna sugestões de índice ausentes para o banco de dados atual. As sugestões de índice ausente devem ser combinadas quando possível entre si e com os índices existentes no banco de dados atual. Saiba como aplicar essas sugestões em ajustar índices não clusterizados com sugestões de índice ausente.

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  '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,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Observação

O script Index-Creation da Caixa de Ferramentas da Tiger da Microsoft examina DMVs de índices ausentes e remove automaticamente 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 NÃO executa comandos de criação de índice. O script Index-Creation é adequado para o SQL Server e a Instância Gerenciada de SQL do Azure. Para o Banco de Dados SQL do Azure, considere implementar o ajuste automático de índice.

Próximas etapas

Saiba mais sobre o recurso de índice ausente nos seguintes artigos: