Démystification du processus d'optimisation de SQL Server

Question

Nous aimerions voir toutes les variantes de plan de requête prises en compte lors d'une optimisation de requête par un optimiseur SQL Server. SQL Server offre des informations assez détaillées à l'aide d' querytraceonoptions. Par exemple, QUERYTRACEON 3604, QUERYTRACEON 8615nous permet d'imprimer la structure MEMO et d' QUERYTRACEON 3604, QUERYTRACEON 8619imprimer une liste des règles de transformation appliquées pendant le processus d'optimisation. C'est génial, cependant, nous avons plusieurs problèmes avec les sorties de trace:

1. Il semble que la structure MEMO ne contienne que des variantes finales du plan de requête ou des variantes qui ont ensuite été réécrites dans la dernière. Existe-t-il un moyen de trouver des plans de requête «infructueux / non prometteurs»?
2. Les opérateurs de MEMO ne contiennent pas de référence aux parties SQL. Par exemple, l'opérateur LogOp_Get ne contient pas de référence à une table spécifique.
3. Les règles de transformation ne contiennent pas de référence précise aux opérateurs MEMO, par conséquent, nous ne pouvons pas être sûrs quels opérateurs ont été transformés par la règle de transformation.

Permettez-moi de le montrer sur un exemple plus élaboré. Permettez-moi d'avoir deux tables artificielles Aet 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;

En ce moment, je lance une requête 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)
    

J'obtiens une sortie assez complexe qui décrit la structure MEMO (vous pouvez essayer par vous-même) ayant 15 groupes. Voici l'image, qui visualise la structure MEMO à l'aide d'un arbre.

Dans l'arborescence, on peut observer que certaines règles ont été appliquées avant que l'optimiseur ne trouve le plan de requête final. Par exemple join commute( JoinCommute), join to hash join( JNtoHS) ou Enforce sort( EnforceSort). Comme mentionné, il est possible d'imprimer l'ensemble des règles de réécriture appliquées par l'optimiseur à l'aide d' QUERYTRACEON 3604, QUERYTRACEON 8619options. Les problèmes:

1. Nous pouvons trouver une règle de réécriture JNtoSM( Join to sort merge) dans la liste 8619, cependant, l'opérateur de tri-fusion n'est pas dans la structure MEMO. Je comprends que le tri-fusion était probablement plus coûteux, mais pourquoi ce n'est pas dans MEMO?
2. Comment savoir si l' LogOp_Getopérateur dans MEMO fait référence à la table A ou à la table B?
3. Si je vois la règle GetToIdxScan - Get -> IdxScandans la liste 8619, comment la mapper aux opérateurs MEMO?

Il existe un nombre limité de ressources à ce sujet. J'ai lu de nombreux articles du blog de Paul White sur les règles de transformation et MEMO, cependant, les questions ci-dessus restent sans réponse. Merci pour toute aide.

Answer 1

J'essaierai de répondre à vos questions:

1. Il semble que la structure MEMO ne contienne que des variantes finales du plan de requête ou des variantes qui ont ensuite été réécrites dans la dernière. Existe-t-il un moyen de trouver des plans de requête «infructueux / non prometteurs»?

Non, malheureusement, il n'y a aucun moyen de faire cela. @Ronaldo a collé un joli lien dans le commentaire. Ma suggestion est d'utiliser leInclude Live Query Statistics

et essayez de savoir si vous voyez un plan de requête différent. Utilisez top 10, top 1000ou *et vous verrez que les différents plans de requête seront proposés. Vous pouvez également utiliser query hintet forcer votre plan de requête à un modèle différent. Fondamentalement, "faites votre propre plan de requête ignoré"

2. Les opérateurs de MEMO ne contiennent pas de référence aux parties SQL. Par exemple, l'opérateur LogOp_Get ne contient pas de référence à une table spécifique.

Utilisez QUERYTRACEON 8605, je peux voir une référence au tableau:

3. Les règles de transformation ne contiennent pas de référence précise aux opérateurs MEMO, par conséquent, nous ne pouvons pas être sûrs quels opérateurs ont été transformés par la règle de transformation

Je n'en vois aucun GetToIdxScan - Get -> IdxScandans la requête que vous avez fournie. Ma suggestion est d'utiliser Use QUERYTRACEON 8605, ou QUERYTRACEON 8606, il devrait y avoir une référence ici.

ÉDITER:

Donc "... est-il possible de voir plus d'informations sur les plans candidats dans SQL Server."

La réponse est non , car il n'y a pas d'autre plan de requête candidat. En fait, il y a une idée fausse courante selon laquelle SQL Server vous renvoie le meilleur plan de requête. SQL Server ne peut tout simplement pas calculer pour vous toutes les solutions possibles: cela prendrait ... je ne sais pas ... minutes ...? heures...? Il est impossible de calculer chaque solution unique.

Mais si vous voulez savoir pourquoi votre plan de requête a choisi ce modèle, vous pouvez utiliser:

• SET SHOWPLAN_ALL ON : et SQL Server vous renverra une arborescence de la logique de chaque calcul de votre plan de requête

• DBCC SHOW_STATISTICS('A', 'PK_A'): qui vous montrera les statistiques sur une table cible et une contrainte. J'ai créé une clé pour vous montrer les résultats, naturellement vous verrez plus d'informations si votre table est interrogée plus souvent

• USE HINT('force_legacy_cardinality_estimation') : vous permettra d'utiliser l'ancienne estimation de cardinalité, afin que vous puissiez vérifier si votre plan de requête aurait pu être plus rapide avec l'estimation de cardinalité héritée.