Временные таблицы и использование их в хранимых процедурах (баг)

Временные таблицы и использование их в хранимых процедурах

По мотивам статей — ссылка и ссылка.

Сегодня я бы хотел поговорить о временных таблицах (temporary tables) в целом и об  их использовании в хранимых процедурах (stored procedures). Есть общие рекомендации по временным таблицам, которые часто не соблюдаются, так же присутствует баг в их связки с хранимыми процедурами. 

Общие рекомендации по временным таблицам (temporary tables):

  • Не используйте конструкцию

    Не стоит заблуждаться, что данный пункт не столь важен. Вы не заметите как ваша выборка, после применения нескольких JOIN, превратиться в огромный объём данных, который сильно замедлит выполнения ваших запросов и увеличит нагрузку на tempdb.
  • Обязательно фильтруйте выборку

    Выбирайте только нужные данные, чтобы их хранение и скорость обработки были на должном уровне.
  • Создавайте индексы на временных таблицах (temporary tables) после загрузки в них данных. Если индексы будут созданы до загрузки данных, то на момент обращениях к временной таблице, её статистика может быть не актуальной
  • Существует так называемая проблема

    Суть её заключается в конкуренции за tempdb. Дело в том, что только один поток, в один момент времени, может создавать и удалять объекты в tempdb, остальные обязаны ожидать завершения операции. Для обхода «Tempdb Latch Contention» достаточно иметь несколько файлов данных tempdb, но они должны быть одного размера, чтобы механизм распределения данных между файлами данных отрабатывал корректно. Для этого необходимо глобально включить флаг трассировки  1118 (одновременное увеличение размера всех файлов данных БД), создать несколько файлов данных tempdb с одинаковым размером и настроить им один размер приращения. Количество файлов должно быть равно вашему количеству ядер процессора, но при количестве процессоров более 8, необходимо эксперементально вычислить это количество. В такой конфигурации начните с 8 файлов данных tempdb и наблюдайте за сервером.

Использование временных таблиц в хранимых процедурах (temporary tables in stored procedures):

В механизме работы временных таблиц в хранимых процедурах есть несколько моментов, которые необходимо знать:

  • Самое основное — создание и последующее удаление временной таблицы внутри хранимой процедуры не приводит к ожидаемому результату. Вместо этого SQL Server делает Truncate и Rename таблицы, после чего использует её снова. В результате мы получаем статистику от прошлого выполнения и как результат возможный плохой план запроса.
  • Так как мы говорим о временных таблицах в контексте хранимых процедур, то не стоит забыть о таком явлении как «Parameter Sniffing» (использование ранее скомпилированного плана для процедуры с прошлым значением параметра. Проблема в том, что для нового параметра такой план может быть не оптимальным, так как объём данных будет другим). Для решения можно добавить к запросу опцию:

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

Решение:

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

Для начала я бы хотел напомнить когда возникает  автоматическое перестроение статистики:

Recompilation Thresholds

Предположим, что наш запрос вернёт 9 строк, это меньше 500, поэтому для автоматического обновления статистики нам потребуется обновить 500+9 строк. Если следующий запрос вернёт 100 строк, то статистика не будет автоматически перестроена, так как счётчик не достигнет значения 509. Множественный вызов процедуры приведёт к автоматическому обновлению, но все запросы между могут выполняться не оптимально. На самом деле счётчик будет обновляться несколько быстрее, так как TRUNCATEтак же увеличивает его. По этой причине нам необходимо обновлять статистику в ручную (UPDATE STATISTICS) после добавления данных и до обращения к таблице.

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

Таким образом мы получаем новый план на новой статистике.

Выводы:

  • Правильно используйте временные таблицы (фильтруйте выборку строк и столбцов)
  • Если вы испытываете проблемы автоматического обновления статистики на временных таблицах в хранимых процедурах, используйте совместно OPTION (RECOMPILE) и  ручное UPDATE STATISTICS после добавления данных и до выборки.

В SQL Server 2014 поведение не изменилось.

Примеры и детали можно посмотреть тут и тут (оригиналы статей на англ.)

Кейс в Microsoft по данному багу (англ.) — ссылка

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube

Запись опубликована в рубрике В помощь администратору с метками , , , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: