Мониторинг экземпляров SQL Server и баз данных позволяет получить информацию, необходимую для диагностики и устранения неполадок производительности SQL Server, а также для тонкой настройки SQL Server. Оптимальную производительность не легко определить и установить, так как, существует компромисс между несколькими программными и аппаратными факторами. Мониторинг также зависит от вашей среды, бизнес-требований и политики компании.
Медленно работающий отчёт в небольшой компании может быть допустим, но на крупном предприятии любое падение производительности в системе отчётности может затронуть большое количество пользователей, тем самым повлиять на бизнес. Подобные проблемы, как правило, неприемлемы и должен быть устранены, как можно быстрее.
После того, как производительность SQL Server настроена, её необходимо постоянно контролировать, отслеживать различные изменения, а иногда и требует ручного вмешательства. Наиболее распространенным примером является устаревшая статистика – запрос работает хорошо, а затем, без каких-либо очевидных причин, производительность резко падает.
Для нормального функционирования SQL Server, администратор баз данных должен постоянно следить за производительностью, иметь набор метрик, которые оперативно могут сообщить о деградации в работе системы. Вовремя получать уведомления, когда текущая нагрузка на сервер выходит за рамки базовых показателей системы, и принять адекватные меры.
Какие счётчики SQL Server необходимо отслеживать?
Любые метрики, которые вам необходимо отслеживать, зависят в первую очередь от ваших целей. Тем не менее, есть целый ряд показателей, которых достаточно для выполнения базовой диагностики. На основании их значений можно получить первые сигналы и уже с помощью дополнительных показателей найти причину падения производительности.
These commonly monitored SQL Server performance metrics are memory and processor usage, network traffic, and disk activity.
Среди основных показателей эффективности SQL Server необходимо контролировать память, процессор, сетевой трафик и активность дисковой подсистемы. Кроме того, параметры SQL Server и операционной системы Windows.
Основные счётчики: processor time (процессорное время), processor queue length (длина очереди), page reads and writes per second (чтение и запись страниц в секунду), page life expectancy (время жизни страницы), target and total server memory (память), buffer cache hit ratio (буферный кэш), batch requests (число команд), processor utilization (утилизация процессора), lazy writes (сбрасывание страниц на диск), network usage (сеть), paging (подкачка), user connections (подключения) и т.д.
В SQL Server есть два встроенных инструмента мониторинга: Activity Monitor и Data Collector.
Activity Monitor
Activity Monitor отслеживает наиболее важные показатели эффективности SQL Server. Чтобы получить их, он выполняет запросы к экземпляру SQL Server каждые 10 секунд. Мониторинг осуществляется только когда инструмент открыт, поэтому побочный эффект от его использования минимальный.
Все метрики показаны на 5 разных панелях: Overview (Обзор), Processes (Процессы), Resource Waits (Ожидания ресурсов), Data File I/O (Ввод/вывод файлов данных), и Recent Expensive Queries (последние затратные запросы).
Overview (Общие сведения). Содержит графики Processor Time (Процессорное время), Number of Waiting Tasks (Количество ожидающих задач), Database I/O (Ввод-вывод в базе данных) и Number of Batch Requests/second (Количество пакетных запросов в секунду).
Processes (Процессы). Показывает все работающие в данный момент процессы во всех БД экземпляра SQL Server. Выводится информация по следующим полям: Логин, название приложения и хост, состояние задач и команд, время ожидания и т.д. Информацию можно отфильтровать по любому из полей таблицы.
Кроме того, есть удобная возможность для администраторов в контекстном меню вызвать SQL Server Profiler и выполнить более детальный анализ любого процесса.
Resource Waits (Ожидание ресурсов). Показывает ожидания для различных ресурсов: памяти, компиляции, сети и т.д.
Можно увидеть время ожидания (сколько времени задача SQL Server ожидала ресурсов от системы), последнее, общее и среднее.
Data File I/O (Ввод-вывод в файлах данных). Выводит информацию о всех файлах БД: MDF, NDF и LDF. Их название, полный путь, активность по чтению и записи и время отклика.
Recent Expensive Queries (Последние ресурсоемкие запросы). Показывает запросы, выполненные в последние 30 секунд, которые используются наибольшее количество аппаратных ресурсов: процессора, памяти, диска и сети. Контекстное меню позволяет открыть запрос в закладке запроса SQL Server Management Studio и просомтреть его план выполнения.
Как работать с Activity Monitor
Activity Monitor можно открыть в SQL Server Management Studio toolbar используя иконку Activity Monitor на панели, сочетанием клавиш Ctrl+Alt+A или через контекстное меню в Object Explorer.
Как уже было сказано выше, Activity Monitor отслеживает только заранее определенный набор наиболее важных показателей производительности SQL Server. Дополнительных параметров указать нельзя, нельзя и удалить что-то из показателей. Мониторинг возможен только в режиме реального времени. Нет возможности сохранить результаты мониторинга для последующего анализа. Таким образом Activity Monitor – это полезный инструмент для беглого анализа и поиска неисправностей, но он не подходит для детального сбора информации, т.к. в нём отсутствует возможность гибкой настройки счётчиков производительности, указания пороговых значений и нет возможности сбора исторических данных.
Data Collector
Data Collector – это ещё один встроенный в SQL Server Management Studio инструмент мониторинга. Он собирает метрики производительности экземпляров SQL Server и сохраняет их в локальный репозиторий, так что они могут быть использованы для последующего анализа. Он использует хранилище данных, SQL Server Agent, и Integration Services.
В отличии от Activity Monitor, Data Collector позволяет задать метрики, которые вы будите отслеживать. Кроме того, есть три встроенных коллекции счётчиков с самыми часто используемыми при анализе производительности. Расширить или создать свои коллекции можно, как на T-SQL, так и с помощью встроенного API.
Как работать с Data Collector
Убедитесь, что SQL Server Integration Services установлен, а SQL Server Agent, Management Data Warehouse и Data Collection включены.
- В Object Explorer среды SQL Server Management Studio раскройте папку Management.
- В контекстном меню Data Collection выберите Configure Management Data Warehouse.
-
Укажите Set up data collection.
-
Нажмите далее (Next).
-
Выберите имя экземпляра SQL Server и базу данных, где будет размещаться хранилище данных управления, и локальную папку, где будет храниться кэш собранных данных.
-
Нажмите Next, проверьте все параметры и затем Finish.
Data Collection имеет три предустановленных набора мониторинга в папке System Data Collection Sets (Object Explorer -> Management -> Data Collection): Disk Usage, Query Statistics и Server Activity. Кроме того, они имеют встроенные отчёты.
Набор Disk Usage показывает информацию по файлам данных (MDF и NDF) и файлам лога транзакций (LDF). Статистику ввода/вывода.
В контекстном меню Data Collection имеется отчёт Disk Usage built-in, который показывает размер файлов, их прирост, в том числе и ежедневный.
Набор Query Statistics показывает статистику, активность и планы 10 самых «тяжёлых» запросов.
Набор Server Activity показывает общую нагрузку на процессор, память, сеть и дисковую подсистему. В отчётах можно увидеть активность экземпляра SQL Server и операционной системы, ЦПУ, память, сеть, ввод\вывод.
Data Collection мощный инструмент, который необходимо сконфигурировать, прежде чем начать использовать. Он имеет три встроенных набора для мониторинга и адекватные отчёты. К сожалению, нет мастера для настройки своих показателей мониторинга и это необходимо делать с помощью кода.
Ещё одним преимуществом инструмента является то, что он не нагружает систему постоянно, а сбор данных осуществляет по указанному расписанию. В качестве недостатка стоит отменить отсутствие фильтра по БД. К сожалению, статистика собирается сразу со всех баз данных, а это лишняя информация, дополнительная нагрузка на сервер и потребность в дополнительном дисковом пространстве.
Функционал не поддерживается в версиях SQL Server ниже 2008-ого. И присутствует только в редакциях Enterprise, Standard, Business Intelligence, и Web.
В отличие от Activity Monitor, нет возможности просматривать графики в реальном времени, но собранная информация может храниться на протяжении нескольких дней. В базовом наборе представлены только основные показатели, а для расширения необходимы знания средств разработки.
Полезные ресурсы:
Activity Monitor
Data Collection
Monitoring SQL Server Performance
Переводчик: Алексей Князев
November 20, 2015
