Início / dba / Perguntas / 134828
Accepted
crokusek
Asked: 2016-04-09 15:54:35 +0800 CST

Como melhorar a estimativa de 1 linha em uma exibição restrita por DateAdd () em relação a um índice

  • 772

Usando o Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Dados uma tabela e um índice:

create table [User].[Session] 
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

As linhas reais para cada uma das consultas a seguir são 3,1 milhões, as linhas estimadas são mostradas como comentários.

Quando essas consultas alimentam outra consulta em uma View , o otimizador escolhe uma junção de loop por causa das estimativas de 1 linha. Como melhorar a estimativa neste nível básico para evitar substituir a dica de junção da consulta pai ou recorrer a um SP?

Usar uma data codificada funciona muito bem:

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

Essas consultas equivalentes são compatíveis com visualização, mas todas estimam 1 linha:

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())         

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();          

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc    
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

Tente algumas dicas (mas N/A para visualizar):

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc > dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s    
    on s.CreatedUtc > d.MinCreatedUtc  
option (recompile);

Tente usar Parâmetro/Dicas (mas N/A para visualizar):

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc > @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate
option (optimize for unknown);

Estimativa vs Real

As estatísticas estão atualizadas.

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

As últimas linhas do histograma (total de 189 linhas) são mostradas:

insira a descrição da imagem aqui

sql-server sql-server-2012

3 respostas

  1. Best Answer

    Uma resposta menos abrangente do que a de Aaron, mas o problema principal é um bug de estimativa de cardinalidade DATEADDao usar o tipo datetime2 :

    Conectar: ​​estimativa incorreta quando sysdatetime aparece em uma expressão dateadd ()

    Uma solução alternativa é usar GETUTCDATE(que retorna datetime):

    WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

    Observe que a conversão para datetime2 deve estar fora do DATEADDpara evitar o bug.

    Uma estimativa de cardinalidade incorreta reproduz para mim em todas as versões do SQL Server até e incluindo 2019 CU8 GDR (compilação 15.0.4083) quando o estimador de cardinalidade de modelo 70 é usado.

    Aaron Bertrand escreveu um artigo sobre isso para SQLPerformance.com:

    • 7

  2. Em alguns cenários, o SQL Server pode ter estimativas realmente loucas para DATEADD/ DATEDIFF, dependendo de quais são os argumentos e de como são seus dados reais. Escrevi sobre isso para DATEDIFFlidar com o início do mês e algumas soluções alternativas, aqui:

    Mas, meu conselho típico é simplesmente parar de usar DATEADD/ DATEDIFFnas cláusulas where/join.

    A abordagem a seguir, embora não seja muito precisa quando um ano bissexto está no intervalo filtrado (incluirá um dia extra nesse caso) e, embora arredondada para o dia, obterá estimativas melhores (mas ainda não ótimas!), Assim como seu não-sargável DATEDIFFcontra a abordagem de coluna e ainda permite que uma busca seja usada:

    DECLARE @start date = DATEFROMPARTS
(
  YEAR(GETUTCDATE())-1, 
  MONTH(GETUTCDATE()), 
  DAY(GETUTCDATE())
);

SELECT ... WHERE CreatedUtc >= @start;

    Você pode manipular as entradas para DATEFROMPARTSevitar problemas em dias bissextos, usar DATETIMEFROMPARTSpara obter mais precisão em vez de arredondar para o dia, etc. Isso é apenas para demonstrar que você pode preencher uma variável com uma data no passado sem usar DATEADD(é apenas um pouco mais de trabalho) e, assim, evitar a parte mais incapacitante do bug de estimativa (que foi corrigido em 2014+).

    Para evitar erros no dia bissexto, você pode fazer isso, começando em 28 de fevereiro do ano passado em vez de 29:

    DECLARE @start date = DATEFROMPARTS
(
  YEAR(GETUTCDATE())-1, 
  MONTH(GETUTCDATE()), 
  CASE WHEN DAY(GETUTCDATE()) = 29 AND MONTH(GETUTCDATE()) = 2 
    THEN 28 ELSE DAY(GETUTCDATE()) END
);

    Você também pode adicionar um dia verificando se já passamos de um dia bissexto este ano e, em caso afirmativo, adicionar um dia ao início (curiosamente, usar DATEADD aqui ainda permite estimativas precisas):

    DECLARE @base date = GETUTCDATE();
