В первой статье серии, посвященной функции динамического управления dm_db_index_operational_stats, «Динамическое управление сбором статистики работы индекса» (опубликованной в Windows IT Pro/RE № 8 за 2016 год), я рассказал о том, как различное поведение запроса отражается на результатах, возвращаемых запросами, которые направляются к функции динамического управления (DMF). Кроме того, я представил типовой сценарий для запроса полной ширины столбцов из этой DMF и сравнения информации, которую можно получить из данного объекта, с информацией из родственного динамического административного представления (DMV): sys.dm_db_index_usage_stats. Во второй статье серии речь пойдет о том, как идентифицировать объекты, вызывающие больше всего проблем со временем ожидания блокировок (lock) и кратковременных блокировок (latch). Мы выясним, в каких случаях существует достаточно оснований, чтобы предпринять проверку на наличие конкуренции блокировок, и какие запросы следует выполнить, чтобы обнаружить основных виновников. В заключение я дам общие рекомендации по использованию собранной информации для устранения блокировок, мешающих организовать высокопроизводительную среду SQL Server.

Полностью основные сведения о функции sys.dm_db_index_operational_stats приведены в первой статье данной серии. Напоминание о том, как выполнять запрос к sys.dm_db_index_operational_stats и столбцам, вы можете найти в листинге 1.

Пользователям, незнакомым с параметрами шаблона в среде SQL Server Management Studio, программная конструкция может показаться странной. Тем, кто не представляет себе соответствующую концепцию параметров шаблона, рекомендуется познакомиться с первой статьей серии. Возможно, у вас нет времени читать длинную статью, поэтому приведу краткий вывод из нее: используйте комбинацию клавиш Ctl+Shift+M в среде SQL Server Management Studio (SSMS), чтобы подставить конкретные значения вместо заполнителей в синтаксисе вида <некоторый_параметр, описание, значение_по_умолчанию>.

Если оставить команду без изменений, она предоставит результаты, охватывающие все объекты (индексы и кучи) и все связанные индексы без учета ограничений до определенной секции. Конечно, таким образом вы получаете огромное количество информации, но ее ценность невелика из-за отсутствия контекста для результатов. Поэтому я всегда объединяю DMO индексации с другими системными представлениями, обеспечивающими контекст для результатов, а также фильтрацию возвращенных строк и столбцов, которые мне нужно увидеть. Системные представления для получения контекста следующие:

  • sys.indexes — предоставляет информацию о ваших индексах SQL Server на уровне базы данных, охватывая имя, тип индекса (кластеризованный, некластеризованный), уникальность и т. д.
  • sys.objects — можно использовать системную функцию OBJECT_NAME (object_id), чтобы возвратить имя таблицы или представления, связанного с object_id, выдаваемого sys.dm_db_index_operational_stats, но также необходимо фильтровать результаты, так как я заинтересован только в объектах пользователя, а не в системных таблицах и представлениях, используемых внутри SQL Server. Для этого требуется доступ к столбцу is_ms_shipped в sys.objects. При этом я могу возвратить имя объекта (имя столбца) и тип объекта (type_desc).

Таким образом мы получаем базовую структуру как в листинге 2.

Конечно, вы захотите сузить диапазон столбцов до sys.dm_db_index_operational_stats, а также предоставить некоторый уровень фильтрации путем использования предикатов поиска через предложение WHERE, но эта конструкция — хороший фундамент для дальнейшего роста. Дополнительные сведения о полном диапазоне столбцов в sys.dm_db_index_operational_stats и их предназначении можно найти в первой статье серии. А здесь мы рассмотрим sys.dm_db_index_operational_stats, блокировки и кратковременные блокировки.

Обзор блокировок и кратковременных блокировок

Один из самых важных выводов, которые можно сделать из sys.dm_db_index_operational_stats, касается того, какие объекты больше всего страдают от ожидания блокировок и кратковременных блокировок. Прежде чем продолжить рассказ о том, как идентифицировать причины снижения производительности, рассмотрим принципы действия блокировок и кратковременных блокировок в Microsoft SQL Server.

В реляционных базах данных блокировкам принадлежит огромная роль в обеспечении соответствия транзакций условиям атомарности, целостности данных, изолированности транзакций и надежности (ACID).

  • Атомарность: транзакции должны завершить все действия или вернуться в исходное состояние.
  • Целостность данных: поведение транзакций всегда единообразно.
  • Изолированность транзакций: транзакции защищены от внешнего влияния до тех пор, пока они не будут завершены.
  • Надежность: реляционная система управления базами данных ведет запись незафиксированных (то есть незавершенных) транзакций для восстановления в случае аварии.

