Поиск первопричины медленных запросов

Автор: Pinal Dave, Finding the Root Cause of Slow Queries

Поиск первопричины медленных запросов начинается с разделения работы и ожиданий, а затем проверки теорий на планах и истории. Вот вам пример показательного вторника. В 10:12 поиск заказа, который обычно возвращался за 180 миллисекунд, занял 14,8 секунды. Сорок три сеанса накопились за пять минут, и команда приложения захотела новый индекс ещё до обеда. CPU оставался скромным, логические чтения оставались привычными, а кэшированный план совпадал со вчерашним быстрым планом. Запрос провёл почти всю свою жизнь в ожидании LCK_M_S. Ночной процесс инвентаризации оставил открытой транзакцию обновления. Видимый запрос был жертвой, а не виновником. Команда проследовала по blocking_session_id, пока цепочка не закончилась на спящем сеансе. Его последний пакет показывал обновление инвентаризации, и данные транзакции подтвердили незафиксированную работу. Как только этот владелец безопасно зафиксировал транзакцию, задержка упала ниже 200 миллисекунд. Никто не редактировал код жертвы, индексы или статистику.

Медленно — это симптом

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

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

Сделайте первое разделение

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

Это первое разделение не решает инцидент. Оно устраняет классы неправильных исправлений до того, как они достигнут производственного сервера.

Параллельные планы требуют осторожности, потому что общее время рабочих процессов может превышать затраченное время. Принцип всё ещё действует: определите, работают ли планировщики или ресурс недоступен. Затем исследуйте текущий тип ожидания, длительность ожидания и ресурс ожидания.

Вертикальный поток технических решений для определения того, берёт ли медленный запрос начало в SQL Server, блокировке, давлении на разделяемые ресурсы, плане выполнения или внешнем уровне приложения.

Наблюдаемый сигнал	Частое неверное прочтение	Лучшая следующая проверка
Долгая длительность, скромный CPU	Запросу нужен индекс	Тип ожидания, ресурс ожидания, цепочка блокировок
Высокий CPU, мало ожиданий	Сервер недоукомплектован	Форма плана, оценки, обработанные строки
Всплески в фиксированное время	Кто-то изменил код	Запланированные задания, задержка файлов, выделения памяти

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

Идентифицируйте точный оператор

Начните с оператора, который испытывают пользователи, а не с похожего пакета, скопированного из документации. Захватите его базу данных, приложение, логин, параметры, хеш запроса, хеш плана и контекст выполнения. Эти детали отделяют один дорогой шаблон от тысяч безобидных выполнений.

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

Читайте фактический план как доказательство

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

Ищите выгрузки, избыточные повторения поиска, завышенные выделения памяти и неожиданные сканирования. Сканирование не обязательно неправильно, а seek не обязательно эффективен. Контекст решает, дорог ли оператор. Процент, напечатанный на плане, — это оценка, а не измеренное затраченное время.

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

Сопоставьте текущее поведение с историей

Снимок объясняет один момент. История показывает, нормален ли этот момент. Query Store сохраняет планы и агрегированную длительность, CPU, чтения и статистику выполнения в рамках захвата и хранения. В SQL Server 2017 или новее включённый захват статистики ожиданий добавляет ожидания на уровне запроса.

Окно сравнения имеет значение. Пиковый трафик оформления заказов не следует сравнивать с ночным окном обслуживания. Сопоставляйте бизнес-циклы, объём данных, частоту выполнения и состав параметров. Более низкая средняя длительность мало что значит, когда лёгкие выполнения доминируют в выборке.

SQL DM от IDERA может группировать запросы по подписи и собирать сведения о производительности, когда включён Query Monitor. Эта история может связать жалобу пользователя с оператором и шаблоном ресурсов, присутствовавшими в тот момент. Записанные доказательства важнее всего после того, как преходящая проблема исчезла.

Следуйте контексту сервера

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

Сопоставляйте временные шкалы, прежде чем возлагать вину. Всплеск задержки хранилища в 10:11 может объяснить несколько несвязанных медленных операторов в 10:12. Разделяемое давление оставляет следы во многих сеансах.

Когда достаточно целенаправленной настройки запроса

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

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

Превратите доказательства в решение

SQL DM от IDERA объединяет живые метрики, сведения о запросах, оповещения и исторический контекст в один путь расследования. Ценность в сохранении достаточного контекста, чтобы быстро и безопасно проверять конкурирующие объяснения.

Хороший диагноз заканчивается утверждением, которое кто-то мог бы опровергнуть. Назовите ресурс, запрос, изменение плана или транзакцию, ответственную за проблему. Запишите подтверждающие метрики и ожидаемый результат исправления. Затем измерьте снова в сопоставимых условиях.

В следующий раз, когда запрос выглядит медленным, остановитесь, прежде чем его менять. Спросите, работал ли он, ждал ли он или изменился ли он со вчерашнего дня. Ответ обычно стоит десять минут и спасает потраченный впустую индекс.

Медленный запрос — это симптом, а первопричина — это работа, ожидание или изменение за ним.