Оптимизация запросов SQL Server

В процессе оптимизации запросов оптимизатор прогнозирует число строк в результатах запроса в виде величины cardinality estimate (CE) для таких операций, как фильтрация, объединения и группирование, чтобы упростить выбор доступной стратегии обработки. Если известны данные, вводимые пользователем, и статистика, оптимизатору удается точно прогнозировать число строк в результатах и, следовательно, сделать правильный выбор. Но при отсутствии статистики и данных, вводимых пользователем, в оптимизаторе используется метод прогнозирования, именуемый оптимизацией для неизвестного (optimize for unknown). Было бы преувеличением назвать этот процесс прогнозированием; на самом деле это в большей степени угадывание. Если повезет, вы получите оценки, довольно близкие к реальности; если нет, то оценки будут неточными, и вряд ли удастся сделать оптимальный выбор.

В этой статье приводятся жестко заданные предположения, используемые оптимизатором в рамках метода «оптимизация для неизвестного». По крайней мере, в этом случае вы знаете, как оптимизатор угадывает неизвестные величины. Оптимальная настройка запросов в значительной мере начинается с умения объяснить оценки числа строк, особенно неточные.

В моих примерах запросы будут направлены к таблице Sales.SalesOrderDetail в тестовой базе данных AdventureWorks2014. Если вы захотите выполнить примеры из этой статьи, но не располагаете установленной базой данных, ее можно загрузить на сайте Codeplex по адресу: msftdbprodsamples.codeplex.com/releases/view/125550. Кроме того, стоит убедиться, что база данных настроена на уровень совместимости 120, при котором SQL Server по умолчанию использует новое средство CE (2014). Сделать это можно с помощью следующего программного кода:

-- Убедитесь, что уровень совместимости базы данных >= 120
для использования по умолчанию нового средства CE

USE AdventureWorks2014; -- https://msftdbprodsamples.codeplex.com/
releases/view/125550
GO

IF ( SELECT compatibility_level
     FROM sys.databases
     WHERE name = N'AdventureWorks2014' ) < 120
   ALTER DATABASE AdventureWorks2014
    SET COMPATIBILITY_LEVEL = 120;
-- использование уровня 130 в 2016

Оценки оптимизации для неизвестных я разделяю на следующие группы операторов:

* >, >=, <, <=
* BETWEEN и LIKE
* =

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

Оптимизация для неизвестного для операторов: >, >=, <, <=

Оценка с оптимизацией для неизвестного для группы операторов >, >=, < и <= составляет 30% количества элементов ввода. Это относится как к новому средству CE (2014), так и к старому (7.0).

Например, предположим, вы направляете запрос к таблице Sales.SalesOrderDetail в базе данных AdventureWorks2014 и используете фильтр, такой как WHERE OrderQty >= <неизвестный_ввод>. Число строк в таблице 121 317, поэтому CE фильтра будет 0,3 * 121317 = 36395,1. Насколько эта величина близка к действительному числу строк в типичном варианте использования, решать вам; однако оптимизатор делает именно такое предположение.

Это первый раздел, в котором демонстрируется метод оптимизации для неизвестного, поэтому начнем с перечисления различных случаев использования данного метода наряду с готовыми к применению примерами.

используется в следующих случаях.

1. При работе с локальными переменными

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

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

SELECT ProductID, COUNT (*)
   AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= 40
GROUP BY ProductID;

План выполнения для этого запроса показан на рисунке 1.

Рисунок 1. План запроса для запроса с константой

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

Выполнив этот программный код после предшествующего запроса, я получил имя статистики _WA_Sys_00000004_44 CA3770. Запомните полученное вами имя. Затем используйте следующий код для просмотра гистограммы после замены имени статистики на полученное вами:

W_STATISTICS
  (N'Sales.SalesOrderDetail',
  N'_WA_Sys_00000004_44CA3770')

  WITH HISTOGRAM;

Последние несколько шагов в полученной гистограмме показаны в таблице 1.

Мы ясно видим, что CE, показанная на рисунке 1, основана на последних трех шагах гистограммы. Оценка довольно точная: 4,00639 при действительном значении 4.

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

DECLARE @Qty AS INT = 40;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= @Qty
GROUP BY ProductID;

План для этого запроса показан на рисунке 2.

Рисунок 2. План запроса при использовании оператора >= с переменной

Как было предсказано, это оценка 30% количества элементов ввода. Примечательно, что из-за неточности оценки оптимизатор выбрал неоптимальную стратегию статистической обработки. Здесь использован алгоритм статистической обработки Hash Match вместо сортировки и алгоритма Stream Aggregate. Это лишь одно из многих возможных последствий неточных оценок.

Существует исключение, при котором оптимизатор может прослушивать переменные: событие перекомпиляции происходит на уровне инструкций. Дело в том, что по определению перекомпиляция на уровне инструкций происходит после того, как выполнено задание всех переменных. Автоматическая перекомпиляция всегда происходит на уровне инструкций. Так было все время после появления SQL Server 2005 и до написания данной статьи. Я тестирую программный код на SQL Server 2016. Для ручной перекомпиляции на уровне инструкций нужно добавить указание запроса RECOMPILE с использованием оператора OPTION:

