(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Как SQL Server каждые два-три часа переключался на использование не оптимального плана выполнения запроса

Источник: habrahabr
BalandinAleksandr

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

В чём проявляется проблема:
Запускаю хранимую процедуру (хранимку) по выборке данных для отчета - выполняется три секунды, смотрю профайлером на бою - у пользователей те же результаты. Но проходит три часа и та же хранимка, с теми же параметрами выполняется уже 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 и далее при выборе плана выполнения запроса.

Тут уже нам самим нужно определиться, что важнее - чтобы большинство запросов выполнялось 1-2 секунды или чтобы ни один запрос не выполнялся дольше 20 секунд в течение дня.

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft »
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 19.06.2014 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft 365 Apps for business (corporate)
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Microsoft Office 365 Бизнес. Подписка на 1 рабочее место на 1 год
Microsoft Office для дома и учебы 2019 (лицензия ESD)
Microsoft 365 Business Standard (corporate)
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Программирование на Visual Basic/Visual Studio и ASP/ASP.NET
Adobe Photoshop: алхимия дизайна
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Realme Narzo 50A (1)
Посмотрела вчера характеристики нового смартфона Realme Narzo 50A...
 
Заработок в сети интернет (15)
Зайди сюда - http://www.netbusin.boom.ru и узнай подробности.
 
Онлайн покер (6)
Подскажите, пожалуйста, где можно научиться играть в покер в интернете и зарабатывать на этом?
 
Можно ли зарабатывать в интернете играя в казино ? (4)
Можно ли зарабатывать в интернете играя в казино ?
 
Рейтинг лучших букмекерских контор в России (3)
Рейтинг букмекеров и топ-10 лучших https://superbet.guru/bookmakers-rating/ в 2020-2021 году....
 
 
 



    
rambler's top100 Rambler's Top100