- PVSM.RU - https://www.pvsm.ru -
Последние пару дней работал над интересной задачей и хотел бы поделиться интересным опытом с сообществом.
В чём проявляется проблема:
Запускаю хранимую процедуру (хранимку) по выборке данных для отчета — выполняется три секунды, смотрю профайлером на бою — у пользователей те же результаты. Но проходит три часа и та же хранимка, с теми же параметрами выполняется уже 2 минуты, и аналогично у пользователей. Причём данные в используемые таблицы не вставлялись/удалялись, окружение не меняли и админы не делали настроек.
Локализовал до запроса:
INSERT INTO @table_variable1
SELECT ...
FROM dbo.[<i>view_with_unions</i>] v1 WITH (READUNCOMMITTED)
LEFT JOIN @<i>table_variable2</i> AS t1
ON t1.Code = v1.DirectionDimensionCode
LEFT JOIN <i>other_table</i> 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, а значит индекс с его статистикой по-прежнему подходит для использования.
Диагноз:
Решение:
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])
Тут уже нам самим нужно определиться, что важнее — чтобы большинство запросов выполнялось 1-2 секунды или чтобы ни один запрос не выполнялся дольше 20 секунд в течение дня.
Автор: BalandinAleksandr
Источник [2]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/sql/62416
Ссылки в тексте:
[1] sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
[2] Источник: http://habrahabr.ru/post/226335/
Нажмите здесь для печати.