IF GETUTCDATE() >= DATEFROMPARTS(YEAR(GETUTCDATE()),3,1) AND 
  TRY_CONVERT(datetime, DATEFROMPARTS(YEAR(GETUTCDATE()),2,29)) IS NOT NULL
BEGIN
  SET @base = DATEADD(DAY, 1, GETUTCDATE());
END

DECLARE @start date = DATEFROMPARTS
(
  YEAR(@base)-1, 
  MONTH(@base),
  CASE WHEN DAY(@base) = 29 AND MONTH(@base) = 2 
    THEN 28 ELSE DAY(@base) END
);

SELECT ... WHERE CreatedUtc >= @start;

    Se você precisa ser mais preciso do que o dia à meia-noite, basta adicionar mais manipulação antes de selecionar:

    DECLARE @accurate_start datetime2(7) = DATETIME2FROMPARTS
(
  YEAR(@start), MONTH(@start), DAY(@start),
  DATEPART(HOUR,  SYSUTCDATETIME()), 
  DATEPART(MINUTE,SYSUTCDATETIME()),
  DATEPART(SECOND,SYSUTCDATETIME()), 
  0,0
);

SELECT ... WHERE CreatedUtc >= @accurate_start;

    Agora, você pode colocar tudo isso em uma exibição e ainda usar uma busca e a estimativa de 30% sem exigir dicas ou sinalizadores de rastreamento, mas não é bonito. Os CTEs aninhados são apenas para que eu não precise digitar SYSUTCDATETIME()centenas de vezes ou repetir expressões reutilizadas - eles ainda podem ser avaliados várias vezes.

    CREATE VIEW dbo.v5 
AS
  WITH d(d) AS ( SELECT SYSUTCDATETIME() ),
  base(d) AS
  (
    SELECT DATEADD(DAY,CASE WHEN d >= DATEFROMPARTS(YEAR(d),3,1) 
      AND TRY_CONVERT(datetime,RTRIM(YEAR(d))+RIGHT('0'+RTRIM(MONTH(d)),2)
      +RIGHT('0'+RTRIM(DAY(d)),2)) IS NOT NULL THEN 1 ELSE 0 END, d)
    FROM d
  ),
  src(d) AS
  (
    SELECT DATETIME2FROMPARTS
    (
      YEAR(d)-1, 
      MONTH(d),
      CASE WHEN MONTH(d) = 2 AND DAY(d) = 29
        THEN 28 ELSE DAY(d) END,
      DATEPART(HOUR,d), 
      DATEPART(MINUTE,d),
      DATEPART(SECOND,d),
      10*DATEPART(MICROSECOND,d),
      7
    ) FROM base
  )
  SELECT DISTINCT SessionId FROM [User].[Session]
    WHERE CreatedUtc >= (SELECT d FROM src);

    Isso é muito mais detalhado do que o seu DATEDIFFcontra a coluna, mas, como mencionei em um comentário , essa abordagem não é sargável e provavelmente terá um desempenho competitivo enquanto a maior parte da tabela deve ser lida de qualquer maneira, mas suspeito que se tornará um fardo como "o último ano" se torna uma porcentagem menor da tabela.

    Além disso, apenas para referência, aqui estão algumas das métricas que obtive quando tentei reproduzir:

    insira a descrição da imagem aqui

    Não consegui obter estimativas de 1 linha e tentei muito corresponder à sua distribuição (3,13 milhões de linhas, 2,89 milhões no ano passado). Mas você pode ver:

    • ambas as nossas soluções executam leituras aproximadamente equivalentes.
    • sua solução é um pouco menos precisa porque considera apenas os limites do dia (e isso pode ser bom, minha visão pode ser menos precisa para corresponder).
    • 4199 + a recompilação realmente não mudou as estimativas (ou os planos).

    Não extraia muito dos números de duração - eles estão próximos agora, mas podem não permanecer próximos à medida que a tabela cresce (novamente, acredito porque mesmo o seek ainda precisa ler a maior parte da tabela).

    Aqui estão os planos para v4 (sua datadiferença contra a coluna) e v5 (minha versão):

    insira a descrição da imagem aqui

    insira a descrição da imagem aqui

    • 6

  3. Substitua dateadd() por datediff() para obter uma aproximação adequada (30% ish).

     select distinct SessionId from [User].Session     -- 1.2M est, 3.0M act.
  where datediff(day, CreatedUtc, sysutcdatetime()) <= 365

    Isso parece ser um bug semelhante ao MS Connect 630583 .

    A recompilação da opção não faz diferença.

    Estatísticas do plano

    • 1

relate perguntas