Как 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. Т.о. образом разность не такая большая, чтобы исследовать проблемы со статистикой и Cardinality.



Однако смотря на Actual Number Of Rows = 0 из раза в раз, у меня возникли сомнения — неужели всегда не возвращается ни одной строки. Оказалось это не так, просто профайлер перехватывает план выполнения до того как запрос выполнился и стали известны Actual-данные. А соответственно не может ничего отобразить кроме того как ноль в Actual Number Of Rows.

Ок, теперь смотрим настоящие значения Actual Number Of Rows!

Далее вопрос встал — почему же Estimated Number Of Rows всегда равно единице? Ведь индекс используется, у него актуальная статистика. А значение Estimated Number Of Rows каждый раз равно 1. Но тут без сюрпризов — SQL Server не использует статистику, если она начинается с низко селективной колонки (т.е. если количество различных значений мало, например: 0, 1, NULL). Поэтому переместил первую колонку в ключе индекса на последнее место. Предварительно убедившись, что все условия по этим колонкам накладываются в WHERE и перечислены через AND, а значит индекс с его статистикой по-прежнему подходит для использования.

Диагноз:

1. Профайлер не отображает на планах выполнения Actual Number Of Rows, причем пишет не n/a, а ноль. Нужно помнить об этом!
2. SQL Server не использует статистику для определения Estimated Number Of Rows, в нашем случае.
3. А даже если начинает использовать статистику, то сильно ошибается.

Решение:

1. Запускаем вручную долгие запросы из профайлера и смотрим реальный Actual Number Of Rows
2. Нужно дать SQL Server'у возможность использовать статистику на индексе, для этого первая колонка в ключе должна иметь много различных значений (например, не три 0, 1, NULL). Т.к. если первый столбец имеет мало разных значений (низко селективный), то SQL Server не имеет возможности адекватно прогнозировать количество строк и поэтому не использует такую статистику.
3. После перестройки индекса нужно обновить статистику с опцией WITH FULLSCAN, чтобы повысить качество прогнозов Estimated Number Of Rows:
   
   UPDATE STATISTICS [dbo].[table_from_union_for_view] WITH FULLSCAN;
   GO

И вот теперь все запросы выполняются не более чем за 2 секунды, план выполнения используется как микс из предыдущих, а Actual Number Of Rows смотрим в Management Studio:



Но и это не всё!

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

Положительная:
скорость выполнения действительно 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 секунд в течение дня.