Любому администратору баз данных, наверняка, приходилось сталкиваться с тем, что все работает медленно, или не работает вообще. Первое, что при этом нужно выяснить — это что вообще происходит на 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, увы, не влазит в один экран, поэтому вот текстовое описание вывода хранимой процедуры, вызываемой без параметров.
- [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 допустимо использование «%»;@filter_type
= 'database', @filter = '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 = '[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. Мониторинг»