Profiler MS SQL

Содержание

В данной теме я хочу поговорить об очень полезном инструменте — SQL Server Profiler.

Как описано на MSDN, приложение SQL Server Profiler — это графический пользовательский интерфейс для трассировки SQL, с помощью которого можно наблюдать за экземпляром компонента Database Engine. Приложение позволяет собирать и сохранять данные о каждом событии в файле или в таблице для последующего анализа. Данное приложение представляет исключительную важность в задачах анализа производительности исполняемых запросов, а также при анализе проблем параллельности работы в базе данных.

На текущий момент Microsoft продвигает другой аналогичный инструмент — Extended Events и рекомендует пользоваться им, тем не менее я считаю полезным уметь работать и с инструментом Profiler.

Настройка приложения

В профайлере, начиная с версии 2005, в настройках приложения присутствует флажок «Показывать значения в столбце «Продолжительность» в микросекундах» (Show values in Duration column in microseconds). Данный флажок управляет как отображением значения в соответствующей колонке, так и значением, устанавливаемым для отбора по данной колонке. На мой взгляд, при работе с Profiler удобнее использовать микросекунды, поэтому советую данный флажок установить. Настройка находится в меню Сервис (Tools) → Параметры (Options).

Общие параметры

Запуск трассировки в Profiler

Для того чтобы запустить новую трассировку в Profiler необходимо:

  1. Открыть приложение SQL Server Profiler
  2. Выбрать пункт основного меню «Файл» (File), в нем «Создать трассировку» (New Trace)
  3. В открывшемся диалоге подключиться к нужному экземпляру SQL Server
  4. В открывшемся окне настроить трассировку
  5. Запустить трассировку

Настройка трассировки

Из вышеприведенного списка действий, самым сложным (а по своей сути — единственным) является настройка трассировки. Она имеет множество вариантов, попробуем разобрать основные из них.

Вкладка общие

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

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

Вывод данных трассировки может происходить:

  1. На экран в новом окне — вывод происходит на экран, при этом в дальнейшем трассировку можно будет сохранить как в файл, так и в таблицу в СУБД (даже если опции записи в файл и/или таблицу не были включены)
  2. Записывать в файл на диске (опционально) — дополнительно к выбранным опциям, данные будут записываться в файл на диске. Далее этот файл можно открыть через профайлер. Эта опция удобна для сохранения и/или для передачи трассировки.
  3. Записывать в таблицу базы данных (опционально) — дополнительно к выбранным опциям, данные будут записываться в таблицу базы данных. Далее, посредством возможностей предоставляемых СУБД, можно произвести анализ данных, например, найти самые длительные события или просуммировать общую длительность.

Последним пунктом настройки предлагается установить время остановки трассировки, если это требуется.

Перед продолжением настройки установим шаблон «Пустой» (Blank), имя трассировки может быть произвольным, все остальные флажки могут быть сняты.

Основные свойства трассировки

Вкладка выбора событий

Событие — это действие экземпляра SQL Server Database Engine. Для анализа проблем, возникающих при работе с 1С, существуют определенные наборы событий, с которыми необходимо уметь работать.

Выбор событий — это основная часть настройки трассировки, он предполагает работу с матрицей: «Событие» — «Свойство события». Таким образом, в этой матрице надо установить флажки по тем событиям и их свойствам, которые мы хотим трассировать.

Помимо матрицы событий и их свойств, на форме присутствуют флажки: «Показать все события» (Show all events) и «Показать все столбцы» (Show all columns). При установленном флажке в матрице раскрываются все события/столбцы, при снятом остаются только выбранные. Помимо этого, флажок «Показать все столбцы» влияет на отображение данных в «Фильтры столбцов» — отображаемый список соответствует отображаемым столбцам в матрице. При этом, даже если столбец скрыт (не выбран в матрице и снят флаг «Показать все столбцы»), но отбор на него был установлен — отбор сработает.

«Фильтры столбцов» (Column Filters) — открывает список столбцов по которым можно установить отборы. Если значение события при трассировке не подходит под значение отбора в столбце, данное событие не будет отражено в трассировке. Таким образом, можно установить отбор на информационную базу, по которой необходимо произвести трассировку.

«Упорядочить столбцы» (Organize Columns) — используется для изменения (организации) порядка следования выводимых колонок.

Выбор событий трассировки

События для получения плана выполнения запроса

Для того чтобы получить план запроса в Profiler следует добавить следующие события:

Событие Описание
Showplan All Выводит подробную информацию о предполагаемом плане запроса в текстовом виде
Showplan Statistics Profile Выводит подробную информацию о действительном плане запроса в текстовом виде
Showplan XML Выводит подробную информацию о предполагаемом плане запроса в XML формате (может быть представлен графически)
Showplan XML Statistics Profile Выводит подробную информацию о действительном плане запроса в XML формате (может быть представлен графически)

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

Событие Описание
RPC:Completed Происходит при завершении удаленного вызова процедуры
SQL:BatchCompleted Возникает при завершении выполнения инструкции Transact-SQL

Среди столбцов, выводимых в трассировке, рекомендуется включить: TextData, BinaryData, Reads, Writes, CPU, Duration, SPID.

События для получения плана выполнения запроса

Также полезно установить фильтры по длительности и базе данных. Как это сделать описано ниже в статье.

Другие способы получения плана запроса (без использования Profiler) описаны в статье «Методы получения плана запроса в СУБД MS SQL Server»

События для получения графа взаимоблокировки

Для получения графа взаимоблокировки достаточно добавить одноименное событие Locks: Deadlock graph.

Событие Deadlock graph возникает одновременно с классом событий Lock: Deadlock. Класс событий Deadlock graph предоставляет XML-описание взаимоблокировки.

Среди столбцов, выводимых в трассировке, рекомендуется включить: EventSequence, SPID, StartTime, TextData.

События для получения графа взаимоблокировки

События для получения информации об эскалации

Для получения информации об эскалации достаточно добавить событие Locks: Escalation.

Событие Escalation возникает при эскалации блокировки, т.е. когда блокировка более мелких фрагментов преобразуется в блокировку более крупных фрагментов.

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

События для получения информации об эскалации

Установка фильтров столбцов

Установить фильтры можно нажав на кнопку «Фильтры столбцов».

Установка отборов по столбцам

Важно понимать, не все события содержат те или иные колонки. Если событие не содержит колонку по которой установлен фильтр, данное событие отфильтровано не будет.

Очень полезным фильтром является отбор по имени базы или ее идентификатору (если в экземпляре находится несколько баз, а трассировать необходимо какую-то определенную). Для установки фильтра по имени базы необходимо для колонки DatabaseName установить значение «Похоже на» или «Не похоже на». Стоит отметить: если установленному значению будут отвечать несколько баз, тогда события будут собираться по каждой из них. Второй вариант фильтрации событий по определенной базе — установка отбора по колонке DatabaseID. Узнать идентификатор базы данных можно выполнив запрос в SQL Server Management Studio:

1 SELECT DB_ID(‘MyBase’)

где MyBase — имя базы, для которой необходимо получить идентификатор.

Еще одним одним полезным фильтром является отбор по SPID (идентификатору серверного процесса). Он помогает произвести трассировку событий по определенному пользователю в то время когда в базе работают другие пользователи. Получить SPID пользователя, работающего в 1С, можно при помощи консоли кластера серверов. Получение данной информации в этой статье не рассматривается.

Обычно задача получения плана выполнения запроса связана с тем что запрос выполняется достаточно долго и для его оптимизации необходимо разобраться с планом выполнения. При этом настройка для получения плана выполнения собирает большое количество коротких по длительности событий. И именно для того чтобы отсечь ненужные короткие события можно применить отбор по длительности. Для установки фильтра по длительности необходимо установить значение отбора для колонки Duration. Отмечу что значение используемое для отбора может быть выражено в миллисекундах или микросекундах. То, какое значение используется, устанавливается в «Общих параметрах» профайлера (см. раздел «Настройка приложения»).

Работа с трассировкой

После запуска, в окно трассировки начнут выводиться события.

Если трассировка запущена, ее можно приостановить (дальнейшее возобновление трассировки оставит в окне накопленные события) или остановить (после возобновления накопленные события будут очищены).

Если трассировка приостановлена или остановлена, тогда ее можно возобновить.

Помимо этого, в любой момент существует возможность очистить окно трассировки.

Для всех вышеприведенных действий на панели инструментов присутствуют необходимы кнопки.

Панель инструментов

Для изменения настройки уже созданной трассировки необходимо ее приостановить (или остановить) и перейти в пункт меню Файл (File) → Свойства (Properties). Выполнив изменения в трассировке, ее можно запустить вновь.

Работа с шаблонами трассировки

Наличие готовых шаблонов трассировки экономит время на настройке новой трассировки, поэтому я рекомендую сохранять необходимые для работы шаблоны. Шаблоны можно создавать, изменять, экспортировать и импортировать; для данных действий предназначен раздел меню «Файл» (File) → «Шаблоны» (Templates).

Создание шаблона

Для создания шаблона трассировки можно воспользоваться пунктом меню «Файл» (File) → «Шаблоны» (Templates) → «Новый шаблон» (New template).

Вторым (и на мой взгляд наиболее удобным) вариантом создания шаблона является сохранение настройки текущей трассировки в виде шаблона. Для этого требуется воспользоваться пунктом меню «Файл» (File) → «Сохранить как» (Save as) → «Шаблон трассировки» (Trace template)

Сохранение трассировки

Как было сказано ранее, трассировка может быть сохранена в файл на диске и/или в таблицу базы данных. Эти действия можно произвести даже если при настройке трассировки не были установлены соответствующие флажки.

Для того чтобы сохранить трассировку в файл, в меню присутствует пункт «Файл» (File) → «Сохранить как» (Save as) → «Файл трассировки» (Trace File). Для сохранения трассировки в таблицу существует аналогичный пункт меню: «Файл» (File) → «Сохранить как» (Save as) → «Таблица трассировки» (Trace Table)

Одним из ключевых параметров, которым измеряется успешность внедрения и работы в информационной системе, является производительность. Если пользователям 1С приходиться долго ждать формирования отчета или проведения документа, это скажется на качестве работы. Поэтому любое готовое решение проверяют на быстродействие перед запуском в эксплуатацию. Один из популярнейших методов оценки быстродействия – Application Performance Index, вшит во все современные типовые конфигурации 1С.

Зачем нужна APDEX?

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

  • Производительности конфигурации 1С;
  • Проблемным узлам информационной системы и приоритетам их оптимизации;
  • Скорости работы функционала в различных условиях;
  • Результатам оптимизации.

Распространены ситуации, когда пользователи 1С жалуются на скорость работы системы, несмотря на неплохую конфигурацию сервера. К сожалению, в типовых системах 1С достаточно неоптимальных и устаревших функций. Методика APDEX поможет найти эти проблемные места, что поможет, например, поставить задачу перед разработчиками и исправить найденные недостатки конфигурации. Также можно попытаться самостоятельно устранить недоработку и сэкономить время и деньги, которые планировали потратить на увеличение мощности сервера.

Наиболее часто можно встретить применение метода APDEX на проектах внедрения информационных систем на крупных предприятиях. Чем больше пользователей, тем больше требования к оптимизации и быстродействию системы. Работа по оптимизации информационных систем 1С с применением методики APDEX проходит в несколько этапов:

Оценка текущей производительности конфигурации с фиксацией результатов;

  1. Определение целевых показателей APDEX;
  2. Составление перечня необходимых работ и расстановка приоритетов;
  3. Выполнение работ поэтапно с отслеживанием изменений показателей APDEX;
  4. Оценка результатов работ и сдача отчета о проделанных работах.

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

В типовые конфигурации 1С встроен механизм измерения времени выполнения операций. Чтобы его активировать, необходимо переменную «Выполнять замеры производительности» установить в значение «Истина». Все результаты измерений будут записывать в регистр сведений «Замеры времени», откуда вы сможете получать необходимые данные для расчета.

Рис.1 Определение целевых показателей APDEX

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

Расчет показателя APDEX

Перед тем как обращаться к формуле и рассчитывать конечный результат, необходимо узнать, сколько действий укладывается в допустимое время выполнения операции. Также мы должны знать количество итераций, которые выполнялись за время от допустимого до этого же показателя, умноженного на 4. Например, пользователи определили максимально приемлемое время проведения документа в 3 секунды. Мы должны по показателям замеров времени узнать, сколько документов проводится быстрее 3 секунд, а сколько – быстрее 12.

Таблица 1

Всего операций
C

Выполнены быстрее
3 секунд
C3

Выполнены
от 3 секунд до 12
C12

Данных, приведенных в таблице, достаточно, чтобы определить индекс APDEX для конкретной операции по простой формуле. Необходимо сложить количество операций, выполненных быстрее допустимого времени и половину количества итераций, выполненных в промежутке от 3 до 12 секунд. Разделить получившуюся сумму на общее количество операций и в результате получить индекс APDEX для конкретного действия.

Рис.2 Расчет показателя APDEX

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

Путем анализа приоритетов операций и полученных данных о качестве производительности формируется общая оценка производительности 1С 8.3. Она позволит сделать выводы о работоспособности системы в текущих условиях, удовлетворенности сотрудников и приоритетов для оптимизации. Эта информация поможет найти проблемные места и определиться с объемами, сроками и стоимостью необходимых работ. Методика APDEX отличается простотой использования и прозрачностью для пользователей, разработчиков и руководства.

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

SQL Profiler — это инструмент, предоставляющий пользовательский интерфейс для утилиты SQL Trace, которая собирает события по мере их возникновения в экземпляре SQL Server. Это дает возможность отслеживать все, что происходит внутри экземпляра. ПО предоставляет простой в использовании интерфейс, который позволяет выбирать события отслеживания и место, где нужно сохранить вывод.

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

Принцип работы SQL Server Profiler

Вам будет интересно:Чем открыть FRW-файлы? Программы

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

Чтобы запустить ПО, переходят в «Пуск» > «Программы» > Microsoft SQL Server > «Инструменты производительности» > SQL Profiler.

Список преимуществ ПО для разработчиков и администраторов баз данных:

  • Ясность — демонстрация работы экземпляра, когда он взаимодействует с клиентом.
  • Средство устранения проблем — помогает сосредоточиться на критичных точках, позволяя захватывать и воспроизводить ключевые события. Эта функция также помогает в стресс-тестировании и идентификации медленно выполняющихся запросов.
  • Разрешает пользователям без прав администратора безопасно создавать следы, может удовлетворить потребности администраторов, разработчиков баз данных, специалистов по бизнес-аналитике, ИТ-специалистов и даже бухгалтеров.
  • Сравнивает активность с базовыми показателями. Это позволяет юзерам сохранять данные трассировки и сравнивать их с новыми данными, чтобы выявить проблемные места.
  • Захватывает трассировки для Transact-SQL, служб SSIS и аналитики.
  • Инструменты анализа баз данных

    Администратору баз данных необходимы инструменты, позволяющие анализировать действия в БД SQL Server для устранения возможных проблем приложения, БД или просто для мониторинга общего состояния системы. Доступ к интерфейсу SQL Profiler получают из Server Management Studio.

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

    Вам будет интересно:Как установить и настроить Tor?

    SQL Profiler имеет много вариантов использования, но его главная цель — отслеживать активность в экземпляре Server. Если есть проблема с последним или просто нужно проверить общее состояние и производительность базы данных, программа дает возможность отслеживать все — от обычной пользовательской активности и транзакции до блокировок и систем ошибки. И также можно выполнить упреждающее обслуживание экземпляра для выявления любых неэффективных запросов, настроек и использовать для захвата большого периода времени воспроизведения тестовой системы.

    Наконец, MS SQL Profiler используется для выполнения аудита. У пользователя есть возможность определять данные входа и выхода из системы, чтобы видеть, кто обращается к экземпляру и из каких систем они получают к нему доступ. Можно записывать все события DDL, которые будут содержать журнал всех изменений, внесенных в среду.

    Доступные версии программы

    MS SQL Profiler доступен только в выпусках SQL Server Enterprise, Business Intelligence и Standard, однако если в среде установлен выпуск Enterprise или Standard, его можно использовать для профилирования любых других выпусков, например, Express SQL Profiler или Web. Расширенные события были выпущены в Server 2008, который считается заменой Profiler или Trace, по-прежнему широко используется и продолжает представлять ценность для удовлетворения многочисленных потребностей администраторов баз данных.

    Как при установке MS SQL server Profiler, так и при создании схемы БД существуют рекомендации, которые следует соблюдать. Есть также некоторые советы, которые следует учитывать при настройке трассировки с использованием Profiler. Эти рекомендации помогут пользователю контролировать экземпляр Server, не вызывая чрезмерных расходов и не увеличивая влияние на проблему производительности.

    Шаблоны для быстрого доступа

    Есть несколько вариантов запуска Profiler. В разных версиях Windows открывают его через «Пуск» или из Metro Interface в более поздних выпусках. Более продвинутые пользователи предпочитают просто запустить его из SSMS, который всегда открыт. Юзер может найти SQL в меню инструментов.

    После запуска приложения открывают диалоговое окно, позволяющее выполнить подключение к серверу, который нужно профилировать. Из раздела «Рекомендации» подключаются к удаленному серверу. После того как заполнены данные подключения, нажимают «Подключиться».

    Теперь, когда пользователь подключен к серверу, он увидит окно «Свойства трассировки», в котором есть две вкладки. Первая — «Общие» — позволяет называть след.

    Перед тем как пользоваться SQL Profiler, требуется выбрать шаблон для сбора данных. Шаблоны — это в основном сохраненный набор событий и столбцов данных, которые нужно отследить. Они позволяют быстро запустить трассировку без необходимости вручную выбирать все события и столбцы данных при каждом запуске нового сеанса. Microsoft предоставляет несколько вариантов, по умолчанию выбран «Стандартный».

    Вам будет интересно:Как перевести аудио в текст быстро и качественно

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

    Выбор событий для отслеживания

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

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

    Событие — это действие, которое происходит в пользовательском экземпляре базы данных SQL Profiler Oracle. В трассировке можно настроить более 150 различных событий.

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

    Определение столбцов данных

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

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

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

    Фильтры в сеансе трассировки

    Profiler позволяет пользователю фильтровать данные, захваченные в сеансе SQL. r, чтобы ограничить объем захватываемых. Фильтр применяется к любому столбцу, являющемуся частью выбранного события. Даже если столбец не сохранен как часть сеанса трассировки SQL, все равно применяют фильтры на его основе. Чтобы создать фильтр, выбирают кнопку «Фильтры столбцов» на вкладке «Выбор».

    После выбора откроется диалоговое окно «Редактировать фильтр». Далее выбирают столбец данных LoginName, в котором содержатся предложения сравнения текста, и отфильтровывают с помощью LoginName, например, именем «Сервер». При использовании оператора LIKE следует иметь в виду, что если пользователь не включит подстановочный знак «%», он будет рассматриваться как оператор равенства (=). Например, если нужно найти какого-либо оператора, выполненного именем входа, которое начинается с «app», в качестве фильтра будет «app%». В зависимости от выбранного столбца появятся различные операторы сравнения.

    Рекомендации по настройке

    Profiler — отличный инструмент, который позволяет увидеть, что происходит внутри Server, и поможет пользователям выяснить, какие наихудшие запросы, как часто выполняется запрос.

    Для реализации наилучших возможных индексов выполняют 4 шага:

  • Сбор текущей информации о рабочей нагрузке.
  • Определение соответствия индексов рабочей нагрузке, используя Profiler.
  • Обновление индексов с помощью Tuning Advisor.
  • Профилировщик автоматизирует сбор данных рабочей нагрузки, а консультант по настройке берет эти данные и создает индексы.
  • Для отслеживания в конкретной БД используют функцию фильтрации столбцов. Запускают трассировку через шаблон настройки и сохраняют данные трассировки в его файл.

    Алгоритм процесса:

  • Нажимают на «Инструменты».
  • Открывают помощник настройки ядра.
  • Предоставляют трассирующий файл.
  • Выбирают БД, в которой необходимо выполнить процесс трассировки.
  • В «Настройках» переходят в «Дополнительные параметры» и определяют максимальное пространство для рекомендации.
  • Начинают анализ.
  • Результаты показывают, насколько можно добиться улучшения, если предложения будут реализованы, и что нужно реализовать для повышения скорости работы БД.

    Для того чтобы перехватить взаимоблокировки с помощью Profiler, делают следующее:

  • Фиксируют тупик, сначала подключаются к БД Server.
  • Открывают Profiler в Server Management Studio.
  • Нажимают на «Инструменты».
  • Нажимают на Profiler.
  • Подключение к серверу и анализ ситуации

    Перед тем как начать пользоваться SQL Server Profiler, подключаются к серверу, на котором нужно выполнить профилирование. В окне «Свойства трассировки» на вкладке «Общие» выбирают пустой шаблон. На вкладке «Выбор событий» выбирают график «тупик» в разделе «Лист блокировки».

    Вам будет интересно:Переменные окружения Linux: описание, особенности

    Выполняют следующие запросы:

    • выполнить запрос в синем прямоугольнике;
    • выполнить запрос в зеленом прямоугольнике;
    • выполнить запрос, выделенный серым цветом;
    • выполнить запрос, выделенный синим.

    Обращают внимание, что идентификаторы процессов отображаются на информационной панели в SQL. «Мертвая блокировка» будет сгенерирована при выполнении 4-го шага: для последующего анализа это можно сохранить в меню «Файл» -> «Экспорт» -> «Извлечь события SQL Server» -> «Извлечь события взаимоблокировки».

    Анализ графа тупиковой ситуации:

  • Овальный с синим крестиком представляет транзакцию или процесс, который был выбран в качестве жертвы тупика SQL Server.
  • Овалы представляют процессы, а крест — транзакцию или процесс, который успешно завершен.
  • Приоритет взаимоблокировки установлен по умолчанию «0».
  • У пользователя есть журнал транзакций. Если транзакция сделала много обновлений, размер журнала будет больше. Следовательно, для выполнения той, которая выполнила большое количество обновлений, потребуется много затрат.

    HoBtID (идентификатор кучи или двоичного дерева), связанный с узлом ресурса, используется для поиска объекта базы данных, участвующего во взаимоблокировке, и представлен с помощью следующего запроса:

    • SELECT object_name() from sys.partitions;
    • WHERE hobt_id = xxxxxx.

    Стрелки представляют типы блокировок, которые имеются на каждом узле ресурса. Обозначения X и S на стрелках представляют эксклюзивные и общие блокировки.

    Использование профиля в 1С

    SQL Server Profiler с графической оболочкой используется в бухгалтерской программе 1С для создания трасс и анализа результирующей трассировки с сохранением событий в трассировочных файлах. На их базе выполняется анализ ситуации образовавшихся проблем.

    Для создания треков в Profiler запускают программу, формируют маршрут и настраивают анализ индикаторов. Перед тем как настроить для 1С SQL Profiler, выполняют следующие шаги:

  • Указывают имя трассы General, где удаленные данные трассировки будут храниться.
  • На вкладке «Выбор события» показаны события для отслеживания
  • Начинают трассировку в профилировщике для 1С после выполнения настроек через кнопку «Выполнить».
  • Например, запускают трек «Получение материалов», чтобы отследить операции, которые занимают больше всего времени.
  • После получения данных анализируют их.
  • Анализ профиля сохраняют в файле или в таблице, например, под названием «материалы».
  • После сохранения работают с ней как с обыкновенной таблицей SQL.
  • Альтернативные инструменты профилировщика

    Несмотря на популярность SQL Profiler, это не единственный способ отслеживать сервер. Следующие альтернативы выполняют аналогичную функцию:

  • Prefix. Одна из замечательных функций, которую имеет Prefix — это просматривать запросы SQL прямо из кода приложения, чтобы получать не только точные, но и контекстные сведения. Он бесплатный, невероятно надежный и предоставляет подробные сведения о каждом веб-запросе.
  • ExpressProfiler — альтернатива, которая использует простой и быстрый графический интерфейс пользователя.
  • Neor Profile SQL предоставляет аналогичные возможности для пользователей MySQL.
  • Трассировка на стороне сервера считается лучшей, поскольку на стороне клиента может истощать достаточно локальных ресурсов для искажения результатов. Такой вид трассировки не использует ресурсы на стороне клиента, требует меньших затрат и обеспечивает более точный мониторинг и отчетность.

    Таким образом, очевидно, что профилировщик позволяет разработчикам и администраторам баз данных (DBA) создавать и обрабатывать трассировки, а также воспроизводить и анализировать их результаты.

    Часть 1
    Продолжаем анализировать что происходит на нашем MS SQL сервере. В этой части посмотрим как получить информацию о работе пользователей: кто и что делает, сколько ресурсов на это расходуется.
    Думаю, вторая часть будет интересна не только админам БД, но и разработчикам (возможно даже разработчикам больше), которым необходимо разбираться, что не так с запросами на рабочем сервере, которые до этого отлично работали в тестовом.
    Задачи анализа действий пользователей условно поделим на группы и рассмотрим каждую отдельно:

    1. проанализировать конкретный запрос
    2. проанализировать нагрузку от приложения в конкретных условиях (например, при нажатии пользователем какой-то кнопки в стороннем приложении работающим с БД)
    3. анализ ситуации происходящей в данный момент

    ПредупреждениеАнализ производительности требует глубокого понимания устройства и принципов работы сервера БД и ОС. Поэтому, чтение только этих статей не сделает из вас эксперта.
    Рассматриваемые критерии и счетчики в реальных системах находятся в сложной зависимости друг от друга. Например высокая нагрузка HDD, часто связана с проблемой не самого HDD, а с нехваткой оперативной памяти. Даже если вы проведете некоторые из замеров — этого недостаточно для взвешенной оценки проблем.
    Цель статей — познакомить с базовыми вещами на простых примерах. Рекомендации не стоит рассматривать как «руководство к действию», рассматривайте их как учебные задачи (упрощенно отражающие действительность) и как варианты «на подумать», призванные пояснить ход мысли.
    Надеюсь, по итогу статей вы научитесь аргументировать цифрами свои заключения о работе сервера. И вместо слов «сервер тормозит» будете приводить конкретные величины конкретных показателей.

    Анализируем конкретный запрос

    Первый пункт довольно прост, остановимся на нем кратко. Рассмотрим только некоторые малоочевидные вещи.
    SSMS, кроме результатов запроса, позволяет получать дополнительную информацию о выполнении запроса:

    • Практически все знают, что план запроса получается кнопками «Display Estimated Execution Plan» (оценочный план) и «Include Actual Execution Plan» (фактический план). Отличаются они тем, что оценочный план строится без выполнения запроса. Соответственно, информация о количестве обработанных строк в нем будет только оценочная. В фактическом плане будут как оценочные данные, так и фактические. Сильные расхождения этих величин говорят о неактуальности статистики. Впрочем, анализ плана — тема для отдельной большой статьи — пока не будем углубляться.
    • Менее известный факт — можно получать замеры затрат процессора и дисковых операций сервера. Для этого необходимо включить SET опции либо в диалоге через меню «Query» / «Query options…»
      Скрин
      либо напрямую командами SET в запросе, например
      SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM Production.Product p JOIN Production.ProductDocument pd ON p.ProductID = pd.ProductID JOIN Production.ProductProductPhoto ppp ON p.ProductID = ppp.ProductID
      В результате выполнения, получим данные по затратам времени на компиляцию и выполнение, а также, количество дисковых операций.
      Пример вывода Время синтаксического анализа и компиляции SQL Server:
      время ЦП = 16 мс, истекшее время = 89 мс.
      Время работы SQL Server:
      Время ЦП = 0 мс, затраченное время = 0 мс.
      Время работы SQL Server:
      Время ЦП = 0 мс, затраченное время = 0 мс.
      (32 row(s) affected)
      Таблица «ProductProductPhoto». Число просмотров 32, логических чтений 96, физических чтений 5, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
      Таблица «Product». Число просмотров 0, логических чтений 64, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
      Таблица «ProductDocument». Число просмотров 1, логических чтений 3, физических чтений 1, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
      Время работы SQL Server:
      Время ЦП = 15 мс, затраченное время = 35 мс.
      Здесь стоит обратить внимание на время компиляции и текст «логических чтений 96, физических чтений 5». При втором и последующих выполнениях одного и того же запроса — физические чтения могут уменьшаться, а повторная компиляция может не потребоваться. Из-за этого часто возникает ситуация, что второй и последующие разы запрос выполняется быстрее чем первый. Причина, как вы поняли, в кэшировании данных и скомпилированных планов запросов.
    • Еще полезная кнопочка рядом с кнопками планов — «Include Client Statistics» — выводит информацию по сетевому обмену, количестве выполненных операций и суммарном времени выполнения, с учетом затрат на сетевой обмен и обработку клиентом.
      Пример, на котором видно что первое выполнение занимает больше времени
    • В SSMS 2016 версии появилась кнопка «Include Live Query Statistics». Отображает картинку как и в случае с планом запроса, только на ней цифры обработанных строк не статические, а меняются на экране прямо в процессе выполнения запроса. Картинка получается очень наглядная — по мигающим стрелкам и бегущим цифрам сразу видно где тратится время. Кнопка есть в 2016 студии, но работает с серверами начиная с 2014 версии.

    Подытожим первую часть:

    • Затраты процессора смотрим используя SET STATISTICS TIME ON.
    • Дисковые операции: SET STATISTICS IO ON. Не забываем, что «логическое чтение» — это операция чтения, завершившаяся в кэше диска без физического обращения к дисковой системе. «Физическое чтение» требует значительно больше времени.
    • Объем сетевого трафика оцениваем с помощью «Include Client Statistics».
    • Детально алгоритм выполнения запроса анализируем по «плану выполнения» с помощью «Include Actual Execution Plan» и «Include Live Query Statistics».

    Анализируем нагрузку от приложения

    Для второго раздела вооружаемся profiler-ом. После запуска и подключения к серверу, необходимо выбрать журналируемые события. Можно пойти простым путем — запустить профилирование со стандартным темплэйтом трассировки. На закладке «General» в поле «Use the template» выбрать «Standard (default)» и нажать «Run».
    Картинка
    Чуть более сложный путь — к выбранному шаблону добавить (или убавить) фильтров или событий. Данные опции на второй закладке диалога. Чтобы увидеть полный набор возможных событий и колонок для выбора — отметьте пункты «Show All Events» и «Show All Columns».
    Картинка
    Из событий нам потребуются (лишние лучше не включать — чтобы создавать меньше трафика):

    • Stored Procedures \ RPC:Completed
    • TSQL \ SQL:BatchCompleted

    Эти события фиксируют все внешние sql-вызовы к серверу. Они возникают, как видно из названия (Completed), после окончания обработки запроса. Имеются аналогичные события фиксирующие старт sql-вызова:

    • Stored Procedures \ RPC:Starting
    • TSQL \ SQL:BatchStarting

    Но они нам подходят меньше, так как не содержат информации о затраченных на выполнение запроса ресурсах сервера. Очевидно, что такая информация доступна только по окончании выполнения. Соответственно, столбцы с данными по CPU, Reads, Writes в событиях *Starting будут пустыми.
    Еще полезные события, которые мы пока не будем включать:

    • Stored Procedures \ SP:Starting (*Completed) — фиксирует внутренний вызов хранимой процедуры (не с клиента, а внутри текущего запроса или другой процедуры).
    • Stored Procedures \ SP:StmtStarting (*Completed) — фиксирует старт каждого выражения внутри хранимой процедуры. Если в процедуре цикл — будет столько событий для команд внутри цикла, сколько итераций было в цикле.
    • TSQL \ SQL:StmtStarting (*Completed) — фиксирует старт каждого выражения внутри SQL-batch. Если ваш запрос содержит несколько команд — будет по событию на каждую. Т.е. аналогично предыдущему, только действует не для команд внутри процедур, а для команд внутри запроса.

    Эти события удобны для отслеживания шагов выполнения. Например, когда использование отладчика невозможно.
    По колонкам
    Какие выбирать, как правило, понятно из названия колонки. Нам будут нужны:

    • TextData, BinaryData — для описанных выше событий содержат сам текст запроса.
    • CPU, Reads, Writes, Duration — данные о затратах ресурсов.
    • StartTime, EndTime — время начала/окончания выполнения. Удобны для сортировки.

    Прочие колонки добавляйте на свой вкус.
    По кнопке «Column Filters…» можно вызвать диалог установки фильтров событий. Если интересует активность конкретного пользователя — задать фильтр по номеру сессии или имени пользователя. К сожалению, в случае подключения приложения через app-server c пулом коннектов — отследить конкретного пользователя сложнее.
    Фильтры можно использовать, например, для отбора только «тяжелых» запросов (Duration>X). Или запросов которые вызывают интенсивную запись (Writes>Y). Да хоть просто по содержимому запроса.
    Что же еще нам нужно от профайлера? Конечно же план выполнения!
    Такая возможность имеется. Необходимо добавить в трассировку событие «Performance \ Showplan XML Statistics Profile». Выполняя наш запрос, мы получим примерно следующую картинку.
    Текст запроса
    План выполнения
    И это еще не всё
    Трассу можно сохранять в файл или таблицу БД (а не только выводить на экран).
    Настройки трассировки можно сохранить в виде личного template для быстрого запуска.
    Запуск трассировки можно выполнять и без профайлера — с помощью t-sql кода, используя процедуры: sp_trace_create, sp_trace_setevent, sp_trace_setstatus, sp_trace_getdata. Пример как это сделать. Данный подход может пригодиться, например, для автоматического старта записи трассы в файл по расписанию. Как именно использовать эти команды, можно подсмотреть у самого профайлера. Достаточно запустить две трассировки и в одной отследить что происходит при старте второй. Обратите внимание на фильтр по колонке «ApplicationName» — проконтролируйте, что там отсутствует фильтр на сам профайлер.
    Список событий фиксируемых профайлером очень обширен и не ограничивается только получением текстов запросов. Имеются события фиксирующие fullscan, рекомпиляции, autogrow, deadlock и многое другое.

    Анализируем активность пользователей в целом по серверу

    Жизненные ситуации бывают и такими, когда информация из разделов выше не помогает:
    Какой-то запрос висит на «выполнении» очень долго и непонятно, закончится он когда-нибудь или нет. Проанализировать проблемный запрос отдельно — хотелось бы — но надо сначала определить что за запрос. Профайлером ловить бесполезно — starting событие мы уже пропустили, а completed неясно сколько ждать.
    А может висит и не пользовательский запрос совсем, а может это сам сервер что-то активно делает…
    Давайте разбираться
    Все вы наверно видели «Activity Monitor». В старших студиях его функционал стал богаче. Чем он может нам помочь? В «Activity Monitor» много полезного и интересного, но третий раздел не о нем. Всё что нужно будем доставать напрямую из системных представлений и функций (а сам Монитор полезен тем, что на него можно натравить профайлер и посмотреть какие запросы он выполняет).
    Нам понадобятся:

    • sys.dm_exec_sessions — информация о сессиях. Отображает информацию по подключенным пользователям. Полезные поля (в рамках этой статьи) — идентифицирующие пользователя (login_name, login_time, host_name, program_name, …) и поля с информацией о затраченных ресурсах (cpu_time, reads, writes, memory_usage, …)
    • sys.dm_exec_requests — информация о запросах выполняющихся в данный момент. Полей тут тоже довольно много, рассмотрим только некоторые:
      • session_id — код сессии для связи с предыдущим представлением
      • start_time — время старта запроса
      • command — это поле, вопреки названию, содержит не запрос, а тип выполняемой команды. Для пользовательских запросов — обычно это что-то вроде select/update/delete/и т.п. (также, важные примечания ниже)
      • sql_handle, statement_start_offset, statement_end_offset — информация для получения текста запроса: хэндл, а также начальная и конечная позиция в тексте запроса — обозначающая часть выполняемую в данный момент (для случая когда ваш запрос содержит несколько команд).
      • plan_handle — хэндл сгенерированного плана.
      • blocking_session_id — при возникновении блокировок препятствующих выполнению запроса — указывает на номер сессии которая стала причиной блокировки
      • wait_type, wait_time, wait_resource — поля с информацией о причине и длительности ожидания. Для некоторых видов ожидания, например, блокировка данных — дополнительно указывается код заблокированного ресурса.
      • percent_complete — по названию понятно, что это процент выполнения. К сожалению, доступен только для команд у которых четко прогнозируемый прогресс выполнения (например, backup или restore).
      • cpu_time, reads, writes, logical_reads, granted_query_memory — затраты ресурсов.
    • sys.dm_exec_sql_text(sql_handle | plan_handle), sys.dm_exec_query_plan(plan_handle) — функции получения текста и плана запроса. Ниже рассмотрим пример использования.
    • sys.dm_exec_query_stats — сводная статистика выполнения в разрезе запросов. Показывает какой запрос сколько раз выполнялся и сколько ресурсов на это потрачено.

    Важные примечания
    Приведенный перечень — лишь малая часть. Полный список всех системных представлений и функций описан в документации. Также, имеется схема связей основных объектов в виде красивой картинки — можно распечатать на А1 и повесить на стену.
    Текст запроса, его план и статистика исполнения — данные хранящиеся в процедурном кэше. Во время выполнения они доступны. После выполнения доступность не гарантируется и зависит от давления на кэш. Да, кэш можно очищать вручную. Иногда это советуют делать когда «поплыли» планы выполнения, но тут очень много нюансов… В общем, «Имеются противопоказания, рекомендовано проконсультироваться со специалистом».
    Поле «command» — для пользовательских запросов оно практически бессмысленно — ведь мы можем получить полный текст… Но не всё так просто. Это поле очень важно для получения информации о системных процессах. Как правило, они выполняют какие-то внутренние задачи и не имеют текста sql. Для таких процессов, информация о команде единственный намек на тип активности. В комментариях к предыдущей статье был вопрос про то, чем занят сервер, когда он, вроде бы, ничем не должен быть занят — возможно ответ будет в значении этого поля. На моей практике, поле «command» для активных системных процессов всегда выдавало что-то вполне понятное: autoshrink/autogrow/checkpoint/logwriter/и т.п.
    Как же это использовать
    Перейдем к практической части. Я приведу несколько примеров использования, но не стоит ограничивать вашу фантазию. Возможности сервера этим не исчерпываются — можете придумывать что-то своё.
    Пример 1: Какой процесс расходует cpu/reads/writes/memory
    Для начала, посмотрим какие сессии больше всего потребляют, например, CPU. Информация в sys.dm_exec_sessions. Но данные по CPU (а также reads, writes) — накопительные. Т.е цифра в поле содержит «итого» за все время подключения. Понятно, что больше всего будет у того кто подключился месяц назад, да так и не отключался ни разу. Это вовсе не означает, что он прямо сейчас грузит систему.
    Немного кода решает проблему, алгоритм примерно такой:

    1. сначала сделаем выборку и сохраним во временную таблицу
    2. затем подождем немного
    3. делаем выборку второй раз
    4. сравниваем результаты первой и второй выборки — разница, как раз и будет затратами возникшими на п.2
    5. для удобства, разницу можем поделить на длительность п.2, чтобы получить усредненные «затраты в секунду».

    Пример скриптаif object_id(‘tempdb..#tmp’) is NULL BEGIN SELECT * into #tmp from sys.dm_exec_sessions s PRINT ‘ждем секунду для накопления статистики при первом запуске’ — при последующих запусках не ждем, т.к. сравниваем с результатом предыдущего запуска WAITFOR DELAY ’00:00:01′; END if object_id(‘tempdb..#tmp1’) is not null drop table #tmp1 declare @d datetime declare @dd float select @d = crdate from tempdb.dbo.sysobjects where id=object_id(‘tempdb..#tmp’) select * into #tmp1 from sys.dm_exec_sessions s select @dd=datediff(ms,@d,getdate()) select @dd AS SELECT TOP 30 s.session_id, s.host_name, db_name(s.database_id) as db, s.login_name,s.login_time,s.program_name, s.cpu_time-isnull(t.cpu_time,0) as cpu_Diff, convert(numeric(16,2),(s.cpu_time-isnull(t.cpu_time,0))/@dd*1000) as cpu_sec, s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0) as totIO_Diff, convert(numeric(16,2),(s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0))/@dd*1000) as totIO_sec, s.reads-isnull(t.reads,0) as reads_Diff, convert(numeric(16,2),(s.reads-isnull(t.reads,0))/@dd*1000) as reads_sec, s.writes-isnull(t.writes,0) as writes_Diff, convert(numeric(16,2),(s.writes-isnull(t.writes,0))/@dd*1000) as writes_sec, s.logical_reads-isnull(t.logical_reads,0) as logical_reads_Diff, convert(numeric(16,2),(s.logical_reads-isnull(t.logical_reads,0))/@dd*1000) as logical_reads_sec, s.memory_usage, s.memory_usage-isnull(t.memory_usage,0) as , s.nt_user_name,s.nt_domain from #tmp1 s LEFT join #tmp t on s.session_id=t.session_id order BY cpu_Diff desc —totIO_Diff desc —logical_reads_Diff desc drop table #tmp GO select * into #tmp from #tmp1 drop table #tmp1
    В коде я использую две таблицы: #tmp — для первой выборки, #tmp1 — для второй. При первом запуске, скрипт создает и заполняет #tmp и #tmp1 с интервалом в одну секунду, и делает остальную часть. При последующих запусках, скрипт использует результаты предыдущего выполнения в качестве базы для сравнения. Соответственно, длительность п.2 при последующих запусках будет равна длительности вашего ожидания между запусками скрипта. Пробуйте выполнять, можно сразу на рабочем сервере — скрипт создает только «временные таблицы» (доступны только внутри текущей сессии и самоуничтожаются при отключении) и не несёт в себе опасности.
    Те, кто не любят выполнять запрос в студии — могут его завернуть в приложение написанное на своём любимом языке программирования. Я покажу как это сделать в MS Excel без единой строки кода.
    В меню «Данные» подключаемся к серверу. Если будет требовать выбрать таблицу — выбираем произвольную — потом поменяем это. Как всегда, жмем «Next» и «Finish» пока не увидим диалог «Импорт данных» — в нем нужно нажать «Свойства…». В свойствах необходимо сменить «тип команды» на значение «SQL» и в поле «текст команды» вставить немного измененный наш запрос.
    Запрос придется немного поменять:

    • добавим «SET NOCOUNT ON» — т.к. Excel не любит отсечки количества строк;
    • «временные таблицы» заменим на «таблицы переменные»;
    • задержка всегда будет 1сек — поля с усредненными значениями не нужны

    Измененный запрос для ExcelSET NOCOUNT ON; declare @tmp table(session_id smallint primary key,login_time datetime,host_name nvarchar(256),program_name nvarchar(256),login_name nvarchar(256),nt_user_name nvarchar(256),cpu_time int,memory_usage int,reads bigint,writes bigint,logical_reads bigint,database_id smallint) declare @d datetime; select @d=GETDATE() INSERT INTO @tmp(session_id,login_time,host_name,program_name,login_name,nt_user_name,cpu_time,memory_usage,reads,writes,logical_reads,database_id) SELECT session_id,login_time,host_name,program_name,login_name,nt_user_name,cpu_time,memory_usage,reads,writes,logical_reads,database_id from sys.dm_exec_sessions s; WAITFOR DELAY ’00:00:01′; declare @dd float; select @dd=datediff(ms,@d,getdate()); SELECT s.session_id, s.host_name, db_name(s.database_id) as db, s.login_name,s.login_time,s.program_name, s.cpu_time-isnull(t.cpu_time,0) as cpu_Diff, s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0) as totIO_Diff, s.reads-isnull(t.reads,0) as reads_Diff, s.writes-isnull(t.writes,0) as writes_Diff, s.logical_reads-isnull(t.logical_reads,0) as logical_reads_Diff, s.memory_usage, s.memory_usage-isnull(t.memory_usage,0) as , s.nt_user_name,s.nt_domain from sys.dm_exec_sessions s left join @tmp t on s.session_id=t.session_id
    Картинки процесса
    Результат
    Когда данные будут в Excel-е, можете их сортировать как вам нужно. Для актуализации информации — жмите «Обновить». В настройках книги, для удобства, можете поставить «автообновление» через заданный период времени и «обновление при открытии». Файл можете сохранить и передать коллегам. Таким образом, мы из навоза и веточек подручных средств собрали ЫнтерпрайзМониторингТул удобный и простой инструмент.
    Пример 2: На что сессия расходует ресурсы
    Итак, в предыдущем примере мы определили проблемные сессии. Теперь определим, что именно они делают. Используем sys.dm_exec_requests, а также функции получения текста и плана запроса.
    Текст запроса и план по номеру сессииDECLARE @sql_handle varbinary(64) DECLARE @plan_handle varbinary(64) DECLARE @sid INT Declare @statement_start_offset int, @statement_end_offset INT, @session_id SMALLINT — для инфы по конкретному юзеру — указываем номер сессии SELECT @sid=182 — получаем переменные состояния для дальнейшей обработки IF @sid IS NOT NULL SELECT @sql_handle=der.sql_handle, @plan_handle=der.plan_handle, @statement_start_offset=der.statement_start_offset, @statement_end_offset=der.statement_end_offset, @session_id = der.session_id FROM sys.dm_exec_requests der WHERE der.session_id=@sid —печатаем текст выполняемого запроса DECLARE @txt VARCHAR(max) IF @sql_handle IS NOT NULL SELECT @txt= FROM sys.dm_exec_sql_text(@sql_handle) PRINT @txt — выводим план выполняемого батча/процы IF @plan_handle IS NOT NULL select * from sys.dm_exec_query_plan(@plan_handle) — и план выполняемого запроса в рамках батча/процы IF @plan_handle IS NOT NULL SELECT dbid, objectid, number, encrypted, CAST(query_plan AS XML) AS planxml from sys.dm_exec_text_query_plan(@plan_handle, @statement_start_offset, @statement_end_offset)
    Подставляйте в запрос номер сессии и выполняйте. После выполнения, на закладке «Results» будут планы (два: первый для всего запроса, второй для текущего шага — если шагов в запросе несколько), на закладке «Messages» — текст запроса. Для просмотра плана — необходимо кликнуть в строке на текст оформленный в виде url. План откроется в отдельной закладке. Иногда бывает что план открывается не в графическом виде, а в виде xml-текста. Это, скорее всего, связано с тем что версия студии ниже чем сервера. Попробуйте пересохранить полученный xml в файл с расширением sqlplan, предварительно удалив из первой строки упоминания «Version» и «Build», а затем отдельно открыть его. Если и это не помогает — напоминаю, что 2016 студия официально доступна бесплатно на сайте MS.
    Картинки
    Очевидно, полученный план будет «оценочным», т.к. запрос еще выполняется. Но некоторую статистику по выполнению получить всё равно можно. Используем представление sys.dm_exec_query_stats с фильтром по нашим хэндлам.
    Допишем в конец предыдущего запроса
    — статистика по плану IF @sql_handle IS NOT NULL SELECT * FROM sys.dm_exec_query_stats QS WHERE QS.sql_handle=@sql_handle
    После выполнения, в результатах получим информацию о шагах выполняемого запроса: сколько раз они выполнялись и какие ресурсы потрачены. Информация в статистику попадает после выполнения — при первом выполнении там, к сожалению, пусто. Статистика не привязана к пользователю, а ведется в рамках всего сервера — если разные пользователи выполняют один и тот же запрос — статистика будет суммарная по всем.
    Пример 3: А можно всех посмотреть
    Давайте объединим рассмотренные системные представления и функции в одном запросе. Это может быть удобно для оценки ситуации в целом.

    — получаем список всех текущих запросов SELECT LEFT((SELECT FROM sys.dm_exec_sql_text(der.sql_handle)),500) AS txt —,(select top 1 1 from sys.dm_exec_query_profiles where session_id=der.session_id) as HasLiveStat ,der.blocking_session_id as blocker, DB_NAME(der.database_id) AS База, s.login_name, * from sys.dm_exec_requests der left join sys.dm_exec_sessions s ON s.session_id = der.session_id WHERE der.session_id<>@@SPID — AND der.session_id>50
    Запрос выводит список активных сессий и тексты их запросов. Для системных процессов, напоминаю, обычно запрос отсутствует, но заполнено поле «command». Видна информация о блокировках и ожиданиях. Можете попробовать скрестить этот запрос с примером 1, чтобы еще и отсортировать по нагрузке. Но будьте аккуратны — тексты запросов могут оказаться очень большими. Выбирать их массово может оказаться ресурсоемко. Да и трафик будет большим. В примере я ограничил получаемый запрос первыми 500 символами, а получение плана не стал делать.
    Примеры запросов выложил на гитхаб.

    Заключение

    Было бы ещё неплохо, получить Live Query Statistics для произвольной сессии. По заявлению производителя, на текущий момент, постоянный сбор статистики требует значительных ресурсов и поэтому, по умолчанию, отключен. Включить не проблема, но дополнительные манипуляции усложняют процесс и уменьшают практическую пользу. Возможно, попробуем это сделать в отдельной статье.
    В этой части мы рассмотрели анализ действий пользователей. Попробовали несколько способов: используя возможности самой студии, используя профайлер, а также прямые обращения к системным представлениям. Все эти способы позволяют оценить затраты на выполнение запроса и получить план исполнения. Ограничиваться одним из них не нужно — каждый способ удобен в своей ситуации. Пробуйте комбинировать.
    Впереди у нас еще анализ нагрузки на память и сеть, а также прочие нюансы. Дойдем и до них. Материала еще на несколько статей.
    Спасибо Владу за помощь в создании статьи.