Aнализ времени ожидания блокировок | Ожидания SQL Server

Содержание:
1. Обзор блокировок и кратковременных блокировок
2. Ожидания SQL Server (Вы читаете данный раздел);
3. Учет блокировок и кратковременных блокировок в DMF статистики работы индекса.

В конечном итоге блокировки и кратковременные блокировки обеспечивают согласованность и упорядоченность в базе данных и объектах памяти, связанных с экземпляром SQL Server. Они являются регулировщиками трафика. Однако за поддерживаемый ими порядок приходится расплачиваться снижением производительности. В результате блокировок увеличивается время ответа для завершения транзакций. Огромное число факторов влияет на общую производительность базы данных: внутренние факторы (такие, как архитектура схемы, индексация, программный код хранимых процедур, структура запросов и уровень изоляции транзакций) и внешние (задержка сети, программный код приложения, аппаратные средства и т. д). Каждый раз, когда SQL Server приходится ожидать освобождения необходимого ресурса, чтобы выполнить запрос, сохраняются сведения о длительности ожидания, базовом ожидаемом ресурсе и объекте, на котором ожидалось освобождение ресурса. Эта информация называется статистикой ожидания SQL Server и может быть получена в динамических административных представлениях (DMV) sys.dm_os_wait_stats и sys.dm_os_waiting_tasks, а также sys.dm_exec_session_wait_stats (новшество SQL Server 2016). В каждом из этих DMV собрана разная информация об ожидании.

• dm_os_wait_stats: репозиторий для информации об ожидании, собранной после перезапуска службы SQL Server или ручного удаления статистики ожидания. Данные группируются по типу ожидания. В случае с крупными базами, для поддержания его работы необходимы достаточно внушительные вычислительные мощности — для их обеспечения подойдет, к примеру, аренда сервера в европе.
• dm_os_waiting_tasks: предоставляет информацию о задачах, в настоящее время ожидающих ресурсов. Данные группируются по типу ожидания.
• dm_exec_session_wait_stats: новейшее DMV для ожиданий. Статистика ожиданий накапливается в форме, аналогичной dmoswaitstats, но также добавляется идентификатор session id в качестве уровня агрегирования, чтобы вы могли оценить типы ожидания, встречающиеся в каждом активном сеансе на экземпляре SQL.

В данной статье мы сосредоточимся на времени, когда запрос статистики ожидания (см. код ниже) может принести самые полезные результаты с последующим запросом к dm_db_index_ operational_stats (см. экран выше). Более подробно о статистике ожидания будет рассказано в следующей статье.

Существует три вида ожиданий, связанных с блокировками и кратковременными блокировками: ожидания блокировок с префиксом «LCK_», ожидания блокировок ввода-вывода с префиксом «PAGEIOLATCH_» и ожидания кратковременных блокировок с префиксом «PAGELATCH_». Ожидания блокировок и кратковременных блокировок содержат тип блокировки в имени типа блокировки.

В случае с экземпляром SQL Server мы обнаруживаем высокий уровень ожиданий, связанных с блокировками и кратковременными блокировками, что можно наблюдать на примере показанных выше выделенных результатов. Однако это лишь часть картины. Она позволяет точнее выявить проблемы производительности, возникающие из-за блокировок и кратковременных блокировок, но не дает сведений об их источнике. Сделать это поможет sys.dm_db_index_ operational_stats.