Предисловие
Есть информационная система, которую я администрирую. Система состоит из следующих компонент:
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 съедает на короткое время всю выделенную ей оперативную память.
Проделал шаги, описанные в статье, но данное решение не помогло.
В принципе можно было и не копать глубже, т к пользователи зависания не замечают и все работает, но если у кого есть какие-то соображения-поделитесь, буду признателен.
Данную статью написал с целью помощи тем, кто наткнется на подобные проблемы, т к комплексного ответа в интернете я не нашел, и было потрачено очень много времени на исследование проблемы и ее решения.
Источники
» Раз ⬝ Два ⬝ Три ⬝ Четыре ⬝ Пять ⬝ Шесть ⬝ Семь ⬝ Восемь
Автор: jobgemws
