SQL Server. Мониторинг

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:
image
и от SQL Server Denali (2012) CTP 3.
image
Сложность анализа заключается в том, что данным инструментом не очень удобно пользоваться, если множество пользователей работаем с ним обновременно. Разбираться будет довольно сложно, хотя, конечно, прогресс на лицо. В Denali Activity Monitor показывает намного больше полезной информации (например на каком конкретно ресурсе происходит ожидание), плюс, мы можем, например, для нужной сессии запустить профайлер прямо из монитора и отслеживать ее уже в профайлере, но, он дополнительно нагружает и без того нагруженный сервер. К тому же проблема с медленной работой уже присутствует, а те запросы, которые на момент запуска профайлера уже начали выполняться, мы не увидим.
Хотелось бы видеть кто и что выполняет именно сейчас.

sp_who и sp_who2

На скриншоте результат выполнения sp_who (сверху) и sp_who2 (снизу), выполненных во время построения все того же отчета:
image
Данное представление информации не очень информативно. Глядя на 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.
Итак, попробуем. На скриншоте результат ее выполнения во время построения все того же отчета:
image
Результат запроса exec sp_whoIsActive, увы, не влазит в один экран, поэтому вот текстовое описание вывода хранимой процедуры, вызываемой без параметров.

  • [dd hh:mm:ss.mss] — для активного запроса показывает время выполнения, для «спящей» сессии — время «сна»;
  • [session_id] — собственно, spid;
  • [sql_text] — показывает текст выполняемого сейчас запроса, либо текст последнего выполненного запроса, если сессия спит;
  • [login_name] — ну, вы поняли;
  • [wait_info] — очень интересный столбец. Он выводится в формате (Ax: Bms/Cms/Dms)E. А — это количество ожидающих задач на ресурсе E. B/C/D — это время ожидания в миллисекундах. Если ожидает освобождения ресурса всего одна сессия (как на скриншоте), будет показано ее время ожидания, если 2 сессии — их времена ожидания в формате B/C. Если же ожидают 3 и более — мы увидим минимальное, среднее и максимальное время ожидания на ЭТОМ ресурсе в формате B/C/D;
  • [CPU] — для активного запроса — суммарное время ЦП, затраченное этим запросом, для спящей сессии — суммарное время ЦП за «всю жизнь» этой сессии;
  • [tempdb_allocations] — для активного запроса — это количество операций записи в TempDB за время выполнения запроса; для спящей сессии — суммарное количество записей в TempDB за все время жизни сессии;
  • [tempdb_current] — для активного запроса — количество страниц в TempDB, выделенных для этого запроса; для спящей сессии — суммарное количество страниц в TempDB, выделенных за все время жизни сессии;
  • [blocking_session_id] — если вдруг мы кем-то заблокированы, покажет spid (session_id) того, кем мы заблокированы;
  • [reads] — для активного запроса — количество логических чтений выполненных при выполнении этого запроса; для спящей сессии — количество прочитанных страниц за все время жизни этой сессии;
  • [writes] — все тоже самое, но про запись;
  • [physical_reads] — для активного запроса — количество физических чтений, выполненных при выполнении этого запроса; для спящей сессии — традиционно, суммарное количество физических чтений за все время жизни сессии;
  • [used_memory] — для активного запроса — количество восьмикилобайтовых страниц, использованных при выполнении этого запроса; для спящей сессии — сколько суммарно страниц памяти выделялось ей за все ее время жизни;
  • [status] — статус сессии — выполняется, спит и т.д.;
  • [open_tran_count] — показывает количество транзакций открытых этой сессией;
  • [percent_complete] — показывает, если есть такая возможность, процесс выполнения операции (например, 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 = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]' — а вдруг вы не хотите видеть информацию о tempDB в выводе sp_whoIsActive? С помощью этого параметра можно управлять тем, что она выводит;
  • @destination_table = 'table_name' — попытается вставить результат выполнения записать в таблицу, но не будет проверять существует ли эта таблица и хватает ли прав на вставку в нее.

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

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

А какие ещё способы мониторинга SQL Server знаете вы?

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube

Запись опубликована в рубрике Мониторинг с метками , . Добавьте в закладки постоянную ссылку.

Один комментарий на «SQL Server. Мониторинг»

  1. Аватар Κирилл Ρябов говорит:

    Data Collector, XEvents, Журнал SQL Server

Добавить комментарий

Войти с помощью: