SQL Server: Копните поглубже, чтобы понять, почему падает производительность

Давайте сразу опустимся на уровень ОС. Нас интересуют рабочие потоки, которые выполняют задачи, необходимые нашим транзакциям. DMO (Dynamic Management Objects, динамические объекты управления) соответствующей категории, имена которых начинаются с sys.dm_os_, предоставляют крайне подробную информацию о том, как SQL Server взаимодействует с ОС и оборудованием. С помощью этих DMO можно получить ответы на всевозможные вопросы по производительности:

• DMO wait_stats покажет, каких событий дожидаются потоки ОС, относящиеся к SQL Server.
• performance_counters покажет значения счетчиков производительности SQL Server и то, как их интепретировать.
• ring_buffers, schedulers и wait_stats будут уведомлять о проблемах, связанных с использованием процессора.
• sys_info выведет характеристики компьютера, на котором выполняется ваш SQL Server.
• sys_memory и process_memory покажут, как используется память в целом.
• memory_cache_counters или buffer_descriptors покажут, как используется кэш-память.

Запросы ко всем этим объектам работают в SQL Server 2005, 2008 или 2008 R2. Кроме того, для них требуется разрешение View Server State. Примечание: для этих объектов следует применять общее название DMO. Однако при использовании термина DMO возникает некоторая путаница с распределенными объектами управления (Distributed Management Objects), не имеющими к ним никакого отношения, поэтому администраторы баз данных часто используют для DMO общее название «динамические представления управления» (Dynamic Management Views или DMV).

Ожидания SQL Server

Пожалуй, самое важное DMV в категории Operating System — sys.dm_os_wait_stats. Каждый раз, когда сеанс должен чего-то ждать, перед тем как продолжить выполнение требуемых операций, SQL Server записывает продолжительность ожидания и данные о ресурсе, который ожидал SQL Server. DMV sys.dm_os_wait_stats выводит эту статистику ожиданий, агрегированную по всем идентификаторам сеансов, чтобы дать вам итоговую сводку о том, где возникает больше всего ожиданий для данного экземпляра.

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

Ожидания и очереди

В статье Тома Дэвидсона «SQL Server 2005 Waits and Queues» говорится о том, что работа с ожиданиями и очередями — отличная основа для оптимизации производительности. Каждый запрос к SQL Server, по сути, инициирует несколько «рабочих задач». SQL Server Scheduler (планировщик SQL Server) назначает каждой задаче рабочий поток. Как правило, для каждого процессора в SQL Server имеется один планировщик для ОС и в любой момент времени может выполняться по одной сессии для каждого планировщика.

Задача планировщика — равномерно распределить нагрузку среди имеющихся рабочих потоков. Если рабочий поток сеанса выполняется процессором, состоянием сеанса (отображается в колонке Status DMV sys.dm_exec_requests) будет Running.

Если поток готов к выполнению, но планировщик, которому он назначен, в настоящее время выполняет другой сеанс, поток помещается в очередь потоков, готовых к выполнению (runnable). Это просто означает, что он стоит в очереди на доступ к процессору. Это называют ожиданием сигнала (signal wait).

Ожидание сигнала

Время ожидания сигнала выводится в столбце signal_wait_time_ms. Оно отражает исключительно время ожидания процессора. Если сеанс ждет, пока станет доступным еще какой-то ресурс, например, заблокированная страница, или если выполняемому сеансу нужно выполнить ввод-вывод, то он перемещается в список ожидания. Это ожидание ресурсов, и состояние такого ожидающего сеанса запоминается как «suspended». Причина ожидания сохраняется и отображается в столбце wait_type DMV sys.dm_os_wait_stats.

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

Ожидание ресурсов = Общее время ожидания – Время ожидания сигналов (Или (wait_time_ms) - (signal_wait_time_ms))

Ожидания сигналов неизбежны в OLTP-системах (online transaction processing, онлайновые системы обработки транзакций), поскольку в них происходит большое количество коротких транзакций. Ключевой показатель, описывающий потенциальную нагрузку на процессор, — процент, который время ожидания сигналов составляет от общего времени ожидания.

Большой процент означает, что процессор находится под нагрузкой. Часто под «большим процентом» понимают более 25 процентов, но это зависит от вашей системы. Значения больше 10–15 процентов — повод для беспокойства. В общем, статистика ожиданий — отличное средство диагностики времени отклика вашей системы. Говоря самыми простыми словами, вы либо работаете, либо ждете.

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

Профилирование для оптимизиации производительности

Наш первый сценарий, основанный на DMV из категории OS, использует DMV sys.dm_os_wait_stats, возвращающее информацию обо всех ожиданиях, с которыми столкнулись выполняемые потоки. С помощью этого агрегированного представления можно выявлять проблемы производительности SQL Server в целом и отдельных запросов и пакетов.

Этот простой запрос вычисляет процент, который составляет время ожидания сигналов и время ожидания ресурсов от общего времени ожидания, чтобы диагностировать потенциальную нагрузку на процессор:

-- Total waits are wait_time_ms (high signal waits indicate CPU pressure)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;

Он полезен, чтобы убедиться в загруженности процессора. Поскольку время ожидания сигналов — это время, ушедшее на то, чтобы дождаться, пока процессор обслужит поток, если вы зафиксируете, что время ожидания больше, чем примерно 10–15 процентов, это будет отчетливым признаком загруженности вашего процессора. Эта статистика ожиданий накапливается с момента последнего перезапуска SQL Server, поэтому нужно знать исходное значение времени ожидание сигналов и следить за его изменением с течением времени.

Вы можете вручную удалить статистику ожиданий, не перезапуская сервер. Для этого выполните DBCC- команду (database consistency checking, проверка целостности базы данных) SQLPERF следующим образом:

-- Clear Wait Stats
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;

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

Наш второй пример сценария (рис. 1) показывает, как с помощью DMV sys.dm_os_wait_stats определить, на ожидание каких ресурсов SQL Server тратит больше всего времени.

Рис. 1.Этот сценарий генерирует отчет по основным причинам ожиданий

-- Isolate top waits for server instance since last restart
-- or statistics clear
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 ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) 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 ; -- percentage threshold

Сценарий на рис. 1 позволяет найти самые главные узкие места на уровне экземпляра. Это позволяет сфокусировать усилия по оптимизации на конкретных типах проблем. Например, если согласно накопленной статистике первое место занимают ожидания, связанные с дисковым вводом-выводом, вы должны произвести дальнейшее исследование этой проблемы, задействовав DMV-запросы и счетчики PerfMon, относящиеся к дискам.

Счетчики производительности

DMV, показывающее данные счетчиков PerfMon, — sys.dm_os_performance_counters. Оно возвращает по записи для каждого счетчика производительности, поддерживаемого сервером. Это полезное DMV, но, возможно, не совсем понятно, как с ним работать.

В зависимости от значения поля cntr_type данной записи, можно тем или иным способом углубиться в детали и извлечь из этого представления полезную информацию. Оно заменяет старую таблицу sys.sysperfinfo из SQL Server 2000.

Сценарий на рис. 2 помогает при исследовании необычных ситуаций при заполнении журнала транзакций. Он возвращает модель восстановления, описание факторов, вызывавших задержку усечения журнала (log reuse wait), размер журнала транзакций, использованное пространство журнала, процент использованного пространства, уровень совместимости и параметры проверки страниц для каждой базы данных текущего экземпляра SQL Server.

Рис. 2.Определите, как заполняется журнал транзакций, с помощью этого сценария

-- Recovery model, log reuse wait description, log file size,
-- log usage size and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_name WHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;

Запрос на рис. 2 полезен для оценки незнакомого сервера баз данных. Кроме того, он еще более полезен с точки зрения наблюдения. Например, если описание фактора, препятствующего усечению журнала, имеет какое-то необычное значение наподобие ACTIVE_TRANSACTION, и ваш журнал транзакций заполнен на 85 процентов, время бить тревогу.

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