[Из песочницы] Проблема с периодически долго выполняемыми запросами в MS SQL Server
Предисловие
Есть информационная система, которую я администрирую. Система состоит из следующих компонент:
1. База данных MS SQL Server
2. Серверное приложение
3. Клиентские приложения
Данные информационные системы установлены на нескольких объектах. Информационная система активно и круглосуточно используется одновременно от 2-х до 20-ти пользователей на каждом объекте. Поэтому нельзя выполнять регламентные работы все и сразу. Т е приходится дефрагментацию индексов размазывать на целый день, а не одним махом все нужные фрагментированные индексы дефрагментировать. Аналогично и с другими работами.
Автообновление статистики выставлено в свойствах самой базы данных. Также статистика обновляется по дефрагментированному индексу.
Проблема
Около года назад столкнулся со следующей проблемой:
Периодически все запросы выполнялись долго. Причем время торможений были случайными. Это происходило на каждом объекте в случайный день. Более того, когда стал анализировать как часто происходят торможения (профайлером), то удалось выяснить, что они происходят каждый день в случайное время. Просто пользователи не всегда обращают на это внимание, а воспринимают как единственную случайную задержку, и потом система опять быстро работает.
Путь к спасению
Были пересмотрены сами запросы, которые выполнялись долго. Но самое интересное, это то, что все запросы в случайное время выполнялись долго. Даже самые простые типа вытащить последнюю запись из таблицы в несколько тысяч строк.
Далее, были проведены следующие работы:
- Проанализированы журналы MS SQL Server и Windows Server — причину торможений не удалось найти
- Проанализированы индексы (фрагментация и т д) — добавлены недостающие и удалены неиспользуемые
- Проанализированы запросы — улучшены некоторые запросы
- Проанализированы задания в SQL Agent — не удалось задачи привязать к проблеме торможений
- Проанализированы задания в Планировщике заданий — не удалось задачи привязать к проблеме торможений
- Profiler тоже выдавал следствие, а не причину торможений.
- Проведена проверка на взаимоблокировки — не было выявлено долгих блокировок вообще
В итоге было потрачено более 3-х месяцев на безуспешные поиски причины периодических торможений. Однако, удалось выявить интересный факт — у всех запросов вырастал показатель ожидания Elapsed, а не сам показатель выполнения Worker. Что натолкнуло на то, что возможно что-то с дисками. Тоже их проверил — все нормально.
Решение
Удивительным было то, что случайным образом удалось установить, что когда запрос в приложении выполняется медленно, то в самом SSMS он выполняется быстро. Тогда для решения помогла следующая статья (она по крайней мере в последствии натолкнула на идею).
Из этой статьи процитирую следующий абзац:
На практике наиболее важной опцией SET является ARITHABORT, потому что значение по-умолчанию для этой опции отличается для приложений и для SQL Server Management Studio. Это объясняет, почему вы можете обнаружить медленный запрос в вашем приложении, и затем получить хорошую скорость, выполняя его в SSMS. Приложение использует план, который был построен для набора значений отличающегося от актуальных, правильных значений. Тогда как если вы запускаете запрос в SSMS, то вероятнее всего в кэше пока еще не имеется плана выполнения для ARITHABORT ON, и поэтому SQL Server построит план для ваших текущих значений.
Разница в выполнении была в параметре SET ARITHABORT. Для всех запросов, выполняемых в SSMS этот параметр включен, а для запросов из вне (из приложений) — выключен. И его нельзя включить даже простым запросом для приложений:
SET ARITHABORT ON;
После этого последовала безумная идея — в момент зависания очищать процедурный кэш: клик.
Для последующей ручной проверки перед запросом в SSMS необходимо писать:
SET ARITHABORT OFF;
Тогда запрос будет выполняться, как если бы он пришел из приложения. Когда запрос выполнялся долго, то я очищал процедурный кэш. И всегда это лечило. Т е до чистки процедурного кэша, запрос мог выполняться до 20–30 секунд, а после — 0 секунд.
После этого был поставлен еще один эксперимент — чистка всего процедурного кэша для всей базы данных каждый час через SQL Agent:
--очищаем кэш по id БД
DBCC FLUSHPROCINDB(@db_id);
После этого все запросы стали просто выполняться очень быстро (менее 0,05 сек.), были лишь единичные выбросы до 5–10 секунд выполнения, но пользователи уже зависаний не замечали. Более того, обновление статистики не улучшало результаты, поэтому я убрал обновление статистики.
После еще нескольких месяцев исследований удалось установить, что единичные зависания происходят, когда на самом сервере либо все съедает кэш, и свободной памяти ничего не остается или остается, но меньше 1 ГБ ОЗУ, либо служба MS SQL Server съедает всю выделенную ей оперативную память (через Диспетчер задач). Но второе происходило всего 2 раза за все исследование.
Дело в том, что в кэш записывается в буквальном смысле все, а вот освобождается кэш не всегда вовремя. Проблему с кэшем удалось решить с помощью программы EmptyStandbyList.exe.
Данное приложение настроил через Планировщик задач на выполнение 1 раз каждый час. После проделанных работ уже более полугода нет торможений по запросам на всех объектах.
Единственное, что осталось непонятным, так это редкие случаи, когда один запрос зависнет на 5–10 секунд 1 раз в месяц в случайный день и в случайное время. Всего было за полгода 4 таких случая и то на двух объектах, а не на всех. При этом служба MS SQL Server съедает на короткое время всю выделенную ей оперативную память.
Проделал шаги, описанные в статье, но данное решение не помогло.
В принципе можно было и не копать глубже, т к пользователи зависания не замечают и все работает, но если у кого есть какие-то соображения-поделитесь, буду признателен.
Данную статью написал с целью помощи тем, кто наткнется на подобные проблемы, т к комплексного ответа в интернете я не нашел, и было потрачено очень много времени на исследование проблемы и ее решения.
Источники
» Раз ⬝ Два ⬝ Три ⬝ Четыре ⬝ Пять ⬝ Шесть ⬝ Семь ⬝ Восемь
