Início / dba / Perguntas / 344314
Accepted
sebeid
Asked: 2024-12-20 07:32:40 +0800 CST

Estimativas do plano de execução vs. reais com filtros de desigualdade

  • 772

Tenho a seguinte consulta SQL:

declare @p1 INT = 20240703;
declare @p2 INT = 20240703;
declare @p3 NVARCHAR(50) = N'USA';

SELECT R.taxareaid, R.filtertypes
FROM region R
JOIN country C ON R.countryid = C.countryid 
where C.name = @p3
and  R.effdate <= @p1 AND R.expdate >= @p2
ORDER BY R.taxareaid 
OPTION (RECOMPILE);

https://www.brentozar.com/pastetheplan/?id=B1FTNkXHkx

CREATE TABLE [dbo].[Region](
    [regionId] [numeric](18, 0) NOT NULL,
    [taxAreaId] [numeric](18, 0) NOT NULL,
    [effDate] [numeric](8, 0) NOT NULL,
    [expDate] [numeric](8, 0) NOT NULL,
    [countryId] [numeric](18, 0) NOT NULL,
    [mainDivisionId] [numeric](18, 0) NOT NULL,
    [subDivisionId] [numeric](18, 0) NOT NULL,
    [cityId] [numeric](18, 0) NOT NULL,
    [postalCodeId] [numeric](18, 0) NOT NULL,
    [cityCompressedId] [numeric](18, 0) NOT NULL,
    [subDivCompressedId] [numeric](18, 0) NOT NULL,
    [filterTypes] [numeric](32, 0) NOT NULL,
    [updateId] [numeric](18, 0) NOT NULL,
 CONSTRAINT pk_region PRIMARY KEY CLUSTERED 
(
    [regionId] ASC
)

Problema:

  • Estimativas do Plano de Execução : Quando olho para o plano de execução, percebo que as estimativas são muito menores do que as linhas realmente processadas. Embora eu esteja usando OPTION (RECOMPILE)para evitar a detecção de parâmetros, ainda não estou obtendo estimativas precisas. Também atualizei as estatísticas na regiontabela usando uma varredura completa, mas as estimativas ainda estão incorretas.

  • Vazamento no TempDB : a consulta está causando um vazamento no TempDB durante a classificação.

O que eu tentei:

  1. Estatísticas atualizadas : executei uma verificação completa para atualizar as estatísticas na regiontabela.
  2. Índices : criei um índice em region(taxareaid)e um índice composto em (countryid, effdate, expdate, taxareaid), mas ainda estou vendo classificação no plano de execução.

Minhas perguntas:

  1. Como posso obter estimativas mais precisas do plano de execução para evitar o vazamento do TempDB durante a classificação?
  2. Como posso evitar a operação de classificação completamente? Existem outras estratégias que eu possa tentar, já que a indexação não parece resolver o problema?
sql-server

2 respostas

  1. Best Answer

    Para fazer literalmente o que é pedido na pergunta, você precisa destes dois índices secundários (e nenhum outro):

    CREATE UNIQUE NONCLUSTERED INDEX 
    [UQ dbo.Country name] 
ON 
    dbo.Country ([name]);

CREATE NONCLUSTERED INDEX 
    [IX dbo.Region countryId, taxAreaId (effDate, expDate, filterTypes)] 
ON 
    dbo.Region (countryId, taxAreaId) 
INCLUDE 
    (effDate, expDate, filterTypes);

    Corrigindo os tipos de dados das variáveis ​​para corresponder à tabela:

    DECLARE 
    @p1 numeric(8, 0) = 20240703,
    @p2 numeric(8, 0) = 20240703,
    @p3 nvarchar(50) = N'USA';

SELECT 
    R.taxAreaId, 
    R.filterTypes
FROM dbo.Region AS R
JOIN dbo.Country AS C 
    ON C.countryId = R.countryId
WHERE 
    C.[name] = @p3
    AND R.effDate <= @p1 
    AND R.expDate >= @p2
ORDER BY
    R.taxAreaId ASC;

    O plano é:

    plano estimado

    O índice exclusivo no país é necessário para garantir que a pesquisa de nome produza no máximo uma linha.

    O índice em Região permite uma busca em countryId , retornando linhas na ordem taxAreaId .

    Isso é o máximo que podemos fazer com um índice b-tree não filtrado. Os outros predicados em effDate e expDate não podem ser usados ​​para buscar enquanto também preservam a ordem taxAreaId . Então, esses dois predicados serão aplicados como testes residuais em todas as linhas que correspondem ao countryId .

    Você pode ou não achar que esse plano tem um desempenho melhor na prática do que aquele com a classificação.

    Observe que você não precisa da RECOMPILEdica acima. As estimativas de cardinalidade podem ou não ser melhores. Elas não importam para esse formato de plano. Você parece sempre querer que os loops aninhados sejam aplicados e nenhuma classificação. Nada depende da estimativa de cardinalidade da tabela Region.

    Faz pouco sentido usar numericcomo o tipo de dados para todas as colunas em uma tabela, especialmente aquelas que presumivelmente armazenam datas. Você nem sempre verá um aviso de conversão de tipo porque o SQL Server se esforça para projetar uma busca mesmo quando o programador foi desleixado com os tipos de dados. Mas essas são questões separadas sobre as quais você não perguntou.

    • 1

  2. Você pode tentar reescrever a consulta fatorando a parte que seleciona countryidem uma consulta separada. Isso pode ajudar a melhorar as estimativas de cardinalidade:

    declare @p1 [numeric](18, 0) = 20240703;
declare @p2 [numeric](18, 0) = 20240703;
declare @p3 NVARCHAR(50) = N'United States';

declare @countryid int = (select countryid from country C where C.name = @p3);

SELECT R.taxareaid, R.filtertypes
FROM region R
where countryid =  @countryid
    and  R.effdate <= @p1 AND R.expdate >= @p2
ORDER BY R.taxareaid 
OPTION (RECOMPILE);

    Para evitar a classificação, crie um índice com taxAreaIda coluna-chave principal:

    CREATE INDEX IX_taxAreaId
ON dbo.Region (taxAreaId) include(countryId, effdate, filtertypes, expdate);

    Criei tabelas e inseri alguns dados fictícios. A consulta reescrita que fatora o countryid selecionado como uma consulta separada produziu estatísticas precisas com sucesso, e o otimizador selecionou IX_taxAreaId, que eliminou a classificação, como a escolha ótima para o plano de consulta. Portanto, no meu caso, manterei esse índice e excluirei o outro.

    No entanto, sua distribuição de dados pode ser diferente. Por favor, deixe-me saber como isso funciona com seus dados!

    • 0

relate perguntas