Varredura de índice de tabela com um registro com 2,2 bilhões de execuções

Vamos começar olhando para o canto superior direito do plano. Essa parte calcula a OperatingDatecoluna:

Como recebemos de volta 1,72 M de linhas para o conjunto de linhas externas, podemos esperar cerca de 1,72 M de buscas de índice ix_days. Isso é de fato o que acontece. Existem 478k linhas para as quais o.[CreationDate] as time) > '16:00:00'a CASEinstrução envia 478k buscas para um ramo e o restante para o outro.

Observe que o índice que você possui não é o mais eficiente possível para essa consulta. Só podemos fazer um predicado de busca contra PKDate. O resto dos filtros são aplicados como um predicado. Isso significa que a busca pode percorrer muitas linhas antes de encontrar uma correspondência. Presumo que a maioria dos dias em sua tabela de calendário não sejam fins de semana ou feriados, portanto, pode não fazer uma diferença prática para essa consulta. No entanto, você pode definir um índice em is_weekend, is_holiday, PKDate. Isso deve permitir que você procure imediatamente a primeira linha desejada.

Para deixar o ponto mais claro, vamos passar por um exemplo simples:

-- does a scan
SELECT TOP 1 PkDate
FROM [Days]
WHERE is_weekend <> 1 AND is_holiday <> 1
AND PkDate >= '2000-04-01'
ORDER BY PkDate;

-- does a seek, reads 3 rows to return 1
SELECT TOP 1 PkDate
FROM [Days]
WHERE is_weekend = 0 AND is_holiday = 0
AND PkDate >= '2000-04-01'
ORDER BY PkDate;

-- create new index
CREATE NONCLUSTERED INDEX [ix_days_2] ON [dbo].[days]
(
    [is_weekend],
    [is_holiday],
    PkDate
)

-- does a seek, reads 1 row to return 1
SELECT TOP 1 PkDate
FROM [Days]
WHERE is_weekend = 0 AND is_holiday = 0
AND PkDate >= '2000-04-01'
ORDER BY PkDate;

DROP INDEX [days].[ix_days_2];

Vamos para a parte mais interessante que é a ramificação para calcular a DeliveryDatecoluna. Vou incluir apenas metade dele:

Suspeito que o que você esperava que o otimizador fizesse fosse calcular isso como um escalar:

dateadd(day,isnull(
                  (select top 1 [operatingdays]
                  from [dbo].[CS]
                  where DefaultService = 1)
                 ,2)+1,Cast(o.[CreationDate] as date))

E para usar o valor disso para fazer um índice procure usando ix_days. Infelizmente, o otimizador não faz isso. Em vez disso, ele aplica uma meta de linha ao índice e faz uma varredura. Para cada linha retornada da varredura, ele verifica se o valor corresponde ao filtro em relação a [dbo].[CS]. A varredura é interrompida assim que encontra uma linha correspondente. O SQL Server estimou que só recuperaria 3,33 linhas em média da verificação até encontrar uma correspondência. Se isso fosse verdade, você veria cerca de 1,5 milhão de execuções contra [dbo].[CS]. Em vez disso, o otimizador fez 2 bilhões de execuções na tabela, então a estimativa foi mais de 1.000 vezes errada.

Como regra geral, você deve examinar cuidadosamente quaisquer varreduras no lado interno de um loop aninhado. Claro, existem algumas consultas para as quais é isso que você deseja. E só porque você tem uma busca não significa que a consulta será eficiente. Por exemplo, se uma busca retornar muitas linhas, pode não haver muita diferença em fazer uma varredura. Você não postou a consulta completa aqui, mas vou passar por algumas ideias que podem ajudar.

Esta consulta é um pouco estranha:

select top 1 [operatingdays]
from [dbo].[CS]
where DefaultService = 1

É não determinista porque você tem TOPsem ORDER BY. No entanto, a própria tabela tem 1 linha e você sempre recupera o mesmo valor para cada linha de o. Se possível, eu tentaria salvar o valor dessa consulta em uma variável local e usar isso na consulta. Isso deve economizar um total de 8 bilhões de varreduras novamente [dbo].[CS]e eu esperaria ver uma busca de índice em vez de uma varredura de índice contra ix_days. Consegui simular alguns dados na minha máquina. Aqui está parte do plano de consulta:

Agora temos todas as buscas e essas buscas não devem processar muitas linhas extras. No entanto, a consulta real pode ser mais complicada do que isso, então talvez você não possa usar uma variável.

Digamos que eu escreva uma condição de filtro diferente que não use TOP. Em vez disso, usarei MIN. O SQL Server é capaz de processar essa subconsulta de maneira mais eficiente. O TOP pode impedir certas transformações de consulta. Aqui está minha subconsulta:

WHERE PKDate > dateadd(day,isnull(
                      (select MIN([operatingdays])
                       from [dbo].[CS]
                       where DefaultService = 1)
                      ,2), Cast(o.[CreationDate] as date))

Veja como pode ser o plano:

Agora faremos apenas cerca de 1,5 milhão de varreduras na CSmesa. também obtemos uma busca de índice muito mais eficiente em relação ao ix_daysíndice que é capaz de usar os resultados da subconsulta:

Claro, não estou dizendo que você deve reescrever seu código para usar isso. Provavelmente retornará resultados incorretos. O ponto importante é que você pode obter as buscas de índice que deseja com uma subconsulta. Você só precisa escrever sua subconsulta da maneira correta.

For one more example, let's assume that you absolutely need to keep the TOP operator in the subquery. It might be possible to add a redundant filter against PkDate to get better performance. I'm going to assume that the results of the subquery are non-negative and small. That means that this query will be equivalent:

  PKDate > Cast(o.[CreationDate] as date) AND 
  PKDate > dateadd(day,isnull(
      (select top 1 [operatingdays]
      from [dbo].[CS]
      where DefaultService = 1)
     ,2)+1,Cast(o.[CreationDate] as date))

This changes the plan to use seeks:

It's important to realize that the seeks may return more just one row. The important point is that SQL Server can start seeking at o.[CreationDate]. If there's a large gap in the dates then the index seek will process many extra rows and the query will not be as efficient.

Agora a pergunta é, relacionada a scans de índice e número de execuções: por que os 2 BILHÕES? Ou 6 bilhões?

Você está obtendo esses números da junção de loop aninhado .

Em sua forma mais simples, uma junção de loops aninhados compara cada linha de um

table (conhecida como tabela externa) para cada linha da outra tabela (conhecida como tabela interna) procurando linhas que satisfaçam o predicado de junção. (Observe que os termos “interno” e “externo” estão sobrecarregados; seu significado deve ser inferido a partir do contexto. “Tabela interna” e “tabela externa” referem-se às entradas para a junção. para as operações lógicas.)

Podemos expressar o algoritmo em pseudocódigo como:

para cada linha R1 na tabela externa para cada linha R2 na tabela interna se R1 se unir com R2 return (R1, R2)

É o aninhamento dos loops for neste algoritmo que dá aos loops aninhados o seu nome.

O número total de linhas comparadas e, portanto, o custo desse algoritmo é proporcional ao tamanho da tabela externa multiplicado pelo tamanho da tabela interna. Como esse custo cresce rapidamente à medida que o tamanho das tabelas de entrada aumenta, na prática tentamos minimizar o custo reduzindo o número de linhas internas que devemos considerar para cada linha externa.] 1

No seu, este é um exemplo de como você obtém cerca de 2B registros.

Outra como você obtém 5B+.

Alguns links sobre como você pode evitar grandes associações de loops aninhados:

1. Como otimizar uma consulta lenta em loops aninhados (junção interna)
2. https://stackoverflow.com/questions/28441468/why-are-nested-loops-chosen-causing-long-execution-time-for-self-join
3. https://www.littlekendra.com/2016/09/06/estimated-vs-actual-number-of-rows-in-nested-loop-operators/

The information that both other answers try to convey but fail (only partly due to assumptions that I understand exactly what they say) is this:

With the query written the way it was in the question the observed performance was inevitable.

While it was fancy and mostly easy to see the purpose it was simply too heavy for the optimizer to work magic on it. It wasn't quite the nested loop problem SqlWorldWide indicated, but the subqueries simply had to be executed for each row and since they were index seeks and scans they multiplied, and multiplied... and multiplied.

What I ended up having was this:

Select
    OID
   ,case when
     Cast(o.[CreationDate] as time) > '16:00:00' 
        then (select top 1 [PKDate] from [calendar].[dbo].days
              where is_workday = 1 and continuous_day > da.continuous_day
              and continuous_day < da.continuous_day+7 order by PKDate asc)
        else (select top 1 [PKDate] from [calendar].[dbo].days
              where is_workday = 1 and continuous_day >= da.continuous_day
              and continuous_day < da.continuous_day+7 order by PKDate asc)
        end  OperatingDate
   ,case when
     Cast(o.[CreationDate] as time) > '16:00:00' 
        then (select top 1 d.[PKDate] from [calendar].[dbo].days d
              where is_workday = 1 and
              continuous_day > (da.continuous_day+isnull(dt.DeliveryDays,2)) and 
              d.continuous_day < da.continuous_day+7 order by PKDate asc)
            else (select top 1 d.[PKDate] from [calendar].[dbo].days d
              where is_workday = 1 and
              continuous_day >= (da.continuous_day+isnull(dt.DeliveryDays,2)) and 
              d.continuous_day < da.continuous_day+7 order by PKDate asc)
            end EstimatedDeliveryDate
  ,(select dateadd(day,3,o.[CreationDate])) DeliveryDate
From o
left join deliverytype dt on o.deliverytypeid = dt.deliverytypeid
join calendar.dbo.days da on (cast o.creationdate as date) = da.pkdate

In addition to streamlining the query - which still is not optimal - I've also reworked the calendar.dbo.days table's indexes. Dropped the constraint (which I really didn't have to, but what the hell, it might cause more problems further down the line) and added this:

/****** Object:  Index [ixc_days]    Script Date: 20/04/2017 10:40:58 ******/
CREATE UNIQUE CLUSTERED INDEX [ixc_days] ON [dbo].[days]
(
    [PKDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_days]    Script Date: 20/04/2017 10:40:58 ******/
CREATE NONCLUSTERED INDEX [ix_days] ON [dbo].[days]
(
    [is_workday] ASC,
    [PKDate] ASC,
    [continuous_day] ASC
)
INCLUDE (   [calendar_year],
    [calendar_month],
    [calendar_week_in_year],
    [calendar_week_in_month],
    [calendar_day_in_year],
    [calendar_day_in_week],
    [calendar_day_in_month],
    [dmy_name_long],
    [description]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Admito que é principalmente para que eu possa utilizar mais plenamente a tabela de calendário (eu mencionei que Jim Horn é um gênio?), mas no momento em que minhas contas as pessoas viram, elas querem cada vez mais as guloseimas que ela armazena ... em todos os lugares .

A conclusão é que, embora seja importante observar todos os aspectos da consulta: lógica, índices, predicados etc., às vezes a única maneira sensata de melhorar as coisas é alterar o código. No meu caso, o tempo de execução da consulta completa (várias inserções, atualizações e CTEs) agora termina em cerca de 2 minutos, em comparação com 15 minutos antes.