Устранение неполадок с медленными запросами, на которые влияет время ожидания оптимизатора запросов

Статья
03/24/2023

Применяется к: SQL Server

В этой статье описывается время ожидания оптимизатора, как оно может повлиять на производительность запросов и как оптимизировать производительность.

Что такое время ожидания оптимизатора?

SQL Server использует оптимизатор запросов на основе затрат (QO). Сведения о QO см. в руководстве по архитектуре обработки запросов. Оптимизатор запросов на основе затрат выбирает план выполнения запроса с наименьшими затратами после создания и оценки нескольких планов запросов. Одна из целей оптимизатора запросов SQL Server заключается в том, чтобы тратить разумное время на оптимизацию запросов по сравнению с выполнением запросов. Оптимизация запроса должна выполняться гораздо быстрее, чем его выполнение. Чтобы достичь этой цели, QO имеет встроенное пороговое значение задач, которые следует учитывать перед остановкой процесса оптимизации. Когда пороговое значение достигнуто до того, как QO рассмотрит все возможные планы, оно достигает предела времени ожидания оптимизатора. Событие Timeout оптимизатора отображается в плане запроса как TimeOut в разделе Причина раннего завершения оптимизации инструкции. Важно понимать, что это пороговое значение основано не на часах, а на количестве возможностей, рассмотренных оптимизатором. В текущих версиях качества обслуживания SQL Server до истечения времени ожидания учитывается более полумиллиона задач.

Время ожидания оптимизатора предназначено для SQL Server, и во многих случаях это не фактор, влияющий на производительность запросов. Однако в некоторых случаях на выбор плана sql-запроса может негативно повлиять время ожидания оптимизатора, что может привести к снижению производительности запросов. При возникновении таких проблем понимание механизма тайм-аута оптимизатора и того, как можно повлиять на сложные запросы, поможет вам устранить неполадки и повысить скорость запросов.

В результате достижения порогового значения времени ожидания оптимизатора SQL Server не рассмотрел весь набор возможностей для оптимизации. Это значит, что он может пропустить планы, которые могли бы привести к более короткому времени выполнения. QO остановится на пороге и рассмотрит план запроса с наименьшей стоимостью на этом этапе, несмотря на то, что могут быть более лучшие, неисследованные варианты. Помните, что план, выбранный после достижения времени ожидания оптимизатора, может обеспечить достаточную длительность выполнения запроса. Однако в некоторых случаях выбранный план может привести к неоптимальному выполнению запроса.

Как определить время ожидания оптимизатора?

Ниже приведены симптомы, указывающие на истечение времени ожидания оптимизатора.

Сложный запрос

У вас есть сложный запрос, который включает в себя большое количество объединенных таблиц (например, восемь или более таблиц соединены).
Медленный запрос

Запрос может выполняться медленнее или медленнее, чем в другой SQL Server версии или системе.
План запроса: StatementOptmEarlyAbortReason=Timeout
- План запроса отображается StatementOptmEarlyAbortReason="TimeOut" в плане XML-запроса.
```
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>
```
- Проверьте свойства левого оператора плана в Microsoft SQL Server Management Studio. Вы увидите значение Аргумент для раннего завершения оптимизации инструкции — TimeOut.

Что вызывает истечение времени ожидания оптимизатора?

Простого способа определить, какие условия могут привести к достижении или превышению порогового значения оптимизатора, не существует. В следующих разделах приведены некоторые факторы, влияющие на то, сколько планов изучает QO при поиске лучшего плана.

В каком порядке следует объединять таблицы?

Ниже приведен пример параметров выполнения трех табличных соединений (Table1, Table2, Table3):
- Соединение Table1 с Table2 и результат с Table3
- Соединение Table1 с Table3 и результат с Table2
- Соединение Table2 с Table3 и результат с Table1
Примечание: Чем больше число таблиц, тем больше возможностей.
Какую структуру доступа к куче или двоичному дереву (HoBT) следует использовать для получения строк из таблицы?
- Кластеризованный индекс
- Некластеризованный индекс1
- Некластеризованный индекс2
- Куча таблиц
Какой метод физического доступа следует использовать?
- Поиск по индексу
- Проверка индекса
- Сканирование таблицы
Какой оператор физического соединения следует использовать?
- Соединение вложенных циклов (NJ)
- Хэш-соединение (HJ)
- Объединение слиянием (MJ)
- Адаптивное соединение (начиная с SQL Server 2017 г. (14.x))
Дополнительные сведения см. в разделе Соединения.
Выполнять части запроса параллельно или последовательно?

Дополнительные сведения см. в разделе Параллельная обработка запросов.

В то время как следующие факторы уменьшат количество рассмотренных методов доступа и, следовательно, рассмотренные возможности:

Предикаты запроса (фильтры в предложении WHERE )
Существование ограничений
Сочетания хорошо продуманной и актуальной статистики

Примечание: Тот факт, что QO достигает порогового значения, не означает, что запрос будет медленнее. В большинстве случаев запрос будет работать хорошо, но в некоторых случаях может наблюдаться более медленное выполнение запроса.

Пример того, как учитываются факторы

