SQL Server 2014: Мониторинг запросов в реальном времени

SQL Server 2014: Мониторинг запросов в реальном времени

Автор: Daniel Farina

Перевод: Александр Гладченко

Проблема

Если вдруг один из запросов к SQL Server выполняется слишком долго, вы может получить его план исполнения, что даст вам понимание того, что этот запрос делает, но из этого плана вы не сможете точно определить, что запрос делает именно в это время, т.е. на каком операторе плана он «застрял»?
Продолжая читать эту статью, вы узнаете, как научится следить за прогрессом исполнения запроса в режиме реального времени.

Решение

Сколько раз вы задавали себе подобный вопрос: сколько времени нужно, чтобы процесс завершился? Вот какой случай произошёл на моей работе несколько дней назад: ко мне подошел аналитик с вопросом, на каком шаге плана исполнения находится его задача. Выполнив несколько запросов, я понял, что текущим шагом в его запросе была хранимая процедура, которая выполняла изменения данных, но это было единственное, что я мог ему сказать. Тут же мне в голову пришла мысль: было бы лучше, если бы эта задача решалась на экземпляре SQL Server 2014, вместо используемого для неё устаревшего SQL Server 2000. Дело в том, что в SQL Server 2014 появилось новое Dynamic Management View, позволяющее отслеживать на физическом уровне, что фактически запрос делает во время работы оператора. Я имею в виду динамическое административное представление (DMV): sys.dm_exec_query_profiles

Немного о sys.dm_exec_query_profiles

Для того чтобы эта DMV заработала, нам необходимо обеспечить доступ к реальным планам исполнения запросов (Actual Execution Plan). Как вы уже могли догадаться, этот способ не работает для планов, хранящихся в кэше (Plan Cache). Возможно у вас возник вопрос, почему мы не можем получить статус любого выполняемого на экземпляре запроса в режиме реального времени, и ответ тут простой: ничто не даётся бесплатно. Доступ к упомянутым выше показателям имеет стоимость в терминах производительности, и она возникает из-за того, что SQL Server для анализа запроса должен собрать детализированную статистику.

Планы, которые хранятся в Plan Cache, не содержат статистику исполнения. Они содержат оценки, которые используются для построения плана действий как такового. Когда мы говорим о статистике исполнения мы говорим о фактическом числе чтений, записи, процессорного времени и так далее.

Несколько способов получения данных о производительности SQL Server в реальном времени

Для SQL Server можно показать три способа, позволяющих получить фактический план исполнения запроса:

  • Установка непосредственно в коде подлежащего анализу запроса одного из параметров исполнения SET STATISTICS PROFILE или SET STATISTICS XML в положение ON предоставляет нам возможность такого анализа.
  • Создание server side trace в том числе с одним из этих двух событий: “Showplan Statistics Profile” или “Showplan Statistics XML”. Кроме того, если вам так удобнее, можно использовать приложение SQL Server Profiler.
  • Создание сессии Extended Events и добавление в ней в качестве целевого события sqlserver.query_post_execution_showplan.

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

Пример сценария трассировки на стороне SQL Server

Ниже представлен пример сценария, создающего трассировку с помощью события “Showplan XML Statistics Profile” и отфильтрованную по имени базы данных.


Пример сценария сеанса SQL Server Extended Events

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


Примеры

Для демонстрации примеров в этой статье я использую базу данных AdventureWorks2012, которую вы можете бесплатно скачать с CodePlex по этой ссылке:

http://msftdbprodsamples.codeplex.com/releases/view/55330

Предположим, у нас есть представленный ниже запрос, давайте посмотрим, что мы можем сделать.


Расчёт предполагаемого процента завершения

Мы можем получить представление о предполагаемом проценте завершения нашего запроса, глядя на процент выполнения физических операторов в плане запроса. Но вы не найдете эту информацию в колонках DMVsys.dm_exec_query_profiles. Мы должны сделать небольшие математические вычисления с использованием значений в колонках estimate_row_count и row_count, предполагая, что estimate_row_count представляет собой 100 процентов задачи.

Здесь могут возникнуть некоторые проблемы; Надежность этого подхода определяется точностью оценки оптимизатором запросов количества строк данных, и это зависит от того, в каком состоянии во время исполнения запроса находится статистика. Вполне возможно, что используемая оптимизатором статистика окажется переоценённой или недооценённой относительно реальной кардинальности, т.ч. не стоит пугаться, если в результате процент исполнения превысит 100%.

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


Следующий снимок экрана показывает результат исполнения этого запроса для анализа показанного ранее запроса, нуждающегося в профилировании.

Просмотр статуса, ассоциированного с операторами задачи

Можно соединить sys.dm_exec_query_profiles
с sys.dm_os_tasks
иsys.dm_os_waiting_tasks по колонке task_address Посмотрите на следующий запрос:


Как можно видеть на скриншоте ниже, для анализируемого в предыдущем примере запроса мы располагаем информацией о состоянии

задачи для каждого физического оператора, его тип ожидания и о продолжительности исполнения, если это применимо.

Estimated Percentage of Completion

Просмотр статуса, ассоциированного с операторами задачи

Можно соединить sys.dm_exec_query_profiles с sys.dm_os_tasks и sys.dm_os_waiting_tasks
по колонке task_address Посмотрите на следующий запрос:


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

Associated Task Status
Запись опубликована в рубрике Полезно и интересно с метками , , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: