Eu estava experimentando índices para acelerar as coisas, mas no caso de uma junção, o índice não está melhorando o tempo de execução da consulta e, em alguns casos, está diminuindo a velocidade.
A consulta para criar a tabela de teste e preenchê-la com dados é:
CREATE TABLE [dbo].[IndexTestTable](
[id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [nvarchar](20) NULL,
[val1] [bigint] NULL,
[val2] [bigint] NULL)
DECLARE @counter INT;
SET @counter = 1;
WHILE @counter < 500000
BEGIN
INSERT INTO IndexTestTable
(
-- id -- this column value is auto-generated
NAME,
val1,
val2
)
VALUES
(
'Name' + CAST((@counter % 100) AS NVARCHAR),
RAND() * 10000,
RAND() * 20000
);
SET @counter = @counter + 1;
END
-- Index in question
CREATE NONCLUSTERED INDEX [IndexA] ON [dbo].[IndexTestTable]
(
[Name] ASC
)
INCLUDE ( [id],
[val1],
[val2])
Agora a consulta 1, que foi melhorada (apenas um pouco, mas a melhoria é consistente) é:
SELECT *
FROM IndexTestTable I1
JOIN IndexTestTable I2
ON I1.ID = I2.ID
WHERE I1.Name = 'Name1'
Estatísticas e plano de execução sem índice (neste caso, a tabela usa o índice clusterizado padrão):
(5000 row(s) affected)
Table 'IndexTestTable'. Scan count 2, logical reads 5580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 294 ms.
Agora com o índice ativado:
(5000 row(s) affected)
Table 'IndexTestTable'. Scan count 2, logical reads 2819, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 231 ms.
Agora, a consulta que fica mais lenta devido ao índice (a consulta não tem sentido, pois é criada apenas para teste):
SELECT I1.Name,
SUM(I1.val1),
SUM(I1.val2),
MIN(I2.Name),
SUM(I2.val1),
SUM(I2.val2)
FROM IndexTestTable I1
JOIN IndexTestTable I2
ON I1.Name = I2.Name
WHERE
I2.Name = 'Name1'
GROUP BY
I1.Name
Com o índice clusterizado ativado:
(1 row(s) affected)
Table 'IndexTestTable'. Scan count 4, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 155106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 17207 ms, elapsed time = 17337 ms.
Agora com o Index desabilitado:
(1 row(s) affected)
Table 'IndexTestTable'. Scan count 5, logical reads 8642, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 165212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 17691 ms, elapsed time = 9073 ms.
As perguntas são:
- Mesmo que o índice seja sugerido pelo SQL Server, por que ele retarda as coisas por uma diferença significativa?
- Qual é a junção Nested Loop que está demorando mais e como melhorar seu tempo de execução?
- Há algo que estou fazendo de errado ou deixei passar?
- Com o índice padrão (somente na chave primária), por que leva menos tempo e com o índice não clusterizado presente, para cada linha na tabela de junção, a linha da tabela unida deve ser encontrada mais rapidamente, porque a junção está na coluna Nome na qual o índice foi criado. Isso se reflete no plano de execução da consulta e o custo de Index Seek é menor quando o IndexA está ativo, mas por que ainda mais lento? Além disso, o que está na junção externa esquerda do loop aninhado que está causando a lentidão?
Usando o SQLServer 2012
As sugestões de índice são feitas pelo otimizador de consulta. Se encontrar uma seleção lógica de uma tabela que não é bem atendida por um índice existente, pode adicionar uma sugestão de "índice ausente" à sua saída. Essas sugestões são oportunistas; eles não se baseiam em uma análise completa da consulta e não levam em consideração considerações mais amplas. Na melhor das hipóteses, eles são uma indicação de que uma indexação mais útil pode ser possível, e um DBA qualificado deve dar uma olhada.
A outra coisa a dizer sobre sugestões de índices ausentes é que elas são baseadas no modelo de custos do otimizador, e o otimizador estima quanto o índice sugerido pode reduzir o custo estimado da consulta. As palavras-chave aqui são "modelo" e "estimativas". O otimizador de consulta sabe pouco sobre sua configuração de hardware ou outras opções de configuração do sistema - seu modelo é amplamente baseado em números fixos que produzem resultados de plano razoáveis para a maioria das pessoas na maioria dos sistemas na maior parte do tempo. Além dos problemas com os números de custo exatos usados, os resultados são sempre estimativas - e as estimativas podem estar erradas.
Há pouco a ser feito para melhorar o desempenho da própria operação de junção cruzada; loops aninhados são a única implementação física possível para uma junção cruzada. O spool da mesa no lado interno da junção é uma otimização para evitar a nova varredura do lado interno para cada linha externa. Se esta é uma otimização de desempenho útil depende de vários fatores, mas em meus testes a consulta fica melhor sem ela. Novamente, isso é uma consequência do uso de um modelo de custo - meu sistema de CPU e memória provavelmente tem características de desempenho diferentes das suas. Não há nenhuma dica de consulta específica para evitar o spool da tabela, mas há um sinalizador de rastreamento não documentado (8690) que você pode usar para testar o desempenho da execução com e sem o spool. Se este fosse um problema real do sistema de produção, o plano sem o spool pode ser forçado usando um guia de plano baseado no plano produzido com o TF 8690 ativado. O uso de sinalizadores de rastreamento não documentados na produção não é recomendado porque a instalação se torna tecnicamente sem suporte e os sinalizadores de rastreamento podem ter efeitos colaterais indesejáveis.
A principal coisa que você está perdendo é que, embora o plano usando o índice não clusterizado tenha um custo estimado menor de acordo com o modelo do otimizador, ele tem um problema significativo de tempo de execução. Se você observar a distribuição de linhas nos encadeamentos no plano usando o Clustered Index, provavelmente verá uma distribuição razoavelmente boa:
No plano que usa o Nonclustered Index Seek, o trabalho acaba sendo feito inteiramente por uma thread:
This is a consequence of the way work is distributed among threads by parallel scan/seek operations. It is not always the case that a parallel scan will distribute work better than an index seek - but it does in this case. More complex plans might include repartitioning exchanges to redistribute work across threads. This plan has no such exchanges, so once rows are assigned to a thread, all related work is performed on that same thread. If you look at the work distribution for the other operators in the execution plan, you will see that all work is performed by the same thread as shown for the index seek.
There are no query hints to affect row distribution among threads, the important thing is to be aware of the possibility and to be able to read enough detail in the execution plan to determine when it is causing a problem.
It should now be clear that the nonclustered index plan is potentially more efficient, as you would expect; it is just poor distribution of work across threads at execution time that accounts for the performance issue.
Para completar o exemplo e ilustrar algumas das coisas que mencionei, uma maneira de obter uma melhor distribuição de trabalho é usar uma tabela temporária para conduzir a execução paralela:
Isso resulta em um plano que usa as buscas de índice mais eficientes, não apresenta um spool de tabela e distribui bem o trabalho entre os encadeamentos:
No meu sistema, esse plano é executado significativamente mais rápido do que a versão Clustered Index Scan.
Se você estiver interessado em aprender mais sobre os aspectos internos da execução de consultas paralelas, talvez queira assistir à gravação da minha sessão do PASS Summit 2013 .
Não é realmente uma questão de índice, é mais uma consulta mal escrita. Você tem apenas 100 valores exclusivos de nome, o que deixa uma contagem exclusiva de 5.000 por nome.
Então, para cada linha na tabela 1, você está juntando 5000 da tabela 2. Você pode dizer 25020004 linhas.
Tente isso, observe que é com apenas 1 índice, o que você listou.
E tempo:
Você não pode culpar os índices SQL por consultas mal formadas