Проблема с периодически долго выполняемыми запросами в MS SQL Server

Предисловие

Есть информационная система, которую я администрирую. Система состоит из следующих компонент:

1. База данных MS SQL Server
2. Серверное приложение
3. Клиентские приложения

Данные информационные системы установлены на нескольких объектах. Информационная система активно и круглосуточно используется одновременно от 2-х до 20-ти пользователей на каждом объекте. Поэтому нельзя выполнять регламентные работы все и сразу. Т е приходится дефрагментацию индексов размазывать на целый день, а не одним махом все нужные фрагментированные индексы дефрагментировать. Аналогично и с другими работами.

Автообновление статистики выставлено в свойствах самой базы данных. Также статистика обновляется по дефрагментированному индексу.

Проблема

Около года назад столкнулся со следующей проблемой:

Периодически все запросы выполнялись долго. Причем время торможений были случайными. Это происходило на каждом объекте в случайный день. Более того, когда стал анализировать как часто происходят торможения (профайлером), то удалось выяснить, что они происходят каждый день в случайное время. Просто пользователи не всегда обращают на это внимание, а воспринимают как единственную случайную задержку, и потом система опять быстро работает.

Путь к спасению

Были пересмотрены сами запросы, которые выполнялись долго. Но самое интересное, это то, что все запросы в случайное время выполнялись долго. Даже самые простые типа вытащить последнюю запись из таблицы в несколько тысяч строк.

Далее, были проведены следующие работы:

1. Проанализированы журналы MS SQL Server и Windows Server – причину торможений не удалось найти
2. Проанализированы индексы (фрагментация и т д) – добавлены недостающие и удалены неиспользуемые
3. Проанализированы запросы – улучшены некоторые запросы
4. Проанализированы задания в SQL Agent – не удалось задачи привязать к проблеме торможений
5. Проанализированы задания в Планировщике заданий – не удалось задачи привязать к проблеме торможений
6. Profiler тоже выдавал следствие, а не причину торможений.
7. Проведена проверка на взаимоблокировки – не было выявлено долгих блокировок вообще

В итоге было потрачено более 3-х месяцев на безуспешные поиски причины периодических торможений. Однако, удалось выявить интересный факт – у всех запросов вырастал показатель ожидания Elapsed, а не сам показатель выполнения Worker. Что натолкнуло на то, что возможно что-то с дисками. Тоже их проверил — все нормально.

Решение

Удивительным было то, что случайным образом удалось установить, что когда запрос в приложении выполняется медленно, то в самом SSMS он выполняется быстро. Тогда для решения помогла следующая статья ^[1] (она по крайней мере в последствии натолкнула на идею).

Из этой статьи процитирую следующий абзац:

На практике наиболее важной опцией SET является ARITHABORT, потому что значение по-умолчанию для этой опции отличается для приложений и для SQL Server Management Studio. Это объясняет, почему вы можете обнаружить медленный запрос в вашем приложении, и затем получить хорошую скорость, выполняя его в SSMS. Приложение использует план, который был построен для набора значений отличающегося от актуальных, правильных значений. Тогда как если вы запускаете запрос в SSMS, то вероятнее всего в кэше пока еще не имеется плана выполнения для ARITHABORT ON, и поэтому SQL Server построит план для ваших текущих значений.

Разница в выполнении была в параметре SET ARITHABORT. Для всех запросов, выполняемых в SSMS этот параметр включен, а для запросов из вне (из приложений) – выключен. И его нельзя включить даже простым запросом для приложений:

SET ARITHABORT ON;

После этого последовала безумная идея – в момент зависания очищать процедурный кэш: клик ^[2].

Для последующей ручной проверки перед запросом в 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 ^[3].

Данное приложение настроил через Планировщик задач на выполнение 1 раз каждый час. После проделанных работ уже более полугода нет торможений по запросам на всех объектах.

Единственное, что осталось непонятным, так это редкие случаи, когда один запрос зависнет на 5-10 секунд 1 раз в месяц в случайный день и в случайное время. Всего было за полгода 4 таких случая и то на двух объектах, а не на всех. При этом служба MS SQL Server съедает на короткое время всю выделенную ей оперативную память.

Проделал шаги, описанные в статье ^[4], но данное решение не помогло.

В принципе можно было и не копать глубже, т к пользователи зависания не замечают и все работает, но если у кого есть какие-то соображения-поделитесь, буду признателен.

Данную статью написал с целью помощи тем, кто наткнется на подобные проблемы, т к комплексного ответа в интернете я не нашел, и было потрачено очень много времени на исследование проблемы и ее решения.

Источники

» Раз ^[5] ⬝ Два ^[6] ⬝ Три ^[4] ⬝ Четыре ^[7] ⬝ Пять ^[8] ⬝ Шесть ^[9] ⬝ Семь ^[1] ⬝ Восемь ^[2]

Автор: jobgemws

Источник ^[10]