Então, eu gostaria de entender um conceito do MSSQL que é um pouco interessante para mim, mas não encontrei nenhuma resposta online até agora.
Tenho uma consulta que roda em uma tabela com mais de 100 mil linhas. Digamos que tenho algumas linhas que foram criadas em '2024-04-14', e não há linhas mais antigas do que isso.
A consulta procura entradas com este filtro:
CreatedAt < '2024-04-15'
A consulta funciona bem e retornará as linhas criadas em '2024-04-14'.
Mas se eu fizer isso:
CreatedAt < '2024-04-14'
A consulta será executada até retornar um tempo limite ou demorará muito para retornar um conjunto de resultados vazio, que é o resultado esperado, já que não temos linhas anteriores a '2024-04-15'.
Você pode me dizer qual conceito de SQL ou MSSQL está envolvido aqui e como posso evitar isso? O problema é que essa consulta é usada para recuperar dados para arquivamento, esses dados serão excluídos, tudo acontece em lotes. Temos um período de retenção de 150 dias, então, usando essas informações, calculamos uma data que usamos no filtro. Depois que o trabalho de arquivamento for executado algumas vezes e arquivar todos os dados encontrados, a consulta parará de encontrar dados mais antigos do que a data especificada, mas continuará a ser executada pelo resto do dia.
ATUALIZAÇÃO 1
Então voltei com mais detalhes.
A mesa:
CREATE TABLE [dbo].[Notifications](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[AlertId] [int] NOT NULL,
[ChannelId] [int] NOT NULL,
[Status] [int] NOT NULL,
[Identifier] [nvarchar](1024) NOT NULL,
[CreatedAt] [datetimeoffset](7) NOT NULL,
[CanBeRetried] [bit] NULL,
[RetryCount] [int] NOT NULL,
...
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Notifications_CreatedAt] ON [dbo].[Notifications]
(
[CreatedAt] ASC
)
CREATE NONCLUSTERED INDEX [IX_Notifications_AlertId_ChannelId_CreatedAt_Include_Identifier_Status_CanBeRetried_RetryCount] ON [dbo].[Notifications]
(
[AlertId] ASC,
[ChannelId] ASC,
[CreatedAt] ASC
)
INCLUDE([Identifier],[Status],[CanBeRetried],[RetryCount])
CREATE NONCLUSTERED INDEX [IX_Notifications_AlertId_ChannelId_Identifier] ON [dbo].[Notifications]
(
[AlertId] ASC,
[ChannelId] ASC,
[Identifier] ASC
)
CREATE NONCLUSTERED INDEX [IX_Notifications_CreatedAt_Status_Include_AlertId] ON [dbo].[Notifications]
(
[CreatedAt] ASC,
[Status] ASC
)
INCLUDE([AlertId])
Agora, os cenários + planos de execução. Para mais contexto, estou selecionando notificações que desejo arquivar, notificações são produzidas por um Alerta e desejo excluir do processo de arquivamento notificações que são produzidas por certos Alertas.
- Criado em < '2024-04-14 09:00:00' E [n].[AlertId] NÃO EM ( '76', '126', '127', '128', '129')
https://www.brentozar.com/pastetheplan/?id=ByV4zrlaA
Este leva cerca de 11 minutos para processar. Não há notificações presentes além das excluídas, mais antigas do que essa data. Então isso produzirá um conjunto de resultados vazio. O otimizador escolheu uma varredura de índice clusterizado.
- Criado em < '2024-04-15 09:00:00' E [n].[AlertId] NÃO EM ( '76', '126', '127', '128', '129')
https://www.brentozar.com/pastetheplan/?id=SJldhHxa0
Este leva cerca de 2 segundos para processar. Há notificações presentes além das excluídas, mais antigas que essa data. O otimizador escolheu, novamente, o mesmo Clustered Index Scan.
- Criado em < '2023-09-13 11:00:00' E [n].[AlertId] NÃO EM ( '76', '126', '127', '128', '129')
https://www.brentozar.com/pastetheplan/?id=SJkp2Hl6R
Este leva 1 segundo. Não há nenhuma notificação, mais antiga que essa data. Este usa um Index Seek no índice IX_Notifications_CreatedAt.
Também encontrei isso em uma postagem: https://stackoverflow.com/questions/12846283/why-is-sql-server-not-using-index-for-very-similar-datetime-query
À medida que o intervalo cresce (e, portanto, o número de pesquisas necessárias), ele estima que será mais rápido apenas escanear todo o índice agrupado (de cobertura) evitando as pesquisas. Possivelmente incorretamente no seu caso. O ponto em que ele muda de um plano para o outro é conhecido como ponto de inflexão.
Essa resposta me deixaria satisfeito se o desempenho fosse ruim nos cenários 1 e 2, mas só ficou lento no cenário 1.
linhas e gols
O problema que você está enfrentando quando os dados não estão lá é um tanto(?) comum. Eu bloguei sobre isso em um post útil intitulado:
Ao usar
TOPem sua consulta, você definiu uma meta de linha para o otimizador. Paul White blogou sobre isso em um post útil intitulado:A história curta é que mesmo quando sua consulta pede dados que não estão lá, o SQL Server continua procurando por eles, tentando satisfazer as 2000 linhas que você solicitou dele. Afinal, o SQL Server opera sob a suposição de que você não desperdiçaria seu tempo procurando por linhas inexistentes.
Para as duas consultas rápidas, o SQL Server encontra 2000 linhas bem rápido. Os planos de consulta postados não correspondem às suas definições de tabela e índice, no entanto. A tabela Notifications tem apenas Id como a chave de índice clusterizado, e o índice não clusterizado tem apenas CreatedAt como uma coluna de chave, mas é capaz de gerar a coluna AlertId e aplicar um predicado a ela. Tem certeza de que forneceu as coisas como estão para todos os três planos de consulta?
De qualquer forma, quando você pede dados que não estão lá, o SQL Server gasta muito mais tempo tentando encontrar essas 2000 linhas, o que requer a leitura de todo o índice clusterizado. Como você está em alguma versão maldita do Microsoft Cloud do SQL Server, seu armazenamento é horrível e você não tem memória suficiente para manter os dados que você se importa em cache na memória. Se eles estivessem em cache na memória, você teria muito menos problemas.
Você acaba esperando por todas essas coisas:
O mais insultuoso é que você gasta 545 segundos esperando para ler páginas do disco para a memória (PAGEIOLATCH_SH) e 132 segundos fazendo solicitações http para aquele péssimo armazenamento em nuvem (PREEMPTIVE_HTTP_REQUEST).
O motivo pelo qual perguntei sobre as definições de tabela e índice anteriormente é porque você normalmente só vê o SQL Server tendo dificuldade em escolher índices não clusterizados não abrangentes, o que o seu não parece estar nos planos que você postou. Não há nenhuma Key Lookup para se envolver em decisões de custo entre clusterizados e não clusterizados. Se você puder fornecer algum esclarecimento, posso atualizar minha resposta com resoluções.
Desde:
Talvez você precise usar esta dica para obter um desempenho consistente:
OPTION(USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));, ou usar uma dica de índice para forçar o uso de[IX_Notifications_CreatedAt_Status_Include_AlertId].