Основы анализа производительности sql server диски disk analysis

Обновлено: 04.07.2024

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

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

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

Анализ производительности требует глубокого понимания устройства и принципов работы сервера БД и ОС. Поэтому, чтение только этих статей не сделает из вас эксперта.

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

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

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

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

Практически все знают, что план запроса получается кнопками «Display Estimated Execution Plan» (оценочный план) и «Include Actual Execution Plan» (фактический план). Отличаются они тем, что оценочный план строится без выполнения запроса. Соответственно, информация о количестве обработанных строк в нем будет только оценочная. В фактическом плане будут как оценочные данные, так и фактические. Сильные расхождения этих величин говорят о неактуальности статистики. Впрочем, анализ плана — тема для отдельной большой статьи — пока не будем углубляться.
Менее известный факт — можно получать замеры затрат процессора и дисковых операций сервера. Для этого необходимо включить SET опции либо в диалоге через меню «Query» / «Query options. »

либо напрямую командами SET в запросе, например

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

Время синтаксического анализа и компиляции SQL Server:
время ЦП = 16 мс, истекшее время = 89 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.

Время работы SQL Server:
Время ЦП = 15 мс, затраченное время = 35 мс.

Пример, на котором видно что первое выполнение занимает больше времени

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

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

Чуть более сложный путь — к выбранному шаблону добавить (или убавить) фильтров или событий. Данные опции на второй закладке диалога. Чтобы увидеть полный набор возможных событий и колонок для выбора — отметьте пункты «Show All Events» и «Show All Columns».

Из событий нам потребуются (лишние лучше не включать — чтобы создавать меньше трафика):

Stored Procedures \ RPC:Completed
TSQL \ SQL:BatchCompleted

Stored Procedures \ RPC:Starting
TSQL \ SQL:BatchStarting

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

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» много полезного и интересного, но третий раздел не о нем. Всё что нужно будем доставать напрямую из системных представлений и функций (а сам Монитор полезен тем, что на него можно натравить профайлер и посмотреть какие запросы он выполняет).

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 — затраты ресурсов.

Приведенный перечень — лишь малая часть. Полный список всех системных представлений и функций описан в документации. Также, имеется схема связей основных объектов в виде красивой картинки — можно распечатать на А1 и повесить на стену.

Текст запроса, его план и статистика исполнения — данные хранящиеся в процедурном кэше. Во время выполнения они доступны. После выполнения доступность не гарантируется и зависит от давления на кэш. Да, кэш можно очищать вручную. Иногда это советуют делать когда «поплыли» планы выполнения, но тут очень много нюансов… В общем, «Имеются противопоказания, рекомендовано проконсультироваться со специалистом».

Поле «command» — для пользовательских запросов оно практически бессмысленно — ведь мы можем получить полный текст… Но не всё так просто. Это поле очень важно для получения информации о системных процессах. Как правило, они выполняют какие-то внутренние задачи и не имеют текста sql. Для таких процессов, информация о команде единственный намек на тип активности. В комментариях к предыдущей статье был вопрос про то, чем занят сервер, когда он, вроде бы, ничем не должен быть занят — возможно ответ будет в значении этого поля. На моей практике, поле «command» для активных системных процессов всегда выдавало что-то вполне понятное: autoshrink/autogrow/checkpoint/logwriter/и т.п.

Как же это использовать

Перейдем к практической части. Я приведу несколько примеров использования, но не стоит ограничивать вашу фантазию. Возможности сервера этим не исчерпываются — можете придумывать что-то своё.

Пример 1: Какой процесс расходует cpu/reads/writes/memory

Для начала, посмотрим какие сессии больше всего потребляют, например, CPU. Информация в sys.dm_exec_sessions. Но данные по CPU (а также reads, writes) — накопительные. Т.е цифра в поле содержит «итого» за все время подключения. Понятно, что больше всего будет у того кто подключился месяц назад, да так и не отключался ни разу. Это вовсе не означает, что он прямо сейчас грузит систему.

Немного кода решает проблему, алгоритм примерно такой:

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

Те, кто не любят выполнять запрос в студии — могут его завернуть в приложение написанное на своём любимом языке программирования. Я покажу как это сделать в MS Excel без единой строки кода.

В меню «Данные» подключаемся к серверу. Если будет требовать выбрать таблицу — выбираем произвольную — потом поменяем это. Как всегда, жмем «Next» и «Finish» пока не увидим диалог «Импорт данных» — в нем нужно нажать «Свойства. ». В свойствах необходимо сменить «тип команды» на значение «SQL» и в поле «текст команды» вставить немного измененный наш запрос.

