Как 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 секунд в течение дня.
Поделиться публикацией

Похожие публикации

Комментарии 21

    +4
    У меня была подобная дрянь — ХП работала правильно довольно долго, но потом меняло план на совершенно неоптимальный. При этом отдельные запросы из окошка студии выполнялись совершенно нормально. Вплоть до того, что один и тот же код, обернутый в процедуру и необернутый, выполнялись совершенно по разному. Решилась проблема опцией OPTION (OPTIMIZE FOR UNKNOWN), или для конкретной переменной-параметра OPTION (OPTIMIZE FOR (@a UNKNOWN)). Потому посмотрите внимательно на параметры процедуры — возможно, подобная штука вам поможет.
      0
      Интересная идея! Проанализировал и попробовал использовать. Вот что получилось.

      Прежде всего освежил теорию со сдачи экзаменов по OPTIMIZE FOR UNKNOWN по этой ссылке blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

      Коротко:
      При установке опции OPTION(OPTIMIZE FOR UNKNOWN) или OPTION(OPTIMIZE FOR (@char_parameter UNKNOWN, @date1 UNKNOWN, @date2 UNKNOWN)) у меня построился и закэшировался сразу неоптимальный план, который не обновлялся с течением времени и изменением параметров в условиях выборки.

      Т.к. в комментариях говорили, что так было бы информативней — привожу не кропнутый план:


      Подробнее:
      Что я помнил про эту опцию — это то, что она мягкая и не является явной директивой оптимизатору. Плюс теория, что опция строит план основываясь только на статистике, не используя значения, с которыми запрос запущен в конкретный раз. Таким образом, в идеальном случае, должен получиться план, который будет не предельно производителен в легких случаях, но и не будет «провисать» в случаях с большой выборкой данных.

      К сожалению, у меня получился не идеальный случай и не был выбран план, подходящий как для легких так и для тяжелых случаев. Я предполагаю, что тут дело в выбранном индексе и статистике. Прежде всего, у меня первая колонка в ключе индекса — текстовое поле, потом идет дата и последней — bit. Относительно статистики — скорее всего не оптимальный план был выбран на основании статистики, т.к. оптимизатор посчитал, что такой план будет «достаточно хорошим» для всех шагов статистики и помочь запутаться ему помогло то, что первая колонка — текстовая.

      Вывод:
      Скорее всего данная опция построила бы подходящий план, если бы у меня в индексе было первым не текстовое поле и шаги в статистике были бы более «удобными» для определения в среднем хорошего плана выполнения. Под шагом статистики подразумеваю столбец RANGE_HI_KEY из третьей таблицы в результате выполнения команды:
      DBCC SHOW_STATISTICS ('table_name', index_name)

      В результате мы остановились на опции RECOMPILE, т.к. руководству принципиально важно, чтобы ни один запрос не выполнялся больше минуты, даже в ущерб тому, что все остальные стали выполняться на секунду/две дольше. Кроме того, наиболее «скоростным» вариантом смотрится обращение не к DWH, а к кубам через MDX. Возможно это и станет завершением нашего оптимизационного пути данной ХП.
      +1
      Хм. Извиняюсь за оффтопик, но я первый раз вижу, чтобы Hash Match работал быстрее чем Nested Loops… Если не секрет, то в чем заключалась задача?
        0
        К сожалению, окошки кропнули. Но если внимательно посмотреть на первые два скриншота, то видно, что в самом начале работы запроса (в правой части окна) используются разные индексы: IX_factNewSale_IsService вместо IX_factNewSale_Activity, и IX_factOldSale_IsService вместо IX_factOldSale_Activity. Гадая исключительно по названиям индексов, я полагаю, что при медленном исполнении запроса индексы первоначальной выборки менее содержательны, чем при быстром ее исполнении, что приводит к увеличенному Actual Number Of Rows еще в самом начале выборки.
        К вопросу о hash match и nested loops: hash match применяется как раз в тех случаях, когда nested loops особо затратны, а требований к порядку вывода результата не существует. Использование hash-функции все-же обычно быстрее полного перебора. Но опять же: вопрос в том количестве записей, по которому нам надо объединить таблицы… Когда я вижу цифру «6» в Actual Number Of Rows (как и «35» в estimated), то понимаю, что разница во времени обработки связи любого из данных типов будет исчезающе мала… Однако, я совершенно точно не понимаю по данному скриншоту, к какой именно выборке относится цифра «6».
        Также зло кропнутых окошек в том, что если ориентироваться только на проценты, то в медленном плане их всего 51, а в быстром — 92 (что немного больше похоже на истину). Где еще 45-49% в первой выборке — неизвестно, возможно во всем виноваты именно они.
        От того мой вывод таков: совершенно непонятно, что именно это было. Гадать — можно, точно сказать — нет.
          0
          Что такое «кропнутые окошки»?
            0
            Я про скриншоты в тексте статьи, которые к сожалению не могут дать полной информации.
              0
              Достаточную они дают информацию. Но не про задачу.
                +1
                Про задачу информации в принципе нет, кроме периодического изменения времени выполнения запроса.
                  0
                  Спасибо, Капитан, но я и сам вижу, что в статье про решаемую запросом задачу ничего не написано. Если бы я увидел в статье ответ на свой вопрос, я бы не спрашивал.
          +1
          Если я правильно Вас понял, то вас интересует бывают ли случаи, когда NestedLoops даст более высокую скорость выполнения запроса, чем Hash Match, пожалуйста поправьте меня, если я не прав.

          Если коротко, то просто Hash Match не было бы, если бы он всегда был хуже чем Nested Loops. Nested Loops удобнее использовать в случае маленьких наборов данных, когда быстрее пробежать «вложенными for'ами» по двум массивам, вместо того, чтобы тратить время на создание хэш-функции, но далее выиграть во времени навигации по данным. Соответственно, когда оптимизатор выбрал Nested Loops, а ему пришло на вход два множества с значительным количество элементов (например, >100), то это приведет к заметному увеличению длительности выполнения запроса по сравнению с Hash Match.
            0
            Наоборот. В моей практике Nested Loops всегда работают быстрее Hash Match. Поэтому я и хочу узнать подробности про вашу задачу.
              0
              Относительно моей задачи:
              Входной поток после 'Concatenation' из четырёх 'Seek' по индексам увеличивается и получается 'Nested Loops' между большими потоками данных, как в приведенном ниже примере из этого комментария.

              Пример, на котором легко проверить, что иногда быстрее работает Hash Match, вместо Nested Loops и наоборот приведен ниже:

              IF (OBJECT_ID('dbo.test_table1') IS NOT NULL)
              	DROP TABLE	dbo.test_table1
              GO
              
              IF (OBJECT_ID('dbo.test_table2') IS NOT NULL)
              	DROP TABLE	dbo.test_table2
              GO
              
              
              CREATE TABLE	dbo.test_table1
              (
              	id INT IDENTITY(1, 1)
              	,	date1 TIMESTAMP
              	,	string VARCHAR(50)
              )
              
              CREATE TABLE	dbo.test_table2
              (
              	id INT IDENTITY(1, 1)
              	,	date1 TIMESTAMP
              	,	string VARCHAR(50)
              )
              
              
              
              DECLARE @counter INT = 20000
              DECLARE	@i INT = 0
              
              WHILE(@i < @counter )
              BEGIN
              	INSERT INTO [dbo].[test_table1]
              			   ([string])
              		 VALUES
              			   ('1')
              	SET @i = @i + 1;
              END
              
              GO
              
              
              DECLARE @counter INT = 20000
              DECLARE	@i INT = 0
              
              WHILE(@i < @counter )
              BEGIN
              	INSERT INTO [dbo].[test_table2]
              			   ([string])
              		 VALUES
              			   ('1')
              	SET @i = @i + 1;
              END
              
              GO
              
              
              SELECT	COUNT(*)
              FROM	test_table1 AS T1 WITH(READUNCOMMITTED)
              	INNER HASH JOIN test_table2 AS T2 WITH(READUNCOMMITTED)
              		ON	T1.id = T2.id
              OPTION(MAXDOP 1)
              
              
              SELECT	COUNT(*)
              FROM	test_table1 AS T1 WITH(READUNCOMMITTED)
              	INNER LOOP JOIN test_table2 AS T2 WITH(READUNCOMMITTED)
              		ON	T1.id = T2.id
              OPTION(MAXDOP 1)
              
              
                0
                перед вторым WHILE нужно заменить DECLARE на SET
                  0
                  Ну, это совсем теоретических пример. И то, если CLUSTERED PRIMARY KEY добавить в таблицы…
                    +1
                    Главная идея — это объединение двух потоков данных, объемом по 10 000 строк (10 000 строк после применения фильтров). Там будет Hash Match работать заметно быстрее чем Nested Loops.

                    Еще один пример когда использование Hash Match даст значительный прирост производительности:
                    stackoverflow.com/questions/8460416/query-optimizer-operator-choice-nested-loops-vs-hash-match-or-merge

                    Вот можете посмотреть подробности про кейсы использования, в которых наиболее подходят каждый из операторов объединения Hash, Merge & Loop:
                    social.msdn.microsoft.com/forums/sqlserver/en-US/521516cd-8133-45c0-83e1-0c8eb43af68e/hash-match-nested-loop-and-merge-join
                      0
                      Я и сам знаю, в каких случаях Hash Match эффективнее. Но вот на практике ни разу не увидел, потому и интересуюсь. А вы мне все теорию рассказываете (
                        +1
                        Вот теперь я Вас понял :)

                        С одной стороны у нас идет выгрузка данных по платёжным документам за разные годы с фильтром по дате (т.е. по сути получается, что в диапазон дат может попасть и много строк и мало), т.е. высока вероятность того, что Estimated Number Of Rows ошибется и покажет намного меньше ожидаемых строк, чем будет на самом деле, т.о. оптимизатор выберет Nested Loops, но придет много строк из верхнего потока.

                        С другой стороны идет поток данных (нижний поток) — это может быть список возможных направлений или возможных продавцов из платежек. Даже если это будет 100 строк.

                        Итак у нас получается, объединение 50 000 платёжек и 100 направлений через Nested Loops займет порядка 5 000 000 чтений, а через Hash Match — 100 000.
          • НЛО прилетело и опубликовало эту надпись здесь
              +1
              И проще, и быстрее. Но совершенно необходимо обращать в документации внимание на невзрачную фразу «will be deprecated».
                +1
                Поясните, пожалуйста, о чем идет речь, что-то не могу найти такого в документации.
                0
                Вы абсолютно правы насчет проще и быстрее, но если посмотреть на систему шире. В данном случае мы заставили SQL Server использовать определенный тип соединений, прописав ему Hint, но если SQL Server делал выбор определенного плана по каким-то причинам, то он эти же причины использует и в других местах, где используется данное представление/индекс/статистика. А значит и то место тоже придется отлавливать и исправлять Hint'ами. А, разобравшись в корне проблемы, и предоставив SQL Server'у все данные для того, чтобы он сделал правильный выбор, мы не только можем не создать/оставить себе проблем в других частях системы, но и ускорить её. Да и все мы знаем, что Hint'ы должны быть не только хорошо прокомментированы в коде для новых поколений, но и должны сопровождаться в течение времени, что тоже трудозатраты. Сопровождаться им нужно, т.к. потоки данных меняются и хинт может со временем начать приносить вместо пользы уже задержки.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое