Daniel Hutmacher's questions

Eu tenho uma tabela de relatórios (cerca de 1 bilhão de linhas) e uma pequena tabela de dimensões:

CREATE TABLE dbo.Sales_unpartitioned (
    BusinessUnit    int NOT NULL,
    [Date]          date NOT NULL,
    SKU             varchar(8) NOT NULL,
    Quantity        numeric(10, 2) NOT NULL,
    Amount          numeric(10, 2) NOT NULL,
    CONSTRAINT PK_Sales_unpartitioned PRIMARY KEY CLUSTERED (BusinessUnit, [Date], SKU)
);

--- Demo data:
INSERT INTO dbo.Sales_unpartitioned
SELECT severity AS BusinessUnit,
       DATEADD(day, message_id, '2000-01-01') AS [Date],
       LEFT([text], 3) AS SKU,
       1000.*RAND(CHECKSUM(NEWID())) AS Quantity,
       10000.*RAND(CHECKSUM(NEWID())) AS Amount
FROM sys.messages
WHERE [language_id]=1033;

--- Artificially inflate statistics of demo data:
UPDATE STATISTICS dbo.Sales_unpartitioned WITH ROWCOUNT=1000000000;

--- Dimension table:
CREATE TABLE dbo.BusinessUnits (
    BusinessUnit    int NOT NULL,
    SalesManager    nvarchar(250) NULL,
    PRIMARY KEY CLUSTERED (BusinessUnit)
);

INSERT INTO dbo.BusinessUnits (BusinessUnit)
SELECT DISTINCT BusinessUnit FROM dbo.Sales;

... ao qual adicionei uma exibição de relatórios usada por um aplicativo para relatórios no estilo OLTP.

CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS

SELECT bu.BusinessUnit,
       s.[Date],
       s.SKU,
       s.Quantity,
       s.Amount
FROM dbo.BusinessUnits AS bu
CROSS APPLY (
    --- Regular sales
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM dbo.Sales_unpartitioned AS t
    WHERE t.BusinessUnit=bu.BusinessUnit
      AND t.SKU LIKE 'T%'

    UNION ALL

    --- This is a special reporting entry. We only
    --- want to see today's row. In case of duplicates,
    --- get the row with the first "SKU".
    SELECT TOP (1) s.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount
    FROM dbo.Sales_unpartitioned AS s
    WHERE s.BusinessUnit=bu.BusinessUnit
      AND s.[Date]=CAST(SYSDATETIME() AS date)
      AND s.SKU LIKE 'S%'
    ORDER BY s.BusinessUnit, s.[Date], s.SKU
) AS s

A ideia é que o aplicativo do usuário consulte essa exibição com uma consulta SELECT que filtra um intervalo de datas e uma ou mais Unidades de Negócios. Para isso, escolhi um CROSS APPLYpadrão, para que a consulta possa fazer um "loop" em cada Unidade de Negócios, buscar um intervalo de Datas e aplicar um filtro residual no SKU.

Exemplo de consulta de aplicativo:

DECLARE @from date='2021-01-01', @to date='2021-12-31';

SELECT *
FROM dbo.SalesReport_unpartitioned
WHERE BusinessUnit=16
  AND [Date] BETWEEN @from AND @to
ORDER BY BusinessUnit, [Date], SKU;

Eu esperaria um plano de consulta parecido com este: Plano desejado

No entanto, o plano fica assim: Plano real

Eu esperava que o SQL Server fizesse um "empilhamento de predicado" na coluna Data, permitindo que o Clustered Index Seek procurasse uma única unidade de negócios e um intervalo de datas e, em seguida, aplicasse um predicado residual no SKU. Isso funciona no Seek na ramificação "s" (aquele com TOP) - provavelmente porque tem um predicado Date codificado na consulta - mas não na ramificação "t".

No entanto, na ramificação "t" o SQL Server busca apenas a BusinessUnit específica com um predicado residual no SKU, recuperando efetivamente todas as datas . Somente no final do plano é aplicado um operador Filtro que filtra na coluna Data.

Em uma tabela grande, isso tem uma penalidade de desempenho muito significativa - você pode acabar lendo 20 anos de dados do disco quando tudo o que você procura é uma semana.

Coisas que eu tentei

Soluções alternativas:

• Converter a exibição em uma função com valor de tabela embutida com os parâmetros @fromDate e @toDate que filtram as consultas "s" e "t" habilitará uma Busca em (BusinessUnit, Date) conforme desejado, mas requer a reescrita do código do aplicativo.
• Mover a UNION ALLsaída de CROSS APPLY(de CROSS APPLY (UNION)para CROSS APPLY() UNION CROSS APPLY()) habilitará o pushdown de predicado. Faz mais uma busca na mesa da BusinessUnit, o que é perfeitamente aceitável.

Corrige o Seek, mas altera os resultados:

• Surpreendentemente, remover o TOP (1)and ORDER BYpara a consulta "s" faz o empilhamento de predicado funcionar em "t", mas pode retornar muitas linhas de "s".
• A eliminação UNION ALLremovendo a consulta "s" ou "t" habilitará o empilhamento de predicado, mas gerará resultados incorretos.

