O abaixo é uma versão simplificada de algo que encontrei na produção (onde o plano ficou catastroficamente pior em um dia em que um número excepcionalmente alto de lotes foi processado).
A reprodução foi testada em 2014 e 2016 com o novo estimador de cardinalidade.
CREATE TABLE T1 (FromDate DATE, ToDate DATE, SomeId INT, BatchNumber INT);
INSERT INTO T1
SELECT TOP 1000 FromDate = '2017-01-01',
ToDate = '2017-01-01',
SomeId = ROW_NUMBER() OVER (ORDER BY @@SPID) -1,
BatchNumber = 1
FROM master..spt_values v1
CREATE TABLE T2 (SomeDateTime DATETIME, SomeId INT, INDEX IX(SomeDateTime));
INSERT INTO T2
SELECT TOP 1000000 '2017-01-01',
ROW_NUMBER() OVER (ORDER BY @@SPID) %1000
FROM master..spt_values v1,
master..spt_values v2
T1contém 1.000 linhas.
Os FromDate, ToDate, e BatchNumbersão idênticos em todos eles. O único valor que difere é SomeIdcom valores entre 0e999
+------------+------------+--------+-----------+
| FromDate | ToDate | SomeId | BatchNumber |
+------------+------------+--------+-----------+
| 2017-01-01 | 2017-01-01 | 0 | 1 |
| 2017-01-01 | 2017-01-01 | 1 | 1 |
....
| 2017-01-01 | 2017-01-01 | 998 | 1 |
| 2017-01-01 | 2017-01-01 | 999 | 1 |
+------------+------------+--------+-----------+
T2contém 1 milhão de linhas
mas apenas 1.000 distintos. Cada um repetido 1.000 vezes como abaixo.
+-------------------------+--------+-------+
| SomeDateTime | SomeId | Count |
+-------------------------+--------+-------+
| 2017-01-01 00:00:00.000 | 0 | 1000 |
| 2017-01-01 00:00:00.000 | 1 | 1000 |
...
| 2017-01-01 00:00:00.000 | 998 | 1000 |
| 2017-01-01 00:00:00.000 | 999 | 1000 |
+-------------------------+--------+-------+
Executando o seguinte
SELECT *
FROM T1
INNER JOIN T2
ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
AND T1.SomeId = T2.SomeId
WHERE T1.BatchNumber = 1
Demora cerca de 7 segundos na minha máquina. As linhas reais e estimadas são perfeitas para todos os operadores no plano.
Agora adicione 3.000 lotes adicionais a T1 (com números de lote de 2 a 3001). Cada um deles clona as mil linhas existentes para o lote número 1
INSERT INTO T1
SELECT T1.FromDate,
T1.ToDate,
T1.SomeId,
Nums.NewBatchNumber
FROM T1
CROSS JOIN (SELECT TOP (3000) 1 + ROW_NUMBER() OVER (ORDER BY @@SPID) AS NewBatchNumber
FROM master..spt_values v1, master..spt_values v2) Nums
e atualize as estatísticas para dar sorte
UPDATE STATISTICS T1 WITH FULLSCAN
E execute a consulta original novamente.
SELECT *
FROM T1
INNER JOIN T2
ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
AND T1.SomeId = T2.SomeId
WHERE T1.BatchNumber = 1
Deixei correr por um minuto antes de matá-lo. Naquela época, ele havia gerado 40.380 linhas, então acho que levaria 25 minutos para produzir o milhão completo.
A única coisa que mudou é que adicionei algumas linhas adicionais que não correspondiam ao T1.BatchNumber = 1predicado.
No entanto, o plano agora mudou. Em vez disso, ele usa loops aninhados e, embora o número de linhas que saem t1ainda seja estimado corretamente em 1.000 (①), a estimativa do número de linhas unidas agora caiu de 1 milhão para mil (②).
Então a pergunta é...
Por que adicionar linhas adicionais de BatchNumber <> 1alguma forma afeta as estimativas para linhas unidas quando BatchNumber = 1?
Certamente parece contra-intuitivo que adicionar linhas a uma tabela acabe reduzindo o número estimado de linhas da consulta como um todo.
É importante lembrar que não há garantia de consistência conforme você altera as consultas ou os dados nas tabelas. O otimizador de consulta pode alternar para o uso de um método diferente de estimativa de cardinalidade (como o uso de densidade em vez de histogramas), o que pode fazer com que duas consultas pareçam inconsistentes entre si. Com isso dito, certamente parece que o otimizador de consulta está fazendo uma escolha irracional no seu caso, então vamos nos aprofundar.
Sua demonstração é muito complicada, então vou trabalhar com um exemplo mais simples que, acredito, mostra o mesmo comportamento. Iniciando a preparação de dados e definições de tabela:
Aqui está a
SELECTconsulta para investigar:Essa consulta é simples o suficiente para que possamos elaborar a fórmula para a estimativa de cardinalidade sem nenhum sinalizador de rastreamento. No entanto, tentarei usar o TF 2363 conforme for para ilustrar melhor o que está acontecendo dentro do otimizador. Não está claro se terei sucesso.
Defina as seguintes variáveis:
C1= número de linhas na tabela T1C2= número de linhas na tabela T2S1= a seletividade doT1.SomeIdfiltroMinha alegação é que a estimativa de cardinalidade para a consulta acima é a seguinte:
C2S1C1C2S1C1Vamos passar por alguns exemplos, embora eu não vá passar por todos os que testei. Para a preparação de dados inicial, temos:
C1= 1000C2= 2S1= 1,0Portanto, a estimativa de cardinalidade deve ser:
A captura de tela impossível de falsificar abaixo prova isso:
Usando o sinalizador de rastreamento não documentado 2363, podemos obter algumas pistas sobre o que está acontecendo:
Com o novo CE, obtemos a estimativa usual de 16% para um
BETWEEN. Isso se deve ao recuo exponencial com o novo 2014 CE. Cada desigualdade tem uma estimativa de cardinalidade de 0,3, entãoBETWEENé calculada como 0,3 * sqrt(0,3) = 0,164317. Multiplique a seletividade de 16% pelo número de linhas em T2 e T1 e obtemos nossa estimativa. Parece bastante razoável. Vamos aumentar o número de linhasT2para 7. Agora temos o seguinte:C1= 1000C2= 7S1= 1,0Portanto, a estimativa de cardinalidade deve ser 1000 porque:
O plano de consulta confirma:
Podemos dar outra olhada no TF 2363, mas parece que a seletividade foi ajustada nos bastidores para respeitar o limite superior. Suspeito que isso
CSelCalcSimpleJoinWithUpperBoundimpede que a estimativa de cardinalidade ultrapasse 1000.Vamos aumentar
T2para 50.000 linhas. Agora temos:C1= 1000C2= 50.000S1= 1,0Portanto, a estimativa de cardinalidade deve ser:
O plano de consulta confirma novamente. É muito mais fácil adivinhar a estimativa depois que você já descobriu a fórmula:
Saída TF:
Para este exemplo, a retirada exponencial parece ser irrelevante:
Agora vamos adicionar 3k linhas a T1 com um
SomeIdvalor de 0. Codifique para fazer isso:Agora temos:
C1= 4000C2= 50.000S1= 0,25Portanto, a estimativa de cardinalidade deve ser:
O plano de consulta confirma:
Este é o mesmo comportamento que você chamou na pergunta. Adicionei linhas irrelevantes a uma tabela e a estimativa de cardinalidade diminuiu. Por que isso aconteceu? Preste atenção nas linhas em negrito:
Seletividade: 0,25
Seletividade: 0,00025
Parece que a estimativa de cardinalidade para este caso foi calculada como:
C1* * * / ( * )S1C2S1S1C1Or for this particular example:
The general formula can of course can be simplified to:
C2*S1Which is the formula that I claimed above. It seems like there's some cancellation going on that shouldn't be. I would expect the total number of rows in
T1to be relevant to the estimate.If we insert more rows into
T1we can see the lower bound in action:The cardinality estimate in this case is 1000 rows. I will omit the query plan and the TF 2363 output.
Para encerrar, esse comportamento é bastante suspeito, mas não sei o suficiente para declarar se é um bug ou não. Meu exemplo não corresponde exatamente à sua reprodução, mas acredito que observei o mesmo comportamento geral. Também eu diria que você tem um pouco de sorte com a forma como escolheu seus dados iniciais. Parece haver uma quantidade razoável de suposições acontecendo pelo otimizador, então eu não ficaria muito preso ao fato de que a consulta original retornou 1 milhão de linhas que correspondiam exatamente à estimativa.