Как Sql Server Перешел На Использование Неоптимального Плана Запросов Каждые Два-Три Часа

Последние пару дней я работал над интересной проблемой и хотел бы поделиться своим интересным опытом с сообществом.

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

Но проходит три часа и то же хранилище, с теми же параметрами работает 2 минуты, и то же самое для пользователей.

При этом данные в используемые таблицы не вставлялись/удалялись, окружение не менялось и админы не производили никаких настроек.

Локализовано перед запросом:

   

 INSERT INTO @table_variable1
 SELECT .


 FROM   dbo.view_with_unions v1 WITH (READUNCOMMITTED) 
        LEFT JOIN @table_variable2 AS t1
               ON  t1.Code = v1.DirectionDimensionCode
        LEFT JOIN other_table v2 WITH (READUNCOMMITTED)
               ON  v2.Code = v1.SaleType
 WHERE  .


 

   

Уже что-то! Потом я стал более внимательно присматриваться к тому, что меняется в плане реализации.

Оказалось, что план длительного выполнения использует объединения NestedLoop, а план быстрого выполнения — HashMatch. Быстрый план:



Медленный (на который SQL Server переключается через 2 часа):



Я не хотел просто писать HINT для использования HASH JOIN, потому что.

нужно понимать, почему SQL Server всё равно выбирает неправильный план.

Первая мысль была, что что-то не так со статистикой, но на плане выполнения от профилировщика Actual Number Of Rows было 0, а Estimated Number Of Rows — 1. Итак.

Так что разница не такая большая, чтобы исследовать проблемы со статистикой и кардинальностью.

Однако, раз за разом глядя на Actual Number Of Rows = 0, у меня возникали сомнения — неужели ни одна строка не возвращается всегда.

Оказалось, что это не так, профайлер просто перехватывает план выполнения до того, как запрос будет выполнен и Актуальные данные станут известны.

И соответственно, он не может отображать в Реальном количестве строк ничего, кроме нуля.

Хорошо, теперь давайте посмотрим на реальные значения Actual Number Of Rows! Тогда возник вопрос — почему Estimated Number Of Rows всегда равно единице? Ведь индекс используется, у него актуальная статистика.

И значение Estimated Number Of Rows каждый раз равно 1. Но здесь нет никаких сюрпризов — SQL Server не использует статистику, если он начинается с малоизбирательного столбца (то есть, если количество разных значений невелико, например: 0, 1, NULL).

Поэтому я переместил первый столбец в индексном ключе на последнее место.

Предварительно убедившись, что все условия для этих столбцов прописаны в WHERE и перечислены через AND, а значит индекс с его статистикой по-прежнему пригоден для использования.

Диагноз:

1. Профилировщик не отображает фактическое количество строк в планах выполнения и записывает не «н/д», а ноль.

   Нам нужно помнить об этом!

2. В нашем случае SQL Server не использует статистику для определения предполагаемого количества строк.

3. И даже если он начнет использовать статистику, то очень сильно ошибается.

1. Вручную запускаем длинные запросы из профайлера и смотрим на реальное фактическое количество строк.

2. Вам необходимо предоставить SQL Server возможность использовать статистику по индексу; для этого первый столбец в ключе должен иметь много разных значений (например, не три 0, 1, NULL).

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

3. После перестроения индекса вам необходимо обновить статистику с помощью опции С FULLSCAN, чтобы улучшить качество прогнозов Estimated Number Of Rows: ОБНОВИТЬ СТАТИСТИКУ [dbo].

   [ table_from_union_for_view ] С ПОЛНЫМ СКАНИРОВАНИЕМ; ИДТИ

Положительный: скорость выполнения действительно 1-2 секунды Отрицательный: периодически запрос выполняется примерно 20-40 секунд, а потом снова продолжает выполняться 1-2 секунды.

Это происходит, когда происходит скачок изменения количества строк в проблемном запросе (либо с больших на маленькие, либо с малых на большие).

Но SQL Server дает нам возможность преодолеть и эту проблему! Для этого вы можете использовать опцию OPTION(RECOMPILE), которая будет перестраивать план выполнения при каждом его выполнении.

Это увеличит время выполнения каждого запроса до 3-4 секунд, но исполнений по 20-40 секунд в течение дня не будет. Кстати, OPTION(RECOMPILE) также помогает получить наиболее правильную оценку Cardinality при использовании временных объектов и табличных переменных, что используется при определении Estimated Number of Rows и далее при выборе плана выполнения запроса.

(подробнее о временных объектах и сути опции RECOMPILE в них описано в очень хорошем посте — sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx ) Здесь нам самим нужно решить, что важнее — чтобы большинство запросов выполнялось за 1-2 секунды или чтобы ни один запрос в течение суток не выполнялся более 20 секунд. Теги: #sqlserver #profiler #cardinality #план выполнения #sql #Microsoft SQL Server

• Как Скопировать Dvd На Новый Ipad?

  19 Oct, 24

• Ищу Полезный Командный Турнир Scrum

  19 Oct, 24

• Keymaster.js: Простая Микробиблиотека Сочетаний Клавиш.

  19 Oct, 24

• Как Работать С «Определенными» Токенами

  19 Oct, 24

• Gdg Devfest Красноярск 2015: Фотоотчет

  19 Oct, 24

• Ваша Днк Уже Внесена Во Все Базы Данных.

  19 Oct, 24

• Предложить В Google – Без Ajax

  19 Oct, 24

• Домашний Сервер На Ubuntu — Или Есть Ли Жизнь После Windows

  19 Oct, 24

• Дайджест Интересных Материалов Для Мобильных Разработчиков №93 (2-9 Марта)

  19 Oct, 24

• Обзор Камеры Galaxy (Часть I)

  19 Oct, 24