DECLARE @Qty AS INT = 40;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= @Qty
GROUP BY ProductID
OPTION (RECOMPILE);

Этот запрос формирует такой же план, как показанный на рисунке 1, где оценка является точной.

Обратите внимание, что если указать параметр WITH RECOMPILE на уровне процедуры, то прослушивание не будет включено — это достигается только указанием в запросе OPTION (RECOMPILE).

Перейдем к следующему случаю использования метода оптимизации для неизвестного.

2. При использовании параметров, но отключенном автоматическом прослушивании параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (@parameter UNKNOWN) или с флагом трассировки 4136

Обычно значения параметров доступны для прослушивания, так как они задаются при выполнении процедуры или функции, прежде чем пакет передается оптимизатору. Однако можно применить метод оптимизации для неизвестного с двумя указаниями запроса. Если нужно отменить прослушивание параметров для всех входов, используйте указание OPTIMIZE FOR UNKNOWN. Если требуется отменить прослушивание для определенного параметра, используйте указание OPTIMIZE FOR (@parameter UNKNOWN). Также можно использовать флаг трассировки 4136 для отключения прослушивания параметров при разных детализациях: запроса, сеанса или глобальной детализации. Обратите внимание, что при использовании хранимой процедуры, скомпилированной в собственном коде, оптимизация для неизвестного выбирается по умолчанию.

В качестве примера следующий программный код создает хранимую процедуру и отключает прослушивание параметров в запросе с использованием указания OPTIMIZE FOR UNKNOWN:

IF OBJECT_ID(N'dbo.Proc1', N'P')
  IS NOT NULL DROP PROC dbo.Proc1;
GO
CREATE PROC dbo.Proc1
  @Qty AS INT
