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

В предыдущей статье (опубликованной в № 10 за 2015 год) мы, образно говоря, отправились в путешествие по новым динамическим административным объектам, реализованным в SQL Server 2016, и нашей первой остановкой стало представление sys.dm_exec_session_wait_stats. Подобно существующему представлению sys.dm_os_wait_stats, это динамическое административное представление возвращает информацию об ожидании (метаданные, показывающие, что именно процессор запроса SQL ожидает получить для ресурсов), перед тем как процессор сможет полностью выполнить запрос, поступивший от пользовательского сеанса. В отличие от существующего динамического административного представления sys.dm_os_wait_stats, представление sys.dm_exec_session_wait_stats возвращает данную информацию с сеансовым уровнем детализации.

Это чрезвычайно интересно, поскольку, включая идентификатор session_id в формате одного из возвращаемых столбцов, данное представление дает нам возможность подключения ко множеству других динамических административных объектов, которые отображают session_id в форме одного из своих столбцов. В дополнение к возможности выявления ограниченных ресурсов для проведения сеанса теперь мы можем использовать идентификатор session_id для подключения к представлению sys.dm_exec_requests с целью агрегирования статистики ожидания в уровень запроса. Кроме того, мы можем выполнять аналогичные действия с использованием уровня журнала регистрации транзакций через представление sys.dm_trans_session_transactions (промежуточное динамическое административное представление, устанавливающее отношения между идентификаторами session_id и transaction_id в системе SQL Server).

После добавления в представление sys.dm_exec_sessions столбца database_id в версии SQL Server 2014 мы можем агрегировать ожидания для всех сеансов, связанных с той или иной конкретной базой данных. До выхода в свет версии SQL Server 2014, для того чтобы возвратить идентификатор database_id или использовать его в качестве фильтра, требовалось объединить представления sys.dm_exec_sessions и sys.dm_exec_request. А это означает, что результаты можно было получать только для сеансов с активными запросами — серьезный недостаток, с точки зрения тех, кто работает с метаданными.

Просто удивительно: на то, чтобы прийти к мысли о включении статистики ожидания уровня сеанса, ушло целых шесть версий SQL, если вести отсчет от момента создания динамических административных объектов. Достаточно беглого взгляда на sys.sysprocesses, чтобы понять, что эти сведения были доступны потребителю еще до появления динамического административного объекта (см. рисунок 1):

SELECT spid
        , lastwaittype
FROM sys.sysprocesses

 

Статистика ожидания уровня сеанса
Рисунок 1. Статистика ожидания уровня сеанса

Отсюда следует, что данная информация записывалась на уровне сеансов и даже более вероятно, что на уровне запросов, поскольку запросы выполнялись процессором Query Engine, но предъявлялись пользователю или администратору баз данных лишь после агрегирования на уровне экземпляра. Причем мы могли видеть эту информацию с другим уровнем детализации только в представлениях sys.dm_exec_sessions и sys.dm_exec_requests и только в виде отдельных «ответов» (а не агрегированных значений на протяжении жизненного цикла сеанса или запроса).

Как было показано в многочисленных статьях, журналах, книгах и презентациях, эта статистика ожиданий является накопительной. Чтобы разобраться с существующим состоянием ожиданий, мы должны приложить некоторые усилия. Результаты представления sys.dm_exec_wait_stats возвращают сведения об ожидании с момента последнего запуска или перезапуска служб. Это означает, что мы видим результаты всех периодов деятельности: нормальные сроки загрузки и обслуживания, когда вполне вероятно скачкообразное увеличение времени ожидания OLEDB вследствие выполнения запросов по представлению sys.dm_db_index_physical_stats, чтобы получить сведения о фрагментации индексов, необходимые для обслуживания индексов. Кроме того, мы получаем статистику ожидания, связанную с такими периодами, когда какой-нибудь сотрудник из отдела кадров выполняет нестандартный запрос с целью получения всех сведений о найме сотрудников компании (поскольку он опять забыл включить в свой запрос действительную дату начала). Здесь я склоняюсь к тому, чтобы осуществлять «нарезку» по времени всей своей коллекции статистических данных ожидания. Я имею в виду периодическое получение в соответствии с определенным графиком дампов представления sys.dm_os_wait_stats, включающего столбец date_stamp, и перенос этих данных в физическую таблицу в базе данных, предназначенную для хранения сведений о задачах администрирования и обслуживания или данных на каждом из моих экземпляров. Это позволяет сокращать периоды ожидания, удерживая их в определенных пределах, а не все нагрузки с того момента, когда службы были перезапущены.