Запрос придется немного поменять:

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

Когда данные будут в Excel-е, можете их сортировать как вам нужно. Для актуализации информации — жмите «Обновить». В настройках книги, для удобства, можете поставить «автообновление» через заданный период времени и «обновление при открытии». Файл можете сохранить и передать коллегам. Таким образом, мы из ~~навоза и веточек~~ подручных средств собрали ~~ЫнтерпрайзМониторингТул~~ удобный и простой инструмент.

Пример 2: На что сессия расходует ресурсы

Итак, в предыдущем примере мы определили проблемные сессии. Теперь определим, что именно они делают. Используем sys.dm_exec_requests, а также функции получения текста и плана запроса.

Подставляйте в запрос номер сессии и выполняйте. После выполнения, на закладке «Results» будут планы (два: первый для всего запроса, второй для текущего шага — если шагов в запросе несколько), на закладке «Messages» — текст запроса. Для просмотра плана — необходимо кликнуть в строке на текст оформленный в виде url. План откроется в отдельной закладке. Иногда бывает что план открывается не в графическом виде, а в виде xml-текста. Это, скорее всего, связано с тем что версия студии ниже чем сервера. Попробуйте пересохранить полученный xml в файл с расширением sqlplan, предварительно удалив из первой строки упоминания «Version» и «Build», а затем отдельно открыть его. Если и это не помогает — напоминаю, что 2016 студия официально доступна бесплатно на сайте MS.

Очевидно, полученный план будет «оценочным», т.к. запрос еще выполняется. Но некоторую статистику по выполнению получить всё равно можно. Используем представление sys.dm_exec_query_stats с фильтром по нашим хэндлам.

Допишем в конец предыдущего запроса

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

Пример 3: А можно всех посмотреть

Давайте объединим рассмотренные системные представления и функции в одном запросе. Это может быть удобно для оценки ситуации в целом.

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

Примеры запросов выложил на гитхаб.

Заключение

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

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

В этой статье мы рассмотрим популярные инструменты, T-SQL запросы и скрипты для обнаружения и решения различных возможных проблем с производительностью SQL Server. Эта статья поможет вам разобраться, когда вашему SQL Server недостаточно ресурсов (памяти, CPU, IOPs дисков), найти блокировки, выявить медленные запросы. Посмотрим какие есть встроенные инструменты и бесплатные сторонние скрипты и утилиты для анализа состояния Microsoft SQL Server.

Инструменты для диагностики SQL Server

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

Обнаружение и решение проблем с производительностью SQL Server

Самой распространенной проблемой с которой сталкивается системный администратор, работающий с SQL Server, это жалобы пользователей на производительность запросов и самого сервера: “тормозит”, “долго выполняется запрос“, и так далее.

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

Анализ использования оперативной памяти SQL Server

Для начала нужно определить сколько памяти доступно SQL Server. Для этого запустите SSMS (SQL Server Management Studio), зайдите на сервер и зайдите в свойства сервера (ПКМ по названию сервера в Обозревателе объектов).

Сам по себе доступный объём RAM вам ничего не скажет. Нужно сравнить это число с используемой памятью в Диспетчере Задач и самим движком SQL Server с помощью DMV.

В Диспетчере задач, во вкладке Подробности, найдите sqlservr.exe и посмотрите сколько оперативной памяти использует этот процесс.

Если на сервере, например, 128 GB оперативной памяти, а процесс sqlservr.exe использует 60 GB и ограничений по RAM у SQL Server нет, то оперативной памяти вам хватает.
Если SQL Server использует 80-90% RAM от заданной или максимальной, то в таком случае нужно смотреть DMV. Имейте в виду, что sqlservr.exe не сможет использовать всю оперативную память. Если на сервере 128 GB, то sqlservr.exe может использовать только 80-90% (100-110 GB), так как остальная память резервируется для операционной системы.

Имейте в виду, что процесс SQL Server’a не отдаёт оперативную память обратно в систему. Например, ваш SQL Server обычно использует 20 GB памяти, но при месячном отчете он увеличивает потребление до 100 GB, и даже когда вычисление отчета закончится и сервер будет работать в прежнем режиме, процесс SQL Server’a всё равно будет использовать 100 GB до перезагрузки службы.

Даже если вы уверены, что оперативной памяти серверу хватает, не будет лишним точно знать объём потребляемой RAM.

Узнать реальное использование RAM можно с помощью Dynamic Management Views. DMV это административные вьюверы (представления). С помощью DMV можно диагностировать практически любую проблему в SQL Server.

Посмотрим sys.dm_os_sys_memory, для удобства используем запрос:

Рассмотрим каждый выводимый параметр:

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

Если Вам нужно убедиться, что серверу хватает RAM, вы можете смотреть только на поля system_low_memory_signal_state, system_high_memory_signal_state и system_memory_state_desc. Если system_low_memory_signal_state = 1, то серверу явно не хватает оперативной памяти.

Загрузка процессора в SQL Server

Нагрузку на процессор определить проще, так как это можно сделать в Диспетчере задач. Чтобы узнать текущую нагрузку на процессор, найдите в Диспетчере задач процесс sqlservr.exe

Если вы хотите узнать нагрузку за прошедшее время, можно воспользоваться запросом:

В результате мы получим поминутную статистику использования процессора.

Анализ нагрузки на диск SQL Server

Посмотрим на загрузку дисков в операционной системе. Для этого запустите resmon.exe.

Нам нужна вкладка Disk. В секции Disk Activity отображаются файлы, к которым идёт обращение, и их скорость read/write на текущий момент. Отфильтруйте эту секцию по Total (кликните на Total). На самом верху будут файлы, которые на данный момент максимально используют диск. В случае с SQL Server это может быть полезно чтобы определить какая база больше всего нагружает диск на текущий момент.

В секции Storage отображаются все диски в системе. В этой секции нам нужны 2 параметра – Active Time и Disk Queue. Active Time в процентах отображает нагрузку на диск, то есть если вы видите на диске C:\ Active Time равный 90, это значит что ресурс чтения/записи диска на текущий момент используется на 90%. Столбец Disk Queue отображает очередь обращений к диску, и если значение очереди не равно нулю, то диск загружен на 100% и не справляется с нагрузкой. Так же если Active Time близок к 100, то диск используется практически на пределе своих возможностей по скорости.

Просмотр блокировок в SQL Server

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

Блокировки можно посмотреть через Activity Monitor в SSMS, но мы воспользуемся T-SQL, так как этот вариант более удобен и нагляден. Выполняем запрос:

Этот запрос возвращает список блокировок в виде дерева. Это удобно в работе, так как обычно, если возникает одна блокировка, она провоцирует за собой другие. Аналогично в Activity Monitor или в выводе sp_who2 можно увидеть поле “Blocked By”.

Если запрос ничего не вернул, то блокировок нет.

Если запрос вернул какие-то данные, то нужно проанализировать цепочку.

HEAD значит что этот запрос является причиной всех остальных блокировок ниже по дереву. 64 – это идентификатор процесса (SPID). После этого пишется тело запроса, который вызвал блокировку. Если у вас хватает ресурсов сервера, то скорее всего дело в самом запросе и во взаимном обращении к каким-то объектам. Для того чтобы сказать точнее, нужно анализировать конкретный запрос, который вызвал блокировку.

Политики SQL Server

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

Политика в SQL Server это, грубо говоря, проверка правила на соответствие заданному значению. Например, с помощью политик вы можете убедиться, что на всех базах на сервере выключен Auto Shrink. Рассмотрим пример импорта и выполнения политики

В SSMS, подключитесь к серверу, на котором хотите выполнять политики (Management -> раздел Policy Management).

Импортируем файл Database Auto Shrink.xml. Жмём Evaluate

На экземпляре node1 две базы данных, test1 и test2. На test2 включен autoshrink. Посмотрим детали.

Политика определила включенный параметр AutoShrink, в описании обычно пишется объяснения к правилам. В данном случае дается объяснение почему auto shrink лучше отключать.

Политики могут выполняться либо по расписанию, либо по требованию (разово). Результаты выполнения политики можно посмотреть в журнале политик.

При установке SQL Server нужно выбирать только используемые компоненты СУБД, и указывать настройки в соответствии с конфигурацией “железа” вашего сервера. Всегда следите чтобы серверу хватало ресурсов, и чтобы на сервере не было блокировок

Самым мощным инструментом для диагностики SQL Server является T-SQL и DMV. Так же рекомендуется построить круглосуточный мониторинг над SQL Server и над обслуживающей его инфраструктурой для обнаружения всех возможных проблем.

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

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

операции чтение страниц с файла данных для загрузки их в Буферный пул;
операции записи данных в файл журнала транзакций при завершении (Commit) транзакций;
операции записи страниц в файл данных для фиксации произведенных модификаций.

Несмотря на то, что SQLServer: Buffer Manager: %Buffer Cache Hit Ratio может составляет 98…99%%, однако существует вероятность, что необходимая вам для выполнения запроса строка окажется на диске, и потребуется две операции ввода – физическая (время выполнения которой может быть до десятков миллисекунд), и логическая (с временем выполнения несколько десятков нано…микросекунд). Как видно разница между микро и мили 1 000 раз, а нано и мили 1 000 000 раз. Отсюда становиться ясно, что даже при огромных размерах страничного кэша (Buffer Pool), достаточно одной строчки, чтобы испортить радужную картину. Конечно не все так ужасно, как я описал, поскольку существует и работает множество алгоритмов для минимизации данной проблемы, но реально, все же, это возможно и такие задержки действительно возникают.

Примечание: Вообще, строго говоря, счетчик SQLServer: Buffer Manager: %Buffer Cache Hit Ratio не может использоваться как индикатор недосттка памяти и показатель достаточности размера буферного пула, поскольку на выполнение операции ReadAhead данный счетчик никак не реагирует, хотя при этом считывается огромные объемы данных. Связано такое поведение с тем, что данный счётчик учитывает только чисто физические операции чтения выполнение которых инициировано процессором запросов для загрузки страниц необходимых для выполнения данного запроса, и он никак не учитывает операции ReadAhead, которые по объему загружаемых с диска данных могут в разы превышать эти операции.

У нас (MSFT) есть пороговые значение на латентность операции чтения с дисков для SQL Server. Эта латентность меряется счетчиком Physical Disk: Avg. Disk sec/Read и не должна превышать 15 мсек. Если вы видите время задержки более этой величины и продолжительное по времени, то это может обозначать наличие дисковой проблемы.

Примечание: Далее рекомендуется посмотреть какое количество операций физического чтения делает ваш SQL Server и присутствуют ли необходимые индексы. Поскольку построив необходимые индексы вы уменьшаете количество физических чтений и, тем самым, снижаете нагрузку на дисковую систему, и, как следствие, уменьшаете латентность дисковых операций. Показания счетчика BufferManager:Page Reads/sec, должны быть не более 90…100, а соотношение SQLServer:Access Method: Index Searches/sec и SQLServer:Access Method: Full Scans/sec должно быть около или более 1000.

SQL Server подобно другим современным СУБД использует механизм упреждающей записи при модификации данных. Суть механизма состоит в том, что модифицированная запись не может появиться в файле данных, до тех пор, пока модификации не будут отображены в журнале транзакций. Особенно критично это для операции Commit, которая является сигналом для СУБД, что данная модификация выполнена успешно и доведена до конца. По сути латентность выполнения транзакций напрямую зависит от задержек связанных с появлением Commit записи в журнале транзакций. Запись в журнал транзакций производится через специальный внутренний буфер (Log Buffer), размер которого управляется SQL Server-ом. Временные задержки, возникающие при этом можно оценить с помощью счетчика SQL Server:Databases: Log Flush Wait Time и SQL Server:Databases: Log Flush Waits/sec. Разделив SQL Server:Databases: Log Flush Wait Time наSQL Server:Databases: Log Flush Waits/sec, мы получим среднее время ожидания при записи данных в журнал транзакций в миллисекундах.

У нас (MSFT) есть пороговые значение на латентность операции записи на диск для SQL Server. Эта латентность меряется счетчиком Physical Disk: Avg. Disk sec/Write и не должна превышать 15 мсек. Реально она должна быть менее этой величины (поскольку операция записи подтверждается при попадании данных в кэш записи контроллера диска) и составлять 3…4 мсек (для SAN) и 8…10 мсек (для локально присоединенных дисков). Если вы видите время задержки более этой величины и продолжительное по времени, то это может обозначать наличие дисковой проблемы.

Выполнение данного вида записи, как правило, не приводит к проблеме, поскольку эти операции производятся специальными Redo thread-ами в асинхронном режиме. К проблемам может стать ситуации, когда:

производилась перестройка индексов и Redo thread-ы не успевают достаточно быстро записать модифицированные данные в файлы данных, отчего объекты с индексами не доступны.
Redo thread на зеркальной (AlwaysOn) базе не успевает записать данные в файлы данных, отчего зеркало (AlwaysOn) не переходит в работоспособное состояние.
Redo thread при восстановлении базы из резервной копии не успевает быстро записать данные в файлы данных, отчего база не переходит в работоспособное состояние.

