Мониторим тяжелые запросы

Публикация № 1051338

Администрирование - Оптимизация БД (HighLoad)

MSSQL highload

28
Мониторинг тяжелых запросов с сохранением результатов для истории.

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

Одним из способов такой оптимизации является поиск тяжелых запросов. Далее определяем, что это за запросы, правим их.

В рамках публикации будет описан мониторинг запросов, создающих наибольшую нагрузку на CPU СУБД. Основой являются материалы ИТС.

На начальном периоде наших работ по оптимизации, пользовались обычной консолью MSSQL: копипастили в нее поисковый запрос, смотрели результаты, делали выводы и что-то дорабатывали. Но уже начали напрягать лишние движения по поиску файла запроса, его запуску и проч. И самое главное, исторической картины мы так и не имели. Т.е., иногда и непонятно, каков же был эффект от доработок.

Поэтому решили возложить всю работу по сбору статистики на сам MSSQL. Теперь он ежечасно запускает хранимую процедуру, которая дописывает данные в специальную таблицу (top_cpu_usage).

 
 Скрипт для таблицы

Поля таблицы совпадают с полями системной view sys.dm_exec_query_stats.

 

Хранимая процедура (sp_store_top_cpu_usage_data) написана по мотивам выше упомянутых материалов ИТС. Ее выполнение немного оптимизировано по сравнению с исходным запросом.

 
 Скрипт для хранимой процедуры

С использование GUI консоли MSSQL создали план обслуживания, ежечасно запускающий ХП.

Вуаля, наши статистики теперь сами собираются и хранятся для истории. Насчет визуализации - пока решаем. Варианты - Grafana, Kibana, MS Power BI.

 

В планах недалекого будущего будет добавление и других таблиц и ХП для сбора данных по другим критериям "тяжести" запросов.

И вот, продолжение статьи.

28

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. capitan 1224 24.04.19 14:03 Сейчас в теме
Статья открылась с предложения от которого немного повеселело
С использование GUI консоли MSSQL создали план обслуживания, ежечасно запускающий ХП

А так конечно задумка хорошая
Интересно еще как по вашему - какую часть проблем 1С снимает исправление тяжелых запросов, тем более не факт что они неправильные ?
2. ImHunter 155 24.04.19 14:55 Сейчас в теме
(1) Честно, не понял, от чего повеселело)

По поводу "какую часть проблем..." - свежие выводы.
Например, увидели, что большая часть нагрузки приходится за запись (insert) ТЧ определенного вида док-тов. Думаем вот, что нужно поменять архитектуру и отказаться от ТЧ в пользу РС.
Еще увидели, насколько много у нас разыменований определенных справочников. Нашли крупный источник, пофиксили, оценили эффект и решили пока остановиться на этом.
Добавили пару-тройку явно необходимых индексов.
3. capitan 1224 24.04.19 15:43 Сейчас в теме
(2)От великая и могучая русская языка )
4. Aleksey.Bochkov 3196 25.04.19 09:11 Сейчас в теме
Какую версию SQL Server используете?
В 2016 и последующих версиях появился Query Store - по-русски вроде называется Диспетчер Хранилица Запросов.
Собирает самую базовую информацию по запросам, которые потребляют много ресурсов, вместе с их планами и аггрегированной основной статистикой.
Не заменит полноценную систему мониторинга типа RedGate, но зато бесплатно и чрезвычайно удобно.

Тут не нашел публикации, поэтому наверное надо написать простую статью :).
5. ImHunter 155 25.04.19 09:39 Сейчас в теме
(4) Пользуем 2012. Но судя по перечисленному - в служебной вьюхе все это тоже есть. И планы, и аггр статистика.
6. Aleksey.Bochkov 3196 26.04.19 12:30 Сейчас в теме
7. ivanow-sv 14.05.19 12:35 Сейчас в теме
я так понимаю это все только для MS? Postgre в пролете?
8. ImHunter 155 14.05.19 12:50 Сейчас в теме
(7) Для PG вроде есть свои источники подобных статистик. Сходу нашел что-то про pg_stat_activity. Ну понятно, что один в один не применить.
Оставьте свое сообщение