Sem alteração ou inviável:

• Substituir TOP (1)por um ROW_NUMBER()padrão não altera a Busca.
• Alterar o CROSS APPLYpara uma correção forçada INNER LOOP JOINdo Seek em "t", mas na verdade altera "s" para um Scan, o que é ainda pior.
• Adicionar o sinalizador de rastreamento 8780 para permitir que o otimizador trabalhe em um plano por mais tempo não altera nada. O plano já está otimizado FULL sem rescisão antecipada.

Um tópico comum parece ser que alterar/simplificar a consulta "s" (remover TOP, ORDER BY) corrige o problema na consulta "t", o que parece contra-intuitivo para mim.

O que estou olhando

Estou tentando entender se isso é uma falha do otimizador, se é o resultado de um mecanismo de otimização/custo deliberado, ou se eu simplesmente ignorei algo.

Eu tenho duas tabelas com colunas de chave identicamente nomeadas, digitadas e indexadas. Um deles possui um índice clusterizado exclusivo , o outro possui um índice não exclusivo .

A configuração do teste

Script de configuração, incluindo algumas estatísticas realistas:

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

A reprodução

Quando eu uno essas duas tabelas em suas chaves de cluster, espero uma junção MERGE de um para muitos, assim:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

Este é o plano de consulta que eu quero:

(Não importa os avisos, eles têm a ver com estatísticas falsas.)

No entanto, se eu alterar a ordem das colunas na junção, assim:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... isto acontece:

O operador Sort parece ordenar os fluxos de acordo com a ordem declarada da junção, ou seja c, a, b, d, e, f, g, h, que adiciona uma operação de bloqueio ao meu plano de consulta.

Coisas que eu olhei

• Eu tentei alterar as colunas para NOT NULL, mesmos resultados.
• A tabela original foi criada com ANSI_PADDING OFF, mas criá-la com ANSI_PADDING ONnão afeta este plano.
• Eu tentei INNER JOINem vez de LEFT JOIN, nenhuma alteração.
• Eu descobri em um 2014 SP2 Enterprise, criei uma reprodução em um 2017 Developer (CU atual).
• A remoção da cláusula WHERE na coluna de índice principal gera o bom plano, mas afeta os resultados.. :)

Por fim, chegamos à questão

• Isso é intencional?
• Posso eliminar a classificação sem alterar a consulta (que é o código do fornecedor, então prefiro não...). Eu posso mudar a tabela e os índices.

Para fins de ajuste e teste de consulta, você pode atribuir manualmente uma contagem de linhas e uma contagem de páginas às estatísticas de índice de uma tabela executando UPDATE STATISTICS. Mas como você recalcula/redefine as estatísticas para o conteúdo real da tabela?

--- Create a table..
CREATE TABLE dbo.StatTest (
    i      int NOT NULL,
    CONSTRAINT PK_StatTest PRIMARY KEY CLUSTERED (i)
);
GO

--- .. and give it a thousand-or-so rows:
DECLARE @i int=1;
INSERT INTO dbo.StatTest (i) VALUES (@i);

WHILE (@i<1000) BEGIN;
    INSERT INTO dbo.StatTest (i) SELECT @i+i FROM dbo.StatTest;
    SET @i=@i*2;
END;

Uma consulta fictícia:

SELECT i%100, COUNT(*) FROM dbo.StatTest GROUP BY i%100;

... retornará o seguinte plano de consulta (a estimativa de linha no Index Scan é de 1024 linhas).

Execute o UPDATE STATISTICScomando..

UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;

... e o plano ficou assim, agora com estimativa de 10 milhões de linhas:

Como faço para redefinir o rowcount para o conteúdo real da tabela sem usar WITH ROWCOUNT?

Eu tentei WITH FULLSCAN, WITH RESAMPLEe WITH SAMPLE n ROWS, mas a contagem de linhas de estatísticas permanece em 10 milhões de linhas. Inserir uma linha ou mesmo excluir todas as linhas não atualiza as estatísticas porque a alteração é muito pequena.

Estou escrevendo em uma próxima postagem do meu blog sobre classificação e funções de janela agregadas, especificamente os iteradores do projeto de segmento e sequência. A forma como eu entendo é que Segment identifica linhas em um stream que constituem o fim/início de um grupo, então a seguinte consulta:

SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)

Usará Segmento para informar quando uma linha pertence a um grupo diferente da linha anterior. O iterador Sequence Project, em seguida, faz o cálculo do número de linha real, com base na saída do iterador de segmento.

Mas a consulta a seguir, usando essa lógica, não deve incluir um segmento, porque não há expressão de partição.

SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)

No entanto, quando tento essa hipótese, ambas as consultas usam um operador de segmento. A única diferença é que a segunda consulta não precisa de um GroupByno segmento. Isso não elimina a necessidade de um segmento em primeiro lugar?

Exemplo

CREATE TABLE dbo.someTable (
    someGroup   int NOT NULL,
    someOrder   int NOT NULL,
    someValue   numeric(8, 2) NOT NULL,
    PRIMARY KEY CLUSTERED (someGroup, someOrder)
);

--- Query 1:
SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)
FROM dbo.someTable;

--- Query 2:
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable;