Maximum Row Size and Query Hints

Автор: Craig Freedman Maximum Row Size and Query Hints

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

CREATE TABLE T (A INT, B CHAR(8000), C CHAR(8000))

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'T' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Какое же отношение ограничение размера записи имеет к подсказкам оптимизатору?

Давайте начнем со следующего простого соединения:

CREATE TABLE T1 (A INT, B CHAR(8000))

CREATE TABLE T2 (A INT, B CHAR(8000))

CREATE TABLE T3 (A INT, B CHAR(8000))

 

SELECT *

FROM T1 JOIN T2 ON T1.A = T2.A

 

  |--Hash Match(Inner Join, HASH:([T2].[A])=([T1].[A]), RESIDUAL:([T2].[A]=[T1].[A]))

       |--Table Scan(OBJECT:([T2]))

       |--Table Scan(OBJECT:([T1]))

 

Пока все хорошо. Теперь предположим, что мы хотим отсортировать результаты этого запроса:

SELECT *

FROM T1 JOIN T2 ON T1.A = T2.A

ORDER BY T1.A

 

  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([T1].[A])=([T2].[A]), RESIDUAL:([T2].[A]=[T1].[A]))

       |--Sort(ORDER BY:([T1].[A] ASC))

       |    |--Table Scan(OBJECT:([T1]))

       |--Sort(ORDER BY:([T2].[A] ASC))

            |--Table Scan(OBJECT:([T2]))

 

Оптимизатор переключится на план с Merge Join. Этот план также может показаться вполне разумным. SQL Server должен отсортировать данные, так почему бы не сделать это перед Join? Вспомним, что Merge Join сохраняет порядок, поэтому SQL Server не нужна повторная сортировка после Join. Однако предположим, что мы упорно хотим получить в плане Hash Join. У нас может возникнуть соблазн добавить хинт, который укажет использование именно этого типа соединения:

SELECT *

FROM T1 JOIN T2 ON T1.A = T2.A

ORDER BY T1.A

OPTION (HASH JOIN)

 

Msg 8618, Level 16, State 2, Line 1

The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider reducing the number and/or size of the fields in the clause. Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.

Что же произошло? Поскольку хэш-соединение не сохраняет порядок, SQL Server должен выполнить сортировку после соединения. Для сортировки нужна промежуточная таблица, в которой должны быть подлежащие сортировке колонки. В последнем примере это T1.B и T2.B. Эти две колонки с типом CHAR(8000) вместе превышают ограничение на размер строки в 8060 байт, поэтому SQL Server не может создать промежуточную таблицу, и запрос завершается ошибкой. Обратите внимание, что само по себе хэш-соединение не является проблемой (исходный запрос выше успешно выполняется с хэш-соединением), тут скорее сортировка вызывает ошибку.

Давайте рассмотрим другой пример:

SELECT *

FROM (T1 JOIN T2 ON T1.A = T2.A) JOIN T3 ON T1.A = T3.A

 

  |--Nested Loops(Inner Join, WHERE:([T2].[A]=[T3].[A]))

       |--Hash Match(Inner Join, HASH:([T3].[A])=([T1].[A]), RESIDUAL:([T1].[A]=[T3].[A]))

       |    |--Table Scan(OBJECT:([T3]))

       |    |--Table Scan(OBJECT:([T1]))

       |--Table Scan(OBJECT:([T2]))

Обратите внимание, что этот запрос использует одно соединение Hash Match и одно Nested Loops. Предположим, что нам тут очень нужны два Hash Match. И мы снова поддадимся искушению использовать подсказку оптимизатору:

SELECT *

FROM (T1 JOIN T2 ON T1.A = T2.A) JOIN T3 ON T1.A = T3.A

OPTION (HASH JOIN)

 

Этот запрос завершается с той же ошибкой - 8618, которую мы видели выше. Почему? Как и в случае с сортировкой, хэш-соединению также потребуется промежуточная таблица. Фактически, хэш-соединение требует существования промежуточной таблицы как для левого, так и правого входов. Независимо от того, какую пару таблиц SQL Server станет соединять первыми, результаты этого соединения будут содержать колонки CHAR (8000), и когда нужно будет соединить итог с третьей таблицей, последнее хэш-соединение будет нуждаться в промежуточной таблице, которая превышает ограничение на размер строки в 8060 байт.

Существует еще множество примеров, когда подсказки могут привести к сбою запроса из-за превышающей ограничение на размер строки промежуточной таблицы. Одним из решений этой проблемы, конечно же, является удаление вредительской подсказки. Другое решение — заменить тип CHAR фиксированной длины на VARCHAR переменной длины.