26.12.2006 Кевин Клайн

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

Больше никаких авралов - просто систематическое наблюдение

Но разве это единственное, что можно сделать? Существует возможность проводить упреждающий мониторинг производительности, простую процедуру управления, которая использует определение базовых параметров работы системы, получение эталонов и непрерывное наблюдение. В этой статье я расскажу о том, как применять упреждающий мониторинг и как создать бесплатную контрольную систему с использованием Windows System Monitor.

Упреждающий мониторинг

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

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

Базовые параметры, эталон, монитор

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

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

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

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

Шаг 1: Определить базовые параметры производительности.

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

Для того чтобы наглядно показать качество функционирования, лучшие базовые параметры используют немного графиков (в идеале один), чтобы с первого взгляда можно было увидеть, как работает сервер. Когда будут определены базовые параметры, нужно сделать следующее. Во-первых, выберите вариант для сохранения данных по производительности в системном журнале или их отображения в реальном времени. Идеально иметь обе возможности: журналы регистрации позволяют вернуться к показаниям в любой момент времени, чтобы проанализировать, какой была производительность, когда непосредственное наблюдение за системой не велось. Мониторинг в реальном времени не занимает рабочее пространство на диске и ресурсы сервера, но требует уделить системе 100 процентов внимания. Во-вторых, нужно определить интервал, через который будет вестись наблюдение, учитывая затраты в производительности для сбора данных и операции ввода-вывода данных и оценить затраты на требуемое пространство. Чем больше интервал, тем выше вероятность, что интересующие данные по производительности не будут получены. И, наконец, выберите локальный или дистанционный мониторинг. Локальный мониторинг, при котором процесс наблюдения использует контролируемый сервер, добавляет непроизводительные издержки на процессор и диск сервера. Дистанционный мониторинг, который использует отдельный сервер, может избавить от подобных проблем, однако это сильно увеличивает рабочую нагрузку на сеть.

В перечислены метрики System Monitor или счетчики, которые рекомендуется использовать для определения базовых параметров. Я не могу сказать, какое значение “правильное” в контексте отдельно взятого приложения, так как оно меняется от системы к системе. Используйте среднее значение различных базовых параметров для установки обычной стандартной (по базовым параметрам) производительности и обозначьте, что этот вариант и является правильным для эксплуатируемой системы

Определение базовых параметров при помощи System Monitor

Теперь для целей сбора базовых параметров вызовем System Monitor. Откроем Control Panel, Administrative Tools, Performance. Дважды щелкнем на Performance Logs and Alerts на левой панели. Нажмем правую кнопку на Counter Logs и укажем New Log Settings. Введите имя для графика, затем нажмите OK. В диалоговом окне Select Counters выберите первый счетчик, затем нажмите Add. Повторяйте эти операции до тех пор, пока все счетчики не будут добавлены, затем нажмите Close.

Для начала попробуйте по умолчанию 15-секундный интервал. Или выберите другой интервал, нажав Properties (либо используйте клавишную комбинацию быстрого вызова Ctrl + Q), а затем введите значение под обозначением Sample automatically every: _ seconds. Более длинные интервалы занимают меньше места, однако они обеспечивают менее подробные данные.

Выберите таблицу Log Files и определите место, где будут храниться данные. Есть возможность просмотреть данные позже, используя представление View Log File Data. System Monitor будет выглядеть так, как на экране 1, когда он собирает данные базовых параметров производительности. Видно, что при одновременном отслеживании множества счетчиков можно собрать очень много данных, так что следует внимательно выбирать счетчики для основной линии.

Шаг 2: Установка эталонных значений

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

Для эталонов используется тот же режим мониторинга, что и для определения базовых параметров. Можно использовать свое решение или один из распространенных промышленных средств, таких как TPC-C или SAP, но лучшие результаты вычисления эталонных значений получаются при разработке обычных индивидуальных сценариев, которые настроены на использование определенного сервера базы данных и его приложений.

Можно создать собственный сценарий, используя набор сценариев T-SQL, утилиты osql либо Query Analyzer, SQL Profiler и System Monitor. Разработка сценариев нагрузочных тестов в T-SQL обычно занимает несколько дней. Еще больше времени может потребоваться на сбор данных выполнения нагрузочных тестов и анализ полученных данных.

После определения базовых параметров производительности сервера при заранее заданных нагрузках можно будет узнать, чего можно ожидать от системы. Используйте данные, собранные при получении эталонных значений для формирования основы планового наблюдения. Например, выяснилось, что сервер способен обеспечить до 249 транзакций в секунду, прежде чем его работа начнет замедляться. В этом случае можно установить уведомление с низким приоритетом, когда сервер достигнет загрузки около 200 TPS и уведомление с высоким приоритетом, когда сервер достигнет 235 TPS. Такой способ позволит администратору узнать о возможных проблемах с сервером и принять необходимые меры до того, как пользователи что-нибудь заметят. И никаких критических ситуаций. Теперь это возможно.

Шаг 3: Плановый мониторинг

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

Можно создать недорогое средство для наблюдения за SQL Server, используя сочетание SQL Server Agent и System Monitor. SQL Server Agent позволяет определить, какое событие вывело ошибку на монитор, установить, кто получает извещения о событиях и автоматически послать извещение, когда появляется событие с ошибкой.

Установка SQL Server Agent может быть продолжительной по времени и сложной, поэтому нужно будет обратиться к разделу описания Alerts в SQL Server Books Online (BOL) . SQL Server Agent обычно осуществляет текущий контроль за сообщениями об ошибках работы сервера базы данных и не контролирует выполнение.

Для контроля производительности сервера используется System Monitor для наблюдения за текущими счетчиками (установите частоту опроса с точностью до 15 минут).

Memory-Pages/sec

Network Interface-Bytes total/sec

Physical Disk-Disk Transfers/sec

Processor-% Processor Time

SQLServer:Access Methods-Full Scans/sec

SQLServer:Buffer Manager-Buffer Cache Hit Ratio

SQLServer:Databases Application Database-Transactions/sec

SQLServer:General Statistics-User onnections

SQLServer:Latches-Average Latch Wait Time

SQLServer:Locks-Average Wait Time

SQLServer:Locks-Lock Timeouts/sec

SQLServer:Locks-Number of Deadlocks/sec

SQLServer:Memory Manager-Memory Grants Pending

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

Для выполнения предупреждений можно использовать бесплатные инструменты, такие как SQL Server Alerts & Notifications, System Monitor либо приобрести Microsoft Operations Manager (MOM) или другие средства. Я рекомендую установить предупреждения, по крайней мере, для следующих ситуаций:

• ошибки, влияющие на эксплуатацию, особенно ошибки с показателем важности от 19 до 25
• блокировки
• использование процессора
• использование диска
• сканирования (SQLServer:Access Methods)

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

• журнал SQL Server
• журнал SQL Agent
• журнал приложений Windows, Security, и System
• журнал исполнения заданий SQL Server

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

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

Таблица 1. Объекты и счетчики System Monitor для определения базовых параметров

Начиная с версии 2008 в сборку SQL Server был добавлен монитор производительности системы - Performance Data Collector (PDC). Новый компонент Management Studio, призванный облегчить мониторинг и настройку производительности экземпляров SQL Server"а конечным пользователям.

Компоненты, устанавливаемые по умолчанию, называют набором системы сбора данных, а именно:

1. Использование дискового пространства. Сбор данных об использовании дискового пространства в базе данных.
2. Статистика запросов . Отчеты о статистики запросов , индивидуальный текст запроса , планы запросов , и конкретных запросов .
3. Мониторинг активности сервера . Собирает статистику использования ресурсов и производительности данных с сервера , операционной системы и SQL сервера .

Преимущества:

1. Простота конфигурации . Конфигурация требует всего несколько кликов .
2. Простота отслеживания событий, тестирования и мониторинга общего состояния системы.
3. Централизованное хранение данных .
4. Адаптивность. Включена возможность создания собственной системы сбора данных

Ограничения:

1. Совместимо только с версией SQL Server 2008.
2. Система сбора данных не отображает информацию о дисковом пространстве в режиме онлайн

Этапы подготовки:

1. На первом этапе подготовки на сервере должна быть создана папка с правами на чтение/запись для службы SQLSERVERAGENT. Вся техническая информация будет собираться в данной папке, а затем загружаться в базу данных системы мониторинга.
2. База данных системы мониторинга должна быть создана до того, как вы запустите мониторинг. Данная база данных по сути является обычной базой данных SQL и содержит все данные, полученные с помощью системы мониторинга.
3. Позаботьтесь заранее о размере дискового пространства. Ожидаемый рост базы данных около 250 - 350 мб в день.
4. По умолчанию данные очищаются каждые 14 дней. Глубину очистки можно менять в зависимости от заданных требований.
5. Набор сбора "Занято место на диске" отслеживает рост базы данных и файлов журнала и предоставляет статистику по файлам, такую как средний рост (в мегабайтах) в день. Опрос состояния диска происходит каждые 5 секунд, каждый час данные записываются в базу данных и хранятся в течение 90 дней. Данные интервалы могут быть скорректированы.
6. Набора сбора "Статистика запросов" собирает данные по статистике запросов, а также тексты отдельных запросов, планы запросов и конкретные запросы. Эти данные в сочетании с системой статистикой и действиями позволяют проводить детализацию углублением ниже уровня сеанса к отдельным запросам. Частота передачи по расписанию - каждые 15 минут, хранение данных в течение 14 дней. Данные интервалы могут быть скорректированы.
7. Набор сбора "Активность сервера" предоставляет общие сведения об активности SQL Server, использовании ресурсов SQL Server и конфликта между ресурсами SQL Server. Этот набор сбора также дает инкапсулированное представление использования всех системных ресурсов, которые позволяет определить связь проблем производительности с действиями за пределами области SQL Server. Запись статистики активности сервера происходит каждые 60 секунд, для активных сессий и запросов данный интервал составляет 10 секунд
8. База данных MSDB используется для хранения информации о конфигурации, о времени выполнения, аудита и ведении журнала сбора информации. SSIS пакетов хранятся в MSDB.
9. Необходимо обязательно установить SQL Server agent.
10. Служба интеграции SQL Server должна быть запущена, т.к. SSIS пакеты используются для сбора данных. SSIS пакеты также генерируют события во время сбора данных, которые используются для мониторинга и устранения неполадок в процессе сбора.
11. Data Collector Security. В окне мастера "Configure Data Warehouse Wizard" необходимо сопоставить роли для сборщика данных и учетные записи пользователей. К ним относятся: mdw_admin, mdw_reader и mdw_writer.

• mdw_reader - используется для входа пользователям, которым необходимо архивные отчеты;
• mdw_writer - роль может загружать и записывать данные в хранилище данных. Поэтому каждая служба SQLServerAgent, используемая на удаленных сборщиках данных, хранит данные в центральном базе данных.
• mdw_admin - чтение, запись, обновление и удаление доступа к базе данных. Любая учетная запись пользователя назначенная на роль mdw_admin может изменить схему на mdw-файла и запускать задания по обслуживанию.

Настройка производительности Data Collector

Во-первых, создадим хранилище данных управления

Теперь, давайте настроим хранилище данных управления.

Нажимаем "Next" на экране приветствия

Указываем имя базы данных и место расположения

Присваиваем пользователю роль mdw_admin.

Проверяем конфигурацию. Если все указано правильно, нажимаем "Finish" и переходим к процессу конфигурирования.

Идет процесс конфигурации...

Конфигурация выполнена.

Теперь, после выполнения на сервере запросов к базе данных можно просмотреть отчеты этих выполнений. Для просмотра отчетов разверните вкладку "Management" -> "Data Collection".

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

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

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

История статистики запросов.

Этот отчет отображает ресурсоемкие запросы по категориям.

Вы можете нажать на любой запрос и получить подробную информацию по данному запросу.

Внизу расположена подробная информация по выбранному запросу.

Средства мониторинга

Activity Monitor

Занимается мониторингом текущей активности. Запускаете тяжелый бухгалтерский отчет и смотрите что покажет Activity Monitor.
На скриншотах монитор активности от SQL Server 2005:

И от SQL Server Denali (2012) CTP 3.


