Início / dba / Perguntas / 149811
Accepted
Martin Smith
Asked: 2016-09-16 08:50:52 +0800 CST

Por que o custo estimado de (mesmo) 1.000 buscas em um índice único difere nesses planos?

  • 772

Nas consultas abaixo, estima-se que ambos os planos de execução executem 1.000 buscas em um índice exclusivo.

As buscas são conduzidas por uma varredura ordenada na mesma tabela de origem, portanto, aparentemente, devem acabar buscando os mesmos valores na mesma ordem.

Ambos os loops aninhados têm<NestedLoops Optimized="false" WithOrderedPrefetch="true">

Alguém sabe por que essa tarefa custa 0,172434 no primeiro plano, mas 3,01702 no segundo?

(O motivo da pergunta é que a primeira consulta foi sugerida para mim como uma otimização devido ao aparente custo de plano muito menor. Na verdade, parece-me que funciona mais, mas estou apenas tentando explicar a discrepância. .)

Configurar

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL); 

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,  
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

Consulta 1 link "Colar o plano"

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Consulta 2 link "Colar o plano"

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Consulta 1

Consulta 2

O acima foi testado no SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

@Joe Obbish aponta nos comentários que uma reprodução mais simples seria

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

vs

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN (SELECT * FROM Target) AS T 
    ON T.KeyCol = S.KeyCol;

Para a tabela de preparação de 1.000 linhas, ambos os itens acima ainda têm a mesma forma de plano com loops aninhados e o plano sem a tabela derivada parecendo mais barato, mas para uma tabela de preparação de 10.000 linhas e a mesma tabela de destino acima, a diferença nos custos altera o plano forma (com uma varredura completa e junção de mesclagem parecendo relativamente mais atraente do que buscas caras) mostrando essa discrepância de custo pode ter outras implicações além de apenas dificultar a comparação de planos.

insira a descrição da imagem aqui

sql-server sql-server-2014

4 respostas

  1. Best Answer

    Alguém sabe por que essa tarefa custa 0,172434 no primeiro plano, mas 3,01702 no segundo?

    De um modo geral, uma busca interna abaixo de uma junção de loops aninhados é custeada assumindo um padrão de E/S aleatório. Há uma redução baseada em substituição simples para acessos subsequentes, contabilizando a chance de que a página necessária já tenha sido trazida para a memória por uma iteração anterior. Esta avaliação básica produz o custo padrão (mais alto).

    Há outro input de custeio, Smart Seek Costing , sobre o qual poucos detalhes são conhecidos. Meu palpite (e isso é tudo neste estágio) é que o SSC tenta avaliar o custo de E/S de busca do lado interno com mais detalhes, talvez considerando a ordenação local e/ou o intervalo de valores a serem buscados. Quem sabe.

    Por exemplo, a primeira operação de busca traz não apenas a linha solicitada, mas todas as linhas dessa página (na ordem do índice). Dado o padrão de acesso geral, buscar as 1.000 linhas em 1.000 buscas requer apenas 2 leituras físicas, mesmo com a leitura antecipada e a pré-busca desativadas. A partir dessa perspectiva, o custo padrão de I/O representa uma superestimativa significativa e o custo ajustado pelo SSC está mais próximo da realidade.

    Parece razoável esperar que o SSC seja mais eficaz onde o loop direciona uma busca de índice mais ou menos diretamente, e a referência externa de junção é a base da operação de busca. Pelo que posso dizer, o SSC sempre é tentado para operações físicas adequadas, mas na maioria das vezes não produz ajuste para baixo quando a busca é separada da junção por outras operações. Filtros simples são uma exceção a isso, talvez porque o SQL Server geralmente pode enviá-los para o operador de acesso a dados. Em qualquer caso, o otimizador tem um suporte bastante profundo para seleções.

    It is unfortunate that the Compute Scalar for the subquery outer projections seems to interfere with SSC here. Compute Scalars are usually relocated above the join, but these ones have to stay where they are. Even so, most normal Compute Scalars are pretty transparent to optimization, so this is a bit surprising.

    Regardless, when the physical operation PhyOp_Range is produced from a simple selection on an index SelIdxToRng, SSC is effective. When the more complex SelToIdxStrategy (selection on a table to an index strategy) is employed, the resulting PhyOp_Range runs SSC but results in no reduction. Again, it seems that simpler, more direct operations work best with SSC.

    I wish I could tell you exactly what SSC does, and show the exact calculations, but I don't know those details. If you want to explore the limited trace output available for yourself, you can employ undocumented trace flag 2398. An example output is:

    Smart seek costing (7.1) :: 1.34078e+154 , 0.001

    That example relates to memo group 7, alternative 1, showing a cost upper bound, and a factor of 0.001. To see cleaner factors, be sure to rebuild the tables without parallelism so the pages are as dense as possible. Without doing that, the factor is more like 0.000821 for your example Target table. There are some fairly obvious relationships there, of course.

    SSC can also be disabled with undocumented trace flag 2399. With that flag active, both costs are the higher value.

    • 21

  2. Não tenho certeza se esta é uma resposta, mas é um pouco longo para um comentário. A causa da diferença é pura especulação da minha parte e talvez possa ser motivo de reflexão para os outros.

    Consultas simplificadas com planos de execução.

    SELECT S.KeyCol, 
       S.OtherCol,
       T.*
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

