Desmitificación del proceso de optimización de SQL Server

Question

Nos gustaría ver todas las variantes del plan de consultas consideradas durante una optimización de consultas por un optimizador de SQL Server. SQL Server ofrece información bastante detallada utilizando querytraceonopciones. Por ejemplo, QUERYTRACEON 3604, QUERYTRACEON 8615nos permite imprimir la estructura MEMO e QUERYTRACEON 3604, QUERYTRACEON 8619imprimir una lista de reglas de transformación aplicadas durante el proceso de optimización. Eso es genial, sin embargo, tenemos varios problemas con las salidas de seguimiento:

1. Parece que la estructura MEMO contiene solo variantes finales del plan de consulta o variantes que luego se reescribieron en el final. ¿Hay alguna manera de encontrar planes de consulta "fallidos / poco prometedores"?
2. Los operadores en MEMO no contienen una referencia a partes SQL. Por ejemplo, el operador LogOp_Get no contiene una referencia a una tabla específica.
3. Las reglas de transformación no contienen una referencia precisa a los operadores MEMO, por lo tanto, no podemos estar seguros de qué operadores fueron transformados por la regla de transformación.

Permítanme mostrarlo con un ejemplo más elaborado. Déjame tener dos mesas artificiales Ay 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;

Ahora mismo, ejecuto una consulta simple

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)
    

Obtengo un resultado bastante complejo que describe la estructura de MEMO (puede intentarlo usted mismo) que tiene 15 grupos. Aquí está la imagen, que visualiza la estructura de MEMO usando un árbol.

En el árbol se puede observar que se aplicaron ciertas reglas antes de que el optimizador encontrara el plan de consulta final. Por ejemplo join commute( JoinCommute), join to hash join( JNtoHS) o Enforce sort( EnforceSort). Como se mencionó, es posible imprimir todo el conjunto de reglas de reescritura aplicadas por el optimizador usando QUERYTRACEON 3604, QUERYTRACEON 8619opciones. Los problemas:

1. Podemos encontrar la regla de reescritura JNtoSM( Join to sort merge) en la lista 8619, sin embargo, el operador de combinación de clasificación no está en la estructura MEMO. Entiendo que la combinación de clasificación fue probablemente más costosa, pero ¿por qué no está en MEMO?
2. ¿Cómo saber si el LogOp_Getoperador en MEMO hace referencia a la tabla A o la tabla B?
3. Si veo la regla GetToIdxScan - Get -> IdxScanen la lista 8619, ¿cómo asignarla a los operadores MEMO?

Hay un número limitado de recursos al respecto. He leído muchas de las publicaciones del blog de Paul White sobre reglas de transformación y MEMO, sin embargo, las preguntas anteriores siguen sin respuesta. Gracias por cualquier ayuda.

Answer 1

Intentaré responder a sus preguntas:

1. Parece que la estructura MEMO contiene solo variantes finales del plan de consulta o variantes que luego se reescribieron en el plan final. ¿Hay alguna manera de encontrar planes de consulta "fallidos / poco prometedores"?

No, lamentablemente no hay forma de hacer eso. @Ronaldo pegó un bonito enlace en el comentario. Mi sugerencia es utilizar elInclude Live Query Statistics

e intente averiguar si ve un plan de consulta diferente. Utilice top 10, top 1000o *y verá que se propondrán diferentes planes de consulta. También puede usar query hinty forzar su plan de consulta a un patrón diferente. Básicamente, "haz tu propio plan de consultas descartadas"

2. Los operadores en MEMO no contienen una referencia a partes SQL. Por ejemplo, el operador LogOp_Get no contiene una referencia a una tabla específica.

Use QUERYTRACEON 8605, puedo ver una referencia a la tabla:

3. Las reglas de transformación no contienen una referencia precisa a los operadores MEMO, por lo tanto, no podemos estar seguros de qué operadores fueron transformados por la regla de transformación.

No veo ninguno GetToIdxScan - Get -> IdxScanen la consulta que proporcionó. Mi sugerencia es usar Usar QUERYTRACEON 8605, o QUERYTRACEON 8606debería haber una referencia allí.

EDITAR:

Entonces, "... es posible ver más información sobre los planes candidatos en SQL Server".

La respuesta es no , porque no hay otro plan de consulta candidato. De hecho, es un error común pensar que SQL Server le devuelve el mejor plan de consulta. SQL Server simplemente no puede calcular para usted todas las soluciones posibles: eso tomaría ... no sé ... minutos ...? horas ...? Calcular todas y cada una de las soluciones es inviable.

Pero si desea investigar por qué su plan de consulta elige ese patrón, puede usar:

• SET SHOWPLAN_ALL ON : y SQL Server le devolverá un árbol de la lógica de cada cálculo de su plan de consulta

• DBCC SHOW_STATISTICS('A', 'PK_A'): que le mostrará las estadísticas sobre una tabla de destino y una restricción. Creé una clave para mostrarte los resultados, naturalmente verás más información si tu tabla se consulta con más frecuencia

• USE HINT('force_legacy_cardinality_estimation') : le permitirá usar la estimación de cardinalidad anterior, para que pueda verificar si su plan de consulta podría haber sido más rápido con la estimación de cardinalidad heredada.