Sql монитор активности как запустить

Обновлено: 17.05.2024

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

Начиная с SQL Server 2005, мы получили новую возможность получать информацию о состоянии сервера - Dynamic Management Views. MSDN говорит так: "Динамические административные представления и функции возвращают данные о состоянии сервера, которые могут использоваться для контроля исправности экземпляра сервера, диагностики проблем и настройки производительности.".
И действительно, в 2005-м SQL Server'е есть набор представлений, связанных с выполнением запросов в текущий момент (впрочем, для просмотра "истории" тоже есть представления): вот они. И их количество, от версии к версии продолжает увеличиваться!
Наверняка, у мастистых администраторов есть наготове куча скриптов, позволяющих получить информацию о текущем состоянии сервера, но что делать, если опыта работы с DMV еще нет, а проблемы уже есть?

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).

И чО? Это все?

@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 и баз данных позволяет получить информацию, необходимую для диагностики и устранения неполадок производительности SQL Server, а также для тонкой настройки SQL Server. Оптимальную производительность не легко определить и установить, так как, существует компромисс между несколькими программными и аппаратными факторами. Мониторинг также зависит от вашей среды, бизнес-требований и политики компании.

Медленно работающий отчёт в небольшой компании может быть допустим, но на крупном предприятии любое падение производительности в системе отчётности может затронуть большое количество пользователей, тем самым повлиять на бизнес. Подобные проблемы, как правило, неприемлемы и должен быть устранены, как можно быстрее.

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

Какие счётчики SQL Server необходимо отслеживать?

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

These commonly monitored SQL Server performance metrics are memory and processor usage, network traffic, and disk activity.

Среди основных показателей эффективности SQL Server необходимо контролировать память, процессор, сетевой трафик и активность дисковой подсистемы. Кроме того, параметры SQL Server и операционной системы Windows.

Основные счётчики: processor time (процессорное время), processor queue length (длина очереди), page reads and writes per second (чтение и запись страниц в секунду), page life expectancy (время жизни страницы), target and total server memory (память), buffer cache hit ratio (буферный кэш), batch requests (число команд), processor utilization (утилизация процессора), lazy writes (сбрасывание страниц на диск), network usage (сеть), paging (подкачка), user connections (подключения) и т.д.

В SQL Server есть два встроенных инструмента мониторинга: Activity Monitor и Data Collector.

Activity Monitor

Activity Monitor отслеживает наиболее важные показатели эффективности SQL Server. Чтобы получить их, он выполняет запросы к экземпляру SQL Server каждые 10 секунд. Мониторинг осуществляется только когда инструмент открыт, поэтому побочный эффект от его использования минимальный.

Все метрики показаны на 5 разных панелях: Overview (Обзор), Processes (Процессы), Resource Waits (Ожидания ресурсов), Data File I/O (Ввод/вывод файлов данных), и Recent Expensive Queries (последние затратные запросы).

Overview (Общие сведения). Содержит графики Processor Time (Процессорное время), Number of Waiting Tasks (Количество ожидающих задач), Database I/O (Ввод-вывод в базе данных) и Number of Batch Requests/second (Количество пакетных запросов в секунду).

Processes (Процессы). Показывает все работающие в данный момент процессы во всех БД экземпляра SQL Server. Выводится информация по следующим полям: Логин, название приложения и хост, состояние задач и команд, время ожидания и т.д. Информацию можно отфильтровать по любому из полей таблицы.

Кроме того, есть удобная возможность для администраторов в контекстном меню вызвать SQL Server Profiler и выполнить более детальный анализ любого процесса.

Resource Waits (Ожидание ресурсов). Показывает ожидания для различных ресурсов: памяти, компиляции, сети и т.д.

Можно увидеть время ожидания (сколько времени задача SQL Server ожидала ресурсов от системы), последнее, общее и среднее.

Data File I/O (Ввод-вывод в файлах данных). Выводит информацию о всех файлах БД: MDF, NDF и LDF. Их название, полный путь, активность по чтению и записи и время отклика.

Recent Expensive Queries (Последние ресурсоемкие запросы). Показывает запросы, выполненные в последние 30 секунд, которые используются наибольшее количество аппаратных ресурсов: процессора, памяти, диска и сети. Контекстное меню позволяет открыть запрос в закладке запроса SQL Server Management Studio и просомтреть его план выполнения.

Как работать с Activity Monitor

Activity Monitor можно открыть в SQL Server Management Studio toolbar используя иконку Activity Monitor на панели, сочетанием клавиш Ctrl+Alt+A или через контекстное меню в Object Explorer.

Как уже было сказано выше, Activity Monitor отслеживает только заранее определенный набор наиболее важных показателей производительности SQL Server. Дополнительных параметров указать нельзя, нельзя и удалить что-то из показателей. Мониторинг возможен только в режиме реального времени. Нет возможности сохранить результаты мониторинга для последующего анализа. Таким образом Activity Monitor – это полезный инструмент для беглого анализа и поиска неисправностей, но он не подходит для детального сбора информации, т.к. в нём отсутствует возможность гибкой настройки счётчиков производительности, указания пороговых значений и нет возможности сбора исторических данных.

Data Collector

Data Collector – это ещё один встроенный в SQL Server Management Studio инструмент мониторинга. Он собирает метрики производительности экземпляров SQL Server и сохраняет их в локальный репозиторий, так что они могут быть использованы для последующего анализа. Он использует хранилище данных, SQL Server Agent, и Integration Services.

В отличии от Activity Monitor, Data Collector позволяет задать метрики, которые вы будите отслеживать. Кроме того, есть три встроенных коллекции счётчиков с самыми часто используемыми при анализе производительности. Расширить или создать свои коллекции можно, как на T-SQL, так и с помощью встроенного API.

Как работать с Data Collector

Убедитесь, что SQL Server Integration Services установлен, а SQL Server Agent, Management Data Warehouse и Data Collection включены.

В Object Explorer среды SQL Server Management Studio раскройте папку Management.
В контекстном меню Data Collection выберите Configure Management Data Warehouse.

Укажите Set up data collection.

Нажмите далее (Next).

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

Нажмите Next, проверьте все параметры и затем Finish.

Data Collection имеет три предустановленных набора мониторинга в папке System Data Collection Sets (Object Explorer -> Management -> Data Collection): Disk Usage, Query Statistics и Server Activity. Кроме того, они имеют встроенные отчёты.

Набор Disk Usage показывает информацию по файлам данных (MDF и NDF) и файлам лога транзакций (LDF). Статистику ввода/вывода.

В контекстном меню Data Collection имеется отчёт Disk Usage built-in, который показывает размер файлов, их прирост, в том числе и ежедневный.

Набор Query Statistics показывает статистику, активность и планы 10 самых «тяжёлых» запросов.

Набор Server Activity показывает общую нагрузку на процессор, память, сеть и дисковую подсистему. В отчётах можно увидеть активность экземпляра SQL Server и операционной системы, ЦПУ, память, сеть, ввод\вывод.

Data Collection мощный инструмент, который необходимо сконфигурировать, прежде чем начать использовать. Он имеет три встроенных набора для мониторинга и адекватные отчёты. К сожалению, нет мастера для настройки своих показателей мониторинга и это необходимо делать с помощью кода.

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

В отличие от Activity Monitor, нет возможности просматривать графики в реальном времени, но собранная информация может храниться на протяжении нескольких дней. В базовом наборе представлены только основные показатели, а для расширения необходимы знания средств разработки.

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

23.03.2020

SQL Server

комментария 3

В этой статье мы рассмотрим популярные инструменты, T-SQL запросы и скрипты для обнаружения и решения различных возможных проблем с производительностью SQL Server. Эта статья поможет вам разобраться, когда вашему SQL Server недостаточно ресурсов (памяти, CPU, IOPs дисков), найти блокировки, выявить медленные запросы. Посмотрим какие есть встроенные инструменты и бесплатные сторонние скрипты и утилиты для анализа состояния Microsoft SQL Server.

Инструменты для диагностики SQL Server

Если вы правильно диагностировали проблему, то половина работы уже сделана. Рассмотрим какие инструменты обычно используются системным администратором для диагностики различных проблем в SQL Server:

Обнаружение и решение проблем с производительностью SQL Server

Самой распространенной проблемой с которой сталкивается системный администратор, работающий с SQL Server, это жалобы пользователей на производительность запросов и самого сервера: “тормозит”, “долго выполняется запрос“, и так далее.

Прежде всего нужно убедиться, что серверу хватает ресурсов. Рассмотрим, как в SQL Server быстро проанализировать использование памяти, CPU, дисков и наличие блокировок.

Анализ использования оперативной памяти SQL Server

Для начала нужно определить сколько памяти доступно SQL Server. Для этого запустите SSMS (SQL Server Management Studio), зайдите на сервер и зайдите в свойства сервера (ПКМ по названию сервера в Обозревателе объектов).

Сам по себе доступный объём RAM вам ничего не скажет. Нужно сравнить это число с используемой памятью в Диспетчере Задач и самим движком SQL Server с помощью DMV.

В Диспетчере задач, во вкладке Подробности, найдите sqlservr.exe и посмотрите сколько оперативной памяти использует этот процесс.

Если на сервере, например, 128 GB оперативной памяти, а процесс sqlservr.exe использует 60 GB и ограничений по RAM у SQL Server нет, то оперативной памяти вам хватает.
Если SQL Server использует 80-90% RAM от заданной или максимальной, то в таком случае нужно смотреть DMV. Имейте в виду, что sqlservr.exe не сможет использовать всю оперативную память. Если на сервере 128 GB, то sqlservr.exe может использовать только 80-90% (100-110 GB), так как остальная память резервируется для операционной системы.