Эти требования удовлетворяются с помощью сложных процессов блокировки, гарантирующих, что одновременные запросы от пользователей не взаимодействуют с результатами любых других запросов. Блокировки применяются к строкам и страницам, участвующим в открытых транзакциях, и снимаются после завершения или отмены этих транзакций. Блокировки также применяются к объектам, участвующим в изменениях схемы для этих объектов. Существует сложный набор правил и логики, определяющий поведение блокировок в Microsoft SQL Server, а также различные типы режимов блокировки, кратко описанные ниже.

  • Общие Shared (S) блокировки: участвуют в операциях только для чтения, которые не изменяют данные.
  • Блокировки изменения Update (U): используются в транзакциях UPDATE, чтобы предотвратить попытки нескольких транзакций обновить одну и ту же строку одновременно.
  • Монопольные Exclusive (X) блокировки: ассоциируются с операциями, которые изменяют данные через запросы INSERT, UPDATE или DELETE, чтобы одна транзакция не пыталась изменить строку одновременно с другой транзакцией.
  • Блокировки схемы Schema (Sch): назначаются, когда происходит изменение определения объекта, например если добавляется столбец к таблице.
  • Блокировки намерения Intent (I): устанавливаются принудительно, чтобы определить «старшинство» блокировок. Блокировки намерения информируют внутренний механизм о том, что транзакция вводится в очередь, чтобы в конечном итоге применить следующую блокировку типа IS (коллективная блокировка намерения), IX (монопольная блокировка намерения) или SIX (коллективная, с монопольной блокировкой намерения).
  • Блокировки массового обновления Bulk Update (BU): используются в определенных условиях, когда в ходе операции применяется массовое копирование и предоставляются указания блокировки.
  • Блокировки диапазона ключа: вступают в действие, когда используется наиболее строгий уровень изоляции транзакций (сериализуемый). Этот тип блокировки защищает диапазон строк, а не единственную строку, участвующую в транзакции в качестве целевой.

Иногда поведение блокировки может меняться в зависимости от уровня изоляции, используемого для транзакции в Microsoft SQL Server. Уровень изоляции определяет степень, в которой транзакции изолированы друг от друга; например, какие типы блокировок мешают применять другие блокировки. Изоляция транзакций — гораздо более широкая тема, и не раскрывается здесь во всей полноте. Дополнительные сведения об уровнях изоляции в Microsoft SQL Server можно получить из официальной документации по адресу: http://msdn.microsoft.com/en-us/library/ms173763.aspx.

Кратковременные блокировки часто представляют как «блокировки SQL для памяти». В общем смысле это верно, но такое описание нельзя назвать точным. Кратковременные блокировки похожи на стандартные блокировки в том, что они обеспечивают управляемый доступ к строкам, страницам, представлениям и таблицам базы данных, наряду с другими объектами. Кратковременные блокировки, с другой стороны, предоставляют управляемый доступ к объектам, размещаемым в памяти SQL Server. Объекты, размещаемые в памяти, делятся на два класса: буферные объекты и небуферные объекты. В отличие от блокировок, кратковременные блокировки применяются и снимаются по мере необходимости и не сохраняются в ходе выполнения транзакции; несколько кратковременных блокировок могут оцениваться на одной странице.

Существуют различные режимы кратковременных блокировок.

  • Кратковременная блокировка удаления Destroy (DT) применяется для удаления и исключения буфера из кэша.
  • Монопольная кратковременная блокировка Exclusive (EX) обеспечивает монопольный доступ к записываемой странице. Не допускает других кратковременных блокировок на той же странице.
  • Кратковременная блокировка сохранения Keep (KP) предназначена для целей, схожих с блокировкой намерения: учет порядка блокировок и размещение блокировки в буферном кэше при применении другой блокировки.
  • Общая кратковременная блокировка Shared (SH) применяется, когда предоставляются права на чтение страницы.
  • Кратковременная блокировка обновления Update (UP) аналогична, но не столь строга, как монопольная кратковременная блокировка, так как разрешает операции чтения страницы, но запрещает запись.

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

Ожидания SQL Server

