Recentemente, alterei um índice em uma tabela e vi uma perda enorme no desempenho de uma consulta. Gostaria de entender por que isso aconteceu.
Esta é a consulta. A chave estrangeira assim como From
e To
estão mudando, mas o resto permanece e é repetido frequentemente.
SELECT COUNT(*)
FROM Table_With_Values
WHERE FK_ObjectTheValuesBelongTo_Id = 460
AND [From]>=CONVERT([datetime2](3),'07.10.2024 00:00:00',(104))
AND [To]<=CONVERT([datetime2](3),'08.10.2024 00:00:00',(104))
A princípio, o índice da tabela TableWithValues
parecia com isto:
CREATE NONCLUSTERED INDEX [Idx_TableWithValues_Fk_ObjectTheValuesBelongTo_Id_From_To] ON [dbo].[TableWithValues]
(
[Fk_ObjectTheValuesBelongTo_Id] ASC,
[From] ASC,
[To] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
Então li a documentação do SQL Server e vi isto:
Considere a ordem das colunas se o índice contiver várias colunas. A coluna que é usada na cláusula WHERE em uma condição de pesquisa igual a (=), maior que (>), menor que (<) ou BETWEEN, ou participa de uma junção, deve ser colocada primeiro. Colunas adicionais devem ser ordenadas com base em seu nível de distinção, ou seja, da mais distinta para a menos distinta .
As colunas From
and To
têm cerca de 140.000 valores distintos, enquanto a coluna foreign key tem apenas 1.600. Portanto, decidi mudar a ordem delas e fiz o índice ficar assim:
CREATE NONCLUSTERED INDEX [Idx_TableWithValues_From_To_Fk_ObjectTheValuesBelongTo_Id] ON [dbo].[TableWithValues]
(
[From] ASC,
[To] ASC,
[Fk_ObjectTheValuesBelongTo_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
No entanto, isso fez com que o desempenho dessa consulta se deteriorasse. Levou 1.000 vezes mais tempo para ser executado. Sei disso porque ativei o repositório de consultas em uma cópia do banco de dados. Então executei o sistema de TI que envia as consultas e verifiquei o valor avg_duration
da tabela query_store_runtime_stats
.
A tabela em si se parece com isso:
CREATE TABLE [dbo].[TableWithValues](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Fk_ObjectTheValuesBelongTo_Id] [int] NOT NULL,
[Value] [decimal](9, 3) NOT NULL,
[From] [smalldatetime] NOT NULL,
[To] [smalldatetime] NOT NULL,
CONSTRAINT [Pk_TableWithValues_Id] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [Uq_TableWithValues_ObjectTheValuesBelongTo_Id_From] UNIQUE NONCLUSTERED
(
[Fk_ObjectTheValuesBelongTo_Id] ASC,
[From] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TableWithValues] WITH CHECK ADD CONSTRAINT [Fk_TableWithValues_ObjectTheValuesBelongTo_Id] FOREIGN KEY([Fk_ObjectTheValuesBelongTo_Id])
REFERENCES [dbo].[ObjectTheValuesBelongTo] ([Id])
GO
O plano de consulta para execuções com ambos os índices parece quase o mesmo. Há duas diferenças. Eles usam índices diferentes (ocultos na captura de tela) e as porcentagens são 13% e 87% (Fk first in Index) em um caso e 20% e 80% (Fk last in index) no outro.
Alguém tem alguma ideia de por que meu banco de dados não se comporta como seria de se esperar ao ler a documentação?
Você perdeu o resto do parágrafo, que explica indiretamente a melhor maneira de indexar.
Adicionei numeração para que você possa ver o que ele está tentando dizer:
Então, no seu caso,
FK_ObjectTheValuesBelongTo_Id
é uma igualdade, então vai primeiro. Isso é independente de quão distinta ela é. Mesmo se for umabit
coluna, se for uma igualdade, ela vai primeiro.Na minha opinião, a decisão sobre outras colunas (desigualdade, junção e outras) depende principalmente de se há requisitos de consulta para agrupamento e classificação, e qual é a cardinalidade do resultado após os predicados de desigualdade. Se a tabela for grande e o predicado estiver filtrando muito, classificar novamente mais tarde não importará, enquanto que se estiver removendo apenas uma pequena porcentagem, a classificação posterior será lenta e precisará do índice para otimização.
Quaisquer outras colunas que não estejam sendo pesquisadas, apenas selecionadas, devem estar no
INCLUDE
, não na chave. A ordem não importa.E se você tiver duas ou mais desigualdades ou unir colunas, então você não pode indexar para ambas. Você precisa decidir qual vai filtrar melhor, então coloque o resto das colunas como
INCLUDE
s.Então o melhor índice é
ou
Além disso, o índice não está sendo buscado corretamente, porque você está passando a
datetime2
em vez desmalldatetime
. Você precisa usar o tipo correto.