Сложность анализа заключается в том, что данным инструментом не очень удобно пользоваться, если множество пользователей работаем с ним обновременно. Разбираться будет довольно сложно, хотя, конечно, прогресс на лицо. В Denali Activity Monitor показывает намного больше полезной информации (например на каком конкретно ресурсе происходит ожидание), плюс, мы можем, например, для нужной сессии запустить профайлер прямо из монитора и отслеживать ее уже в профайлере, но, он дополнительно нагружает и без того нагруженный сервер. К тому же проблема с медленной работой уже присутствует, а те запросы, которые на момент запуска профайлера уже начали выполняться, мы не увидим.
Хотелось бы видеть кто и что выполняет именно сейчас.

sp_who и sp_who2

На скриншоте результат выполнения sp_who (сверху) и sp_who2 (снизу), выполненных во время построения все того же отчета:


Данное представление информации не очень информативно. Глядя на sp_who мы можем увидеть только то, что что-то выполняется или несколько каких-то SELECT’ов.
sp_who2 показывает уже больше информации. Теперь мы можем видеть сколько процессорного времени затрачено сессией (и столбиком сложить суммарное время, видимо), количество i/o-операций, имя базы данных в которой все это выполняется и кем заблокирована эта сессия (если она заблокирована).
Activity Monitor, как мы видим, дает больше информации.

DMV

Начиная с SQL Server 2005, мы получили новую возможность получать информацию о состоянии сервера - Dynamic Management Views . MSDN говорит так: «Динамические административные представления и функции возвращают данные о состоянии сервера, которые могут использоваться для контроля исправности экземпляра сервера, диагностики проблем и настройки производительности.».
И действительно, в 2005-м SQL Server’е есть набор представлений, связанных с выполнением запросов в текущий момент (впрочем, для просмотра «истории» тоже есть представления): вот они . И их количество, от версии к версии продолжает увеличиваться!
Наверняка, у опытных администраторов есть наготове куча скриптов, позволяющих получить информацию о текущем состоянии сервера, но что делать, если опыта работы с DMV еще нет, а проблемы уже есть?

sp_WhoIsActive

Adam Machanic (SQL Server MVP и MCITP) разработал и постоянно дорабатывает хранимую процедуру sp_WhoIsActive, которая опирается как раз на эти самые DMV и очень легка в освоении. Скачать последнюю версию sp_WhoIsActive можно . У самого Адама есть цикл статей, посвященных sp_WhoIsActive, состоящий аж из 30 (тридцати!) штук, почитать его можно , а я же, постараюсь заинтересовать вас в прочтении этого материала:).
Итак, будем считать, что вы скачали и запустили этот скрипт на одном из тестовых серверов (на любой версии, начиная с 2005 и заканчивая Denali). Адам советует хранить ее в системной базе данных master, чтобы ее можно было вызвать в контексте любой БД, но это не обязательно, просто при вызове ее в контексте другой БД, придется писать название полностью - БД.схема.sp_whoIsActive.
Итак, попробуем. На скриншоте результат ее выполнения во время построения все того же отчета:

Результат запроса exec sp_whoIsActive, увы, не влазит в один экран, поэтому вот текстовое описание вывода хранимой процедуры, вызываемой без параметров.

• - для активного запроса показывает время выполнения, для «спящей» сессии - время «сна»;
• - собственно, spid;
• - показывает текст выполняемого сейчас запроса, либо текст последнего выполненного запроса, если сессия спит;
• - ну, вы поняли;
• - очень интересный столбец. Он выводится в формате (Ax: Bms/Cms/Dms)E. А - это количество ожидающих задач на ресурсе E. B/C/D - это время ожидания в миллисекундах. Если ожидает освобождения ресурса всего одна сессия (как на скриншоте), будет показано ее время ожидания, если 2 сессии - их времена ожидания в формате B/C. Если же ожидают 3 и более - мы увидим минимальное, среднее и максимальное время ожидания на ЭТОМ ресурсе в формате B/C/D;
• - для активного запроса - суммарное время ЦП, затраченное этим запросом, для спящей сессии - суммарное время ЦП за «всю жизнь» этой сессии;
• - для активного запроса - это количество операций записи в TempDB за время выполнения запроса; для спящей сессии - суммарное количество записей в TempDB за все время жизни сессии;
• - для активного запроса - количество страниц в TempDB, выделенных для этого запроса; для спящей сессии - суммарное количество страниц в TempDB, выделенных за все время жизни сессии;
• - если вдруг мы кем-то заблокированы, покажет spid (session_id) того, кем мы заблокированы;
• - для активного запроса - количество логических чтений выполненных при выполнении этого запроса; для спящей сессии - количество прочитанных страниц за все время жизни этой сессии;
• - все тоже самое, но про запись;
• - для активного запроса - количество физических чтений, выполненных при выполнении этого запроса; для спящей сессии - традиционно, суммарное количество физических чтений за все время жизни сессии;
• - для активного запроса - количество восьмикилобайтовых страниц, использованных при выполнении этого запроса; для спящей сессии - сколько суммарно страниц памяти выделялось ей за все ее время жизни;
• - статус сессии - выполняется, спит и т.д.;
• - показывает количество транзакций открытых этой сессией;
• - показывает, если есть такая возможность, процесс выполнения операции (например, BACKUP, RESTORE), никогда не покажет на сколько процентов выполнен SELECT .

Остальные столбцы в стандартном выводе sp_WhoIsActive малоинтересны, и описывать их я не буду - их назначение, я думаю, понятно всем (host_name, database_name, program_name, start_time, login_time, request_id, collection_time).
Но это еще не все. Еще я расскажу о том с какими (наиболее интересными и полезными, с моей точки зрения) параметрами можно вызывать sp_WhoIsActive и что из этого получится.

• @help - это ужасно полезный параметр. При вызове sp_whoIsActive @help = 1 , мы получаем на экран информацию обо ВСЕХ параметрах и выводимых столбцах. Так что если что-то останется непонятным, всегда можно посмотреть «помощь»
• @filter_type и @filter - позволяют отфильтровать результат выполнения. @filter_type может принимать значения ‘session’, ‘program’, ‘database’, ‘login’ и ‘host’. В параметре @filter мы указываем какой именно объект выбранного типа нас интересует. Например, мы хотим увидеть все сессии, выполняющиеся в БД master, для этого вызываем exec sp_whoIsActive @filter_type = "database", @filter = "master" . В параметре @filter допустимо использование «%»;
• @not_filter_type и @not_filter - позволяют нам фильтровать «наоборот». Т.е., например, мы хотим видеть все, кроме тех сессий, у которых в поле «database» стоит ‘master’, для этого выполняем exec sp_WhoIsActive @not_filter_type = "database", @not_filter = "master" . Ну, или, мы захотим увидеть что выполняют все пользователи кроме пользователя sa… Применений может быть множество. В параметре @not_filter допустимо использование «%»;
• @show_system_spids = 1 - покажет информацию о системных сессиях;
• @get_full_inner_text = 1 - в поле sql_text будет находиться не просто текст текущего запроса (стэйтмента) в пакете (батче), а текст всего батча целиком;
• @get_plans - добавит к выводу столбец с планами выполнения запросов;
• @get_transaction_info = 1 - добавит к выводу количество и объем записей в журналы транзакций, а так же время начала последней транзакции;
• @get_locks = 1 - добавит к выводу информацию о всех блокировках, наложенных во время выполнения запроса;
• @find_block_leaders = 1 - проследит цепочку блокировок и покажет суммарное количество сессий, ожидающих снятия блокировки текущей сессией;
• @output_column_list = "[%]" - а вдруг вы не хотите видеть информацию о tempDB в выводе sp_whoIsActive? С помощью этого параметра можно управлять тем, что она выводит;
• @destination_table = "table_name" - попытается вставить результат выполнения записать в таблицу, но не будет проверять существует ли эта таблица и хватает ли прав на вставку в нее.

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

версии 11.0.1 имеет ряд новых важных возможностей, существенно отличающих новую версию от всех предыдущих. 11.0.1 может работать с любой версией SQL Server, начиная с 4.9.2 и кончая System 11.

Однако некоторые наиболее интересные виды информации о характере использования объектов баз данных и взаимодействии сервера с сетью выдаются только при мониторинге SQL Server System 10 и System 11. Естественно, данные о работе именованных кэш-буферов выдаются только при контроле производительности SQL Server System 11.

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

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

При запуске необходимо отменить проверку памяти сервера, выполняемую командой dbcc memusage, поскольку эта команда существенно замедляет работу сервера. Для этого при запуске sqlmon (клиентского модуля) необходимо указать параметр – nomem.

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

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