Для иллюстрации рассмотрим пример соединения между тремя таблицами (t1, t2и t3) и каждой таблицей с кластеризованным индексом и некластеризованным индексом.

Во-первых, рассмотрим типы физического соединения. Здесь задействовано два соединения. Так как существует три варианта физического соединения (NJ, HJ и MJ), запрос может выполняться 3² = 9 способами.

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

Затем рассмотрим порядок соединения, который вычисляется с использованием перестановок: P (n, r). Порядок первых двух таблиц не имеет значения, поэтому может быть P(3,1) = 3 возможности:

Объединить t1 с, t2 а затем с t3
Объединить t1 с, t3 а затем с t2
Объединить t2 с, t3 а затем с t1

Далее рассмотрим кластеризованные и некластеризованные индексы, которые можно использовать для получения данных. Кроме того, для каждого индекса у нас есть два метода доступа: поиск или сканирование. Это означает, что для каждой таблицы есть 2² = 4 варианта. У нас есть три таблицы, поэтому может быть 4³ = 64 варианта.

Наконец, учитывая все эти условия, может быть 9*3*64 = 1728 возможных планов.

Теперь предположим, что в запросе есть n таблиц, объединенных, и каждая таблица имеет кластеризованный индекс и некластеризованный индекс. Учитывайте следующие факторы.

Заказы на присоединение: P(n,n-2) = n!/2
Типы соединений: 3^n-1
Различные типы индексов с методами поиска и сканирования: 4ⁿ

Умножьте все это выше, и мы можем получить количество возможных планов: 2*n!*12^n-1. Если n = 4, число равно 82 944. Если n = 6, число равно 358 318 080. Таким образом, с увеличением числа таблиц, участвующих в запросе, число возможных планов увеличивается геометрически. Кроме того, если включить возможность параллелизма и другие факторы, можно представить, сколько возможных планов будет рассмотрено. Таким образом, запрос с большим количеством соединений с большей вероятностью достигнет порогового значения времени ожидания оптимизатора, чем запрос с меньшим количеством соединений.

Обратите внимание, что приведенные выше вычисления иллюстрируют наихудший сценарий. Как мы уже отмечали, существуют факторы, которые сокращают количество возможностей, таких как предикаты фильтра, статистика и ограничения. Например, предикат фильтра и обновленная статистика сокращают количество методов физического доступа, так как использование поиска индекса может быть более эффективным, чем сканирование. Это также приведет к меньшему выбору соединений и т. д.

Почему отображается время ожидания оптимизатора с простым запросом?

Ничего с оптимизатором запросов не является простым. Существует много возможных сценариев, и степень сложности настолько высока, что трудно понять все возможности. Оптимизатор запросов может динамически задавать пороговое значение времени ожидания на основе стоимости плана, найденного на определенном этапе. Например, если найден план, который выглядит относительно эффективным, ограничение задачи для поиска лучшего плана может быть сокращено. Таким образом, заниженная оценка кратности (CE) может быть одним из сценариев раннего превышения времени ожидания оптимизатора. В этом случае основное внимание в расследовании уделяется CE. Это более редкий случай по сравнению со сценарием выполнения сложного запроса, который обсуждался в предыдущем разделе, но это возможно.

Решения

Время ожидания оптимизатора, отображающееся в плане запроса, не обязательно означает, что это является причиной низкой производительности запроса. В большинстве случаев вам может не потребоваться ничего делать в этой ситуации. План запроса, который SQL Server заканчивается, может быть разумным, и выполняемый запрос может работать хорошо. Возможно, вы никогда не знаете, что столкнулись с истечением времени ожидания оптимизатора.

Если вам потребуется настроить и оптимизировать, выполните следующие действия.

Шаг 1. Создание базового плана

Проверьте, можно ли выполнить один и тот же запрос с тем же набором данных в другой сборке SQL Server, с другой конфигурацией CE или в другой системе (спецификациях оборудования). Основной принцип настройки производительности заключается в том, что без базовых показателей не возникает проблем с производительностью. Поэтому было бы важно создать базовый план для того же запроса.

Шаг 2. Поиск "скрытых" условий, которые приводят к истечению времени ожидания оптимизатора

Подробно изучите запрос, чтобы определить его сложность. При первоначальном изучении может быть не очевидно, что запрос является сложным и включает в себя много соединений. Распространенный сценарий здесь заключается в том, что используются представления или функции с табличным значением. Например, на поверхности запрос может показаться простым, так как он объединяет два представления. Но при изучении запросов в представлениях может оказаться, что каждое представление объединяет семь таблиц. В результате при соединении двух представлений в конечном итоге вы 14-табличное соединение. Если в запросе используются следующие объекты, выполните детализацию по каждому объекту, чтобы увидеть, как выглядят базовые запросы внутри него:

Во всех этих сценариях наиболее распространенным решением является перезапись запроса и его разделение на несколько запросов. Дополнительные сведения см. в разделе Шаг 7. Уточнение запроса .

Вложенные запросы или производные таблицы

Ниже приведен пример запроса, который объединяет два отдельных набора запросов (производные таблицы) с 4–5 соединениями в каждом. Однако после анализа с помощью SQL Server он будет компилирован в один запрос с восемью объединенными таблицами.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

Общие табличные выражения (CTA)

Использование нескольких общих табличных выражений (CTE) не является подходящим решением для упрощения запроса и предотвращения истечения времени ожидания оптимизатора. Несколько CTA только усложнят запрос. Таким образом, использование CTA при решении проблем с истечением времени ожидания оптимизатора является контрпродуктивным. CTA выглядят как логические разрывы запроса, но они будут объединены в один запрос и оптимизированы как одно большое соединение таблиц.

Ниже приведен пример CTE, который будет компилироваться как один запрос с большим количеством соединений. Может показаться, что запрос к my_cte представляет собой простое соединение из двух объектов, но на самом деле в CTE есть семь других таблиц.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

Представления

Убедитесь, что вы проверили определения представлений и включили все таблицы. Как и в случае с CES и производными таблицами, соединения могут быть скрыты внутри представлений. Например, соединение между двумя представлениями в конечном итоге может быть одним запросом с восемью таблицами:

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

Функции с табличным значением (ТВФ)

Некоторые соединения могут быть скрыты внутри TFV. В следующем примере показано, что отображается как соединение между двумя TFV, а таблица может быть соединением из девяти таблиц.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Union

Операторы объединения объединяют результаты нескольких запросов в один результирующий набор. Они также объединяют несколько запросов в один запрос. Затем вы можете получить один сложный запрос. В следующем примере будет один план запроса, который включает 12 таблиц.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

Шаг 3. Если у вас есть базовый запрос, который выполняется быстрее, используйте его план запроса

Если вы определили, что определенный базовый план, полученный на шаге 1 , лучше подходит для запроса через тестирование, используйте один из следующих параметров, чтобы заставить QO выбрать этот план:

Шаг 4. Уменьшение количества вариантов планов

Чтобы снизить вероятность истечения времени ожидания оптимизатора, попробуйте уменьшить возможности, которые необходимо учитывать при выборе плана. Этот процесс включает в себя тестирование запроса с различными параметрами указания. Как и в случае с большинством решений с QO, выбор не всегда детерминирован на поверхности, поскольку необходимо учитывать большое количество факторов. Таким образом, не существует ни одной гарантированной успешной стратегии, и выбранный план может повысить или уменьшить производительность выбранного запроса.

Принудительное применение заказа JOIN

Используйте OPTION (FORCE ORDER) для устранения перестановок порядка:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

Сокращение возможностей JOIN

Если другие альтернативы не помогли, попробуйте уменьшить количество сочетаний планов запросов, ограничив выбор операторов физических соединений указаниями соединения. Например, или OPTION (HASH JOIN, MERGE JOIN)OPTION (HASH JOIN, LOOP JOIN)OPTION (MERGE JOIN).

Примечание: При использовании этих указаний следует соблюдать осторожность.

В некоторых случаях ограничение оптимизатора с меньшим количеством вариантов соединения может привести к тому, что лучший вариант соединения будет недоступен и может фактически замедлить выполнение запроса. Кроме того, в некоторых случаях оптимизатору требуется определенное соединение (например, цель строки), и запрос может не создать план, если это соединение не является вариантом. Таким образом, после указания соединения для определенного запроса проверка, если вы найдете сочетание, которое обеспечивает более высокую производительность и устраняет время ожидания оптимизатора.

Ниже приведены два примера использования таких указаний:

Используйте OPTION (HASH JOIN, LOOP JOIN) , чтобы разрешить только хэш-соединения и циклы и избежать объединения слиянием в запросе:
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

Принудительное соединение между двумя таблицами:

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

Шаг 5. Изменение конфигурации CE

Попробуйте изменить конфигурацию CE, переключившись между прежними версиями CE и New CE. Изменение конфигурации CE может привести к выбору другого пути качества, когда SQL Server оценивает и создает планы запросов. Таким образом, даже если возникает проблема времени ожидания оптимизатора, возможно, вы получите план, который работает более оптимально, чем выбранный с помощью альтернативной конфигурации CE. Дополнительные сведения см. в разделе Активация оптимального плана запроса (оценка кратности).

Шаг 6. Включение исправлений оптимизатора

Если вы не включили исправления оптимизатора запросов, попробуйте включить их с помощью одного из следующих двух методов:

Уровень сервера: используйте флаг трассировки T4199.
Уровень базы данных: используйте ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON или измените уровни совместимости базы данных для SQL Server 2016 и более поздних версий.

Исправления качества обслуживания могут привести к тому, что оптимизатор примет другой путь при просмотре плана. Поэтому он может выбрать более оптимальный план запроса. Дополнительные сведения см. в SQL Server модели обслуживания 4199 оптимизатора запросов.

Шаг 7. Уточнение запроса

Рассмотрите возможность разделения одного запроса с несколькими таблицами на несколько отдельных запросов с помощью временных таблиц. Разбиение запроса — это лишь один из способов упростить задачу для оптимизатора. Пример приведен в следующей статье:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Чтобы оптимизировать запрос, попробуйте разбить один запрос на два запроса, вставив часть результатов соединения во временную таблицу:

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Поделиться через