Найдите узкие места в запросах, которые замедляют процессы вашей базы данных

Когда ваша база данных растет, легко потерять связь со всеми процессами, которые она выполняет. Было бы здорово отслеживать медленные запросы, которые тормозят операции?

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

  • в какой базе данных и объекте найти запрос (например, в FreshPot хранимой процедуре в Coffee базе данных)
  • собственно сам запрос
  • план запроса
  • время создания и последнего выполнения
  • количество казней
  • общее, минимальное, максимальное и среднее количество возвращенных строк, прошедшее время выполнения и прошедшее время процессора

Обратите внимание, что в этой статье анализируются базы данных SQL Server, но аналогичные функции существуют и для Postgres; ознакомьтесь с этой статьейдля получения статистики по всем запросам в базе данных Postgres.

Охота на медленные запросы

Мы собираемся написать запрос, который предоставляет информацию о том, какие запросы медленные, и предлагает нам инструменты для анализа запроса. Тогда мы можем

  1. Обнаружение: какие запросы вызывают проблемы?
  2. Дополнение: добавление дополнительной информации, которая дает нам представление о причине замедления.
  3. Анализ: где мы можем найти запрос и в чем его проблема?
  4. Оптимизация: как мы можем улучшить наши запросы и базу данных?

Полный запрос можно найти внизу этой статьи или здесь. Обратите внимание, что для некоторых представлений, которые мы будем использовать, требуется разрешение VIEW DATABASE STATE.

1. Поиск проблемных запросов

В этой первой части мы используем CTE для выбора интересующих нас запросов. Мы хотим взять некоторые данные из sys.dm_exec_query_stats ; это отслеживает статистику производительности для кэшированных планов запросов в SQL Server.

Цель здесь — выбрать интересующие нас записи. В этом примере мы выбираем новые записи (менее 30 дней с момента последнего выполнения) и часто используемые. (более 100 раз за последние 30 дней). Мы не против медленных запросов, которые выполняются очень редко. Затем мы упорядочиваем запросы по среднему времени процессора и затем возвращаем 10 самых медленных запросов.

Конечно, есть много критериев, по которым мы можем фильтровать:

  • записи с большим разбросом времени выполнения (например, большая разница между минимальным и максимальным временем ЦП)
  • записи с большой разницей между прошедшим временем и временем ЦП (время, которое ЦП фактически выполняет; возможно, запрос тратит много времени на ожидание)
  • порядок по возвращенному avg_rows или общему процессорному времени.

2. Предоставление дополнительной информации о запросах

В предыдущей части мы выбрали запросы, которые мы выполняем часто и на выполнение которых уходит много времени; это наши проблемные запросы. Цель этой части — собрать как можно больше релевантной информации об этих запросах. Давайте пройдемся по коду

Приведенный выше код довольно длинный, но он делает всего несколько вещей:

  • используйте представление sys.dm_exec_sql_text в строке 34, чтобы добавить фактический оператор SQL (и очистить оператор в строках с 21 по 31)
  • используйте представление sys.dm_exec_query_plan, чтобы добавить план запроса (строка 35)
  • используйте представление sys.dm_exec_plan_attributes, чтобы получить идентификатор базы данных и идентификатор объекта, которые нам понадобятся позже. В строках с 37 по 41 мы преобразуем записи dbid и objectid из строк в столбцы, чтобы упростить их перекрестное применение в строке 36.

Это вывод (с анонимным текстом заявления):

3. Анализ наших проблемных запросов

Теперь мы можем приступить к анализу запросов; ниже приведены некоторые предложения:

  1. Проверьте разницу между временем процессора и прошедшим временем; может запрос много ждет?
  2. Ознакомьтесь с различиями между минимальным и максимальным процессором и временем выполнения; может быть, запланировать определенные задания на ночь, когда на сервере больше памяти?
  3. Проанализируйте текст оператора (отформатируйте sql в vscode; cntrl-shift-p → format document with → sql)
  4. Нажмите и проверьте query_plan

Как только вы обнаружите проблему или возможность улучшить запрос, нам нужно найти местоположение запроса. У нас уже есть имя базы данных; давайте найдем имя объекта, в котором находится запрос (например, в хранимой процедуре). Мы можем сделать это, подключив db_name и objectid in к запросу ниже:

SELECT * FROM CoffeeDB.sys.objects WHERE object_id = 808389949

Это скажет вам тип и имя объекта, который мы ищем. Например:

Теперь мы можем искать хранимую процедуру под названием «My_Stored_Procedure» в CoffeeDB и ускорить запрос!

4. Дальнейшая оптимизация

Ознакомьтесь с этими статьями, чтобы улучшить свой запрос, особенно с этой.

Заключение

С этой статьей вы будете полностью подготовлены для оптимизации вашей базы данных! Мы увидели, как работает код и как мы можем его использовать, теперь пришло время применить его к вашей ситуации. Ознакомьтесь с полным запросом здесь.

Если у вас есть предложения/пояснения, пожалуйста, прокомментируйте, чтобы я мог улучшить эту статью. А пока ознакомьтесь с моими другими статьями на всевозможные темы, связанные с программированием, например:

Удачного кодирования!

— Майк

P.S. Нравится, что я делаю? "Подписывайтесь на меня!"



Присоединяйтесь к Medium по моей реферальной ссылке — Майк Халс
Как участник Medium, часть вашего членского взноса идет авторам, которых вы читаете, и вы получаете полный доступ ко всем историям… mikehuls.medium.com