Табличные переменные и временные таблицы в SQL Server 2014: кардинальность с флагом трассировки 2453
Содержание:
1. Типы таблиц, оптимизированных для размещения в оперативной памяти, и параметры TVP;
2. Параллельная инструкция SELECT INTO;
3.Кардинальность с флагом трассировки 2453 (Вы читаете данный раздел).
Последнее новшество, рассматриваемое в этой статье, относится к табличным переменным и доступно в версиях SQL Server 2014 RTM CU3 и SQL Server 2012 SP2. Справочную информацию можно найти по адресу: (http://support.microsoft.com/kb/2952444/en-us). Как известно, в SQL Server нет средств статистики, таких как гистограммы и векторы плотности для табличных переменных. Однако SQL Server ведет подсчет строк в таблице, что в некоторых случаях может быть очень полезно для оптимизатора.
Хороший пример — ситуация, когда нужно сохранить набор ключей, скажем, идентификаторов заказа, а затем объединить табличную переменную с пользовательской таблицей, чтобы получить данные из связанных строк. При малом числе значений в табличной переменной оптимальная стратегия — использовать последовательный план с алгоритмом соединения вложенными циклами. При большом числе значений предпочтительно использовать параллельный план с алгоритмом хеш-соединения.
Хотя SQL Server учитывает число строк в табличной переменной, эти сведения обычно недоступны оптимизатору. Причина в том, что первоначальная единица оптимизации представляет собой целый пакет, а не единственную инструкцию (в отличие от единицы повторной компиляции). Оптимизатор не выполняет программный код, который заполняет табличную переменную перед оптимизацией запроса; поэтому оптимизация запроса производится без сведений о числе строк. По умолчанию предполагается, что таблица очень мала (обычно состоит из одной строки).
Соединение с вложенными циклами
В качестве примера рассмотрим программный код, приведенный выше.
План для соединения с вложенными циклами
План для запроса показан на рисунке выше. Обратите внимание, что прогнозируемое число строк равно 1, хотя в действительности их 100000 (впрочем, задействование такого огромного числа строк на низкопроизводительном оборудовании может привести к негативным последствиям, вплоть до его поломки, так что без его ремонта будет не обойтись (подробности на http://cyber.dp.ua/ (http://cyber.dp.ua/))). В результате оптимизатор выбирает последовательный план с алгоритмом соединения с вложенными циклами.
План для соединения с хешем
Типовое решение этой задачи — заставить SQL Server перекомпилировать запрос при каждом выполнении программного кода, указывая параметр RECOMPILE. В нашем случае просто уберите символ комментария в коде. Если данный параметр применяется, код оптимизируется на уровне инструкций после заполнения табличной переменной, поэтому число строк известно оптимизатору. Выполните программный код после удаления символа комментария параметра. План для запроса показан на рисунке выше. Обратите внимание, что на этот раз мощность связи точна и потому оптимизатор выбирает параллельный план с алгоритмом хеш-соединения.
Таким образом, благодаря параметру RECOMPILE вы получаете эффективный план на основе известного числа строк, но за это приходится платить перекомпиляцией при каждом выполнении.
Создание типа таблицы с именем OrderlDs
Другой способ предоставить оптимизатору информацию о числе строк в табличной переменной — передать его хранимой процедуре в качестве входного параметра TVP. Оптимизатор может определить число строк, так как табличная переменная заполняется до того, как она будет передана в хранимую процедуру как TVP. то есть до начала оптимизации. Чтобы продемонстрировать это решение, сначала создайте тип таблицы с именем OrderlDs, выполнив программный код, представленный выше.
1. Типы таблиц, оптимизированных для размещения в оперативной памяти, и параметры TVP;
2. Параллельная инструкция SELECT INTO;
3.
Последнее новшество, рассматриваемое в этой статье, относится к табличным переменным и доступно в версиях SQL Server 2014 RTM CU3 и SQL Server 2012 SP2. Справочную информацию можно найти по адресу: (http://support.microsoft.com/kb/2952444/en-us). Как известно, в SQL Server нет средств статистики, таких как гистограммы и векторы плотности для табличных переменных. Однако SQL Server ведет подсчет строк в таблице, что в некоторых случаях может быть очень полезно для оптимизатора.
Хороший пример — ситуация, когда нужно сохранить набор ключей, скажем, идентификаторов заказа, а затем объединить табличную переменную с пользовательской таблицей, чтобы получить данные из связанных строк. При малом числе значений в табличной переменной оптимальная стратегия — использовать последовательный план с алгоритмом соединения вложенными циклами. При большом числе значений предпочтительно использовать параллельный план с алгоритмом хеш-соединения.
Хотя SQL Server учитывает число строк в табличной переменной, эти сведения обычно недоступны оптимизатору. Причина в том, что первоначальная единица оптимизации представляет собой целый пакет, а не единственную инструкцию (в отличие от единицы повторной компиляции). Оптимизатор не выполняет программный код, который заполняет табличную переменную перед оптимизацией запроса; поэтому оптимизация запроса производится без сведений о числе строк. По умолчанию предполагается, что таблица очень мала (обычно состоит из одной строки).
Соединение с вложенными циклами
В качестве примера рассмотрим программный код, приведенный выше.
План для соединения с вложенными циклами
План для запроса показан на рисунке выше. Обратите внимание, что прогнозируемое число строк равно 1, хотя в действительности их 100000 (впрочем, задействование такого огромного числа строк на низкопроизводительном оборудовании может привести к негативным последствиям, вплоть до его поломки, так что без его ремонта будет не обойтись (подробности на http://cyber.dp.ua/ (http://cyber.dp.ua/))). В результате оптимизатор выбирает последовательный план с алгоритмом соединения с вложенными циклами.
План для соединения с хешем
Типовое решение этой задачи — заставить SQL Server перекомпилировать запрос при каждом выполнении программного кода, указывая параметр RECOMPILE. В нашем случае просто уберите символ комментария в коде. Если данный параметр применяется, код оптимизируется на уровне инструкций после заполнения табличной переменной, поэтому число строк известно оптимизатору. Выполните программный код после удаления символа комментария параметра. План для запроса показан на рисунке выше. Обратите внимание, что на этот раз мощность связи точна и потому оптимизатор выбирает параллельный план с алгоритмом хеш-соединения.
Таким образом, благодаря параметру RECOMPILE вы получаете эффективный план на основе известного числа строк, но за это приходится платить перекомпиляцией при каждом выполнении.
Создание типа таблицы с именем OrderlDs
Другой способ предоставить оптимизатору информацию о числе строк в табличной переменной — передать его хранимой процедуре в качестве входного параметра TVP. Оптимизатор может определить число строк, так как табличная переменная заполняется до того, как она будет передана в хранимую процедуру как TVP. то есть до начала оптимизации. Чтобы продемонстрировать это решение, сначала создайте тип таблицы с именем OrderlDs, выполнив программный код, представленный выше.