Проблема с перестройкой индексов может быть решена путем выполнения Online Index Rebuilding.

Скорость работы REDO-thread зависит, в основном, от скорости работы дисковой подсистемы. Вы можете рассчитать какова текущая разница между данными, хранящимися в файле первично и вторичной реплик. Для этого вам необходимо Database Replica: Recovery Queue разделить на Database Replica: Redone Bytes/sec .

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

Сначала посмотрим на основные счетчики системы и начнем мы с дисков, поскольку алгоритм Log Shipping устроен таким образом, что при его работе активно используются именно диски.

Обратите внимание, что в некоторые моменты времени латентность дисковых операций составляет 91 секунду, что 6 000 раз более максимально допустимого значения для SQL Server (15 мсек).

Необходимо понять, что является причиной таких огромных задержек дисковых операций. Для этого попробуем проанализировать отдельно счетчики Avg. Disk sec/Read и Avg. Disk sec/Write. Как мы знаем латентность операций записи должна быть в несколько раз меньше латентности операции чтения, поскольку подтверждение операции записи приходит с кэш-контроллера, а чтения после переноса данных с физических носителей в буферы сервера.

Однако это не так, как видно из рисунка оба вида задержек почти однопорядковые. Отсюда мы можем сделать вывод, что перегружена вся дисковая система.
В данном случае мы имеем дело с локально присоединенными дисками (Local Attached Storage), в качестве которой используется PowerVault MD3000 Direct Attached Storage. Стойка состоит из 20-ти (10 000 rpm) дисков объединенных в RAID 10. Т.е. количество информационных дисков в данном случае 10. Исходя из расчетной скорости передачи одного диска: MAX 10 000/60 = 166 IOPS (при полностью последовательном чтении) и MIN 10 000/60/2 = 83 IOPS.(при полностью произвольном чтении) получаем среднюю скорость передачи таких дисков около 110…120 IOPS. Отсюда получаем производительность стойки из 10 дисков равную MAX 10х166 = 1660 IOPS и MIN 10х83 = 830 IOPS.
Давайте же посмотрим какое количество IOPS отправляется на стойку.

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

И это действительно так. Как видно из рисунка ниже очередь действительно есть.

В среднем она составляет 111, максимум 255. Очередь 111 превышает норму для локально присоединенных дисков в 5 раз (норма составляет не более чем 2 умножить на количество информационных дисков, т.е. в данном случае очередь должна быть не более 20). Очередь же 255 указывает на полное блокирование контроллера дисковой стойки, и о его не способности обрабатывать такой поток данных. Очередь 255 говорит о том, что дисковый стек Windows полностью загружен IRP пакетами и они более не передаются на контроллер, поступление новых пакетов от приложения отвергается ОС.

Примечание: Анализ дисковых систем на основе очередей применим лишь для локально присоединенных систем и не может применяться для SAN (Storage Area Network), поскольку там не возможно определить количество дисков задействованных в операциях ввода/вывода.

Возникает справедливый вопрос: Неужели при таких задержках нет никаких записей в журналах? Да они есть.

Это строки из журнала Application

BackupDiskFile::CreateMedia: Backup device ‘E:\backup_tmp\TransactionLogShipping\Приложение_службы_поиска_DB\Приложение_службы_поиска_DB_36993c336ce14e99a678fd3a52f4dd3f_20140305014550.trn’ failed to create. Operating system error 1117(failed to retrieve text for this error. Reason: 15105).

Ошибка 1117 означает “Запрос не может быть выполнен из-за ошибки ввода/вывода.” Данная ошибка являются индикатором того, что существует проблема с дисками.

Отсюда рекомендации заказчику: “Непременно и срочно заняться своей дисковой подсистемой”.

Давайте выполним еще одно упражнение. Попробуем понять какой из процессов ОС “съедает” все ресурсы. Для этого мы воспользуемся счетчиком

Как показал анализ счетчика Process(*):IO Data Bytes/sec наибольшее количество операций ввода/вывода порождает SQL Server. Более того, его активность по времени точно совпадает с дисковыми проблемами. Следовательно, можно предположить, что именно этот процесс и является основной причиной дисковых перегрузок.

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

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

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)

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

Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»:

Рис.1. Средняя длина очереди к диску для чтения и записи

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

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

