Confuso sobre por que um determinado índice está sendo usado

Criar um índice na coluna envolvida na chave de agrupamento pode parecer um pouco estranho. Alguém pergunta por que criar outro índice, quando já existe um?

O índice clusterizado é a tabela. Ou seja, seu índice clusterizado para esta tabela é semelhante a um índice como:

CREATE INDEX IX_Panel
ON dbo.Panel(SubId ASC)
INCLUDE (
    LineageId
    , Buck
    , Lot
    , GlassType
    , ETA
);

Claramente, isso não é o mesmo que um índice não clusterizado na chave de clustering, que no seu caso apenas "inclui" a LineageIdcoluna:

CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId]);

Como teste, criei um modelo de sua tabela e inseri mais de 700.000 linhas nela:

INSERT INTO dbo.Panel(LineageId, Buck, Lot, GlassType, ETA)
SELECT (ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) % 35)
    , SUBSTRING(o2.name, 1, 15)
    , 'lot'
    , 'GlassType'
    , o3.name + o2.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

Em seguida, executei a seguinte consulta para ver as estatísticas sobre os dois índices:

SELECT o.name
    , ps.index_id
    , ps.index_type_desc
    , ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Panel'),-1,0, NULL) ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id;

Os resultados da consulta acima são:

Claramente, o índice não clusterizado é menor que o índice clusterizado. Neste caso, cerca de 1/5 do tamanho. Ser um índice menor significa que o otimizador de consulta escolherá usá-lo quando o índice atender aos requisitos da consulta, de uma forma ou de outra.

Por exemplo,

SELECT SubId
FROM dbo.Panel;

só precisará ler 1.304 páginas do disco verificando o índice não clusterizado, em vez de ter que ler 7.105 páginas para verificar o índice clusterizado.

Por outro lado, para uma consulta que faz referência a colunas que não estão includedno índice não clusterizado, o SQL Server provavelmente usará o índice clusterizado, pois ele atende aos requisitos da consulta.

Tomemos, por exemplo, a consulta a seguir, onde você pode pensar intuitivamente que o otimizador de consulta pode escolher o índice não clusterizado, pois pode usá-lo para cumprir a WHEREcláusula, no entanto, o simples ato de procurar a Buckcoluna significa que é mais rápido simplesmente busque o índice clusterizado para os valores no WHERE, então retorne as 3 colunas na SELECTcláusula:

SELECT SubId
    , LineageId
    , Buck
FROM dbo.Panel
WHERE LineageId = 6
    AND SubId >= 27
    AND SubId <= 42;

Isso é um pouco simplificado, eu recomendo olhar para as páginas de índice de Brent Ozar

Escolher um índice mais restrito geralmente é melhor, especialmente se cobrir, o que significa que as consultas que usam buscas provavelmente só precisam de SubID e possivelmente LineageID. Embora, mesmo que o índice não cubra, ele ainda pode escolher o índice mais restrito e realizar pesquisas para a(s) coluna(s) adicional(is) se a contagem de linhas for pequena o suficiente para que isso ainda seja menos dispendioso no geral.

A menos que as consultas realmente exijam todas ou a maioria das colunas da tabela, o índice clusterizado seria uma escolha ruim, porque está espalhado por muito mais páginas, o que significa mais E/S para obter o subconjunto de colunas de que você precisa. Se tudo isso puder ser obtido do índice mais restrito, será a maneira mais barata de acessar os dados.

O custo é amplamente determinado pelo número de páginas que precisam ser lidas para satisfazer a consulta. O índice clusterizado é a tabela inteira, portanto, nunca será mais fino do que qualquer índice não clusterizado. Isso significa que, geralmente, para o mesmo número de linhas, o índice clusterizado exigirá mais E/S do que o índice não clusterizado. As escalas caem quando o índice não clusterizado não atende à consulta, o que significa que o SQL Server precisa escolher entre ir para o índice clusterizado e ir para o índice não clusterizado e realizar pesquisas. O último é julgado com base aproximadamente no número estimado de linhas e larguras de coluna, então realmente há muito "depende" que entrará em jogo.

Infelizmente, as estatísticas de uso do índice DMV não distinguem entre uma busca singleton e uma busca disfarçando uma varredura de intervalo (seja 2 linhas ou 2 milhões de linhas), portanto, apenas ver uma série de buscas versus varreduras não nos diz realmente o toda a história. E não temos informações suficientes sobre suas consultas reais para mostrar exatamente por que essa escolha foi feita, mas espero ter indicado o motivo provável pelo menos em alto nível.