Простой мониторинг активности SQL Server. Кто активен?

О чем я вообще?

Любому администратору баз данных, наверняка, приходилось сталкиваться с тем, что все работает медленно, или не работает вообще. Первое, что при этом нужно выяснить — это что вообще происходит на 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, увы, не влазит в один экран, поэтому вот текстовое описание вывода хранимой процедуры, вызываемой без параметров.

• [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, увы, запустить не получится.