В статье «Новые динамические административные представления в SQL Server 2016» (опубликована в предыдущем номере журнала) были показаны изменения в CTP-версиях 2 и 3 в Microsoft SQL Server 2016, связанные с широко известным де-факто запросом статистики ожидания. Этот запрос статистики ожидания, широко обсуждаемый в прессе, на конференциях, совещаниях и других мероприятиях, более не предоставляет точных результатов по сравнению с предшествующими версиями Microsoft SQL Server. Данное утверждение справедливо и для версии SQL Server 2014.
Это открытие заставило меня привлечь помощника со стороны, чтобы подтвердить точность моих измерений. Лучшим кандидатом на эту роль был мой друг Джо Сэк, главный разработчик архитектуры программного обеспечения для Microsoft SQL Server в группе Customer Advisory Team (CAT) компании Microsoft. Он подтвердил мои подозрения: чего-то недостает в стандартном запросе «статистики ожидания», который использовался сообществом SQL Server в течение десятилетия. Мы обнаружили повторяющиеся результаты при использовании этого запроса в новейших версиях SQL Server, а также странное поведение при отображении текущего процента ожиданий.
Исходный запрос статистики ожидания
В листинге 1 приводится программный код, с помощью которого более не удается получить точных результатов во всех версиях SQL Server, использующих динамические объекты управления (существующие начиная с версии SQL Server 2005).
Проблемы вычисления, похоже, устраняются внутри рекурсивного соединения CTE в теле запроса. При тестировании заполнения CTE поведение программного кода, представляющего собой источник CTE, соответствует ожиданиям. Поэтому мы решили пересмотреть запрос, но оставили его структуру и вывод в целом неизменными.
Новый универсальный запрос статистики ожидания
Новый запрос должен был устранить скрытые проблемы с рекурсивным соединением CTE. Для этого мы разделили запрос на три части:
- Создание таблицы temp, служащей хранилищем состояний ожидания из динамического административного представления sys.dm_os_wait_stats.
- Упрощенное обобщенное табличное выражение (CTE) на основе таблицы temp вместо прямого заполнения из динамического административного представления.
- Тело запроса похоже на предшествующий запрос, но с небольшими изменениями, которые я внес, чтобы избавиться от несущественного времени ожидания сигнала — между освобождением или предоставлением ожидаемого ресурса (память, блокировка, завершение параллельных потоков и т. д.) и началом работы ожидающего потока.
Цель запроса — идентифицировать ожидания ресурсов, а ожидания сигнала затрудняют получение этой информации и последующие решения. Следует рассматривать время ожидания сигнала как процент общих ожиданий для диагностики возможной нагрузки на процессор, но я рекомендую выделить его из этого запроса и на этом основывать свои решения. Это проявляется в исключении времени ожидания сигнала из результатов как в столбце ожиданий, так и в столбце процентов. Существуют другие разновидности исходного запроса состояний ожидания, в которых изолировано ожидание сигнала (все же представленное в виде отдельного столбца), но обычно я вижу ожидания сигнала, смешанные с вычислениями процента.
Кроме того, я дополняю описанные этапы очисткой таблицы temp. Если бы это был часто выполняемый или имеющий высокую ценность запрос на экземпляре с проблемами tempdb, то я в первую очередь сформировал бы таблицу temp наряду с кластеризованным индексом и заполнил ее с помощью инструкции INSERT… INTO из динамического административного представления. Поскольку это не так, конструкция «создание или заполнение», доступная через SELECT… INTO, допустима.
С учетом сказанного новый запрос имеет вид как в листинге 2.
Это приводит к результатам, представленным на приведенном экране, где мы видим список наиболее частых ожиданий ресурсов со времени последнего удаления статистики ожидания вручную или путем перезапуска службы или сервера SQL Server.
.jpg) |
| Экран. Список наиболее частых ожиданий ресурсов |
Результаты накапливаются до тех пор, пока не будет достигнут порог в 95%, так как после этого результаты превращаются в список не оказывающих влияния ожиданий, занимающих не более 1% общего времени ожидания. Как правило, я обращаю основное внимание на верхние два-три ожидания, и обычно картина ограничений для ресурсов характеризует уровень настройки производительности. Используя приведенные на экране результаты, я исследовал проблемы, связанные с количеством блокировок в экземпляре SQL. У них может быть несколько причин: многочисленные физические операции чтения; недостаточный размер оперативной памяти для рабочей нагрузки; внутренние неполадки, приводящие к увеличению времени выполнения транзакций и, как следствие, времени блокировки; неэффективные запросы или планы, связанные с большим числом операций чтения, которое можно уменьшить с помощью настроек. Состояния ожидания необходимо учитывать в первую очередь при поиске ограничений для экземпляра SQL Server, что поможет найти отправную точку для настройки.
Важно помнить, что статистические данные со временем накапливаются, начиная с момента, когда статистика ожиданий сброшена одним из указанных выше действий. Таким образом, будут охвачены все нагрузки за все время, в том числе затраченное на резервное копирование и обслуживание, а также, возможно, незаконные запросы или случайные действия, не характерные для ваших обычных нагрузок. Лучше всего периодически запрашивать и сохранять информацию, а затем анализировать различия, вместо того чтобы рассматривать результаты как общую сумму.
В целом, независимо от способов исследования описанных метрик, старые методы больше не приносят точных результатов.
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 (/* Вставить список допустимых типов ожидания, чтобы отфильтровать несущественные ожидания */) ) 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; -- процентный порог;
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
(
/*** отфильтровывание дополнительных несущественных ожиданий ***/
‘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', 'PREEMPTIVE_XE_DISPATCHER', 'XE_LIVE_TARGET_TVF'
);
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;