Varreduras inesperadas durante a operação de exclusão usando WHERE IN

"Estou me perguntando por que o otimizador de consulta usaria o plano que usa atualmente."

Em outras palavras, a questão é por que o plano a seguir parece mais barato para o otimizador, em comparação com as alternativas (que são muitas ).

O lado interno da junção está essencialmente executando uma consulta da seguinte forma para cada valor correlacionado de BrowserID:

DECLARE @BrowserID smallint;

SELECT 
    tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
    tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);

Observe que o número estimado de linhas é 185.220 (não 289.013 ), pois a comparação de igualdade exclui implicitamente NULL(a menos que ANSI_NULLSseja OFF). O custo estimado do plano acima é de 206,8 unidades.

Agora vamos adicionar uma TOP (1)cláusula:

DECLARE @BrowserID smallint;

SELECT TOP (1)
    tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
    tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);

O custo estimado agora é de 0,00452 unidades. A adição do operador físico Top define uma meta de linha de 1 linha no operador Top. A questão torna-se então como derivar um 'objetivo de linha' para o Clustered Index Scan; ou seja, quantas linhas a varredura deve processar antes que uma linha corresponda ao BrowserIDpredicado?

A informação estatística disponível mostra 166 valores distintos BrowserID(1/[All Density] = 1/0.006024096 = 166). O cálculo de custos assume que os valores distintos são distribuídos uniformemente pelas linhas físicas, de modo que a meta da linha no Clustered Index Scan é definida como 166,302 (considerando a alteração na cardinalidade da tabela desde que as estatísticas amostradas foram reunidas).

O custo estimado de varredura das 166 linhas esperadas não é muito grande (mesmo executado 339 vezes, uma vez para cada alteração de BrowserID) - o Clustered Index Scan mostra um custo estimado de 1,3219 unidades, mostrando o efeito de escala da meta de linha. Os custos do operador sem escala para E/S e CPU são mostrados como 153,931 e 52,8698 , respectivamente:

Na prática, é muito improvável que as primeiras 166 linhas varridas do índice (em qualquer ordem em que sejam retornadas) contenham um de cada um dos BrowserIDvalores possíveis. No entanto, o DELETEplano tem um custo total de 1,40921 unidades e é selecionado pelo otimizador por esse motivo. Bart Duncan mostra outro exemplo desse tipo em um post recente intitulado Row Goals Gone Rogue .

Também é interessante notar que o operador Top no plano de execução não está associado ao Anti Semi Join (em particular o 'curto-circuito' que Martin menciona). Podemos começar a ver de onde vem o Top desabilitando primeiro uma regra de exploração chamada GbAggToConstScanOrTop :

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

Esse plano tem um custo estimado de 364.912 , e mostra que o Top substituiu um Group By Aggregate (agrupamento pela coluna correlacionada BrowserID). A agregação não se deve à redundância DISTINCTno texto da consulta: é uma otimização que pode ser introduzida por duas regras de exploração, LASJNtoLASJNonDist e LASJOnLclDist . Desativar esses dois também produz este plano:

DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');

Esse plano tem um custo estimado de 40729,3 unidades.

Sem a transformação de Group By para Top, o otimizador 'naturalmente' escolhe um plano hash join com BrowserIDagregação antes do anti semi join:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

E sem a restrição MAXDOP 1, um plano paralelo:

Outra maneira de 'consertar' a consulta original seria criar o índice ausente nos BrowserIDrelatórios do plano de execução. Os loops aninhados funcionam melhor quando o lado interno é indexado. Estimar a cardinalidade para semijunções é desafiador na melhor das hipóteses. Não ter uma indexação adequada (a tabela grande nem tem uma chave exclusiva!) não ajudará em nada.

Escrevi mais sobre isso em Row Goals, Part 4: The Anti Join Anti Pattern .

Quando executo seu script para criar um banco de dados somente de estatísticas e a consulta na pergunta, obtenho o seguinte plano.

As Cardinalidades da Tabela mostradas no plano são

• tblFEStatsPaperHits:48063400
• tblFEStatsBrowsers:339

Assim, estima que precisará realizar a varredura em tblFEStatsPaperHits339 vezes. Cada varredura tem o predicado correlacionado tblFEStatsBrowsers.BrowserID=tblFEStatsPaperHits.BrowserID AND tblFEStatsPaperHits.BrowserID IS NOT NULLque é inserido no operador de varredura.

No entanto, o plano não significa que haverá 339 varreduras completas. Como está sob um operador anti semi join, assim que a primeira linha correspondente em cada varredura é encontrada, pode causar um curto-circuito no restante. O custo estimado da subárvore para este nó é 1.32603e o plano inteiro tem um custo de 1.41337.

Para o Hash Join dá o plano abaixo

O plano geral tem 418.415um custo (cerca de 300 vezes mais caro do que o plano de loops aninhados) com a varredura de índice clusterizada completa única tblFEStatsPaperHitscustando 206.8apenas um. Compare isso com a 1.32603estimativa de 339 varreduras parciais fornecidas anteriormente (Custo médio estimado de varredura parcial = 0.003911592).

Portanto, isso indicaria que cada varredura parcial está custando 53.000 vezes menos do que uma varredura completa. Se os custos fossem dimensionados linearmente com a contagem de linhas, isso significaria que, em média, seria necessário processar apenas 900 linhas em cada iteração antes de encontrar uma linha correspondente e causar um curto-circuito.

No entanto, não acho que os custos sejam dimensionados dessa maneira linear. Acho que eles também incorporam algum elemento de custo inicial fixo. Tentando vários valores de TOPna seguinte consulta

SELECT TOP 147 BrowserID 
FROM [dbo].[tblFEStatsPaperHits] 

147fornece o custo de subárvore estimado mais próximo de 0.003911592em 0.0039113. De qualquer forma, está claro que está baseando o custo na suposição de que cada varredura terá que processar apenas uma pequena proporção da tabela, na ordem de centenas de linhas em vez de milhões.

Não tenho certeza exatamente em que matemática ele baseia essa suposição e realmente não se soma às estimativas de contagem de linhas no restante do plano (as 236 linhas estimadas saindo da junção de loops aninhados implicariam que havia 236 casos em que nenhuma linha correspondente foi encontrada e uma verificação completa foi necessária). Presumo que este seja apenas um caso em que as suposições de modelagem feitas caem um pouco e deixam o plano de loops aninhados significativamente abaixo do custo.

No meu livro, mesmo uma varredura de 50 milhões de linhas é inaceitável ... Meu truque usual é materializar os valores distintos e delegar ao mecanismo mantê-lo atualizado:

create view [dbo].[vwFEStatsPaperHitsBrowserID]
with schemabinding
as
select BrowserID, COUNT_BIG(*) as big_count
from [dbo].[tblFEStatsPaperHits]
group by [BrowserID];
go

create unique clustered index [cdxVwFEStatsPaperHitsBrowserID] 
  on [vwFEStatsPaperHitsBrowserID]([BrowserID]);
go

Isso fornece um índice materializado de uma linha por BrowserID, eliminando a necessidade de verificar 50 milhões de linhas. O mecanismo irá mantê-lo para você e o QO irá usá-lo 'como está' na declaração que você postou (sem qualquer sugestão ou reescrita de consulta).

A desvantagem é, obviamente, a contenção. Qualquer operação de inserção ou exclusão tblFEStatsPaperHits(e eu acho que é uma tabela de log com inserções pesadas) terá que serializar o acesso a um determinado BrowserID. Existem maneiras de tornar isso viável (atualizações atrasadas, registro em dois estágios, etc.) se você estiver disposto a comprá-lo.