Демистификация процесса оптимизации SQL Server

Question

Хотелось бы видеть все варианты плана запроса, учитываемые при оптимизации запроса оптимизатором SQL Server. SQL Server предлагает довольно подробные сведения об использовании querytraceonпараметров. Например, QUERYTRACEON 3604, QUERYTRACEON 8615позволяет нам распечатать структуру MEMO и QUERYTRACEON 3604, QUERYTRACEON 8619распечатать список правил преобразования, применяемых в процессе оптимизации. Это здорово, однако у нас есть несколько проблем с выводами трассировки:

1. Кажется, что структура MEMO содержит только финальные варианты плана запроса или варианты, которые позже были переписаны в окончательный. Есть ли способ найти «неудачные / бесперспективные» планы запросов?
2. Операторы в MEMO не содержат ссылки на части SQL. Например, оператор LogOp_Get не содержит ссылки на конкретную таблицу.
3. Правила преобразования не содержат точной ссылки на операторы MEMO, поэтому мы не можем быть уверены, какие операторы были преобразованы с помощью правила преобразования.

Позвольте мне показать это на более подробном примере. Дайте мне две искусственные таблицы Aи 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;

Прямо сейчас я выполняю один простой запрос

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)
    

Я получаю довольно сложный вывод, описывающий структуру MEMO (вы можете попробовать самостоятельно) с 15 группами. Вот картинка, которая визуализирует структуру MEMO в виде дерева.

Из дерева можно заметить, что до того, как оптимизатор нашел окончательный план запроса, применялись определенные правила. Например join commute( JoinCommute), join to hash join( JNtoHS) или Enforce sort( EnforceSort). Как уже упоминалось, можно распечатать весь набор правил перезаписи, применяемых оптимизатором, используя QUERYTRACEON 3604, QUERYTRACEON 8619параметры. Проблемы:

1. Мы можем найти правило перезаписи JNtoSM( Join to sort merge) в списке 8619, однако оператора сортировки-слияния нет в структуре MEMO. Я понимаю, что сортировка-слияние, вероятно, было более затратным, но почему этого нет в MEMO?
2. Как узнать, LogOp_Getссылается ли оператор в MEMO на таблицу A или таблицу B?
3. Если я вижу правило GetToIdxScan - Get -> IdxScanв списке 8619, как сопоставить его с операторами MEMO?

Об этом есть ограниченное количество ресурсов. Я прочитал много сообщений в блоге Пола Уайта о правилах трансформации и MEMO, однако вышеперечисленные вопросы остались без ответа. Спасибо за любую помощь.

Answer 1

Постараюсь ответить на ваши вопросы:

1. Кажется, что структура MEMO содержит только окончательные варианты плана запроса или варианты, которые позже были переписаны в окончательный. Есть ли способ найти «неудачные / бесперспективные» планы запросов?

Нет, к сожалению, это невозможно. @Ronaldo вставил красивую ссылку в комментарий. Я предлагаю использоватьInclude Live Query Statistics

и попробуйте выяснить, видите ли вы другой план запроса. Используйте top 10, top 1000или, *и вы увидите, что будут предложены разные планы запросов. Вы также можете использовать query hintдругой шаблон для своего плана запроса. В основном "сделайте свой собственный план отклоненных запросов"

2. Операторы в MEMO не содержат ссылки на части SQL. Например, оператор LogOp_Get не содержит ссылки на конкретную таблицу.

Пользуюсь QUERYTRACEON 8605, вижу ссылку на таблицу:

3. Правила преобразования не содержат точной ссылки на операторы MEMO, поэтому мы не можем быть уверены, какие операторы были преобразованы с помощью правила преобразования.

Я не вижу ничего GetToIdxScan - Get -> IdxScanв заданном вами запросе. Я предлагаю использовать Use QUERYTRACEON 8605, или QUERYTRACEON 8606там должна быть ссылка.

РЕДАКТИРОВАТЬ:

Итак, «... можно ли увидеть больше информации о планах-кандидатах в SQL Server».

Ответ - нет , потому что нет другого плана запроса кандидата. Фактически, распространено заблуждение, что SQL Server возвращает вам лучший план запроса. SQL Server просто не может рассчитать для вас все возможные решения: это займет ... не знаю ... минут ...? часов ...? Рассчитывать каждое решение невозможно.

Но если вы хотите выяснить, почему ваш план запроса выбирает этот шаблон, вы можете использовать:

• SET SHOWPLAN_ALL ON : и SQL Server вернет вам дерево логики каждого расчета вашего плана запроса.

• DBCC SHOW_STATISTICS('A', 'PK_A'): который покажет вам статистику о целевой таблице и ограничении. Я создал ключ, чтобы показать вам результаты, естественно, вы увидите больше информации, если ваша таблица будет запрашиваться чаще

• USE HINT('force_legacy_cardinality_estimation') : позволит вам использовать предыдущую оценку мощности, чтобы вы могли проверить, мог ли ваш план запроса быть быстрее с устаревшей оценкой мощности.