Desmistificação do processo de otimização do SQL Server

Question

Gostaríamos de ver todas as variantes do plano de consulta consideradas durante uma otimização de consulta por um otimizador do SQL Server. O SQL Server oferece uma visão bastante detalhada usando querytraceonopções. Por exemplo, QUERYTRACEON 3604, QUERYTRACEON 8615permite-nos imprimir a estrutura MEMO e QUERYTRACEON 3604, QUERYTRACEON 8619imprimir uma lista de regras de transformação aplicadas durante o processo de otimização. Isso é ótimo, no entanto, temos vários problemas com saídas de rastreamento:

1. Parece que a estrutura MEMO contém apenas variantes finais do plano de consulta ou variantes que foram reescritas posteriormente no plano final. Existe uma maneira de localizar planos de consulta "malsucedidos / pouco promissores"?
2. Os operadores em MEMO não contêm uma referência a partes SQL. Por exemplo, o operador LogOp_Get não contém uma referência a uma tabela específica.
3. As regras de transformação não contêm uma referência precisa aos operadores MEMO, portanto, não podemos ter certeza de quais operadores foram transformados pela regra de transformação.

Deixe-me mostrar um exemplo mais elaborado. Deixe-me ter duas tabelas artificiais Ae B:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

Agora, eu executo uma consulta simples

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)
    

Recebo uma saída bastante complexa que descreve a estrutura MEMO (você pode tentar por si mesmo) com 15 grupos. Aqui está a imagem, que visualiza a estrutura do MEMO usando uma árvore.

Da árvore, pode-se observar que certas regras foram aplicadas antes que o otimizador encontrasse o plano de consulta final. Por exemplo join commute( JoinCommute), join to hash join( JNtoHS) ou Enforce sort( EnforceSort). Conforme mencionado, é possível imprimir todo o conjunto de regras de reescrita aplicadas pelo otimizador usando QUERYTRACEON 3604, QUERYTRACEON 8619opções. Os problemas:

1. Podemos encontrar JNtoSM( Join to sort merge) regra de reescrita na lista 8619, no entanto, o operador sort-merge não está na estrutura MEMO. Eu entendo que o sort-merge foi provavelmente mais caro, mas por que não está no MEMO?
2. Como saber se o LogOp_Getoperador em MEMO faz referência à tabela A ou à tabela B?
3. Se eu GetToIdxScan - Get -> IdxScanvir a regra na lista 8619, como mapeá-la para os operadores MEMO?

Há um número limitado de recursos sobre isso. Eu li muitas das postagens do blog de Paul White sobre regras de transformação e MEMO, no entanto, as perguntas acima permanecem sem resposta. Obrigado por qualquer ajuda.

Answer 1

Vou tentar responder às suas perguntas:

1. Parece que a estrutura MEMO contém apenas variantes finais do plano de consulta ou variantes que foram reescritas posteriormente no plano final. Existe uma maneira de localizar planos de consulta "malsucedidos / pouco promissores"?

Não, infelizmente, não há como fazer isso. @Ronaldo colou um link legal no comentário. Minha sugestão é usar oInclude Live Query Statistics

e tente descobrir se você vê um plano de consulta diferente. Use top 10, top 1000ou *e você verá que planos de consulta diferentes serão propostos. Você também pode usar query hinte forçar seu plano de consulta para um padrão diferente. Basicamente, "faça seu próprio plano de consulta descartado"

2. Os operadores em MEMO não contêm uma referência a partes SQL. Por exemplo, o operador LogOp_Get não contém uma referência a uma tabela específica.

Use QUERYTRACEON 8605, posso ver uma referência à tabela:

3. As regras de transformação não contêm uma referência precisa aos operadores MEMO, portanto, não podemos ter certeza de quais operadores foram transformados pela regra de transformação

Não vejo nenhum GetToIdxScan - Get -> IdxScanna consulta que você forneceu. Minha sugestão é usar Use QUERYTRACEON 8605, ou QUERYTRACEON 8606, deve haver uma referência lá.

EDITAR:

Portanto, "... é possível ver mais informações sobre os planos candidatos no SQL Server."

A resposta é não , porque não há outro plano de consulta candidato. Na verdade, é um equívoco comum que o SQL Server retorna o melhor plano de consulta. O SQL Server simplesmente não pode calcular para você todas as soluções possíveis: isso levaria ... não sei ... minutos ...? horas ...? Calcular cada solução é inviável.

Mas se você quiser investigar por que seu plano de consulta escolha esse padrão, você pode usar:

• SET SHOWPLAN_ALL ON : e o SQL Server retornará a você uma árvore da lógica de cada cálculo do seu plano de consulta

• DBCC SHOW_STATISTICS('A', 'PK_A'): que irá mostrar as estatísticas sobre uma tabela de destino e restrição. Eu criei uma chave para mostrar os resultados, naturalmente você verá mais informações se sua tabela for consultada com mais frequência

• USE HINT('force_legacy_cardinality_estimation') : permitirá que você use a estimativa de cardinalidade anterior, para que você possa verificar se seu plano de consulta pode ter sido mais rápido com a estimativa de cardinalidade legada.