Há muitos motivos pelos quais você pode não ter solicitações de índice ausentes!

Veremos alguns dos motivos com mais detalhes e também falaremos sobre algumas das limitações gerais do recurso.

Limitações Gerais

Primeiro, de: Limitações do recurso de índices ausentes :

• Ele não especifica uma ordem para as colunas a serem usadas em um índice.

Conforme observado nesta P e R: Como o SQL Server determina a ordem das colunas-chave em solicitações de índice ausentes? , a ordem das colunas na definição do índice é ditada pelo predicado Equality vs Inequality e, em seguida, pela posição ordinal da coluna na tabela.

Não há suposições sobre a seletividade e pode haver uma ordem melhor disponível. É seu trabalho descobrir isso.

Índices especiais

As solicitações de índice ausentes também não cobrem índices 'especiais', como:

• Agrupado
• Filtrado
• Particionado
• Comprimido
• XML-ed
• Edição espacial
• Columnstore-d
• Visualização indexada

Quais colunas são consideradas?

Colunas de chave de índice ausentes são geradas a partir de colunas usadas para filtrar resultados, como aquelas em:

• JOINs
• cláusula WHERE

As colunas ausentes do índice incluído são geradas a partir de colunas exigidas pela consulta, como aquelas em:

• SELECIONAR
• AGRUPAR POR
• ORDENAR POR

Embora com bastante frequência, as colunas pelas quais você está ordenando ou agrupando podem ser benéficas como colunas-chave. Isso remonta a uma das limitações:

• Não se destina a ajustar uma configuração de indexação.

Por exemplo, essa consulta não registrará uma solicitação de índice ausente, embora a adição de um índice em LastAccessDate evite a necessidade de classificar (e despejar no disco).

SELECT TOP (1000) u.DisplayName
FROM dbo.Users AS u
ORDER BY u.LastAccessDate DESC;

Nem este agrupamento consulta a localização.

SELECT TOP (20000) u.Location
FROM dbo.Users AS u
GROUP BY u.Location

Isso não soa muito útil!

Bem, sim, mas é melhor do que nada. Pense em solicitações de índice ausentes como um bebê chorando. Você sabe que há um problema, mas cabe a você, como adulto, descobrir qual é esse problema.

Você ainda não me disse por que eu não os tenho, embora...

Relaxa, cara. Estamos chegando lá.

Sinalizadores de rastreamento

Se você habilitar o TF 2330 , as solicitações de índice ausentes não serão registradas. Para descobrir se você tem isso ativado, execute isto:

DBCC TRACESTATUS;

Recriações de índice

A reconstrução de índices limpará as solicitações de índice ausentes. Portanto, antes de ir para Hi-Ho-Silver-Away reconstruindo cada índice no segundo em que um pingo de fragmentação se infiltra, pense nas informações que você está limpando toda vez que faz isso.

Você também pode querer pensar sobre Por que desfragmentar seus índices não está ajudando , de qualquer maneira. A menos que você esteja usando Columnstore .

Adicionando, removendo ou desabilitando índices

Adicionar, remover ou desabilitar um índice limpará todas as solicitações de índice ausentes para essa tabela. Se você estiver trabalhando em várias alterações de índice na mesma tabela, certifique-se de fazer o script de todas elas antes de fazer qualquer.

Planos triviais

Se um plano for simples o suficiente, a escolha de acesso ao índice for óbvia o suficiente e o custo for baixo o suficiente, você obterá um plano trivial.

Isso significa efetivamente que não houve decisões baseadas em custo para o otimizador tomar.

Por Paulo Branco :

Os detalhes de quais tipos de consulta podem se beneficiar do Trivial Plan mudam com frequência, mas coisas como junções, subconsultas e predicados de desigualdade geralmente impedem essa otimização.

Quando um plano é trivial, as fases de otimização adicionais não são exploradas e os índices ausentes não são solicitados .

Veja a diferença entre essas consultas e seus planos :

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2;

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2
AND 1 = (SELECT 1);

O primeiro plano é trivial e nenhuma solicitação é mostrada. Pode haver casos em que os bugs impeçam que os índices ausentes apareçam nos planos de consulta; eles geralmente são registrados de forma mais confiável nos DMVs de índice ausentes.

Capacidade de SARG

Predicados em que o otimizador não seria capaz de usar um índice com eficiência, mesmo com um índice, podem impedir que sejam registrados.

Coisas que geralmente não são SARGable são:

• Colunas envolvidas em funções
• Coluna + SomeValue = SomePredicate
• Coluna + OutraColuna = AlgumPredicado
• Coluna = @Variable OU @Variable É NULO

Exemplos:

SELECT *
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 1000) > 1000;


SELECT *
FROM dbo.Users AS u
WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000


SELECT *
FROM dbo.Users AS u
WHERE u.UpVotes + u.DownVotes > 10000000


DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren'
SELECT *
FROM dbo.Users AS u
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo 
      OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;

Nenhuma dessas consultas registrará solicitações de índice ausentes. Para mais informações sobre eles, confira os links a seguir:

• Parâmetros opcionais e solicitações de índice ausentes
• Cláusula SARGable WHERE para duas colunas de data
• Quais são as diferentes maneiras de substituir ISNULL() em uma cláusula WHERE que usa apenas valores literais?

Você já tem um índice OK

Pegue este índice:

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

Parece bom para esta consulta:

SELECT p.OwnerUserId, p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20070101'
AND   p.CreationDate < '20181231'
AND   p.Score >= 25000
AND 1 = (SELECT 1)
ORDER BY p.Score DESC;

O plano é uma simples Busca...

Mas como a coluna de chave principal é para o predicado menos seletivo, acabamos fazendo mais trabalho do que deveríamos:

Tabela 'Postagens'. Contagem de varredura 13, leituras lógicas 136890

Se alterarmos a ordem das colunas da chave do índice, trabalharemos muito menos:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

E significativamente menos leituras:

Tabela 'Postagens'. Contagem de varredura 1, leituras lógicas 5

SQL Server está criando índices para você

Em certos casos, o SQL Server escolherá criar um índice dinamicamente por meio de um spool de índice. Quando um spool de índice estiver presente, uma solicitação de índice ausente não estará. Certamente adicionar o índice você mesmo pode ser uma boa ideia, mas não conte com o SQL Server ajudando você a descobrir isso.