Простой мониторинг активности 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, увы, запустить не получится.