Нагрузка на диски обусловлена быстрым вытеснением данных из кеша SQL Server.

Рис.2. Демонстрация вытеснения данных из кеша SQL Server

На рисунке 2 показаны 3 условных этапа различной нагрузки на диск. На этапе 1 и этапе 3 – очереди к диску были минимальны. Почему же на этапе 2 очередь резко возросла и это привело к появлению проблем производительности у пользователей? Ответ на этот вопрос легко найти на втором графике рисунка 2: «Ожидаемый срок жизни страницы памяти», который показывает предполагаемое время нахождения страницы данных в кеше SQL Server. Между двумя этапами видим резкое понижение этого графика со значения 3000 до 200. С точки зрения логики работы SQL Server это означает, что данные будут находится к кеше не 3000 секунд как раньше, а 200 секунд, следовательно, если пользователь запросит данные через 300 секунд, то SQL Server с почти 100% вероятностью не найдет их в оперативной памяти (кеше) и придется выполнять операцию чтения с диска. Этими операциями обеспечивается рост очереди к диску. В течение всего этапа 2 кеш «прогревался» (заполнялся данными) и на этапе 3 нагрузка на диск упала.

Мы определили вид проблемы, теперь рассмотрим варианты решения.

Что надо сделать:

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

- Возможно проблема в качестве обслуживания статистик и индексов MS SQL Server.

Что не надо делать:

- Не надо покупать новые диски (дисковые массивы), это не решает проблему, а скорее ее усугубляет.

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

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

2. Нагрузка на диски, обусловленная свопированием памяти на диски вследствие нехватки свободной памяти.

Рис.1. Практический пример повышенной нагрузки на диск

На рисунке 1 показана практическая ситуация на сервере БД SQL Server у клиента в течение 1,5 часов. Как видно по счетчику «Средней длины очереди к диску» диск нагружен и не справляется с количеством обращений к нему.

На рисунке также показаны два других показателя: «Нагрузка CPU», «Свободная оперативная память» для поиска причин торможения диска. Условно делим ситуацию на два этапа: первый этап – очередь к диску практически равна 0 и пользователи работают в обычном режиме, и второй этап – в течение которого очередь к диску поднимается до максимальных значений (342) и пользователи не могут качественно работать. Чем же обусловлена такая нагрузка на диск?

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

Показатель «Свободная оперативная память» как раз показывает доступность реальной оперативной памяти для других процессов, а, следовательно, чем его значение больше, тем меньше вероятность свопирования. На рисунке 1 значение свободной оперативной памяти на сервере баз данных постоянно уменьшается до 500 Мб, далее до 200 Мб, это в свою очередь и привело к нагрузке на диск (на этапе 2).

Встает вопрос – а зачем на рисунке 1 мы показали счетчик «Нагрузка CPU»? Все просто, на этапе 1 средняя загрузка CPU была около 50%, на этапе 2 – 40%, при этом в системе работало аналогичное количество пользователей. Такое уменьшение значения говорит о том, что процессор недозагружен и узкое место в производительности сместилось в сторону диска (он не справляется).

Для исправления этой ситуации достаточно правильно распределить потребление оперативной памяти и не допустить уменьшение ее объема до 500Мб (как рекомендация). Неправильным вариантом решения была бы покупка более производительного физического диска или хранилища.

3. Нагрузка на диски, обусловленная внутренними механизмами работы SQL Server.

Рис. 2. Периодическая нагрузка на диск

Как видно из рисунка 2, периодически очередь к диску увеличивается, причем эти «скачки» происходят через одинаковые временные интервалы. Это может говорить о том, что есть периодически повторяемые регламентные операции.

Из нашего опыта это могут быть следующие операции:

- Увеличение размера файлов данных и лога транзакций (особенно если указан фиксированный размер прироста).

- Резервная копия файла данных или журнала транзакций.

Сбор и анализ данных осуществлялся с использованием мониторинга производительности PerfExpert.

Microsoft SQL Server располагает системными объектами Dynamic Management Objects, которые предоставляют исчерпывающие метаданные, относящиеся к характеристикам производительности экземпляров SQL Server. В частности, использование одного из них, sys.dm_io_virtual_file_stats, позволяет специалисту в области данных оценить влияние ввода-вывода хранилища данных, структуры файлов и даже плохо спроектированных запросов и гиперактивных конечных пользователей. В предлагаемой статье мы рассмотрим, как определить самые высокие задержки чтения и записи данных и файлов журналов соответственно.

