Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 21
У меня была подобная дрянь — ХП работала правильно довольно долго, но потом меняло план на совершенно неоптимальный. При этом отдельные запросы из окошка студии выполнялись совершенно нормально. Вплоть до того, что один и тот же код, обернутый в процедуру и необернутый, выполнялись совершенно по разному. Решилась проблема опцией OPTION (OPTIMIZE FOR UNKNOWN), или для конкретной переменной-параметра OPTION (OPTIMIZE FOR (@a UNKNOWN)). Потому посмотрите внимательно на параметры процедуры — возможно, подобная штука вам поможет.
Интересная идея! Проанализировал и попробовал использовать. Вот что получилось.
Прежде всего освежил теорию со сдачи экзаменов по 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. Возможно это и станет завершением нашего оптимизационного пути данной ХП.
Прежде всего освежил теорию со сдачи экзаменов по 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. Возможно это и станет завершением нашего оптимизационного пути данной ХП.
Хм. Извиняюсь за оффтопик, но я первый раз вижу, чтобы
Hash Match работал быстрее чем Nested Loops… Если не секрет, то в чем заключалась задача?К сожалению, окошки кропнули. Но если внимательно посмотреть на первые два скриншота, то видно, что в самом начале работы запроса (в правой части окна) используются разные индексы: 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% в первой выборке — неизвестно, возможно во всем виноваты именно они.
От того мой вывод таков: совершенно непонятно, что именно это было. Гадать — можно, точно сказать — нет.
К вопросу о hash match и nested loops: hash match применяется как раз в тех случаях, когда nested loops особо затратны, а требований к порядку вывода результата не существует. Использование hash-функции все-же обычно быстрее полного перебора. Но опять же: вопрос в том количестве записей, по которому нам надо объединить таблицы… Когда я вижу цифру «6» в Actual Number Of Rows (как и «35» в estimated), то понимаю, что разница во времени обработки связи любого из данных типов будет исчезающе мала… Однако, я совершенно точно не понимаю по данному скриншоту, к какой именно выборке относится цифра «6».
Также зло кропнутых окошек в том, что если ориентироваться только на проценты, то в медленном плане их всего 51, а в быстром — 92 (что немного больше похоже на истину). Где еще 45-49% в первой выборке — неизвестно, возможно во всем виноваты именно они.
От того мой вывод таков: совершенно непонятно, что именно это было. Гадать — можно, точно сказать — нет.
Если я правильно Вас понял, то вас интересует бывают ли случаи, когда NestedLoops даст более высокую скорость выполнения запроса, чем Hash Match, пожалуйста поправьте меня, если я не прав.
Если коротко, то просто Hash Match не было бы, если бы он всегда был хуже чем Nested Loops. Nested Loops удобнее использовать в случае маленьких наборов данных, когда быстрее пробежать «вложенными for'ами» по двум массивам, вместо того, чтобы тратить время на создание хэш-функции, но далее выиграть во времени навигации по данным. Соответственно, когда оптимизатор выбрал Nested Loops, а ему пришло на вход два множества с значительным количество элементов (например, >100), то это приведет к заметному увеличению длительности выполнения запроса по сравнению с Hash Match.
Если коротко, то просто Hash Match не было бы, если бы он всегда был хуже чем Nested Loops. Nested Loops удобнее использовать в случае маленьких наборов данных, когда быстрее пробежать «вложенными for'ами» по двум массивам, вместо того, чтобы тратить время на создание хэш-функции, но далее выиграть во времени навигации по данным. Соответственно, когда оптимизатор выбрал Nested Loops, а ему пришло на вход два множества с значительным количество элементов (например, >100), то это приведет к заметному увеличению длительности выполнения запроса по сравнению с Hash Match.
Наоборот. В моей практике Nested Loops всегда работают быстрее Hash Match. Поэтому я и хочу узнать подробности про вашу задачу.
Относительно моей задачи:
Входной поток после 'Concatenation' из четырёх 'Seek' по индексам увеличивается и получается 'Nested Loops' между большими потоками данных, как в приведенном ниже примере из этого комментария.
Пример, на котором легко проверить, что иногда быстрее работает Hash Match, вместо Nested Loops и наоборот приведен ниже:
Входной поток после '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)
перед вторым WHILE нужно заменить DECLARE на SET
Ну, это совсем теоретических пример. И то, если CLUSTERED PRIMARY KEY добавить в таблицы…
Главная идея — это объединение двух потоков данных, объемом по 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
Еще один пример когда использование 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
Я и сам знаю, в каких случаях Hash Match эффективнее. Но вот на практике ни разу не увидел, потому и интересуюсь. А вы мне все теорию рассказываете (
Вот теперь я Вас понял :)
С одной стороны у нас идет выгрузка данных по платёжным документам за разные годы с фильтром по дате (т.е. по сути получается, что в диапазон дат может попасть и много строк и мало), т.е. высока вероятность того, что Estimated Number Of Rows ошибется и покажет намного меньше ожидаемых строк, чем будет на самом деле, т.о. оптимизатор выберет Nested Loops, но придет много строк из верхнего потока.
С другой стороны идет поток данных (нижний поток) — это может быть список возможных направлений или возможных продавцов из платежек. Даже если это будет 100 строк.
Итак у нас получается, объединение 50 000 платёжек и 100 направлений через Nested Loops займет порядка 5 000 000 чтений, а через Hash Match — 100 000.
С одной стороны у нас идет выгрузка данных по платёжным документам за разные годы с фильтром по дате (т.е. по сути получается, что в диапазон дат может попасть и много строк и мало), т.е. высока вероятность того, что Estimated Number Of Rows ошибется и покажет намного меньше ожидаемых строк, чем будет на самом деле, т.о. оптимизатор выберет Nested Loops, но придет много строк из верхнего потока.
С другой стороны идет поток данных (нижний поток) — это может быть список возможных направлений или возможных продавцов из платежек. Даже если это будет 100 строк.
Итак у нас получается, объединение 50 000 платёжек и 100 направлений через Nested Loops займет порядка 5 000 000 чтений, а через Hash Match — 100 000.
Проще и быстре хинты написать, чем копаться и перестраивать индексы.
И проще, и быстрее. Но совершенно необходимо обращать в документации внимание на невзрачную фразу «will be deprecated».
Вы абсолютно правы насчет проще и быстрее, но если посмотреть на систему шире. В данном случае мы заставили SQL Server использовать определенный тип соединений, прописав ему Hint, но если SQL Server делал выбор определенного плана по каким-то причинам, то он эти же причины использует и в других местах, где используется данное представление/индекс/статистика. А значит и то место тоже придется отлавливать и исправлять Hint'ами. А, разобравшись в корне проблемы, и предоставив SQL Server'у все данные для того, чтобы он сделал правильный выбор, мы не только можем не создать/оставить себе проблем в других частях системы, но и ускорить её. Да и все мы знаем, что Hint'ы должны быть не только хорошо прокомментированы в коде для новых поколений, но и должны сопровождаться в течение времени, что тоже трудозатраты. Сопровождаться им нужно, т.к. потоки данных меняются и хинт может со временем начать приносить вместо пользы уже задержки.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Как SQL Server каждые два-три часа переключался на использование не оптимального плана выполнения запроса