Так, для поддержки 20 окон в командном файле запуска серверного модуля необходимо указать параметр п2 0. При этом потребуется изменить адрес начала разделяемой области памяти сервера с помощью команды buildmaster и некоторых других действий. Эти действия ни в коем случае нельзя производить во время работы SQL Server. (Подробно о процессе расширения количества одновременно поддерживаемых клиентов см. в руководстве по серверному модулю Server Supplement.)

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

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

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

также не позволяет долгое время выдавать на экран динамику изменения показателей производительности.

Он способен представить на экране данные за 60 последовательных интервалов измерения производительности. В зависимости от выбранной продолжительности каждого интервала такая статистика может охватить довольно большой промежуток времени. Однако этот прием не дает возможности сопоставить текущие данные с показателями месячной или годовой давности.

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

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

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

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

Главное окно (Main Window)
Здесь содержится перечень окон, поддерживаемых программой. В случае, если при запуске sglmon - клиентского модуля - не был указан параметр – nomem, в этом окне также будет выдана круговая диаграмма использования памяти серверной машины.

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

Кэш-буфер данных, только для SQL Server System 11 (Data Cach)
Окно сообщает количество операций физического и логического ввода-вывода по каждому из именованных кэш-буферов, сконфигурированных на сервере.

Дисковый ввод/вывод (Device I/O)
Здесь находятся графики и сводные таблицы по текущему и полному количеству обращений к дискам. Они помогают оптимизировать распределение нагрузки ввода-вывода среди имеющихся серверных устройств. При анализе выдаваемой информации полезно использовать стандартную схему выбора названий серверных устройств по названиям соответствующих разделов физических дисков, поскольку, наблюдая за скоростью обмена с серверными устройствами, следует знать, к какому дисковому контроллеру подключено каждое из этих устройств.

Работа с сетью, только для SQL Server System 10 и 11 (Network Activity)
В окне сообщается статистическая информация о сетевом вводе”выводе - размеры пакетов, объемы трафика и т.п.

Блокировка доступа к объектам, только для SQL Server System 10 и 11 (Object Lock Status)
Здесь выдается информация о блокировках доступа к таблицам данных, включая подробное распределение используемых типов блокировок, названия процессов, удерживающих блокировки и т.д.

Ввод-вывод страниц объектов, только для SQL Server System 10 и 11 (Object Page I/O)
Окно содержит информацию об интенсивности ввода-вывода страниц одной из таблиц данных сервера. Обратите внимание на эффективность при составлении перечня наиболее часто используемых таблиц сервера. Подобные сведения не выдаются процедурой sp_sysmon.

Сводка данных о производительности (Performance Summar)
Здесь представлена общая картина функционирования SQL Server - процент использования времени процессора, количество обрабатываемых транзакций в секунду, объем сетевого трафика, дискового ввода-вывода, а также интенсивность использования блокировок.

Динамика показателей производительности (Performance Trend)
В окне строятся непрерывные графики зависимости от времени показателей производительности сервера, выдаваемых в окне Performance Summary.

Активность серверных процессов (Process Activit)
Окно позволяет выбрать один или несколько серверных процессов и следить за использованием процессора и объемами ввода-вывода по каждому из процессов.

Подробные данные о процессе (Process Detail)
Окно содержит подробную информацию о выбранном серверном процессе.

Список процессов (Process List)
Окно содержит перечень всех имеющихся в данный момент серверных процессов с указанием их состояния. Очень похоже на выдачу серверной команды sp_who.

Использование блокировок (Process Lock Activity)
Окно выдает информацию об использовании блокировок выбранным вами серверным процессом.

Использование хранимых процедур (Stored Procedure Activity)
Окно содержит сведения о выполнении хранимых процедур и времени работы каждой процедуры.

Обработка транзакций (Transaction Activity)
В окне можно увидеть столбчатую диаграмму, показывающую количество обрабатываемых транзакций с распределением по различным типам транзакций. Видно, например, какую часть транзакций удается выполнить с использованием механизма обновления записей на месте (update in place).

Любому администратору баз данных, наверняка, приходилось сталкиваться с тем, что все работает медленно, или не работает вообще. Первое, что при этом нужно выяснить - это что вообще происходит на SQL Server в данный момент. Казалось, бы в арсенале администратора столько всяких полезных штук: гуевый Activity Monitor, куча Dynamic Management Views (dmv), хранимые процедуры sp_who и sp_who2, оставшиеся в наследство еще со времен SQL Server 7 и SQL Server 2000.
Но, давайте разберемся…

Средства мониторинга

Activity Monitor

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

и от SQL Server Denali (2012) CTP 3.


М-да. А если десяток человек запустит такие отчеты? А это ведь не редкость… Разбираться будет довольно неудобно, хотя, конечно, прогресс на лицо. В Denali Activity Monitor показывает намного больше полезной информации (например на каком конкретно ресурсе происходит ожидание), плюс, мы можем, например, для нужной сессии запустить профайлер прямо из монитора и отслеживать ее уже в профайлере, но, черт побери, он дополнительно нагружает и без того нагруженный сервер. К тому же проблема с тормозами уже есть, а те запросы которые на момент запуска профайлера уже начали выполняться, мы не увидим.
А я хочу видеть именно это - кто и что выполняет именно сейчас.

sp_who и sp_who2

На скриншоте результат выполнения sp_who (сверху) и sp_who2 (снизу), выполненных во время построения все того же злосчастного отчета:


Ага. Очень информативно. Глядя на sp_who мы можем увидеть только то, что что-то выполняется. Конечно выполняется - мы ж для того и смотрим, а видим, что выполняется какой-то SELECT. Или несколько каких-то SELECT"ов. Здорово.
sp_who2 показывает уже больше информации. Теперь мы можем видеть сколько процессорного времени затрачено сессией (и столбиком сложить суммарное время, видимо), количество i/o-операций, имя базы данных в которой все это выполняется и кем заблокирована эта сессия (если она заблокирована).
Activity Monitor, как мы видим, дает больше информации.

DMV

Начиная с SQL Server 2005, мы получили новую возможность получать информацию о состоянии сервера - Dynamic Management Views . MSDN говорит так: "Динамические административные представления и функции возвращают данные о состоянии сервера, которые могут использоваться для контроля исправности экземпляра сервера, диагностики проблем и настройки производительности.".
И действительно, в 2005-м SQL Server"е есть набор представлений, связанных с выполнением запросов в текущий момент (впрочем, для просмотра "истории" тоже есть представления): вот они . И их количество, от версии к версии продолжает увеличиваться!
Наверняка, у мастистых администраторов есть наготове куча скриптов, позволяющих получить информацию о текущем состоянии сервера, но что делать, если опыта работы с DMV еще нет, а проблемы уже есть?

sp_WhoIsActive

Adam Machanic (SQL Server MVP и MCITP) разработал и постоянно дорабатывает хранимую процедуру sp_WhoIsActive, которая опирается как раз на эти самые DMV и чертовски легка в использовании. Скачать последнюю версию sp_WhoIsActive можно . У самого Адама есть цикл статей, посвященных sp_WhoIsActive, состоящий аж из 30 (тридцати!) штук, почитать его можно , а я же, постараюсь заинтересовать вас в прочтении этого материала:).
Итак, будем считать, что вы скачали и запустили этот скрипт на одном из тестовых серверов (на любой версии, начиная с 2005 и заканчивая Denali). Адам советует хранить ее в системной базе данных master, чтобы ее можно было вызвать в контексте любой БД, но это не обязательно, просто при вызове ее в контексте другой БД, придется писать название полностью - БД.схема.sp_whoIsActive.
Итак, попробуем. На скриншоте результат ее выполнения во время построения все того же отчета:

Результат запроса exec sp_whoIsActive, увы, не влазит в один экран, поэтому вот текстовое описание вывода хранимой процедуры, вызываемой без параметров.

• - для активного запроса показывает время выполнения, для "спящей" сессии - время "сна";
• - собственно, spid;
• - показывает текст выполняемого сейчас запроса, либо текст последнего выполненного запроса, если сессия спит;
• - ну, вы поняли;
• - очень интересный столбец. Он выводится в формате (Ax: Bms/Cms/Dms)E. А - это количество ожидающих задач на ресурсе E. B/C/D - это время ожидания в миллисекундах. Если ожидает освобождения ресурса всего одна сессия (как на скриншоте), будет показано ее время ожидания, если 2 сессии - их времена ожидания в формате B/C. Если же ожидают 3 и более - мы увидим минимальное, среднее и максимальное время ожидания на ЭТОМ ресурсе в формате B/C/D;
• - для активного запроса - суммарное время ЦП, затраченное этим запросом, для спящей сессии - суммарное время ЦП за "всю жизнь" этой сессии;
• - для активного запроса - это количество операций записи в TempDB за время выполнения запроса; для спящей сессии - суммарное количество записей в TempDB за все время жизни сессии;
• - для активного запроса - количество страниц в TempDB, выделенных для этого запроса; для спящей сессии - суммарное количество страниц в TempDB, выделенных за все время жизни сессии;
• - если вдруг мы кем-то заблокированы, покажет spid (session_id) того, кем мы заблокированы;
• - для активного запроса - количество логических чтений выполненных при выполнении этого запроса; для спящей сессии - количество прочитанных страниц за все время жизни этой сессии;
• - все тоже самое, но про запись;
• - для активного запроса - количество физических чтений, выполненных при выполнении этого запроса; для спящей сессии - традиционно, суммарное количество физических чтений за все время жизни сессии;
• - для активного запроса - количество восьмикилобайтовых страниц, использованных при выполнении этого запроса; для спящей сессии - сколько суммарно страниц памяти выделялось ей за все ее время жизни;
• - статус сессии - выполняется, спит и т.д.;
• - показывает количество транзакций открытых этой сессией;
• - показывает, если есть такая возможность, процесс выполнения операции (например, BACKUP, RESTORE), никогда не покажет на сколько процентов выполнен SELECT .

Остальные столбцы в стандартном выводе sp_WhoIsActive малоинтересны, и описывать их я не буду - их назначение, я думаю, понятно всем (host_name, database_name, program_name, start_time, login_time, request_id, collection_time).

И чО? Это все?

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

• @help - это ужасно полезный параметр. При вызове sp_whoIsActive @help = 1 , мы получаем на экран информацию обо ВСЕХ параметрах и выводимых столбцах. Так что если что-то останется непонятным, всегда можно посмотреть "помощь"
• @filter_type и @filter - позволяют отфильтровать результат выполнения. @filter_type может принимать значения "session", "program", "database", "login" и "host". В параметре @filter мы указываем какой именно объект выбранного типа нас интересует. Например, мы хотим увидеть все сессии, выполняющиеся в БД master, для этого вызываем exec sp_whoIsActive @filter_type = "database", @filter = "master" . В параметре @filter допустимо использование "%";
• @not_filter_type и @not_filter - позволяют нам фильтровать "наоборот". Т.е., например, мы хотим видеть все, кроме тех сессий, у которых в поле "database" стоит "master", для этого выполняем exec sp_WhoIsActive @not_filter_type = "database", @not_filter = "master" . Ну, или, мы захотим увидеть что выполняют все пользователи кроме пользователя sa… Применений может быть множество. В параметре @not_filter допустимо использование "%";
• @show_system_spids = 1 - покажет информацию о системных сессиях;
• @get_full_inner_text = 1 - в поле sql_text будет находиться не просто текст текущего запроса (стэйтмента) в пакете (батче), а текст всего батча целиком;
• @get_plans - добавит к выводу столбец с планами выполнения запросов;
• @get_transaction_info = 1 - добавит к выводу количество и объем записей в журналы транзакций, а так же время начала последней транзакции;
• @get_locks = 1 - добавит к выводу информацию о всех блокировках, наложенных во время выполнения запроса;
• @find_block_leaders = 1 - проследит цепочку блокировок и покажет суммарное количество сессий, ожидающих снятия блокировки текущей сессией;
• @output_column_list = "[%]" - а вдруг вы не хотите видеть информацию о tempDB в выводе sp_whoIsActive? С помощью этого параметра можно управлять тем, что она выводит;
• @destination_table = "table_name" - попытается вставить результат выполнения записать в таблицу, но не будет проверять существует ли эта таблица и хватает ли прав на вставку в нее.

Вот теперь все

В итоге, мы имеем еще один чрезвычайно удобный и гибкий инструмент для отслеживания текущей активности на SQL Server. Для нормальной его работы вполне достаточно разрешения VIEW SERVER STATE и прав на обращение к dmv.
Стоит также добавить, в том случае, когда к серверу возможно подключение только по