Программа для мониторинга sql сервера. Использование монитора производительности для определения узких мест аппаратных средств, на которых запущен SQL Server

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

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

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" - попытается вставить результат выполнения записать в таблицу, но не будет проверять существует ли эта таблица и хватает ли прав на вставку в нее.

Список контрольных вопросов аудита производительности

Введите ваши результаты в таблицу, приведенную выше.

Использование монитора производительности (Performance Monutor) для идентификации узких места аппаратных средств SQL Server

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

В идеале Вы должны использовать монитор производительности для создания файла регистрации (журнала) показаний ключевых счетчиков сроком на 24 часа. Вам предстоит выбрать "типичный" 24-часовой период для создания файла регистрации. Например, выберите типичный рабочий день, не конец недели или праздник.

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

Как интерпретировать ключевые счетчики монитора производительности

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

Память: Страницы/секунды

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

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

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

В большинстве случаев, на физическом сервере, специализированном под SQL Server, с адекватным количеством оперативной памяти, среднее значение обмена страницами будет меньше чем 20. Адекватное количество оперативной памяти для SQL Server можно определить по следующему критерию: сервер должен иметь коэффициент удачного обращения в кэш буфера (Buffer Hit Cache Ratio) 99 % и выше. Данный счетчик описан ниже в этой статье. Если Вы имеете SQL Server, у которого этот коэффициент имеет значение 99 % или выше в течение 24 часов, но Вы получаете среднее значение обмена страницами более 20 в течение того же самого периода времени, это может указывать на то, что у Вас выполняются и другие приложения на физическом сервере помимо SQL Server. Если дело обстоит именно так, Вы должны в идеальном случае удалить эти приложения, позволив SQL Server быть единственным главным приложением на физическом сервере.

Если ваш Сервер SQL не выполняет никакие другие приложения, и обмен страницами превышает 20 в среднем в течение 24 часов, это может означать, что Вы изменили параметры настройки памяти SQL Server. SQL Server должен быть конфигурирован так, чтобы была установлена опция "Dynamically configure SQL Server memory" (Динамически конфигурировать память SQL Server), а установка "Maximum Memory" должна находиться в наибольшем значении. Для оптимальной работы, SQL Server нужно позволить взять столько оперативной памяти, сколько ему требуется для собственных нужд, не испытывая необходимости конкурировать за оперативную память с другими приложениями.

Память: Доступное пространство

Другой способ выяснить, имеет ли ваш SQL Server достаточно физической оперативной памяти, состоит в том, чтобы проверить счетчик Memory Object: Available Bytes. Его значение должно быть более 5 МБ. В противном случае, ваш Сервер SQL нуждается в большем количестве физической оперативной памяти. На сервере, специализированном под SQL Server, последний пытается удерживать от 4-10MB свободной физической памяти. Оставшаяся физическая оперативная память используется операционной системой и SQL Server. Когда объем доступной памяти близко к 5 МБ или ниже, наиболее вероятно, что SQL Server испытывает перегрузку из-за нехватки памяти. Если это имеет место, Вы должны увеличить количество физической оперативной памяти в сервере, уменьшить нагрузку на сервер или изменить параметры настройки конфигурации памяти вашего SQL Server соответственно.

Физический диск: Время работы диска %

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

Как эмпирическое правило, счетчик времени диска должен показывать менее 55 %. Если показания счетчика превышают 55 % в течение непрерывных периодов (свыше 10 минут в течение ваших 24 часов мониторинга), то ваш SQL Server может испытывать проблемы с операциями ввода/вывода. Если Вы наблюдаете это поведение лишь изредка в течение ваших 24 часов мониторинга, я бы не волновался слишком сильно, но если бы это случалось часто (скажем, несколько раз час), то я начал бы искать способы увеличить производительность операций ввода/вывода на сервере или уменьшить загрузку сервера. Некоторые способы увеличивать дисковый ввод/вывод состоят в добавлении новых дисков в массив (если это возможно), замены дисков на более быстрые, добавлении кэш-памяти на плате контроллера (если это возможно), использования различных версий RAID или установки более быстрого контроллера.

Перед использованием этого счетчика под NT 4.0, нужно вручную включить его, введя в Command Prompt следующее: "diskperf-y". После этого нужно будет перезагрузить ваш сервер. Таким образом, требуется сразу включать дисковые счетчики под Windows NT 4.0. Если Вы работаете под Windows 2000, этот счетчик включен по умолчанию.

Физический диск: Средняя длина очереди диска

Помимо наблюдения за значением счетчика "Физический Диск: Время работы диска", желательно также отслеживать значения счетчика средней длины очереди диска (Avg. Disk Queue Length). Если это значение превышает значение 2 для непрерывных периодов (свыше 10 минут в течение вашего 24 часового мониторинга) для каждого дисковода в массиве, то этот массив может оказаться узким местом производительности системы. Подобно счетчику времени работы диска, если это происходит изредка в течение 24 часов периода мониторинга, я не сильно бы волновался, но если это происходит часто, тогда я бы начал искать способы увеличить производительность системы ввода/вывода сервера, как это описано выше.

Вам придется вычислить этот показатель, поскольку Performance Monitor не знает, сколько физических дисков находится в вашем массиве. Например, если у вас имеется массив из 6 физических дисков, и средняя длина очереди равна 10 для этого массива, тогда фактическое среднее значение дисковой очереди для каждого диска составляет 1.66 (10/6=1.66), что хорошо укладывается в рекомендованный показатель 2 на один физический диск.

Перед использованием этого счетчика под NT 4.0, не забудьте вручную включить его, набрав на приглашение к вводу команд NT (Command Prompt) следующее: "diskperf-y" с последующей перезагрузка вашего сервера. Поэтому требуется включать дисковые счетчики сразу после установки Windows NT 4.0. Если Вы используете Windows 2000, то этот счетчик будет включен по умолчанию.

Используйте оба описанных выше счетчика, чтобы точно выяснить, испытывает ли ваш сервер проблемы с системой ввода/вывода. Например, если Вы видите много периодов времени, в течение которых время работы диска более 55 %, и когда среднее значение длины очереди диска составляет более 2 на один физический диск, Вы можете быть уверенными, что сервер имеет проблемы с системой ввода - вывода.

Процессор: Процессорное время %

Счетчик Processor Object: % Processor Time имеется для каждого центрального процессора и оценивает использование каждого отдельного центрального процессора. Аналогичный счетчик имеется также для всей совокупности центральных процессоров (общее количество). Это ключевой счетчик для слежения за использованием центрального процессора. Если общее время загрузки процессоров по этому счетчику превышает 80 % в течение непрерывных периодов (свыше 10 минут в течение 24 часового периода мониторинга), то Вы можете считать центральный процессор узким местом системы. Если эти периоды сильной загрузки происходят изредка, и Вы полагаете, что можете смириться с этим, то все в порядке. Но если они возникают часто, Вам следует рассмотреть такие варианты снижения загрузки сервера, как приобретение более быстрых центральных процессоров, установку большего количества центральных процессоров, или приобретение центральных процессоров, которые имеют больший встроенный кэш второго уровня (L2).

Система: Длина очереди процессора

Наряду со счетчиком процессорного времени, Вам следует также контролировать счетчик длины очереди процессора (Processor Queue Length). Если этот показатель превышает значение 2 на один центральный процессор в течение непрерывных периодов (свыше 10 минут в течение вашего 24 часового периода мониторинга), то вероятно это является узким звеном системы. Например, если на Вашем сервере имеется 4 центральных процессора, длина очереди процессора не должна превышать в общей сложности значение 8.

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

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

Буфер SQL Server: Коэффициент удачного обращения в кэш буфера