Имейте в виду, что процесс SQL Server’a не отдаёт оперативную память обратно в систему. Например, ваш SQL Server обычно использует 20 GB памяти, но при месячном отчете он увеличивает потребление до 100 GB, и даже когда вычисление отчета закончится и сервер будет работать в прежнем режиме, процесс SQL Server’a всё равно будет использовать 100 GB до перезагрузки службы.

Даже если вы уверены, что оперативной памяти серверу хватает, не будет лишним точно знать объём потребляемой RAM.

Узнать реальное использование RAM можно с помощью Dynamic Management Views. DMV это административные вьюверы (представления). С помощью DMV можно диагностировать практически любую проблему в SQL Server.

Посмотрим sys.dm_os_sys_memory, для удобства используем запрос:

Рассмотрим каждый выводимый параметр:

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

Если Вам нужно убедиться, что серверу хватает RAM, вы можете смотреть только на поля system_low_memory_signal_state, system_high_memory_signal_state и system_memory_state_desc. Если system_low_memory_signal_state = 1, то серверу явно не хватает оперативной памяти.

Загрузка процессора в SQL Server

Нагрузку на процессор определить проще, так как это можно сделать в Диспетчере задач. Чтобы узнать текущую нагрузку на процессор, найдите в Диспетчере задач процесс sqlservr.exe

Если вы хотите узнать нагрузку за прошедшее время, можно воспользоваться запросом:

Не забудьте поменять @lastNMin на нужное вам число в минутах.

В результате мы получим поминутную статистику использования процессора.

Анализ нагрузки на диск SQL Server

Посмотрим на загрузку дисков в операционной системе. Для этого запустите resmon.exe.

Нам нужна вкладка Disk. В секции Disk Activity отображаются файлы, к которым идёт обращение, и их скорость read/write на текущий момент. Отфильтруйте эту секцию по Total (кликните на Total). На самом верху будут файлы, которые на данный момент максимально используют диск. В случае с SQL Server это может быть полезно чтобы определить какая база больше всего нагружает диск на текущий момент.

В секции Storage отображаются все диски в системе. В этой секции нам нужны 2 параметра – Active Time и Disk Queue. Active Time в процентах отображает нагрузку на диск, то есть если вы видите на диске C:\ Active Time равный 90, это значит что ресурс чтения/записи диска на текущий момент используется на 90%. Столбец Disk Queue отображает очередь обращений к диску, и если значение очереди не равно нулю, то диск загружен на 100% и не справляется с нагрузкой. Так же если Active Time близок к 100, то диск используется практически на пределе своих возможностей по скорости.

Просмотр блокировок в SQL Server

После того как мы убедились, что серверу хватает ресурсов, можно переходить к просмотру блокировок.

Блокировки можно посмотреть через Activity Monitor в SSMS, но мы воспользуемся T-SQL, так как этот вариант более удобен и нагляден. Выполняем запрос:

Этот запрос возвращает список блокировок в виде дерева. Это удобно в работе, так как обычно, если возникает одна блокировка, она провоцирует за собой другие. Аналогично в Activity Monitor или в выводе sp_who2 можно увидеть поле “Blocked By”.

Если запрос ничего не вернул, то блокировок нет.

Если запрос вернул какие-то данные, то нужно проанализировать цепочку.

HEAD значит что этот запрос является причиной всех остальных блокировок ниже по дереву. 64 – это идентификатор процесса (SPID). После этого пишется тело запроса, который вызвал блокировку. Если у вас хватает ресурсов сервера, то скорее всего дело в самом запросе и во взаимном обращении к каким-то объектам. Для того чтобы сказать точнее, нужно анализировать конкретный запрос, который вызвал блокировку.

Политики SQL Server

Даже когда у вас всё работает хорошо и жалоб нет, на самом деле может быть много проблем, которые всплывут позже. Для этого в SQL Server есть политики.

Политика в SQL Server это, грубо говоря, проверка правила на соответствие заданному значению. Например, с помощью политик вы можете убедиться, что на всех базах на сервере выключен Auto Shrink. Рассмотрим пример импорта и выполнения политики

В SSMS, подключитесь к серверу, на котором хотите выполнять политики (Management -> раздел Policy Management).

Импортируем файл Database Auto Shrink.xml. Жмём Evaluate

На экземпляре node1 две базы данных, test1 и test2. На test2 включен autoshrink. Посмотрим детали.

Политика определила включенный параметр AutoShrink, в описании обычно пишется объяснения к правилам. В данном случае дается объяснение почему auto shrink лучше отключать.

Политики могут выполняться либо по расписанию, либо по требованию (разово). Результаты выполнения политики можно посмотреть в журнале политик.

При установке SQL Server нужно выбирать только используемые компоненты СУБД, и указывать настройки в соответствии с конфигурацией “железа” вашего сервера. Всегда следите чтобы серверу хватало ресурсов, и чтобы на сервере не было блокировок

Самым мощным инструментом для диагностики SQL Server является T-SQL и DMV. Так же рекомендуется построить круглосуточный мониторинг над SQL Server и над обслуживающей его инфраструктурой для обнаружения всех возможных проблем.

Читайте также: