Ao unir uma tabela mestra a uma tabela de detalhes, como posso incentivar o SQL Server 2014 a usar a estimativa de cardinalidade da tabela maior (detalhe) como a estimativa de cardinalidade da saída da junção?
Por exemplo, ao unir 10 mil linhas mestras a 100 mil linhas de detalhes, quero que o SQL Server estime a associação em 100 mil linhas -- o mesmo que o número estimado de linhas de detalhes. Como devo estruturar minhas consultas e/ou tabelas e/ou índices para ajudar o estimador do SQL Server a aproveitar o fato de que cada linha de detalhes sempre tem uma linha mestra correspondente? (O que significa que uma junção entre eles nunca deve reduzir a estimativa de cardinalidade.)
Aqui estão mais detalhes. Nosso banco de dados possui um par de tabelas mestre/detalhe: VisitTargetpossui uma linha para cada transação de vendas e VisitSalepossui uma linha para cada produto em cada transação. É um relacionamento de um para muitos: uma linha VisitTarget para uma média de 10 linhas VisitSale.
As tabelas ficam assim: (estou simplificando apenas as colunas relevantes para esta questão)
-- "master" table
CREATE TABLE VisitTarget
(
VisitTargetId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
SaleDate date NOT NULL,
StoreId int NOT NULL
-- other columns omitted for clarity
);
-- covering index for date-scoped queries
CREATE NONCLUSTERED INDEX IX_VisitTarget_SaleDate
ON VisitTarget (SaleDate) INCLUDE (StoreId /*, ...more columns */);
-- "detail" table
CREATE TABLE VisitSale
(
VisitSaleId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
VisitTargetId int NOT NULL,
SaleDate date NOT NULL, -- denormalized; copied from VisitTarget
StoreId int NOT NULL, -- denormalized; copied from VisitTarget
ItemId int NOT NULL,
SaleQty int NOT NULL,
SalePrice decimal(9,2) NOT NULL
-- other columns omitted for clarity
);
-- covering index for date-scoped queries
CREATE NONCLUSTERED INDEX IX_VisitSale_SaleDate
ON VisitSale (SaleDate)
INCLUDE (VisitTargetId, StoreId, ItemId, SaleQty, TotalSalePrice decimal(9,2) /*, ...more columns */
);
ALTER TABLE VisitSale
WITH CHECK ADD CONSTRAINT FK_VisitSale_VisitTargetId
FOREIGN KEY (VisitTargetId)
REFERENCES VisitTarget (VisitTargetId);
ALTER TABLE VisitSale
CHECK CONSTRAINT FK_VisitSale_VisitTargetId;
Por motivos de desempenho, desnormalizamos parcialmente copiando as colunas de filtragem mais comuns (por exemplo SaleDate, ) da tabela mestre para as linhas de cada tabela de detalhes e, em seguida, adicionamos índices de cobertura em ambas as tabelas para oferecer melhor suporte a consultas filtradas por data. Isso funciona muito bem para reduzir a E/S ao executar consultas filtradas por data, mas acho que essa abordagem está causando problemas de estimativa de cardinalidade ao unir as tabelas mestre e de detalhes.
Quando juntamos essas duas tabelas, as consultas ficam assim:
SELECT vt.StoreId, vt.SomeOtherColumn, Sales = sum(vs.SalePrice*vs.SaleQty)
FROM VisitTarget vt
JOIN VisitSale vs on vt.VisitTargetId = vs.VisitTargetId
WHERE
vs.SaleDate BETWEEN '20170101' and '20171231'
and vt.SaleDate BETWEEN '20170101' and '20171231'
-- more filtering goes here, e.g. by store, by product, etc.
O filtro de data na tabela de detalhes ( VisitSale) é redundante. Ele está lá para habilitar a E/S sequencial (também conhecido como operador de busca de índice) na tabela de detalhes para consultas filtradas por um intervalo de datas.
O plano para esses tipos de consultas é assim:
Um plano real de uma consulta com o mesmo problema pode ser encontrado aqui .
Como você pode ver, a estimativa de cardinalidade para a junção (a dica de ferramenta no canto inferior esquerdo da imagem) é 4x mais baixa: 2,1 M reais versus 0,5 M estimados. Isso causa problemas de desempenho (por exemplo, derramamento para tempdb), especialmente quando essa consulta é uma subconsulta usada em uma consulta mais complexa.
Mas as estimativas de contagem de linhas para cada ramificação da junção estão próximas das contagens de linhas reais. A metade superior da junção é 100K reais versus 164K estimados. A metade inferior da junção tem 2,1 milhões de linhas reais versus 3,7 milhões estimados. A distribuição de hash bucket também parece boa. Essas observações me sugerem que as estatísticas estão corretas para cada tabela e que o problema é a estimativa da cardinalidade da junção.
No começo eu pensei que o problema era o SQL Server esperando que as colunas SaleDate em cada tabela fossem independentes, enquanto na verdade elas eram idênticas. Então, tentei adicionar uma comparação de igualdade para as datas de venda à condição de junção ou à cláusula WHERE, por exemplo
ON vt.VisitTargetId = vs.VisitTargetId and vt.SaleDate = vs.SaleDate
ou
WHERE vt.SaleDate = vs.SaleDate
Isso não funcionou. Isso até piorou as estimativas de cardinalidade! Portanto, o SQL Server não está usando essa dica de igualdade ou outra coisa é a causa raiz do problema.
Tem alguma ideia de como solucionar problemas e, esperançosamente, corrigir esse problema de estimativa de cardinalidade? Meu objetivo é que a cardinalidade da junção mestre/detalhe seja estimada da mesma forma que a estimativa para a entrada maior ("tabela de detalhes") da junção.
Se for importante, estamos executando o SQL Server 2014 Enterprise SP2 CU8 build 12.0.5557.0 no Windows Server. Não há sinalizadores de rastreamento habilitados. O nível de compatibilidade do banco de dados é SQL Server 2014. Vemos o mesmo comportamento em vários SQL Servers diferentes, portanto, parece improvável que seja um problema específico do servidor.
Há uma otimização no Estimador de cardinalidade do SQL Server 2014 que é exatamente o comportamento que estou procurando:
O novo CE, no entanto, usa um algoritmo mais simples que pressupõe que há uma associação de junção um-para-muitos entre uma tabela grande e uma tabela pequena. Isso pressupõe que cada linha na tabela grande corresponde exatamente a uma linha na tabela pequena. Esse algoritmo retorna o tamanho estimado da entrada maior como a cardinalidade de junção.
Idealmente, eu poderia obter esse comportamento, onde a estimativa de cardinalidade para a junção seria a mesma que a estimativa para a tabela grande, mesmo que minha tabela "pequena" ainda retorne mais de 100 mil linhas!