В конечном итоге блокировки и кратковременные блокировки обеспечивают согласованность и упорядоченность в базе данных и объектах памяти, связанных с экземпляром 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 для ожиданий. Статистика ожиданий накапливается в форме, аналогичной dm_os_wait_stats, но также добавляется идентификатор session_id в качестве уровня агрегирования, чтобы вы могли оценить типы ожидания, встречающиеся в каждом активном сеансе на экземпляре SQL.

В данной статье мы сосредоточимся на времени, когда запрос статистики ожидания (см. листинг 3) может принести самые полезные результаты с последующим запросом к dm_db_index_operational_stats (см. экран 1). Более подробно о статистике ожидания будет рассказано в следующей статье. Упомянутый выше запрос описан в статье «Полный запрос статистики ожидания SQL 2005-2016» (опубликованной в Windows IT Pro/RE № 2 за 2016 год).

 

Результаты запроса статистики ожидания
Экран 1. Результаты запроса статистики ожидания

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

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

Учет блокировок и кратковременных блокировок в DMF статистики работы индекса

Мы рассмотрим следующие столбцы dm_db_index_operational_stats, связанные с блокировками и кратковременными блокировками в Microsoft SQL Server:

  • row_lock_count;
  • row_lock_wait_count;
  • row_lock_wait_in_ms;
  • page_lock_count;
  • page_lock_wait_count;
  • page_lock_wait_in_ms;
  • index_lock_promotion_attempt_count;
  • index_lock_promotion_count;
  • page_latch_wait_count;
  • page_latch_wait_in_ms;
  • page_io_latch_wait_count;
  • page_io_latch_wait_in_ms.

Выявление индексов, связанных с длительными ожиданиями блокировок и кратковременных блокировок, происходит по простому протоколу.

  1. Если выяснилось, что ожидания блокировок и кратковременных блокировок — одни из самых длительных по средней величине, то перейти к диагностике баз данных, участвующих в блокировках.
  2. Перейти к деталям объектов и индексов, участвующих в наиболее часто применяемых блокировках.

Шаг 1. Определение баз данных, участвующих в ожиданиях блокировок и кратковременных блокировок

Запрос в листинге 4 изолирует базы данных, объекты и индексы, с которыми связано больше всего блокировок. В этом примере встречаются в основном ожидания краткосрочных блокировок, поэтому мы используем краткосрочную блокировку ввода-вывода из трех приведенных ниже вариантов (в зависимости от самого большого типа ожидания для первоначального запроса статистики ожиданий).

Обратите внимание, что если вы заинтересованы в деталях только определенных столбцов, связанных с нужными ожиданиями, можно сократить список столбцов (см. листинг 5).

Использование последнего из трех запросов показывает, что я сосредоточился на идентификации объектов и индексов, задействованных в первом наборе столбцов, а также метрики для всех столбцов блокировок и кратковременных блокировок. Я хочу убедиться, что результаты ограничены исключительно строками с нужными блокировками, а затем, поскольку наиболее распространенным типом ожидания являются ожидания кратковременных блокировок, выполняется сортировка в порядке убывания по времени кратковременной блокировки. Полученные результаты показаны на экране 2.

 

Получение метрик для всех столбцов блокировок и кратковременных блокировок
Экран 2. Получение метрик для всех столбцов блокировок и кратковременных блокировок

Меня могут спросить, почему я выполняю первоначальное обнаружение просто для сужения результатов до базы данных. Это сделано потому, что для идентификации участвующих основных индексов я должен иметь возможность присоединить dm_db_index_operational_stats к sys.indexes. dm_db_index_operational_stats — значение уровня сервера, поэтому результаты пересекают границы баз данных; независимо от того, на какой базе данных выполняется запрос к DMF, результаты получаются одинаковые. Но того же нельзя сказать о запросах к sys.indexes. Это системное представление, уникальное для каждой базы данных. Возвращаются только результаты для индексов в базе данных, из которой вызвано представление. Чтобы объединить dm_db_index_operational_stats и sys.indexes, необходимо полностью определить sys.indexes с именем базы данных. И index_id, и object_id объединяют столбцы между этими объектами и не уникальны во всех базах данных. Это означает, что необходимо сначала идентифицировать базу данных, чтобы перейти к шагу 2, на котором мы получаем подробные сведения об индексе благодаря знанию имени базы данных.

Шаг 2. Обнаружение сведений об индексе для решения проблем блокировок и кратковременных блокировок

В зависимости от типа ожидания блокировок и кратковременных блокировок мы запускаем приведенный в листинге 6 запрос с одним из трех вариантов сортировки, чтобы получить дополнительные сведения об индексах (см. экран 3). Продолжим рассмотрение кратковременных блокировок — в частности, кратковременных блокировок ввода-вывода, с учетом того, что будем использовать любые другие диагностические запросы, относящиеся к ожиданиям блокировок или кратковременных блокировок, в зависимости от наиболее распространенного типа ожидания при анализе состояний ожидания для настройки производительности.

 

Дополнительные сведения об индексах
Экран 3. Дополнительные сведения об индексах

На данном этапе мы выяснили, что индекс lifeboat..Database_Files_History.PK_Database_Files_History_1 виновен в значительной доле ожиданий кратковременных блокировок ввода-вывода. Теперь необходима точная диагностика на уровне индекса. С помощью dm_db_index_operational_stats, благодаря применению ожиданий как инструмента настройки производительности, мы прошли путь от понимания, что ожидания кратковременной блокировки ввода-вывода являются главной проблемой, до выяснения, какие именно объекты порождают ее.

Выявление основных типов ожиданий — только часть задачи. Определение объектов, вносящих вклад в эти ожидания, с последующей целевой настройкой этих объектов — следующий, решающий шаг процесса настройки производительности. Благодаря информации, получаемой от dm_db_index_operational_stats, мы можем без труда определить вызывающие затруднения таблицы и индексы.

Листинг 1. запрос к sys.dm_db_index_operational_stats и столбцам
SELECT *
FROM sys.dm_db_index_operational_stats
(
        DB_ID(),
        ,
        ,
        
);
Листинг 2. Базовая структура запроса
SELECT O.name AS [object_name]
        , O.type_desc AS object_type
        , I.name AS index_name
        , I.type_desc AS index_type
        , ixO.*
FROM sys.dm_db_index_operational_stats
        (
                DB_ID(),
                ,
                ,
                
        ) AS ixO
        INNER JOIN sys.indexes I
                ON ixO.object_id = I.object_id
                        AND ixO.index_id = I.index_id
        INNER JOIN sys.objects AS O
                ON O.object_id = ixO.object_id
WHERE O.is_ms_shipped = 0;
Листинг 3. Запрос статистики ожидания
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#dm_os_wait_stats','U') IS NOT NULL
        DROP TABLE #dm_os_wait_stats;
GO

