Диагностика проблем регулятора ресурсов

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

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

• Вначале нужно создать функцию.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• Потом зарегистрировать ее в регуляторе ресурсов.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• Затем нужно обновить конфигурацию регулятора ресурсов, хранимую в памяти.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

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

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

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

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

Чтобы устранить неполадки неправильно написанной функции-классификатора, можно использовать выделенное административное соединение, поскольку оно не подлежит классификации и может использоваться, даже если регулятор ресурсов работает и выполняет классификацию входящих сеансов. Дополнительные сведения см. в разделе Использование выделенного административного соединения.

Примечание
Если выделенное административное соединение недоступно для устранения неполадок, можно перезапустить систему в однопользовательском режиме. Хотя однопользовательский режим не подлежит классификации, нельзя выполнить диагностику классификации регулятора ресурсов, пока он работает.

Сведения о функции-классификаторе можно получить путем запроса к следующим объектам:

• sys.dm_exec_query_stats. (содержит информацию об инструкциях, но не саму функцию);

• sys.dm_exec_sql_text (используется совместно с объектом sql_handle, полученным с помощью запроса к sys.dm_exec_query_stats);

• класс событий PreConnect:Starting (предоставляет идентификатор и имя функции-классификатора).

В этом случае пользователь сообщает о падении производительности, и есть основания полагать, что система регулирует количество запросов.

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

• Выполнить запрос к объекту sys.dm_os_waiting_tasks с целью выяснения того, не имеются ли ожидающие запросы с типом ожидания RESMGR_THROTTLED. Если такие запросы есть, это значит, что регулирование количества запросов включено.

• Запустите системный монитор и соберите данные с помощью счетчиков Запросы в очереди и Активные запросы. Если значение счетчика Запросы в очереди не равно нулю, это значит, что регулирование количества запросов включено.

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

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

Для политики, управляемой событиями, которые создаются регулятором ресурсов, можно использовать событие, создаваемое при достижении порогового значения загрузки ЦП.

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

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

• Запустите сеанс SQL-трассировки и соберите все события класса CPU Threshold Exceeded. Событие SQL-трассировки создается сервером автоматически, когда пользовательский запрос достигает порогового значения загрузки ЦП. Если для параметра установлено слишком низкое значение, будет создаваться большое количество таких событий.

Примечание
Это событие также доступно в виде уведомления о событии сервера. Можно создать сценарий, который будет реагировать на это событие.

• Запустите системный монитор и соберите данные с помощью счетчика Max request cpu time (ms). Значение этого счетчика можно использовать как ориентировочную величину при установке соответствующего лимита для группы рабочей нагрузки.

В данной ситуации существует подозрение, что система регулирует пропускную способность ЦП, потому что значение счетчика производительности CPU usage % сравнялось или почти сравнялось со значением параметра регулятора ресурсов MAX_CPU_PERCENT. Следующий запрос возвращает загрузку ЦП в процентах для всех групп рабочей нагрузки и пулов ресурсов экземпляра SQL Server.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

Дополнительные сведения см. в разделе sys.dm_os_performance_counters (Transact-SQL).

Чтобы определить, происходит ли регулирование пропускной способности ЦП, нужно проверить следующие параметры системы.

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

• Проверьте распределение ресурса ЦП среди пулов ресурсов. Пул ресурсов может подвергнуться регулированию, если у другого пула задано высокое минимальное значение загрузки ЦП. Сравните показатели ожидаемой (вычисляемой) загрузки ЦП с реальной загрузкой.

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

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

• Возможно, рабочая нагрузка блокируется другими сеансами, а не подвергается регулированию под действием настроек регулятора ресурсов.

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

В этом случае есть подозрение, что медленное выполнение запроса обусловлено текущим размером выделяемой памяти.

Регулятор ресурсов обеспечивает соблюдение лимита памяти для запроса, снижая объем выделяемой памяти, чтобы большие запросы могли уложиться в лимит. Если запрос получает меньше 100% нужного объема выделенной памяти, может возникнуть необходимость сброса временных данных и записи их на диск, что заметно влияет на производительность.

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

• Узнать текущее состояние выделения объемов памяти с помощью запроса к таблице sys.dm_exec_query_memory_grants. Значение столбца ideal_memory_kb показывает оптимальную величину, вычисленную на основе оценки количества элементов. Значение столбца requested_memory_kb показывает запрашиваемую величину, которая может быть уменьшена после достижения максимально допустимого количества запросов. Если значение requested_memory_kb существенно ниже значения ideal_memory_kb, запрос будет вынужден часто сбрасывать временные данные (исходя из предположения, что оценка количества элементов верна).

• Запустите системный монитор и соберите данные с помощью счетчика Reduced memory grants/sec. Значение этого счетчика представляет собой относительное количество запросов на выделение памяти, которым выделено меньше оптимального объема из-за того, что максимальный объем выделенной памяти уже был достигнут ранее. Большие запросы могут выполняться намного медленнее тех, которые получают оптимальный объем памяти, потому что им приходится сбрасывать данные на диск, чтобы уложиться в предел выделяемой памяти.

Чтобы компенсировать проблему с выделением памяти, можно увеличить максимальный размер пула или максимальный объем выделения памяти.

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

Допустим, выполнение запроса завершается ошибкой, потому что истекло время ожидания при выделении памяти.

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

Оптимальные параметры пула ресурсов можно определить при помощи следующих действий.

• Выяснить количество операций выделения памяти и количество ожидающих запросов данной группы и данного пула с помощью запроса к таблице sys.dm_exec_query_memory_grants.

• Узнать текущее состояние выделенных объемов памяти и целевые показатели с помощью запроса к таблице sys.dm_exec_query_resource_semaphores.

Если потребность в выделенной памяти превышает доступный объем, можно рассмотреть возможность повышения максимального размера пула ресурсов.

Выполнение запроса завершается ошибкой из-за недостатка памяти.

Устранение основных неполадок

Диагностика проблем повышенной сложности

Ошибка «Нехватка памяти» (701) представляет собой стандартную ошибку, возвращаемую, если в каких-то задачах попытка выделить блок памяти через диспетчер памяти завершается неудачей. Дополнительные сведения см. в разделе MSSQLSERVER_701.

Эта ошибка может быть вызвана одним из следующих условий.

• Пул памяти достиг своего общего предельного размера.

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

• Выделение многостраничного или виртуального адресного пространства завершается неудачей, потому что в виртуальном адресном пространстве нет свободного блока нужной величины. Это часто случается в системах с 32-разрядной архитектурой и маловероятно для системы с 64-разрядной архитектурой.

• Выделение многостраничного или виртуального адресного пространства завершается неудачей, потому что общий объем выделенной памяти в системе достигает предела выделенной виртуальной памяти. Это возможно в системах и с 32-разрядной архитектурой, и с 64-разрядной архитектурой.

При возникновении ошибки нехватки памяти лучше всего начинать диагностику с журнала ошибок. Журнал содержит сообщения примерно следующего вида:

2006-01-28 04:27:15.43 spid51 Не удалось выделить страницы: FAIL_PAGE_ALLOCATION 1

Возможные сообщения, занесенные в журнал ошибок, следующие.

• FAIL_PAGE_ALLOCATION, а затем количество страниц, по отношению к которым была предпринята попытка распределения.

• FAIL_VIRTUAL_RESERVE, а затем число байтов, по отношению к которым была предпринята попытка резервирования.

• FAIL_VIRTUAL_COMMIT, а затем число байтов, по отношению к которым была предпринята попытка фиксации.

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

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

• Параметр MEMORYCLERK_* означает, что конфигурация сервера или рабочая нагрузка требует определенного выделения памяти. Компонентам SQL Server соответствуют свои клерки памяти, и отдельные компоненты могут иметь несколько клерков памяти. Дополнительные сведения см. в разделе sys.dm_os_memory_clerks. Иногда по данным клерков памяти можно выяснить, какая рабочая нагрузка вызывает проблему. Но чаще всего приходится исследовать объекты памяти, связанные с клерками, для выяснения того, чем вызвано потребление больших объемов памяти.

• CACHESTORE_*, USERSTORE_*, OBJECTSTORE_* представляют собой типы кэшей. Если кэш потребляет большое количество памяти, это может означать следующее.

  • Память в кэше выделена, но еще не вставлена в виде записи, которую можно удалить из кэша. Это очень похоже на описанную выше ситуацию с объектом MEMORYCLERK.

  • Все записи кэша используются, так что ни одну из них нельзя удалить. Это можно выяснить, посмотрев на счетчики sys.dm_os_memory_cache_counters и сравнив значения столбцов entries_count и entries_in_use_count.

• Параметр MEMORYCLERK_SQLQERESERVATIONS показывает, сколько памяти зарезервировано выполнением запроса для запуска запросов с сортировкой или соединением. Если этот показатель высок и при этом возникает ошибка нехватки памяти, это обычно служит признаком программной ошибки в сервере.

Показатель состояния памяти в журнале ошибок также указывает, какой именно пул памяти исчерпан. Брокеры памяти для каждого пула показывают распределение памяти между заимствованной (компиляция), кэшированной и зарезервированной (выделенной) памятью. Показатели для трех брокеров соответствуют предыдущим объектам памяти, связанным с клерками памяти. Можно узнать, сколько памяти выделено данному пулу с помощью данного клерка или объекта памяти, получив информацию из полного дампа с помощью пользовательского сценария, а динамическое административное представление sys.dm_os_memory_cache_entries показывает идентификатор пула pool_id, с которым связана каждая запись.

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

• Журнал ошибок, в котором показана ошибка нехватки памяти и вывод состояния памяти на момент ошибки.

• Вывод следующих инструкций:

  dbcc memorystatus
  dbcc sqlperf(spinlockstats)
  select * from sys.dm_os_memory_clerks
  select * from sys.dm_os_wait_stats order by wait_type
  select * from sys.dm_os_waiting_tasks
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
  select * from sys.dm_os_memory_cache_clock_hands
  

• По возможности, дамп системы, вызванный нехваткой памяти и собранный с помощью устройства T8004. Этот мини-дамп содержит ценную информацию, такую как статистические показатели кольцевых буферов, а также спин-блокировок и (или) ожидания. Счетчик дампа для устройства T8004 можно сбросить, не перезапуская сервер, если выключить T8004 и снова включить.

Существует подозрение, что запрос выполняется медленно, потому что относящийся к нему план запроса не оптимален. Оптимизатор запросов может создать неоптимальный план запроса, если получит недостаточно памяти из-за низкого максимального объема памяти, выделенного в настройках пулу ресурсов.

Оптимальные параметры пула ресурсов можно определить при помощи следующих действий.

• Определить, компилируется ли для данной группы рабочей нагрузки большое количество запросов, по счетчику Query optimizations/sec.

• Определить, часто ли оптимизатор запросов создает неоптимальные планы, по счетчику Suboptimal plans/sec.

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