Думаю, вторая часть будет интересна не только администраторам баз данных, но и разработчикам (возможно, даже больше), которым необходимо понять, что не так с запросами на рабочем сервере, которые раньше прекрасно работали на тестовом сервере.
Условно разделим задачи анализа действий пользователя на группы и рассмотрим каждую отдельно:
проанализировать конкретный запрос анализировать нагрузку от приложения при конкретных условиях (например, при нажатии пользователем кнопки в стороннем приложении, работающем с базой данных) анализ текущей ситуации Предупреждение Анализ производительности требует глубокого понимания конструкции и принципов работы сервера базы данных и операционной системы.Поэтому чтение только этих статей не сделает вас экспертом.
Рассмотренные критерии и счетчики в реальных системах находятся в сложной зависимости друг от друга.
Например, высокая загрузка HDD часто связана с проблемой не самого HDD, а с нехваткой оперативной памяти.
Даже если провести некоторые измерения, этого недостаточно для взвешенной оценки проблем.
Цель статей — познакомить с основными вещами на простых примерах.
Рекомендации не следует рассматривать как «руководство к действию»; рассматривайте их как учебные задачи (упрощенно отражающие реальность) и как варианты «подумайте», призванные объяснить ход мыслей.
Надеюсь, что в результате статей вы научитесь обосновывать свои выводы о работе сервера цифрами.
И вместо слов «сервер медленный» вы будете давать конкретные значения конкретных показателей.
Анализ конкретного запроса
Первый пункт достаточно прост, остановимся на нем вкратце.Давайте рассмотрим лишь некоторые менее очевидные вещи.
ССМС , помимо результатов запроса, позволяет получить дополнительную информацию о выполнении запроса:
Практически всем известно, что план запроса получается с помощью кнопок «Показать предполагаемый план выполнения» и «Включить фактический план выполнения».Они отличаются тем, что план оценки строится без выполнения запроса.
Соответственно, информация о количестве обработанных строк будет лишь оценочной.
Фактически будут как расчетные данные, так и фактические данные.
Сильные расхождения между этими значениями указывают на неактуальность статистики.
Впрочем, анализ плана – это тема для отдельной большой статьи, углубляться пока не будем.
Менее известный факт заключается в том, что вы можете получить данные о работе процессора и диска сервера.
Для этого необходимо включить опцию SET либо в диалоге через меню «Запрос» / «Параметры запроса.
».
Экран 
или напрямую с помощью команд 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 мс.Здесь стоит обратить внимание на время компиляции и текст «логическое чтение 96, физическое чтение 5».Время работы SQL Server: Время процессора = 0 мс, затраченное время = 0 мс.
Время работы SQL Server: Время процессора = 0 мс, затраченное время = 0 мс.
(затронуто 32 строки) Таблица «ПродуктПродуктФото».
Количество просмотров 32, логическое чтение 96, физическое чтение 5, прямое чтение 0, логическое чтение 0, физическое чтение 0, прямое чтение 0. Таблица продуктов.
Количество просмотров 0, логическое чтение 64, физическое чтение 0, упреждающее чтение 0, логическое чтение 0, физическое чтение 0, упреждающее чтение 0. Таблица «Документ Товара».
Количество просмотров 1, логическое чтение 3, физическое чтение 1, прямое чтение 0, логическое чтение 0, физическое чтение 0, прямое чтение 0. Время работы SQL Server: Время процессора = 15 мс, затраченное время = 35 мс.
При втором и последующих выполнениях одного и того же запроса количество физических операций чтения может быть сокращено, и перекомпиляция может не потребоваться.
Из-за этого часто возникает ситуация, что второй и последующие разы запрос выполняется быстрее, чем в первый.
Причина, как вы понимаете, в кэшировании данных и составленных планах запросов.
Еще одна полезная кнопка рядом с кнопками плана — «Включить статистику клиентов» — она отображает информацию о сетевом обмене, количестве выполненных операций и общем времени выполнения с учетом затрат на сетевой обмен и обработку клиентов.
Пример, показывающий, что первое выполнение занимает больше времени 
В версии SSMS 2016 появилась кнопка «Включить статистику запросов в реальном времени».
Выводит картинку как в случае с планом запроса, только в ней количество обработанных строк не статично, а меняется на экране непосредственно во время выполнения запроса.
Картина оказывается очень наглядной — по мигающим стрелкам и бегущим цифрам сразу видно, куда тратится время.
Кнопка доступна в студии 2016, но работает с серверами начиная с версии 2014. Подведем итоги первой части:
Мы смотрим на затраты процессора, используя SET STATISTICS TIME ON. Операции с диском: ВКЛЮЧИТЕ СТАТИСТИКУ IO. Не забывайте, что «логическое чтение» — это операция чтения, которая завершается в дисковом кеше без физического доступа к дисковой системе.«Физическое чтение» требует значительно больше времени.
Оцениваем объем сетевого трафика с помощью «Включить статистику клиентов».
Подробно анализируем алгоритм выполнения запроса по «плану выполнения», используя «Включить фактический план выполнения» и «Включить статистику текущих запросов».
Анализ загрузки приложения
Для второго раздела вооружаемся профилировщик - хм.После запуска и подключения к серверу необходимо выбрать записываемые события.
Можно пойти простым путем — запустить профилирование со стандартным шаблоном трассировки.
На вкладке «Общие» в поле «Использовать шаблон» выберите «Стандартный (по умолчанию)» и нажмите «Выполнить».
Картина 
Чуть более сложный способ — добавить (или убрать) фильтры или события в выбранный шаблон.
Эти параметры находятся на второй вкладке диалогового окна.
Чтобы увидеть полный набор возможных событий и столбцов для выбора, установите флажки «Показать все события» и «Показать все столбцы».
Картина 
Из событий нам понадобятся (ненужные лучше не включать - чтобы создавать меньше трафика):
Они возникают, как следует из названия (Completed), после обработки запроса.
Существуют аналогичные события, записывающие начало sql-вызова:
Хранимые процедуры\RPC:Запуск TSQL\SQL:BatchStarting Но они нам менее подходят, так как не содержат информации о затраченных на выполнение запроса ресурсах сервера.Очевидно, что такая информация доступна только в конце исполнения.
Соответственно, столбцы с данными о CPU, Reads, Writes в событиях *Starting будут пустыми.
Другие полезные события, которые мы пока не будем включать:
Хранимые процедуры\SP:Starting (*Completed) — записывает внутренний вызов хранимой процедуры (не от клиента, а внутри текущего запроса или другой процедуры).Хранимые процедуры \ SP:StmtStarting (*Completed) — записывает начало каждого выражения внутри хранимой процедуры.
Если процедура содержит цикл, событий для команд внутри цикла будет столько, сколько итераций было в цикле.
TSQL\SQL:StmtStarting (*Completed) — записывает начало каждого выражения внутри SQL-пакета.
Если ваш запрос содержит несколько команд, для каждой будет свое событие.
Те.
аналогичен предыдущему, только распространяется не на команды внутри процедур, а на команды внутри запроса.
Эти события полезны для отслеживания шагов выполнения.
Например, когда использование отладчика невозможно.
По столбцам Какой из них выбрать, обычно понятно из названия колонки.
Нам понадобится:
TextData, BinaryData — для описанных выше событий содержат сам текст запроса.CPU, Reads, Writes, Duration — данные о потреблении ресурсов.
StartTime, EndTime — время начала/окончания выполнения.
Удобно для сортировки.
Добавьте другие столбцы на свой вкус.
Нажав кнопку «Фильтры столбцов.
», вы можете вызвать диалог настройки фильтров событий.
Если вас интересует активность конкретного пользователя, установите фильтр по номеру сессии или имени пользователя.
К сожалению, если приложение подключено через app-сервер с пулом подключений, отследить конкретного пользователя сложнее.
Фильтры можно использовать, например, для отбора только «тяжелых» запросов (Длительность> X).
Или запросы, вызывающие интенсивную запись (Writes> Y).
Да хотя бы просто исходя из содержания запроса.
Что еще нам нужно от профилировщика? План выполнения, конечно! Есть такая возможность.
Вам необходимо добавить в трассировку событие «Профиль статистики Performance\Showplan XML».
Выполнив наш запрос, мы получим примерно следующее изображение.
Текст запроса 
План выполнения 
И это еще не все
Трассировку можно сохранить в файл или таблицу базы данных (а не просто отобразить).
Настройки трассировки можно сохранить в виде личного шаблона для быстрого запуска.
Запустить трассировку можно и без профилировщика — с помощью кода t-sql, используя процедуры: sp_trace_create, sp_trace_setevent, sp_trace_setstatus, sp_trace_getdata.
Пример того, как это сделать.Такой подход может быть полезен, например, для автоматического запуска записи трассы в файл по расписанию.
Вы можете узнать, как именно использовать эти команды, в самом профилировщике.
Достаточно запустить две трассировки и в одной дорожке узнать, что произойдет при запуске второй.
Обратите внимание на фильтр в столбце «ApplicationName» — убедитесь, что для самого профилировщика нет фильтра.
Список событий, фиксируемых профайлером, очень обширен и не ограничивается только получением текстов запросов.
Есть события, которые записывают полное сканирование, перекомпиляцию, авторастение, взаимоблокировку и многое другое.
Анализируем активность пользователей на сервере в целом
Бывают и жизненные ситуации, когда информация из разделов выше не помогает: Какой-то запрос очень долго висит на «выполнении» и непонятно, завершится он когда-нибудь или нет. Хотелось бы отдельно проанализировать проблемный запрос, но надо сначала определить, что это за запрос.Ловить стартовое событие профайлером бесполезно — стартовое событие мы уже пропустили, и непонятно, сколько ждать завершенного события.
А может это вообще не запрос пользователя, а может сам сервер что-то активно делает. Давайте разберемся Все вы наверняка видели «Монитор активности».
В старых студиях его функционал стал богаче.
Как он может нам помочь? В «Мониторе активности» есть много полезного и интересного, но третий раздел не об этом.
Все необходимое мы будем получать непосредственно из системных представлений и функций (а сам Монитор полезен тем, что на нем можно запустить профилировщик и посмотреть, какие запросы он выполняет).
Нам понадобится:
- sys.dm_exec_sessions — информация о сеансах.
Отображает информацию о подключенных пользователях.
Полезные поля (в рамках данной статьи) - идентификация пользователя (имя_логина, время_входа, имя_хоста, имя_программы,.
) и поля с информацией о затраченных ресурсах (время_процессора, чтение, запись, использование_памяти,.
)
- sys.dm_exec_requests — информация о выполняемых в данный момент запросах.
Полей здесь тоже довольно много, давайте рассмотрим лишь некоторые:
- session_id — код сессии для соединения с предыдущим представлением
- start_time — время начала запроса
- команда – это поле, вопреки своему названию, содержит не запрос, а тип команды, которую необходимо выполнить.
Для пользовательских запросов это обычно что-то вроде выбора/обновления/удаления/и т. д. (также важные примечания ниже)
- sql_handle,statement_start_offset,statement_end_offset — информация для получения текста запроса: дескриптор, а также начальная и конечная позиция в тексте запроса — с указанием исполняемой в данный момент части (для случая, когда ваш запрос содержит несколько команд).
- plan_handle — дескриптор созданного плана.
- blocking_session_id — при возникновении блокировки, препятствующей выполнению запроса — указывает номер сессии, вызвавшей блокировку
- wait_type, wait_time, wait_resource — поля с информацией о причине и продолжительности ожидания.
Для некоторых видов ожидания, например блокировки данных, дополнительно указывается код заблокированного ресурса.
- процент_завершения — как следует из названия, это процент выполнения.
К сожалению, он доступен только для команд, ход выполнения которых четко предсказуем (например, резервное копирование или восстановление).
- cpu_time, читает, пишет, логические_чтения, предоставленная_запрос_память — затраты ресурсов.
- sys.dm_exec_sql_text(sql_handle | plan_handle) , sys.dm_exec_query_plan(plan_handle) — функции получения текста и плана запроса.
Ниже рассмотрим пример использования.
- sys.dm_exec_query_stats — сводная статистика выполнения по запросу.
Показывает какой запрос был выполнен, сколько раз и сколько ресурсов на него было потрачено.
Полный список всех представлений и функций системы описан в документация .
Также имеется схема подключения основных объектов в виде красивая картина - можно распечатать на А1 и повесить на стену.
Текст запроса, его план и статистика выполнения — это данные, хранящиеся в процедурном кэше.
Они доступны во время выполнения.
После выполнения доступность не гарантируется и зависит от нагрузки на кэш.
Да, вы можете очистить кеш вручную.
Иногда это рекомендуют делать, когда планы выполнения пошли не так, но есть масса нюансов.
В общем, «Есть противопоказания, рекомендуется проконсультироваться со специалистом».
Поле «команда» практически бессмысленно для пользовательских запросов — ведь мы можем получить полный текст… Но не все так просто.
Это поле очень важно для получения информации о системных процессах.
Как правило, они выполняют какие-то внутренние задачи и не имеют sql-текста.
Для таких процессов информация о команде является единственной подсказкой о типе активности.
В комментариях к предыдущей статье был вопрос о том, чем занят сервер, когда вроде бы он ничем не должен быть занят — возможно, ответ будет в значении этого поля.
В моей практике поле «команда» для активных системных процессов всегда выдавало что-то вполне понятное: autoshrink/autogrow/checkpoint/logwriter/etc. Как это использовать Перейдем к практической части.
Я приведу несколько примеров использования, но не ограничивайте свою фантазию.
На этом возможности сервера не заканчиваются — вы можете придумать что-то свое.
Пример 1. Какой процесс потребляет ресурсы процессора/чтения/записи/памяти? Для начала посмотрим, какие сессии потребляют больше всего, например процессор.
Информация в sys.dm_exec_sessions. Но данные ЦП (а также операции чтения и записи) являются накопительными.
То есть число в поле содержит «сумму» за все время соединения.
Понятно, что больше всего будет у того, кто подключился месяц назад и ни разу не отключался.
Это не значит, что он загружает систему прямо сейчас.
Небольшой код решает проблему, алгоритм примерно такой:
- Для начала сделаем выборку и сохраним ее во временную таблицу
- тогда давай подождем немного
- давай попробуем второй раз
- сравниваем результаты первой и второй выборки - разница и будет затратами, понесенными на шаге 2
- для удобства мы можем разделить разницу на продолжительность шага 2, чтобы получить среднюю «стоимость в секунду».
if object_id('tempdb.#tmp') is NULL
BEGIN
Теги: #Администрирование баз данных #ms sql сервер #для чайников #программирование баз данных
-
Натурные Съемки – Что Нужно Знать
19 Oct, 24 -
Когда Интернет Становится Бедным?
19 Oct, 24 -
Неизвестные Родственники
19 Oct, 24 -
Разбираем «Слона» Вместе С Коллегами
19 Oct, 24 -
Канобувости, 22-Й Выпуск
19 Oct, 24 -
Наш Путь К Грин-Карте
19 Oct, 24