Вечные проблемы

В 2010 году я написал книгу о динамических объектах управления в соавторстве с Луисом Дэвидсоном, обладателем сертификата SQL Server MVP. Теперь, по прошествии нескольких лет, я еще выше ценю возможности, которые открывают эти конструкции как перед администраторами баз данных, так и перед разработчиками при идентификации проблем с производительностью. Данная статья стала результатом споров с аналитиком приложений относительно причин низкой производительности приложений, в которой обычно винят «медленное хранилище данных».

Экран 1. Показатели ввода-вывода утилиты PAL

Этот пример для меня не нов. В течение длительного времени данная проблема докучала нашей группе администраторов баз данных. Мы уже выполнили миграцию с хранилища VNX «уровня 2» на платформу хранения данных «уровня 1» (HP 9500 SAN.) Мне также было известно, что приложение CA Service Desk имеет существенные изъяны, и в дополнение к ним два человека в нашей компании направляли в базу данных чрезвычайно неэффективные запросы и никак не реагировали на рекомендации по настройке производительности от администраторов баз данных. Среда созрела для повышения производительности как внутренних, так и подготовленных внешним поставщиком запросов, но аналитики хотели только одного: чтобы инженеры, отвечающие за инфраструктуру (сервер, хранилище данных, администраторы баз данных), добавляли оборудование, вместо того чтобы внедрять оптимальные стандарты проектирования и кодирования.

Медлительность целиком объяснялась тем обстоятельством, что в отчетах PAL задержка ввода-вывода для чтения и записи измеряется миллисекундами. Другими словами, реальные задержки ввода-вывода приемлемы. Но поскольку PAL выделила эти элементы как проблемные, не было смысла обращаться к аналитикам. Я воспользовался своим любимым средством, динамическими объектами управления, чтобы решить задачу с учетом фактов и метаданных, применяя знание математики и личный опыт.

Далее в статье мы рассмотрим способы определения нагрузки ввода-вывода на нескольких экземплярах в средах SQL Server и сопоставления результатов с задержкой ввода-вывода, что позволит сделать заключение о влиянии соотношения задержки и нагрузки на производительность ввода-вывода. Это поможет заняться плохо настроенными запросами, вместо того чтобы терять драгоценное время в погоне за призраками на уровне хранилища. Однако мы будем не спеша приближаться к цели и сначала посмотрим, как измерить нагрузку и задержку ввода-вывода на уровне экземпляра и как использовать некоторые широко распространенные инструменты анализа и подготовки отчетов для представления данных администраторам баз данных и аналитикам приложений. Надеюсь, с помощью этой статьи вы освоите применение различных динамических объектов управления, а также рассматриваемых здесь средств Excel, Tableau и Microsoft Power Tools и статистических функций mean, median и mode.

Базовая функция динамического управления

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

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

Возвращенные столбцы содержат сведения, относящиеся к файлу и базе данных (database_id и file_id), количество времени в образце в миллисекундах (sample_ms), а затем показатели ввода-вывода для операций обоих типов, количество прочитанных байтов и замедление ввода-вывода в миллисекундах как для чтения, так и для записи (см. экран 2). Динамическое административное представление (DMV) также предоставляет общее замедление ввода-вывода для операций чтения и записи, текущий размер файла и file_handle, уникальный идентификатор для файла в экземпляре SQL. Обратите внимание, что sample_ms в этих выходных данных является отрицательным числом. Это значение нельзя использовать как показатель непрерывной работы, так как оно возвращается к исходному после достижения предела для этого типа данных.

Экран 2. Сведения о показателях ввода-вывода

В процессе подготовки этого материала я заинтересовался идентификаторами файлов database_id и file_id, а также столбцами нагрузки ввода-вывода, в частности num_of_reads, io_stall_read_ms, num_of_writes и io_stall_write_ms. С помощью этих столбцов можно определить среднюю задержку ввода-вывода для операций чтения и записи и назначить ее любому файлу в любой базе данных.

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

Сохранение метаданных DMO на диске

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

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

Я немного расширил коллекцию, включив вычисленные столбцы для расчета дневных значений ввода-вывода, а также изменения единиц измерения от байтов. Я добавил эти столбцы, поскольку в дальнейшем планировал расширить оценку ввода-вывода и при сохранении данных от наших 150+ серверов SQL Server -всего лишь за 90 дней не беспокоился о пространстве на диске.

Где были созданы эти таблицы? Вы можете сделать это локально на каждом SQL Server, но я покажу, как собрать информацию для всех баз данных вашего домена для сравнения в масштабах среды. Если вы хотите отыскать шумные серверы и базы данных — скрипучие колеса, сделать это можно следующим образом. У меня есть центральная база данных, именуемая «спасательной лодкой» (lifeboat), и это действительно спасательная лодка в случаях, когда требуется быстро найти ответы на разнообразные вопросы.

Теперь, имея целевые таблицы, настало время представить сценарий сбора, который будет получать данные из sys.dm_io_virtual_file_stats (и некоторых других системных объектов) для заполнения этой базовой таблицы ежедневного сбора данных (листинг 2).

Заметьте, что я расширил некоторые описательные столбцы для базы данных и файла присоединением к системному представлению sys.master_files. Я также рассчитал количество дней, в течение которых ведется сбор данных из DMF с помощью create_date для tempdb. В конечном итоге мы объединим данные из этого экземпляра SQL со всеми остальными данными в нашей среде, которые будут иметь разное время работы, поэтому единственный способ корректного сравнения — на дневной основе.

В завершение теории: простой запрос

Мы углубимся в аналитику чуть позже. А пока попытаемся ответить на три вопроса:

Какие пять файлов данных имеют самую высокую среднюю задержку чтения при вводе-выводе?
Какие пять файлов журналов имеют самую высокую среднюю задержку записи при вводе-выводе?
К какому типу по уровню задержки ввода-вывода относится база данных, которая стала первопричиной всей этой работы?

Пять файлов данных с самой высокой средней задержкой чтения при вводе-выводе определяются с помощью кода листинга 3.

Неудивительно, что три файла с худшими показателями задержки принадлежат к нашей реализации Sharepoint (экран 3). Однако 5 секунд для каждого — это неожиданно и требует разъяснения. Вы заметите, что и здесь я включил ежедневную нагрузку чтения. Высокая задержка для файла с низкой активностью не повод для беспокойства. Однако именно благодаря активности эта проблема оказалась в поле моего зрения.

Экран 3. Три файла с худшими показателями задержки

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

В этом случае становится ясно, почему я включил ежедневную активность записи. Файлы с худшими показателями в данном случае имеют очень низкую активность записи. Я скрыл сервер и имя базы данных, но неизлечимо больной — наш устаревший сервер SharePoint (опять SharePoint!), как показано на экране 4.

Экран 4. Файлы с худшими показателями записи

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

Наконец, посмотрим на базу данных, которая послужила отправной точкой этой статьи. Для собственной защиты я параметризовал имя базы данных и сервер (что позволяет задействовать этот сценарий как шаблон без переработки с вашей стороны). Я ранжирую нагрузку ввода-вывода и задержку ввода-вывода от лучшего результата к худшему. Как в гольфе, в данном случае низкий показатель лучше (листинг 5).

Показатели файлов данных и ввода-вывода при чтении этих файлов находятся в пределах приемлемой задержки ввода-вывода (экран 5). Интересно, что основной файл данных находится в верхней десятке самых активных по ежедневной нагрузке чтения при вводе-выводе и при этом имеет превосходную среднюю задержку чтения 21 мс. Тем не менее мой оппонент, аналитик, придерживался иного мнения и не воспринимал моих утверждений о том, что корень проблемы — в плохих запросах, приводящих к большому числу лишних операций чтения. Аналогичная картина с операциями записи (листинг 6).

Экран 5. Приемлемые задержки при чтении файлов

И вновь ежедневная нагрузка ввода-вывода довольно высока (седьмой по величине дневной показатель записи при вводе-выводе), и все же задержка составляет в среднем 1 мс для операции записи — в пределах допусков, установленных компанией Microsoft (экран 6).

Экран 6. Приемлемые задержки при записи файлов

Итак, на данном этапе мы выяснили, как получать метрики ввода-вывода из функции динамического управления sys.dm_io_virtual_file_stats и как централизованно разместить данные по всем экземплярам через добавление кода или с помощью вашего привычного метода. В заключение мы определили элементы с худшими показателями, а также показали ошибочность мнения всезнающего аналитика.

Мы не рассматривали распределение данных и использование статистического анализа, чтобы отбросить отклоняющиеся значения (например, результаты 5 секунд и экземпляры с низким объемом ввода-вывода), а также способы применения функций mean, median и mode. Об этом пойдет речь в следующих статьях.

Читайте также: