Средства мониторинга за вашими экземплярами и базами данных SQL Server

Мониторинг экземпляров 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 включены.

  1. В Object Explorer среды SQL Server Management Studio раскройте папку Management.
  2. В контекстном меню Data Collection выберите Configure Management Data Warehouse.
  3. Укажите Set up data collection.

  4. Нажмите далее (Next).

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

  6. Нажмите 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, нет возможности просматривать графики в реальном времени, но собранная информация может храниться на протяжении нескольких дней. В базовом наборе представлены только основные показатели, а для расширения необходимы знания средств разработки.

ApexSQL Monitor

ApexSQL Monitor – это система мониторинга, которая позволяет отслеживать работу вашей системы, экземпляров SQL Server, производительность баз данных в реальном времени с помощью более чем 30 счётчиков. Вы можете указать конкретный экземпляр SQL Server, выбрать нужную вам базу данных, которую вы хотите отслеживать, выбрать необходимые вам счётчики, задать для них пороговые значения при достижении которых будет подниматься авария. ApexSQL Monitor позволяет осуществлять мониторинг в реальном времени, просматривать отчёты и сохранять информацию в центральной БД, для последующего анализа.

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

Вкладка Overview показывает наиболее важную информацию по SQL Server: статистику ожидания и показатели производительности (buffer cache hit ratio, full scans per second, lazy writes per second, и lock requests per second).

Overview tab - image 1

Overview tab - image 2

Вкладка System performance показывает основные счётчики производительности операционной системы: processor queue length/utilization of processor time (длина очереди ЦПУ, утилизация и время), network utilization (утилизация сети), paging file usage (использования файла подкачки), page input per second, pages per second, average bytes per write/read (активность записи/чтения), average and current disk queue length (очереди к диску), average read/write time (время чтения/записи), disk writes/reads per second (активность диска в секунду), свободная и общедоступная память в MB.

System performance tab

Вкладка SQL Server performance показывает основные счётчики, связанные с экземпляром SQL Server: the number of batch requests (количество запросов), compilations и re-compilations (компиляция и перекомпиляция в секунду), longest running transaction (длительные транзакции), full scans (полное сканирование), index searches (поиск по индексу), forwarded records (количество записей в секунду, считываемых через указатели переадресуемых записей), free list stalls (ожидания освобождения страниц), lazy writes, lock requests (блокировки), и deadlocks (мёртвые блокировки в секунду), user connections (пользовательские подключения), target и total server memory (доступно и всего памяти в Кб), memory grants pending (очередь предоставления памяти), buffer cache hit ratio (буферный кэш), page life expectancy (время жизни страницы), page reads/writes (чтение и запись страниц) и page splits (расщепление страниц в секунду).

SQL Server performance tab

Database performance показывает информацию по базе данных, размер файлов данных и журнала транзакций и количество транзакций в секунду.

Database Performance tab

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

Query waits tab

Вкладка Query performance показывает самые популярные запросы, которые использовали наибольшее количество ресурсов в течение выбранного периода времени, в том числе графическое представление самых медленных запросов по среднему процессорному времени, среднему времени выполнения, и ввода/вывода в секунду. Количество запросов может быть выбрано пользователем.

Query performancetab

Вкладку Custom tabs можно настроить под свои нужды. Пользовательские вкладки можно легко настраивать под себя, использую необходимые счётчики системы, SQL Server и БД, располагая их в удобной для вас последовательности. Что позволит вам оперативнее диагностировать падение производительности.

Как работать с ApexSQL Monitor

  1. Запустите ApexSQL Monitor
  2. Нажмите кнопку Add, чтобы добавить экземпляр SQL Server для мониторинга. Все экземпляры мониторинга будут доступны в панели слева.
  3. Для настройки счётчиков производительности выберите в меню Metrics. Счётчики могут быть уровня устройства, БД, экземпляра или машины.

    Metrics tab

  4. На этом же шаге можно настроить уведомления. Достаточно выбрать Alerting и указать пороговые значения либо используйте Baseline threshold, если пороговые значения неизвестны, и вы их хотите рассчитать.

  5. После выбора все метрик и настройки уведомлений, нажмите Apply.

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

Alerts information

ApexSQL Monitor – это лёгкий в настройках и очень функциональный инструмент мониторинга. Для его настройки вам не потребуется написать ни строчки кода. В отличии от родных инструментов мониторинга (Activity Monitor и Data Collector), ApexSQL Monitor позволяет получить более детальную информацию. Позволяет самим выбрать нужные счётчики производительности и указать нужные БД для отслеживания. Доступные метрики позволяют отслеживать работоспособность БД, экземпляра и ОС. Благодаря этому, уменьшаются накладные расходы в процессе мониторинга.

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

Полезные ресурсы:
Activity Monitor
Data Collection
Monitoring SQL Server Performance

Переводчик: Алексей Князев

November 20, 2015