AS
SELECT ProductID, COUNT(*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= @Qty
GROUP BY ProductID
OPTION (OPTIMIZE FOR UNKNOWN);
GO

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

EXEC dbo.Proc1 @Qty = 40;

Я получил такой же план запроса, как показанный на рисунке 2, с оценкой 30%.

Рассмотрим еще один сценарий, в котором используется метод оптимизации для неизвестного.

3. Статистика недоступна

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

ALTER DATABASE AdventureWorks2014
  SET AUTO_CREATE_STATISTICS OFF;
GO
DROP STATISTICS Sales.SalesOrderDetail.
  _WA_Sys_00000004_44 CA3770;

Затем выполните код, в котором используется константа в фильтре:

SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= 40
GROUP BY ProductID;

Выполнив этот запрос ранее, вы получили план, показанный на рисунке 1, с точной оценкой. Но на этот раз у оптимизатора не было гистограммы, поэтому используется метод оптимизации для неизвестного и создается план, показанный на рисунке 2, с оценкой 30%.

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

ALTER DATABASE AdventureWorks2014
  SET AUTO_CREATE_STATISTICS ON;

Вы можете повторно запустить запрос и убедиться, что вы получаете план, как на рисунке 1.

Оценки оптимизации для неизвестных для операторов BETWEEN и LIKE

При использовании предиката BETWEEN жестко заданные предположения зависят от сценария и применяемой CE. В старых CE во всех случаях используется оценка 9%. Это демонстрирует следующий запрос. Флаг трассировки 9481 запроса используется, чтобы применить старую CE.

DECLARE @FromQty AS INT = 40,
  @ToQty AS INT = 41;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty BETWEEN @FromQty
  AND @ToQty
GROUP BY ProductID
OPTION (QUERYTRACEON 9481);

План для этого запроса показан на рисунке 3. Оценка 0,09 * 121317 = 10918,5.

Рисунок 3. План, показывающий оценку 9%

В новой CE задействованы различные оценки при применении констант и отсутствующей гистограмме и при использовании переменных или параметров с отключенным прослушиванием. В первом случае используется оценка 9%; во втором — оценка 16,4317%.

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

DECLARE @FromQty AS INT = 40,
  @ToQty AS INT = 41;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty BETWEEN @FromQty
  AND @ToQty
GROUP BY ProductID
OPTION (QUERYTRACEON 9481);

Я получил такой же план, как на рисунке 3, с оценкой 9%.

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

DECLARE @FromQty AS INT = 40,
  @ToQty AS INT = 41;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty BETWEEN @FromQty
  AND @ToQty
GROUP BY ProductID;

Я получил план, приведенный на рисунке 4, показывающий оценку 16,4317%.

Рисунок 4. План, показывающий оценку 16,4317%

При использовании предиката LIKE во всех сценариях оптимизации для неизвестного как в старых, так и в новых CE применяется оценка 9%. Ниже приведен пример с использованием локальных переменных:

DECLARE @Carrier AS NVARCHAR (50) =
   N’4911-403C-%’;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail

WHERE CarrierTrackingNumber LIKE
  @Carrier
GROUP BY ProductID;

Вы увидите ту же оценку 9%, как показано на рисунке 3, хотя в данном случае действительное число строк 12, а ранее было 3.

Оценки оптимизации для неизвестных для оператора =

При использовании оператора = различают три основных случая:

• уникальный столбец;
• неуникальный столбец и доступная плотность;
• неуникальный столбец и недоступная плотность.

Если фильтруемый столбец уникален (для него определены уникальный индекс, ограничение PRIMARY KEY или UNIQUE), то оптимизатору известно, что совпадений не может быть более одного, поэтому оценка равна 1. Ниже приводится запрос, демонстрирующий этот случай:

DECLARE @rowguid AS UNIQUEIDENTIFIER
  = ‘B207C96D-D9E6-402B-8470-
  2CC176C42283’;
SELECT *
FROM Sales.SalesOrderDetail
WHERE rowguid = @rowguid;

На рисунке 5 показан план для этого запроса с оценкой 1.

Рисунок 5. Оценка 1 для оператора = с уникальным столбцом

Если столбец не уникален и оптимизатору доступна информация о плотности (средний процент для отдельного значения), то оценка основывается на плотности. Если не отключено автоматическое создание статистики или для столбца сформирован индекс, то эта информация будет доступна оптимизатору. Чтобы продемонстрировать это, сначала убедитесь, что автоматическое создание статистики включено, выполнив следующий программный код:

ALTER DATABASE AdventureWorks2014
  SET AUTO_CREATE_STATISTICS ON;
Затем выполните следующий запрос:
DECLARE @Qty AS INT = 1;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty = @Qty
GROUP BY ProductID;

Помните, что плотность — средний процент для отдельного значения в столбце. Величина рассчитывается как 1/<отдельные_значения>. В столбце OrderQty 41 отдельное значение, поэтому 1/41 = 0,02439. Если применить этот процент к числу строк в таблице, то полученное значение будет очень близким к оценке на рисунке 6. Чтобы увидеть информацию о плотности, используемую SQL Server при выполнении следующего кода (с использованием имени статистики, полученного из запроса, приведенного в листинге):

DBCC SHOW_STATISTICS
  (N'Sales.SalesOrderDetail',
  N'_WA_Sys_00000004_44 CA3770')
WITH DENSITY_VECTOR;
был получен следующий вывод:
All density Average Length Columns
------------- -------------- ----------
0.02439024 2 OrderQty

Рисунок 6. План, показывающий оценки на основе плотности

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

При использовании неуникального столбца и недоступной плотности в старой и новой CE применяются различные методы. В старой CE используется оценка C^0,75 (степень три четвертых), где C — входное число элементов, а в новой используется оценка C^0,5 (квадратный корень).

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

ALTER DATABASE AdventureWorks2014
  SET AUTO_CREATE_STATISTICS OFF;
GO
DROP STATISTICS Sales.SalesOrderDetail.
  _WA_Sys_00000004_44 CA3770;

Используйте следующий программный код для тестирования старого метода CE:

DECLARE @Qty AS INT = 1;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty = @Qty
GROUP BY ProductID
OPTION (QUERYTRACEON 9481);

План для этого запроса показан на рисунке 7.

Рисунок 7. План, показывающий оценку старого CE при C^3/4

Оценка 6500,42 — результат вычисления 121317^3/4.

Используйте следующий программный код для тестирования нового метода CE:

DECLARE @Qty AS INT = 1;
SELECT ProductID, COUNT (*)
  AS NumOrders
FROM Sales.SalesOrderDetail

WHERE OrderQty = @Qty
GROUP BY ProductID;

План для этого запроса показан на рисунке 8.

Рисунок 8. План, показывающий оценку нового CE при C^0,5

Оценка 348,306 получена в результате вычисления 121317^0,5.

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

ALTER DATABASE AdventureWorks2014
  SET AUTO_CREATE_STATISTICS ON;

Таким образом, метод оптимизации для неизвестного используется оптимизатором SQL Server, чтобы создать оценку CE при неизвестных входных данных или недостатке статистики.

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

1. Использование переменных (кроме случаев использования RECOMPILE на уровне инструкций).
2. Использование параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (@parameter UNKNOWN) или флагом трассировки 4136 (всегда при использовании хранимой процедуры, скомпилированной в собственном коде).
3. Статистика недоступна.

В таблице 2 приведена сводка оценок оптимизации для неизвестного, используемых для различных групп операторов.

SELECT S.name AS stats_name
FROM sys.stats AS S
  INNER JOIN sys.stats_columns AS SC
    ON S.object_id = SC.object_id
   AND S.stats_id = SC.stats_id
  INNER JOIN sys.columns AS C
    ON SC.object_id = C.object_id
   AND SC.column_id = C.column_id
WHERE S.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')
  AND auto_created = 1
  AND C.name = N'OrderQty';