Затруднения с выполнением этого процесса продолжались вплоть до выпуска SQL Server 2016.

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

Это очень важно для нас, потому что в процессе ожидания экземпляра мы получаем информацию о том, в чем этот экземпляр как единое целое усматривает ресурсные ограничения, исходя из общей рабочей нагрузки. Периоды ожидания сеансов дают представление не только о том, в чем именно с точки зрения сеансов заключаются ограничения в отношении обращения к ресурсам. Эти ожидания позволяют понять, в чем состоит вклад, вносимый данным сеансом в общее ожидание экземпляра. В дополнение к этому (как вы увидите при рассмотрении трех запросов в конце статьи) рабочие нагрузки не всех сеансов ожидают возможности обращения к одним и тем же ресурсам, и, если данный экземпляр имеет высокий показатель по ожиданиям PAGEIOLATCH_EX, это не означает, что такой же показатель будет иметь каждый сеанс. Так что это новое динамическое административное представление позволяет осуществлять критический разбор связанных с быстродействием вопросов так, как не представлялось возможным при использовании sys.dm_os_wait_stats, реализованного в предыдущих версиях SQL Server.

Дополнительная сложность состоит в том, что старый способ сбора статистики ожидания в SQL Server 2016 не работает. В процессе подготовки материалов для данной статьи я обнаружил кое-что любопытное для SQL Server 2016, во всяком случае в версии CTP 2.4: оказывается, старый добрый метод опроса представления sys.dm_os_wait_stats теперь возвращает повторяющиеся данные, а существующая методика расчета текущего процента от общего времени ожиданий не дает корректных результатов. Объясняется ли это изменениями в механизме решения проблем изоляции транзакций для базовых конструкций ожидания, проблемами параллелизма или все дело в каких-то иных изменениях, выполненных с целью создания, а также успешного сбора и организации отчетности по ожиданиям уровня сеанса, пока неясно. Но как бы то ни было, это означает, что нам придется изменить применяемый в SQL Server 2016 подход к проблеме сбора ожиданий (либо на уровне экземпляра, либо на уровне сеансов).

Старый метод оценки метаданных ожидания просто не будет работать. Когда мы выполняем существующий стандартный запрос, годами использовавшийся в той или иной форме многими организаторами презентаций (см. листинг 1), мы получаем в системе SQL Server 2016 (CTP 2.4) результаты, показанные на рисунке 2.

 

Исполнение стандартного запроса в системе SQL Server 2016 (CTP 2.4)
Рисунок 2. Исполнение стандартного запроса в системе SQL Server 2016 (CTP 2.4)

Если вы посмотрите на элемент A, то увидите, что, как только вы доведете текущее значение процента до 100, начнется пересчет этого значения, а не выполнение правила, применяемого к предложению HAVING. В конечном итоге возникает вопрос B c повторяющимися записями. При добавлении вспомогательного критерия подключения к W2.wait_type = W1.wait_type повторяющиеся записи (вопрос B) удаляются, но проблема, касающаяся сбоя при подсчете текущего процентного значения, остается. До выпуска данной версии SQL Server подобная реакция не отмечалась. Не было и повторяющихся записей, несмотря на самообъединение только на W2.rn <= W1.rn. Фактическое добавление дополнительного соединения в более ранних версиях SQL Server, предшествующих версиям 2016 CTP, сводит на нет ограничения указания HAVING.

Я дважды выполнил указанный запрос в системе SQL Server 2014: один раз без изменений, а второй раз с добавлением дополнительного соединения на wait_type (см. рисунки 3 и 4).

 

Результаты самообъединения в системе SQL Server 2014 только на W2.rn <= W1.rn
Рисунок 3. Результаты самообъединения в системе SQL Server 2014 только на W2.rn <= W1.rn

 

Результаты самообъединения в системе SQL Server 2014 для обоих случаев: W2.rn <= W1.rn и W2.wait_type = W1.wait_type
Рисунок 4. Результаты самообъединения в системе SQL Server 2014 для обоих случаев: W2.rn <= W1.rn и W2.wait_type = W1.wait_type

Так что же нам делать? Конечно, внедрять новые решения.

Сбор данных по ожиданиям экземпляра и по ожиданиям сеанса в SQL Server 2016

Два изменения в механизме сбора статистики ожидания дают отличные результаты при обоих уровнях детализации:

  1. Выполните все запросы по сбору статистики ожидания при значении MAXDOP, равном 1.
  2. Вместо самообъединения с обобщенным табличным выражением используйте оконную функцию для расчета текущей процентной доли ожиданий.

Однако надо учитывать, что оконные функции могут использоваться только в SELECT и WHERE. Именно по этой причине, как вы увидите, я изменил приведенные здесь запросы таким образом, чтобы они возвращали сведения по трем важнейшим ожиданиям, а не по всем ожиданиям до порогового значения в 95% всех запросов, которые входят в стандартный запрос по ожиданиям, применявшийся повсюду, от книги о динамических административных объектах, которую я вместе с Луисом Дэвидсоном написал в 2010 году, до веб-сайтов и презентаций по всему миру. Я всегда считал это пороговое значение несколько чрезмерным, поскольку чаще всего интерес представляют типы ожиданий, составляющие основную массу ограничений по ресурсам, а не те типы, которые, возможно, составляют лишь 1-2% всех ожиданий, когда значения приближаются к порогу в 95% в старом запросе.

Разумеется, вы можете модифицировать публикуемые ниже запросы таким образом, чтобы они включали 5, 10 или 42 периода ожидания. Решать вам.

Рассмотрим два варианта опроса нового представления sys.dm_exec_session_wait_stats, а также новый подход к опросу представления sys.dm_os_wait_stats на предмет получения статистики ожидания уровня экземпляра в SQL Server 2016.

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

Прежде всего мы исследуем пересмотренный код для опроса статистики ожиданий уровня экземпляра с помощью существующего динамического административного представления sys.dm_os_wait_stats.

Второй запрос возвращает информацию о конкретном идентификаторе session_id с помощью нового динамического административного представления sys.dm_exec_session_waits.

Последний запрос содержит код для возвращения информации обо всех сеансах, связанных с конкретной базой данных, с помощью представления sys.dm_exec_session_waits.

При составлении каждого из этих запросов учитывалась необходимость запуска в качестве MAXDOP 1 и применения оконной функции для расчета текущей процентной доли в соответствии с изложенными выше соображениями.

Новый процесс сбора информации об ожидании экземпляра в SQL Server 2016

Как я разъяснил выше, теперь мы отмечаем изменение в поведении SQL Server 2016 (CTP 2.4), влияющее на результаты хорошо зарекомендовавшего себя запроса на сведения по статистике ожиданий, который мы использовали в течение долгого времени (см. рисунок 5). В листинге 2 представлено описание процесса, который будет осуществляться в системе SQL Server 2016.

 

Новый запрос для сбора статистических данных об ожидании экземпляра в системе SQL Server 2016 с использованием представления sys.dm_os_wait_stats
Рисунок 5. Новый запрос для сбора статистических данных об ожидании экземпляра в системе SQL Server 2016 с использованием представления sys.dm_os_wait_stats

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

 

Ожидания конкретного сеанса в системе SQL Server 2016
Рисунок 6. Ожидания конкретного сеанса в системе SQL Server 2016

Вы наверняка обратили внимание на синтаксис параметра Template Parameter для указания на идентификатор session_id. Этот синтаксис дает возможность повторно использовать код и выгружать этот параметр либо с помощью кнопки в строке меню SSMS с надписью Query, либо с помощью комбинации клавиш Ctrl+Shift+M.

Возвращение суммарной статистики ожиданий для всех сеансов работы с конкретной базой данных

Здесь положение несколько осложняется ограничениями, связанными с этими оконными функциями, а также вследствие того факта, что после агрегирования нескольких сеансов в данный запрос мы получаем вероятность появления дубликатов wait_types. Это означает, что перед упомянутым запросом нужно предусмотреть дополнительный этап, обеспечивающий сбор всех ожиданий и способствующий агрегированию wait_types с целью устранения этих дубликатов из оставшейся части запроса. Существует два пути решения задачи: с помощью временной таблицы или с использованием табличной переменной. В данном случае я склоняюсь к временным таблицам (см. листинг 4).

Обратите внимание: вставляя записи в данную временную таблицу (#WAITS), мы группируем их по имени базы данных (которое опять-таки вводится как Template Parameter) и по wait_type, с тем чтобы удалить дубликаты этих wait_types перед обработкой оставшейся части запроса (см. рисунок 7).

 

Ожидания для всех сеансов, связанных с конкретной базой данных в SQL Server 2016
Рисунок 7. Ожидания для всех сеансов, связанных с конкретной базой данных в SQL Server 2016

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

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

Листинг 1. Стандартный запрос
WITH Waits AS
(
SELECT
   wait_type,
   wait_time_ms / 1000. AS wait_time_s,
   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
   NOT IN
     (-- отфильтровываем дополнительные ожидания, не имеющие отношения к делу
                      'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR',
                      'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
                      'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT',
                      'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT', 'KSOURCE_WAKEUP',
                      'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS',
                      'ONDEMAND_TASK_QUEUE',
                      'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE',
                      'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT',
                      'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                      'BROKER_EVENTHANDLER',
                      'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
                      'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP',
                      'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE',
                      'HADR_NOTIFICATION_DEQUEUE',
                      'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL', 'HADR_TIMER_TASK',
                      'HADR_SYNC_COMMIT',
                      'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
                      'PREEMPTIVE_HADR_LEASE_MECHANISM',
                      'PREEMPTIVE_OS_GETFILEATTRIBUTES', 'PREEMPTIVE_OS_CREATEFILE',
                      'PREEMPTIVE_OS_FILEOPS',
                      'XE_LIVE_TARGET_TVF')
)
)
 
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) 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.wait_time_s,
W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- процентный порог;
Листинг 2. Процесс в системе SQL Server 2016
--======================================================================
-- КУМУЛЯТИВНЫЕ ОЖИДАНИЯ ЭКЗЕМПЛЯРА
--======================================================================

WITH Waits AS
        (
        SELECT
                WS.wait_type
                , WS.wait_time_ms
                , WS.signal_wait_time_ms
                , (WS.wait_time_ms - WS.signal_wait_time_ms) AS resource_wait_time_ms
                , WS.waiting_tasks_count
                , CASE WS.waiting_tasks_count
                        WHEN 0 THEN 0
                        ELSE WS.[wait_time_ms]/WS.[waiting_tasks_count]
                END AS avg_wait_time_ms
                , 100. * WS.wait_time_ms / SUM(WS.wait_time_ms) OVER() AS pct
                , ROW_NUMBER() OVER(ORDER BY (WS.wait_time_ms - WS.signal_wait_time_ms) DESC) AS rn
        FROM sys.dm_os_wait_stats AS WS
        WHERE WS.wait_type
                NOT IN (        -- filter out additional irrelevant waits
                                'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR',
                                'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
                                'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT',
                                'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT',
                                'KSOURCE_WAKEUP',
                                'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS',
                                'ONDEMAND_TASK_QUEUE',
                                'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE',
                                'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT',
                                'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                                'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'BROKER_EVENTHANDLER',
                                'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
                                'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
                                'SP_SERVER_DIAGNOSTICS_SLEEP',
                                'HADR_WORK_QUEUE', 'HADR_NOTIFICATION_DEQUEUE',
                                'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL',
                                'HADR_TIMER_TASK', 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
                                'PREEMPTIVE_HADR_LEASE_MECHANISM',
                                'PREEMPTIVE_OS_GETFILEATTRIBUTES',
                                'PREEMPTIVE_OS_CREATEFILE', 'PREEMPTIVE_OS_FILEOPS',
                                'HADR_SYNC_COMMIT'
                                , 'XE_LIVE_TARGET_TVF')
                )
                                 
            
SELECT TOP 3 W1.wait_type
        , CAST(W1.wait_time_ms AS DECIMAL(20, 0)) AS wait_time_ms
        , CAST(W1.signal_wait_time_ms AS DECIMAL(20, 0)) AS signal_wait_time_ms
        , CAST(W1.resource_wait_time_ms AS DECIMAL(20, 0)) AS resource_wait_time_ms
        , W1.waiting_tasks_count
        , W1.avg_wait_time_ms
        , CAST(W1.pct AS DECIMAL(5, 2)) AS pct
        , CAST(SUM(W1.pct) OVER(ORDER BY (W1.wait_type) DESC)AS DECIMAL(5,2)) AS running_pct
FROM Waits AS W1
GROUP BY W1.rn
        , W1.wait_type
        , W1.wait_time_ms
        , W1.signal_wait_time_ms
        , W1.resource_wait_time_ms
        , W1.waiting_tasks_count
        , W1.avg_wait_time_ms
        , W1.pct
ORDER BY W1.pct DESC OPTION (MAXDOP 1);
Листинг 3. Суммарные статистические данные об ожидании конкретного сеанса
--============================================================
-- ОЖИДАНИЯ КОНКРЕТНОГО СЕАНСА
--============================================================
WITH Waits AS
        (
        SELECT
                SWS.wait_type,
                SWS.wait_time_ms,
                SWS.signal_wait_time_ms,
                (SWS.wait_time_ms - SWS.signal_wait_time_ms) AS resource_wait_time_ms,
                SWS.waiting_tasks_count,
                CASE SWS.waiting_tasks_count
                        WHEN 0 THEN 0
                        ELSE SWS.wait_time_ms/SWS.waiting_tasks_count
                END AS avg_wait_time_ms,
                (100. * SWS.wait_time_ms) / SUM(SWS.wait_time_ms) OVER() AS pct,
                ROW_NUMBER() OVER(ORDER BY (SWS.wait_time_ms - SWS.signal_wait_time_ms)
                DESC) AS row_num
        FROM sys.dm_exec_session_wait_stats SWS
        WHERE SWS.wait_type
                NOT IN (-- отфильтровка дополнительных ожиданий, не имеющих отношения к делу
                                'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR',
                                'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
                                'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT',
                                'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT', 'KSOURCE_WAKEUP',
                                'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS',
                                'ONDEMAND_TASK_QUEUE',
                                'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE',
                                'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT',
                                'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                                'BROKER_EVENTHANDLER',
                                'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
                                'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP',
                                'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE',
                                'HADR_NOTIFICATION_DEQUEUE',
                                'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL', 'HADR_TIMER_TASK',
                                'HADR_SYNC_COMMIT',
                                'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
                                'PREEMPTIVE_HADR_LEASE_MECHANISM',
                                'PREEMPTIVE_OS_GETFILEATTRIBUTES', 'PREEMPTIVE_OS_CREATEFILE',
                                'PREEMPTIVE_OS_FILEOPS',
                                'XE_LIVE_TARGET_TVF')
                AND SWS.session_id = 
                )
                                               
SELECT TOP 3 W.wait_type,
        CAST(W.wait_time_ms AS DECIMAL(20, 0)) AS wait_time_ms,
        CAST(W.signal_wait_time_ms AS DECIMAL(20, 0)) AS signal_wait_time_ms,
        CAST(W.resource_wait_time_ms AS DECIMAL(20, 0)) AS resource_wait_time_ms,
        W.waiting_tasks_count,
        W.avg_wait_time_ms,
        CAST(W.pct AS DECIMAL(5, 2)) AS pct,
        CAST(SUM(W.pct) OVER(ORDER BY (W.resource_wait_time_ms) DESC)AS DECIMAL(5,2))
        AS running_pct

FROM Waits AS W
GROUP BY W.row_num,
        W.wait_type,
        W.wait_time_ms,
        W.signal_wait_time_ms,
        W.resource_wait_time_ms,
        W.waiting_tasks_count,
        W.avg_wait_time_ms,
        W.pct
ORDER BY W.pct DESC
OPTION (MAXDOP 1);
Листинг 4. Суммарная статистика ожиданий для всех сеансов работы с конкретной базой данных
--======================================================
-- ОЖИДАНИЯ СЕАНСОВ С КОНКРЕТНОЙ БАЗОЙ ДАННЫХ
--======================================================
IF NOT EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '#WAITS_%')
        BEGIN
                CREATE TABLE #WAITS
                        (
                                database_id sysname
                                , wait_type nvarchar(60)
                                , waiting_tasks_count bigint
                                , wait_time_ms bigint
                                , max_wait_time_ms bigint
                                , signal_wait_time_ms bigint
                        )
        END
ELSE
        BEGIN
                TRUNCATE TABLE #WAITS;
        END
INSERT INTO #WAITS
        (
                database_id
                , wait_type
                , waiting_tasks_count
                , wait_time_ms
                , signal_wait_time_ms
        )
SELECT eS.database_id
        , SWS.wait_type
        , SUM(SWS.waiting_tasks_count) AS waiting_tasks_count
        , SUM(SWS.wait_time_ms) AS wait_time_ms
        , SUM(SWS.signal_wait_time_ms) AS signal_wait_time_ms
FROM sys.dm_exec_session_wait_stats SWS
        INNER JOIN sys.dm_exec_sessions eS
                ON SWS.session_id = eS.session_id
WHERE SWS.wait_type
        NOT IN (-- filter out additional irrelevant waits
                        'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR'
                        , 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER',
                        'CHECKPOINT_QUEUE'
                        , 'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE',
                        'CLR_AUTO_EVENT'
                        , 'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_
                        WAIT', 'KSOURCE_WAKEUP'
                        , 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
                        'MISCELLANEOUS', 'ONDEMAND_TASK_QUEUE'
                        , 'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK',
                        'TRACEWRITE'
                        , 'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT',
                        'XE_TIMER_EVENT'
                        , 'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_
                        FLUSH_SLEEP'
                        , 'BROKER_EVENTHANDLER', 'QDS_CLEANUP_STALE_
                        QUERIES_TASK_MAIN_LOOP_SLEEP'
                        , 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
                        'SP_SERVER_DIAGNOSTICS_SLEEP'
                        , 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                        'HADR_WORK_QUEUE', 'HADR_NOTIFICATION_DEQUEUE'
                        , 'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL',
                        'HADR_TIMER_TASK', 'HADR_SYNC_COMMIT'
                        , 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
                        'PREEMPTIVE_HADR_LEASE_MECHANISM'
                        ,'PREEMPTIVE_OS_GETFILEATTRIBUTES',
                        'PREEMPTIVE_OS_CREATEFILE',
                        'PREEMPTIVE_OS_FILEOPS'
                        , 'XE_LIVE_TARGET_TVF')
        AND DB_NAME(es.database_id) = ''
        AND eS.is_user_process = 1
GROUP BY eS.database_id
        , SWS.wait_type
OPTION (MAXDOP 1);

WITH Waits AS
        (
        SELECT SWS.database_id
                , SWS.wait_type
                , SWS.wait_time_ms
                , SWS.signal_wait_time_ms
                , (SWS.wait_time_ms - SWS.signal_wait_time_ms)
                AS resource_wait_time_ms
                , SWS.waiting_tasks_count
                , CASE SWS.waiting_tasks_count
                        WHEN 0 THEN 0
                        ELSE SWS.wait_time_ms/SWS.waiting_tasks_count
                END AS avg_wait_time_ms
                , (100. * SWS.wait_time_ms) / SUM(SWS.wait_time_ms)
                OVER() AS pct
                , ROW_NUMBER() OVER(ORDER BY
                (SWS.wait_time_ms - SWS.signal_wait_time_ms) DESC) AS row_num
        FROM #WAITS SWS
                )
                                              
SELECT TOP 3 DB_NAME(W.database_id) AS the_database
        , W.wait_type
        , CAST(W.wait_time_ms AS DECIMAL(20, 0)) AS wait_time_ms
        , CAST(W.signal_wait_time_ms AS DECIMAL(20, 0))
        AS signal_wait_time_ms
        , CAST(W.resource_wait_time_ms AS DECIMAL(20, 0))
        AS resource_wait_time_ms
        , W.waiting_tasks_count
        , W.avg_wait_time_ms
        , CAST(W.pct AS DECIMAL(5, 2)) AS pct
        , CAST(SUM(W.pct) OVER(ORDER BY
        (W.resource_wait_time_ms) DESC)AS DECIMAL(5,2)) AS running_pct
FROM Waits AS W
GROUP BY W.database_id
        , W.row_num
        , W.wait_type
        , W.wait_time_ms
        , W.signal_wait_time_ms
        , W.resource_wait_time_ms
        , W.waiting_tasks_count
        , W.avg_wait_time_ms
        , W.pct
ORDER BY W.pct DESC
OPTION (MAXDOP 1);
DROP TABLE #WAITS;
GO