SELECT wait_type
    , (wait_time_ms - signal_wait_time_ms) / 1000. AS wait_time_s
    , waiting_tasks_count
    , CASE waiting_tasks_count
            WHEN 0 THEN 0
            ELSE (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count
    END AS avg_wait_ms
    , 100. * (wait_time_ms - signal_wait_time_ms) / SUM
    (wait_time_ms - signal_wait_time_ms) OVER ( ) AS pct
    , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
INTO #dm_os_wait_stats
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
(
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',
        N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER',
        N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
        N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',
        N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL',
        N'DISPATCHER_QUEUE_SEMAPHORE',
        N’EXECSYNC’, N’FSAGENT’, N’FT_IFTS_SCHEDULER_IDLE_WAIT’,
        N’FT_IFTSHC_MUTEX’,
        N’HADR_CLUSAPI_CALL’, N’HADR_FILESTREAM_IOMGR_
        IOCOMPLETION’, N’HADR_LOGCAPTURE_WAIT’,
        N’HADR_NOTIFICATION_DEQUEUE’, N’HADR_TIMER_TASK’,
        N’HADR_WORK_QUEUE’,
        N’KSOURCE_WAKEUP’, N’LAZYWRITER_SLEEP’,
        N’LOGMGR_QUEUE’,
        N’MEMORY_ALLOCATION_EXT’, N’ONDEMAND_TASK_QUEUE’,
        N’PREEMPTIVE_OS_LIBRARYOPS’, N’PREEMPTIVE_OS_COMOPS’,
        N’PREEMPTIVE_OS_CRYPTOPS’,
        N’PREEMPTIVE_OS_PIPEOPS’, N’PREEMPTIVE_OS_
        AUTHENTICATIONOPS’,
        N’PREEMPTIVE_OS_GENERICOPS’, N’PREEMPTIVE_OS_
        VERIFYTRUST’,
        N’PREEMPTIVE_OS_FILEOPS’, N’PREEMPTIVE_OS_DEVICEOPS’,
        N’PWAIT_ALL_COMPONENTS_INITIALIZED’, N’QDS_PERSIST_
        TASK_MAIN_LOOP_SLEEP’,
        N’QDS_ASYNC_QUEUE’,
        N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,
        N’REQUEST_FOR_DEADLOCK_SEARCH’,
        N’RESOURCE_QUEUE’, N’SERVER_IDLE_CHECK’,
        N’SLEEP_BPOOL_FLUSH’, N’SLEEP_DBSTARTUP’,
        N’SLEEP_DCOMSTARTUP’, N’SLEEP_MASTERDBREADY’,
        N’SLEEP_MASTERMDREADY’,
        N’SLEEP_MASTERUPGRADED’, N’SLEEP_MSDBSTARTUP’,
        N’SLEEP_SYSTEMTASK’, N’SLEEP_TASK’,
        N’SLEEP_TEMPDBSTARTUP’, N’SNI_HTTP_ACCEPT’,
        N’SP_SERVER_DIAGNOSTICS_SLEEP’,
        N’SQLTRACE_BUFFER_FLUSH’, N’SQLTRACE_INCREMENTAL_
        FLUSH_SLEEP’, N’SQLTRACE_WAIT_ENTRIES’,
        N’WAIT_FOR_RESULTS’, N’WAITFOR’,
        N’WAITFOR_TASKSHUTDOWN’, N’WAIT_XTP_HOST_WAIT’,
        N’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’,
        N’WAIT_XTP_CKPT_CLOSE’, N’XE_DISPATCHER_JOIN’,
        N’XE_DISPATCHER_WAIT’, N’XE_LIVE_TARGET_TVF’,
        N’XE_TIMER_EVENT’,
        N’PREEMPTIVE_SP_SERVER_DIAGNOSTICS’,
        N’PREEMPTIVE_HADR_LEASE_MECHANISM’, N’TRACEWRITE’,
        N’PREEMPTIVE_OS_WRITEFILEGATHER’,
        N’PREEMPTIVE_OS_LOOKUPACCOUNTSID’, N’CXPACKET’);

WITH Waits AS
    (
        SELECT wait_type
                        , wait_time_s
                        , waiting_tasks_count
                        , avg_wait_ms
                        , pct
                        , rn
        FROM #dm_os_wait_stats
    )
SELECT W1.wait_type
    , CAST(W1.wait_time_s AS DECIMAL(12, 1)) AS wait_time_s
    , W1.waiting_tasks_count
    , CAST(W1.avg_wait_ms AS DECIMAL(12, 1)) AS avg_wait_ms
    , CAST(W1.pct AS DECIMAL(12, 1)) AS pct
    , CAST(SUM(W2.pct) AS DECIMAL(12, 1)) AS running_pct
FROM Waits AS W1
        INNER JOIN Waits AS W2
                ON W2.rn <= W1.rn
GROUP BY W1.rn
    , W1.wait_type
    , W1.waiting_tasks_count
    , W1.avg_wait_ms
    , W1.wait_time_s
    , W1.pct
HAVING SUM(W2.pct) - W1.pct < 95 /* процентный порог */
ORDER BY W1.pct DESC;

IF OBJECT_ID('tempdb..#dm_os_wait_stats','U') IS NOT NULL
        DROP TABLE #dm_os_wait_stats;
GO

SET NOCOUNT OFF;
Листинг 4. Выбор объектов с наибольшим числом блокировок
SELECT TOP 10
        DB_NAME(database_id) AS database_name
     , OBJECT_NAME(object_id, database_id) AS table_name
     , index_id
     , partition_number
     , row_lock_count
     , row_lock_wait_in_ms
     , CASE row_lock_wait_count
                WHEN 0 THEN row_lock_wait_in_ms
                ELSE row_lock_wait_in_ms / row_lock_wait_count
        END AS avg_row_lock_wait_in_ms
     , page_lock_count
         , page_lock_wait_in_ms
     , CASE page_lock_count   
                WHEN 0 THEN page_lock_wait_in_ms
                ELSE page_lock_wait_in_ms / page_lock_count
        END AS avg_page_lock_wait_in_ms
     , page_latch_wait_count
     , page_latch_wait_in_ms
         , CASE page_latch_wait_count
                WHEN 0 THEN page_latch_wait_in_ms
                ELSE page_latch_wait_in_ms / page_latch_wait_count
        END AS avg_page_latch_wait_in_ms
     , page_io_latch_wait_count
     , page_io_latch_wait_in_ms
         , CASE page_io_latch_wait_count
                WHEN 0 THEN page_io_latch_wait_in_ms
                ELSE page_io_latch_wait_in_ms / page_io_latch_wait_count
        END AS avg_page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
WHERE row_lock_wait_in_ms > 0
                OR page_lock_wait_in_ms > 0
                OR page_latch_wait_in_ms > 0
                OR page_io_latch_wait_in_ms > 0
                AND database_id > 4
ORDER BY page_io_latch_wait_in_ms DESC;

/*
ИЛИ ВЫПОЛНИТЕ СОРТИРОВКУ ПО ОДНОМУ ИЗ СЛЕДУЮЩИХ КРИТЕРИЕВ:
page_latch_wait_in_ms DESC -- когда PAGELATCH_% самое большое ожидание
(row_lock_wait_in_ms + page_lock_wait_in_ms) DESC -- когда тип ожидания блокировки представляет самое большое ожидание
*/
Листинг 5. Укороченный запрос столбцов с блокировками
---LOCKING
SELECT TOP 3
        DB_NAME(database_id) AS database_name
     , OBJECT_NAME(object_id, database_id) AS table_name
     , index_id
     , partition_number
     , row_lock_count
     , row_lock_wait_in_ms
     , CASE row_lock_wait_count
                WHEN 0 THEN row_lock_wait_in_ms
                ELSE row_lock_wait_in_ms / row_lock_wait_count
        END AS avg_row_lock_wait_in_ms
     , page_lock_count
         , page_lock_wait_in_ms
     , CASE page_lock_count
                WHEN 0 THEN page_lock_wait_in_ms
                ELSE page_lock_wait_in_ms / page_lock_count
        END AS avg_page_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
ORDER BY (row_lock_wait_in_ms + page_lock_wait_in_ms) DESC;

--PAGELATCH
SELECT TOP 3
        DB_NAME(database_id) AS database_name
     , OBJECT_NAME(object_id, database_id) AS table_name
     , index_id
     , partition_number
     , page_latch_wait_count
     , page_latch_wait_in_ms
         , CASE page_latch_wait_count 
                WHEN 0 THEN page_latch_wait_in_ms
                ELSE page_latch_wait_in_ms / page_latch_wait_count
        END AS avg_page_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
ORDER BY page_latch_wait_in_ms DESC;

--PAGEIOLATCH
SELECT TOP 3
        DB_NAME(database_id) AS database_name
     , OBJECT_NAME(object_id, database_id) AS table_name
     , index_id
     , partition_number
     , page_io_latch_wait_count
     , page_io_latch_wait_in_ms
         , CASE page_io_latch_wait_count
                WHEN 0 THEN page_io_latch_wait_in_ms
                ELSE page_io_latch_wait_in_ms / page_io_latch_wait_count
        END AS avg_page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
ORDER BY page_io_latch_wait_in_ms DESC;
Листинг 6. Дальнейшее уточнение имен объектов
--ДАЛЬНЕЙШЕЕ УТОЧНЕНИЕ ИМЕН ОБЪЕКТОВ
SELECT TOP 4
        DB_NAME(ixOS.database_id) AS database_name
     , OBJECT_NAME(ixOS.object_id, ixOS.database_id) AS table_name
     , I.name AS index_name
         , I.type_desc AS index_type
     , ixOS.partition_number
     , ixOS.page_io_latch_wait_count
     , ixOS.page_io_latch_wait_in_ms
         , CASE ixOS.page_io_latch_wait_count
                WHEN 0 THEN ixOS.page_io_latch_wait_in_ms
                ELSE ixOS.page_io_latch_wait_in_ms / ixOS.page_io_latch_wait_count
        END AS avg_page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) AS ixOS
        INNER JOIN lifeboat.sys.indexes AS I
                ON I.index_id = ixOS.index_id
                        AND I.object_id = ixOS.object_id
ORDER BY ixOS.page_io_latch_wait_in_ms DESC;

/*
ИЛИ ВЫПОЛНИТЕ СОРТИРОВКУ ПО ОДНОМУ ИЗ СЛЕДУЮЩИХ КРИТЕРИЕВ:
page_latch_wait_in_ms DESC -- когда PAGELATCH_% самое большое ожидание
(row_lock_wait_in_ms + page_lock_wait_in_ms) DESC -- когда тип ожидания блокировки представляет самое большое ожидание
*/
Купить номер с этой статьей в PDF