Этот счетчик (SQL Server Buffer: Buffer Cache Hit Ratio) показывает, как часто SQL Server обращается к буферу, а не к жесткому диску, чтобы получить данные. В приложениях OLTP, этот коэффициент должен превышать 90 %, а в идеале быть выше 99 %. Если ваш коэффициент удачного обращения в буферный кэш ниже 90 %, Вам следует пойти и купить больше оперативной памяти уже сегодня. Если этот коэффициент лежит в диапазоне между 90 % и 99 %, то Вы должны серьезно рассмотреть вариант покупки дополнительной оперативной памяти, так как чем ближе Вы приближаетесь к 99 %, тем быстрее ваш SQL Server будет работать. В некоторых случаях, если ваша база данных является очень большой, Вам не удастся приблизиться к 99 %, даже если Вы поставите максимально допустимое количество оперативной памяти на вашем сервере. Тогда все, что Вы можете сделать, - это добавить память по максимуму и смириться с существующим положением вещей.

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

SQL Server: Пользовательские подключения

Поскольку число пользователей Сервер SQL, влияет на его производительность, рекомендуется следить за счетчиком пользовательских подключений (SQL Server General Statistics Object: User Connections counter). Он показывает число пользовательских подключений, а не число пользователей, которые подключены к SQL Server в данный момент времени.

Если показания этого счетчика превышают 255, то Вам следует увеличить значение конфигурационного параметра "Maximum Worker Threads" (максимальное число рабочих нитей), значение по умолчанию которого равно 255. Если число подключений превышает имеющееся число рабочих нитей, то SQL Server начнет совместно использовать рабочие нити, что может отрицательно сказаться на производительности. Установка этого параметра должна быть выше, чем максимальное число подключений, которое может быть достигнуто на вашем сервере.

Что дальше

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

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

и еще, как можно проследить за действиями пользователей?

Что ты хочешь отслеживать?

Если историю посомтреть с графиками то это Data Collection

Если надо на данный момент посомреть то можно пользоваться Data management View а ля

SELECT * FROM sys.dm_exec_sessions AS des

SELECT * FROM sys.dm_exec_requests AS der

или по старинке exec sp_who

либо запустить монитор активности =)

Судя по тому что ты там ничего не видишь, то на сервере у вас 1 запрос в час, который SQL смело делает.

Спрашивается зачем что-то завершать? Если уж на то пошло сессии ниже 50 - это системные службы и на них думаю пока не стоит замахиватьсяч если не понимаешь что делаешь.

Для начала попробуй вот так

SELECT * FROM sys.dm_exec_requests AS der WHERE der.session_id > 50

Если посомтришь что тебе монитор активности показывает то увидишь знакомые таблички...

SELECT = s.session_id, = CONVERT(CHAR(1), s.is_user_process), = s.login_name, = ISNULL(db_name(p.dbid), N""), = ISNULL(t.task_state, N""), = ISNULL(r.command, N""), = ISNULL(s.program_name, N""), = ISNULL(w.wait_duration_ms, 0), = ISNULL(w.wait_type, N""), = ISNULL(w.resource_description, N""), = ISNULL(CONVERT (varchar, w.blocking_session_id), ""), = CASE -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN "1" -- session is either not blocking someone, or is blocking someone but is blocked by another party ELSE "" END, = s.cpu_time, = (s.reads + s.writes) * 8 / 1024, = s.memory_usage * 8192 / 1024, = ISNULL(r.open_transaction_count,0), = s.login_time, = s.last_request_start_time, = ISNULL(s.host_name, N""), = ISNULL(c.client_net_address, N""), = ISNULL(t.exec_context_id, 0), = ISNULL(r.request_id, 0), = ISNULL(g.name, N"") FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id) LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id) LEFT OUTER JOIN (-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as -- waiting for several different threads. This will cause that thread to show up in multiple rows -- in our grid, which we don"t want. Use ROW_NUMBER to select the longest wait for each thread, -- and use it as representative of the other wait relationships this thread is involved in. SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num FROM sys.dm_os_waiting_tasks) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id) LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id) LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid) ORDER BY s.session_id;

Ну и счетчики производительности тоже никто не отменял. А ля количество запросов в секунду и т.д.

Любому администратору баз данных, наверняка, приходилось сталкиваться с тем, что все работает медленно, или не работает вообще. Первое, что при этом нужно выяснить - это что вообще происходит на 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". В параметре мы указываем какой именно объект выбранного типа нас интересует. Например, мы хотим увидеть все сессии, выполняющиеся в БД master, для этого вызываем exec sp_whoIsActive @filter_type = "database", = "master" . В параметре допустимо использование "%";
• @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.
Стоит также добавить, в том случае, когда к серверу возможно подключение только по

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 для определения базовых параметров

Объект и счетчик	Описание
Memory-Pages/sec	Число страниц чтения или записи на диск в секунду. Этот счетчик - первичный индикатор типов ошибок, вызванных системными задержками или проблемами с производительностью
Network Interface-Bytes total/sec	Число байтов, проходящих по сетевому интерфейсу в секунду. Когда показатель этого счетчика снижается или имеет такую тенденцию, это указывает на то, что проблемы с сетью могут оказывать влияние на приложение
PhysicalDisk-Disk Transfers/sec	Оценка дисковых операций чтения/записи. Установите счетчик для каждого физического диска на сервере
Processor-% Processor Time	Процентное соотношение времени, которое процессор тратит выполнение рабочего потока. Этот счетчик работает как первичный индикатор деятельности процессора. Если все процессоры, работающие на SQL Server, показывают стопроцентное использование, запросы конечного пользователя, скорее всего игнорируются
SQLServer:Access Methods-Full Scans/sec	Число неограниченных заполненных таблиц или индексных сканирований в секунду. Понижение значений этого счетчика к лучшему, потому что просмотры часто вызывают нехватку ресурсов проблемы кеширования
SQLServer:Buffer Manager-Buffer Cache Hit Ratio	Процентное отношение страниц, которые не требовали чтения от диска. Чем выше их число, тем меньше производится ввода/вывода на диск. В хорошо настроенной системе это значение должно быть 80 или выше.
SQLServer:Databases-Log Growths	На сколько, для конкретной базы данных, вырос файл транзакций. В хорошо настроенной системе значение этого счетчика должно быть низким, вероятно, меньше чем один в несколько дней
SQLServer:Databases Application Database-Percent Log Used	Процентное отношение свободного места в журнальном файле. Этот счетчик планово варьирует, но не должен достигать 100
SQLServer:Databases Application Database-Transactions/sec	Число транзакций, подтвержденных в базе данных. Этот счетчик временами опускается в эталонах. Наблюдайте за тем, когда транзакции начинают выстраиваться в очередь, это указывает на то, что дисковый ввод/вывод может быть медленным
SQLServer:Latches-Average Latch Wait Time	Среднее время задержки запроса перед заполнением. Это значение счетчика может быть высоким, когда сервер сталкивается с соперничеством за ресурсы, особенно за память или за ввод/вывод
SQLServer:Locks-Average Wait Time, Lock Waits/sec, Number of Deadlocks/sec	Временные блокировки удерживают ресурсы SQL Server. Наблюдайте за восходящей тенденцией этих связанных с блокировкой счетчиков, что указывает на возможную проблему с производительностью
SQLServer:General Statistics-User Connections	Число пользовательских подключений к серверу базы данных. Проверяйте любые заметные сдвиги в значении этого счетчика. Они могут указывать на сетевые проблемы и свидетельствовать о нагрузках и замедлении
SQLServer:Memory Manager-Memory Grants Pending	Текущее число процессов, ожидающих предоставления пространства памяти. Высокое или растущее значение может указывать на недостаточный объем памяти
SQLServer:User Settable-Query (a tracer query)	Специализированный счетчик, также известный как указатель запросов. Этот счетчик - созданный пользователем запрос, который указывает общую скорость или эффективность системы. Чтобы устанавливать это значение, приложение вызывает sp_user_counter1 и возвращает числовое значение.