SELECT S.KeyCol, 
       S.OtherCol,
       T.*
FROM staging AS S 
  LEFT OUTER JOIN (
                  SELECT *
                  FROM Target
                  ) AS T 
    ON T.KeyCol = S.KeyCol;

    insira a descrição da imagem aqui

    A principal diferença entre essas consultas equivalentes que realmente podem resultar em planos de execução idênticos é o operador escalar de computação. Não sei por que tem que estar lá, mas acho que é o máximo que o otimizador pode ir para otimizar a tabela derivada.

    Meu palpite é que a presença do escalar de computação é o que está atrapalhando o custo de E/S para a segunda consulta.

    De Dentro do Otimizador: Planejamento de Custos

    O custo da CPU é calculado como 0,0001581 para a primeira linha e 0,000011 para as linhas subsequentes.
    ...
    O custo de I/O de 0,003125 é exatamente 1/320 – refletindo a suposição do modelo de que o subsistema de disco pode executar 320 operações aleatórias de I/O por segundo
    ...
    o componente de custo é inteligente o suficiente para reconhecer que o número total de as páginas que precisam ser trazidas do disco nunca podem exceder o número de páginas necessárias para armazenar a tabela inteira.

    No meu caso, a tabela ocupa 5.618 páginas e, para obter 1.000 linhas de 1.000.000 linhas, o número estimado de páginas necessárias é 5,618, dando o custo de IO de 0,015625.

    O custo de CPU para ambas as consultas parece ser o mesmo, 0.0001581 * 1000 executions = 0.1581.

    Portanto, de acordo com o artigo vinculado acima, podemos calcular o custo da primeira consulta como 0,173725.

    E supondo que eu esteja correto sobre como o escalar de computação está bagunçando o custo de IO, ele pode ser calculado para 3,2831.

    Não é exatamente o que é mostrado nos planos, mas é bem ali no bairro.

    • 8

  3. (This would be better as a comment to Paul's answer, but I don't have enough rep yet.)

    I wanted to provide the list of trace flags (and a couple DBCC statements) I used to come to a near-conclusion, in case it will be helpful to investigate similar discrepancies in the future. All of these should not be used on production.

    First, I had a look at the Final Memo to see what physical operators were being used. They certainly look the same according to the graphical execution plans. So, I used trace flags 3604 and 8615, the first directs output to the client and the second reveals the Final Memo:

    SELECT S.*, T.KeyCol
FROM Staging AS S
      LEFT OUTER JOIN Target AS T
       ON T.KeyCol = S.KeyCol
OPTION(QUERYTRACEON 3604, -- Output client info
       QUERYTRACEON 8615, -- Shows Final Memo structure
       RECOMPILE);

    Tracing back from the Root Group, I found these nearly identical PhyOp_Range operators:

    1. PhyOp_Range 1 ASC 2.0 Cost(RowGoal 0,ReW 0,ReB 999,Dist 1000,Total 1000)= 0.175559(Distance = 2)
    2. PhyOp_Range 1 ASC 3.0 Cost(RowGoal 0,ReW 0,ReB 999,Dist 1000,Total 1000)= 3.01702(Distance = 2)

    The only obvious difference to me was the 2.0 and 3.0, which refer to their respective "memo group 2, original" and "memo group 3, original". Checking the memo, these refer to the same thing - so no differences revealed yet.

    Second, I looked into a whole mess of trace flags that proved fruitless to me - but have some interesting content. I lifted most from Benjamin Nevarez. I was looking for clues as to optimization rules that were applied in one case and not the other.

     SELECT S.*, T.KeyCol
 FROM Staging AS S
      LEFT OUTER JOIN Target AS T
        ON T.KeyCol = S.KeyCol
 OPTION (QUERYTRACEON 3604, -- Output info to client
         QUERYTRACEON 2363, -- Show stats and cardinality info
         QUERYTRACEON 8675, -- Show optimization process info
         QUERYTRACEON 8606, -- Show logical query trees
         QUERYTRACEON 8607, -- Show physical query tree
         QUERYTRACEON 2372, -- Show memory utilization info for optimization stages 
         QUERYTRACEON 2373, -- Show memory utilization info for applying rules
         RECOMPILE );

    Third, I looked at which rules were applied for our PhyOp_Ranges that look so similar. I used a couple trace flags mentioned by Paul in a blog post. 

    SELECT S.*, T.KeyCol
FROM Staging AS S
      LEFT OUTER JOIN (SELECT KeyCol
                      FROM Target) AS T
       ON T.KeyCol = S.KeyCol
OPTION (QUERYTRACEON 3604, -- Output info to client
        QUERYTRACEON 8619, -- Show applied optimization rules
        QUERYTRACEON 8620, -- Show rule-to-memo info
        QUERYTRACEON 8621, -- Show resulting tree
        QUERYTRACEON 2398, -- Show "smart seek costing"
        RECOMPILE );

    Na saída, vemos que JOINaplicou diretamente esta regra para obter nosso PhyOp_Rangeoperador: Rule Result: group=7 2 <SelIdxToRng>PhyOp_Range 1 ASC 2 (Distance = 2). Em vez disso, a subseleção aplicou esta regra: Rule Result: group=9 2 <SelToIdxStrategy>PhyOp_Range 1 ASC 3 (Distance = 2). Também é aqui que você vê as informações de "custo de busca inteligente" associadas a cada regra. Para o direto- JOINesta é a saída (para mim): Smart seek costing (7.2) :: 1.34078e+154 , 0.001. Para a subseleção, esta é a saída: Smart seek costing (9.2) :: 1.34078e+154 , 1.

    No final, não consegui concluir muito - mas a resposta de Paul fecha a maior parte da lacuna. Eu gostaria de ver mais algumas informações sobre custeio de busca inteligente.

    • 6

  4. Isso também não é uma resposta - como observou Mikael, é difícil discutir esse problema nos comentários ...

    Curiosamente, se você converter a subconsulta (select KeyCol FROM Target)em um TVF embutido, verá que o plano e seus custos são os mesmos da consulta original simples:

    CREATE FUNCTION dbo.cs_test()
RETURNS TABLE
WITH SCHEMABINDING
AS 
RETURN (
    SELECT KeyCol FROM dbo.Target
    );

/* "normal" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN Target AS T ON T.KeyCol = S.KeyCol;

/* "subquery" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN (SELECT KeyCol FROM Target) AS T ON T.KeyCol = S.KeyCol;

/* "inline-TVF" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN dbo.cs_test() t ON s.KeyCol = t.Keycol

    Os planos de consulta ( cole o link do plano ):

    insira a descrição da imagem aqui

    A dedução me leva a acreditar que o mecanismo de custos está confuso sobre o impacto potencial que esse tipo de subconsulta pode ter .

    Tomemos por exemplo, o seguinte:

    SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN (
        SELECT KeyCol = CHECKSUM(NEWID()) 
        FROM Target
        ) AS T ON T.KeyCol = S.KeyCol;

    Como você custaria isso? O otimizador de consulta escolhe um plano muito semelhante à variante "subconsulta" acima, contendo um escalar de computação ( link pastetheplan.com ):

    insira a descrição da imagem aqui

    O escalar de computação tem um custo bastante diferente da variante "subconsulta" mostrada acima, mas ainda é apenas um palpite, pois o otimizador de consulta não tem como saber, a priori, qual pode ser o número de linhas retornadas. O plano usa uma correspondência de hash para a junção externa esquerda, pois as estimativas de linha não podem ser conhecidas e, portanto, definidas para o número de linhas na tabela Target.

    insira a descrição da imagem aqui

    Não tenho uma grande conclusão disso, exceto que concordo com o trabalho que Mikael fez em sua resposta e espero que alguém possa encontrar uma resposta melhor.

    • 4

relate perguntas