Технология Клиент-Сервер 2007'4

Home

Поиск

Издания

Контакты

k-press.ru

Продолжается подписка на наши издания! Вы не забыли подписаться?

Resource Governor: управление ресурсами в SQL Server 2008. Часть 1.

Автор: Николай Денищенко

Опубликовано: 11.02.2008

Уверен, что теме распределения аппаратных ресурсов и управления рабочей нагрузкой в SQL Server 2008 будет посвящено много заметок в блогах, статей и даже книг. Возможно, кто-то сочинит мюзикл, где Ларс Ульрих сыграет на барабанах. Возможно всё. Но сейчас, пока уважаемый читатель способен однозначно сказать, в каком ухе у него жужжит, я хочу поделиться своими впечатлениями от использования Resource Governor. Благо, что я участвовал в программе тестирования новой технологии.

Учат в школе, учат в школе, учат в школе…

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

Мы делили апельсин,

Много нас, а он один.

Эта долька – для ежа,

Эта долька – для стрижа,

Эта долька – для утят,

Эта долька – для котят,

Эта долька – для бобра,

А для волка – кожура.

Он сердит на нас – беда!!!

Разбегайтесь кто куда!

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

Став старше, я сменил слюнявчик на галстук, и знаю, что если оставить бухгалтерию «с кожурой» в виде еле трепыхающегося SQL-сервера, да ещё в период сдачи отчётности, то убегать будет некуда. Главбух достанет даже в Гонолулу.

Причин, приводящих к таким ситуациям, может быть много. Одна из них — это конкурентная борьба за ресурсы сервера (те самые апельсиновые дольки!) между приложениями и даже отдельными пользователями. Наш малый и, чего греха таить, средний бизнес всё ещё скрепя сердце идёт на покупку выделенных серверов под каждое приложение. К тому же интуитивно понятно, что ёмкости этих ресурсов порой не хватает именно в считанные дни, тогда как в остальное время сервер превосходно справляется со своей задачей.

Что делать, когда менее значимое для компании SQL-приложение ведёт себя как саранча, поедая всю доступную серверу память и мешая нормальному функционированию намного более критичных систем? Искать причину в коде? Оптимизировать на коленке? А если это проприетарный продукт, рождённый в муках какой-нибудь софтверной компании, где у программистов руки растут для красоты?

Другими словами, в MS SQL Server давно не хватало инструмента, позволяющего регулировать потребление ресурсов и гибко управлять нагрузкой. И такой инструмент появился в Katmai (кодовое название SQL Server 2008) под названием Resource Governor.

Концепция Resource Governor

На данный момент в поле зрения Resource Governor (RG) находятся два типа разделяемых ресурсов — оперативная память и CPU. При этом контроль осуществляется только над ресурсами, которые могут быть выделены процессу MS SQL Server операционной системой. Так, если у вас на той же самой машине установлен, к примеру, IIS, нагружающий процессор на 70%, то оставшиеся 30% — это максимум, на который может претендовать экземпляр SQL-сервера, и максимум того, чем может распоряжаться его Resource Governor. Исходя из этих соображений, легко понять, почему RG не умеет регулировать нагрузку на другие компоненты SQL-сервера, такие как Analysis Services, Integration Services или Reporting Services. Все перечисленные компоненты являются отдельными процессами ОС.

Коротко рассмотрим основные понятия Resource Governor.

Пулы ресурсов (Resource Pools)

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

Проведём аналогию с поэтическим зоопарком, описанным ранее.

Допустим, у нас есть апельсин, состоящий из 7 долек, и этот апельсин — ресурс возобновляемый. Мы создали один пул, в котором постановили, что бобёр должен получить не менее 3-х и не более 5 долей апельсина в условиях конкуренции за него. И ещё один пул для ежа, где тому полагается минимум 4 и максимум 6 долек (опять же если на него кто-то ещё претендует). Тем самым, мы задали гарантированный минимум продовольственного обеспечения, который получат бобёр и ёж.

Предположим, что пока ёж в командировке, апельсин находится в полном распоряжении бобра. Внимание, вопрос: сколько может съесть бобёр? Ответ — Resource Governor позволит ему покушаться на весь апельсин целиком до тех пор, пока из тумана не выйдет ёжик (безраздельное владение бобра ресурсами в отсутствие других потребителей повышает эффективность использования оных).

И тут начинается самое интересное. Если ёжик не очень голоден (с утра съел гамбургер) и претендует только на 2 дольки, то бобёр (неделю росинки маковой во рту не было) уже сможет посягнуть только на свой ограниченный паёк в 3-5 долек. А вот если ёж попался жадный и пытается у бобра отобрать его часть фруктового десерта, то Resource Governor не даст в обиду строителя плотин. Бобёр всегда может быть уверен, что при необходимости получит не меньше 3-х долек апельсина.

Теперь, когда вы имеете образное представление о пулах, можно обсудить, как они настраиваются в MS SQL Server 2008. Делается это с помощью пары параметров MIN% и MAX% для каждого типа ресурсов (одна пара для CPU, другая — для оперативной памяти). Собственно, мы уже обсудили их назначение. MIN% задаёт гарантированный минимум ресурса, который при необходимости будет предоставлен потребителям пула, а MAX% — верхнюю границу в условиях конкуренции за ресурс.

Очевидно, что сумма минимумов всех пулов не должна превышать 100%, в то время как MAX% может быть задан в диапазоне от MIN% до 100%.

В MS SQL Server 2008 есть два предопределённых пула — Internal Pool и Default Pool.

Internal Pool используется сервером для внутренних нужд. Естественно, что изменять настройки этого пула запрещено. От доступности ресурсов в Internal Pool зависит функционирование самого SQL Server, поэтому Resource Governor может позволить ему потеснить «коллег». Даже если такое перераспределение помешает остальным пулам получить свой гарантированный минимум. Для работы с Internal Pool предусмотрена одноимённая группа нагрузки (о понятии workload group см. ниже). Разумеется, что другие группы этот пул использовать не могут.

В отличие от internal, Default Pool допускает изменение настроек. По своему назначению он похож на любой другой пользовательский пул с тем лишь отличием, что его нельзя удалить. Правда, у Default Pool есть ещё одно назначение. Когда вы выключаете Resource Governor, настройки пулов сбрасываются (MIN%=0, MAX%=100), и все новые сессии начинают разделять пул Default. Таким образом, конкурентная борьба за ресурсы будет выглядеть приблизительно так, как это происходило в MS SQL Server 2005.

Когда речь заходит о пулах, вы можете столкнуться с понятиями Effective MAX% и Shared%.

Фактический MAX% (Effective MAX%) и Shared%

Каждый раз, создавая пул с ненулевым значением MIN%, вы тем самым перераспределяете фактически доступный максимум для остальных пулов. Effective MAX% показывает верхний предел ресурсов, доступных пулу в условиях конкуренции и с учётом гарантированных минимумов других пулов. Последнее как раз и отличает Effective MAX% от MAX%.

Для вычисления фактического MAX% BOL предлагает использовать формулу:

Effective Max%x = min(MAX%x, 100 - SUM1..n (MIN%)),

где x — интересующий нас пул, а n — общее количество пулов.

Согласитесь, не очень наглядно. Поэтому приведённый ниже пример (табличка тоже из BOL) я попытался отобразить графически.

Пример. Предположим, мы задали следующую конфигурацию (описание таблицы приведено ниже):

Pool name	MIN % setting	MAX % setting	Calculated effective MAX %	Calculated shared %
internal	0	100	100	0
default	0	100	25	30
Pool 1	20	100	45	25
Pool 2	50	70	70	20
Pool 3	5	100	30	25

Учитывая, что к Internal понятие Effective Max% неприменимо (вернее, его значение всегда равно 100%), я продемонстрирую только два самых показательных случая с Pool 1 и Pool 2 (остальные по аналогии).

На рисунке 1 показано, в каком процентном соотношении мы распределили ресурс, задав MIN%. Даже если все пулы одновременно (кроме Internal) будут нуждаться в ресурсе, RG сможет гарантировать им установленный минимум. На диаграмме видно, что 25% (заштрихованная область) не закреплено ни за одним пулом, а значит, является разделяемой (shared) частью и может использоваться совместно всеми пулами.

Указывая MAX% = 100% для Pool 1, мы полагаем, что максимальная доля ресурса, доступная пулу, будет колебаться от 20% до 100%. Однако активность потребителей Pool 2 и Pool 3 вынудит сервер отдать им как минимум по 50% и 5% соответственно. Тогда в распоряжении Pool 1 останется 20% минимума и не более чем 25% из разделяемой части. В итоге фактический MAX% уменьшится со 100% до 45% (20% + 25%).

Эта ситуация проиллюстрирована на рисунке 2. Мы переместили долю Pool 1 (от перестановки соотношение не меняется) на верхний уровень диаграммы и спроецировали на неё MAX%, взяв за основание нижнюю границу Pool 1 MIN%. Область, обведённая жирной линией, как раз и будет составлять Effective MAX%.

Тем же способом рассчитывается фактический максимум для второго пула (рисунок 3). Переместив Pool 2 MIN% на вершину распределения, мы отразили на нём MAX%.

Минимальная доля этого пула равна 50%. Как и все остальные пулы, Pool 2 может претендовать на общую часть в 25%, что в сумме даёт 50% + 25% = 75%. Но мы сами ограничили этот показатель, установив MAX% = 70% и, следовательно, Effective MAX% составит 70% (обведено жирной линией).

С фактическим максимумом тесно связано понятие Shared%. Этот показатель используется, чтобы понять, кто из пулов может на время одолжить ресурс и в каком количестве. Вычисляется он по формуле:

Shared%x = Effective Max%x - MIN%x

На наших схемах Shared% — пересечение заштрихованной области с окном, обведённым красной рамкой. Так, к примеру, Pool 2 Shared% = 70% - 50% = 20%.

Группы нагрузки (Workload Groups)

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

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

В детском стихотворении, с которого мы начали наше предание о Resource Governor, у бобра есть все признаки группы нагрузки, пусть даже и состоящей из одного представителя. И утята ничем не хуже, они тоже группа нагрузки. И котята. И даже волк-зубами-щёлк, запрашивающий ресурс, подозрительно похож на workload group.

На каждый из предопределённых пулов разработчики предусмотрели по группе нагрузки.

Internal group является официальным потребителем пула Internal. Мы уже говорили, что Internal Pool оберегается как священная корова, поэтому никакие другие группы не могут на него посягать. Зато такая схема позволяет отслеживать «представительские расходы» самого сервера.

Default group создана с целью использования пула Default. Вы можете менять настройки группы, но никогда не заставите ее сменить пул. Удалить ее тоже не получится, как ни пытайтесь.

Я не буду сейчас вдаваться в подробности поведения внутри групп (например, как утята поделят свою долю апельсина), этот вопрос будет подробно рассмотрен ниже.

Классификация (Classification)

Процесс, позволяющий серверу определить, какой запрос к какой группе нагрузки относится, называется классификацией. В качестве объекта классификации в Katmai всегда выступает сессия, а её принадлежность к группе выявляется только на этапе образования. Сменить группу после классификации сессия уже не может (как говорится, бобром родился — бобром умрёшь).

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

Карл проникся проблемами друзей наших меньших и разработал систему научной классификации (изданную впоследствии как «Systema naturae sive regna tria naturae systematice proposita per classes, ordines, genera, & species»). Натуралист выделил у каждого животного отличительные признаки, позволяющие безошибочно определять его группу. Благодаря усилиям Карла, в лесу вновь восторжествовал закон и порядок.

В Resource Governor классификация выглядит приблизительно также. Вы создаёте скалярную пользовательскую функцию (UDF), играющую роль Линнея, и регистрируете её в качестве классификатора. Каждый раз, когда возникает новое подключение, сервер вызывает вашу функцию и ожидает, что она вернёт имя группы для этой сессии. Вы можете реализовать свою логику классификации (вашу собственную «Systema Naturae»), основываясь на так называемых атрибутах соединения (Connection-specific attributes). К ним относятся такие атрибуты сессии, как имя пользователя, имя хоста, база данных по умолчанию и т.д.

Чтобы отразить процесс классификации, я взял рисунок 4 из BOL и изменил его до неузнаваемости :).

Рисунок 4.

Пользователь подключается к MS SQL Server 2008.
Resource Governor вызывает функцию-классификатор.
Функция возвращает имя группы согласно реализованной в ней логике (предположим, это Group 3).
Resource Governor направляет всю последующую нагрузку этой сессии в соответствующую workload group (раз функция вернула Group 3, значит в Group 3).
Группе доступны ресурсы ассоциированного с ней пула (для Group3 это Pool 1).

Ещё раз подчеркну важный момент. Если при создании сессия была направлена в Group 3, то перебросить потом её нагрузку, например, в Group 2 или Group 1 уже не представляется возможным.

Также следует внимательно относиться к «внутренностям» функции-классификатора и тщательно её тестировать перед вводом в эксплуатацию. Неправильная работа этой UDF может привести к невозможности подключиться к серверу или к длительному ожиданию подключения пользователями.

Правда, некоторые последствия некорректной реализации classifier UDF сервер может устранить сам. Продемонстрируем это на примере с лесными обитателями (я уже чувствую, как во мне просыпается Николай Дроздов).

Итак, лесная опушка. Солнце светит, бобёр строит плотину, ничто не предвещает беды. И тут с высоких гор спускается Йети и просит выделить ему долю апельсина. Звери в шоке, Линней хватается за сердце, на поляне возникает паника. Где-то вдалеке раздаются автоматные трели и недовольные выкрики «понаехали тут!».

Никто не знает что делать. С одной стороны, в «Systema Naturae» о Йети не сказано ни слова. С другой — снежный человек существует и стоит во всей красе прямо перед ними, хоть сейчас бери и портрет пиши.

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

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

К счастью, Resource Governor не может себе позволить ссорится с пользователями. Если функция-классификатор не даёт однозначного ответа о принадлежности сессии к группе (возвращает null или название несуществующей группы), то нагрузка этой сессии будет перенесена на плечи Default group, а ресурсы будут черпаться из пула Default.

реконФигурное катание

Разработчики MS SQL Server 2008 приложили массу усилий, чтобы общение с новой технологией проходило в лёгкой и непринуждённой атмосфере. На зависть всем Эллочкам Людоедкам лексикон Katmai обогатился словарным запасом из семи интуитивно понятных DDL-инструкций, так что «трудности перевода» обойдут вас стороной, и прибегать к языку жестов, настраивая RG, не придётся.

Принцип завинчивания гаек в Resource Governor чем-то напоминает работу с процедурой sp_configure. Заданные через sp_configure значения не применяются до тех пор, пока не будет выполнена команда RECONFIGURE. Аналогичный подход используется и для установки параметров RG (рисунок 5).

Рисунок 5.

Сначала с помощью DDL-инструкций мы дотошно объясняем серверу, какую конфигурацию хотим получить: «Землю — крестьянам, фабрики — рабочим, броневик — в автосервис и т.д.» (1). Сервер, как Надежда Крупская, стенографирует наши пожелания в блокнотик с метаданными. Руководствуясь поговоркой «семь раз отмерь, один раз отрежь», просим показать, что в итоге получилось (2). Если всё в порядке, и мы ничего не напутали, распоряжаемся эти указания претворить в жизнь (3). Результат можно проконтролировать, используя соответствующие DMV (4).

Эксперимент 1. Настройка RG и распределение CPU между пулами.

Для выполнения нам потребуются три учётные записи user1, user2 и user3, входящие только в роль public. Также предполагается, что на первом этапе эксперимента Resource Governor выключен, и вы не меняли его конфигурацию.

Подключитесь к серверу под административной учётной записью и установите контекст на БД master. Все изменения метаданных осуществляйте в рамках транзакции. Она позволит обеспечить целостность настроек и сэкономит вам не один миллион нервных клеток.

use master
go

set nocount on

begin tran

Опишите пулы и группы нагрузки.

create resource pool Pool1
with (
  min_cpu_percent = 30
  , max_cpu_percent = 100
)

create resource pool Pool2
with (
  min_cpu_percent = 50
  , max_cpu_percent = 70
  
)

create resource pool Pool3
with (
  min_cpu_percent = 5
  , max_cpu_percent = 100
  
)

create workload group Group1
using Pool1

create workload group Group2
using Pool2

create workload group Group3
using Pool3

Если задать для Pool3 min_cpu_percent равным не 5, а 55, то сумма минимумов превысит 100%. Это чревато ошибкой на этапе выполнения: «The specified 'min_cpu_percent' value, 55, causes the sum of minimums on all resource pools to exceed 100 percent». Отсутствие открытой транзакции в данном случае выльется в создание Pool1 и Pool2, в то время как Pool3 и группы нагрузки созданы не будут. Так что не усложняйте себе жизнь, всегда обрамляйте настройки RG в транзакцию.

Реализуйте функцию-классификатор.

-- Завершаем предыдущий batch, так как объявление функции
-- должно быть первой инструкцией в пакете. 
go

-- Классификатор может быть создан только в БД master.
create function dbo.rg_class_simple() returns sysname
with schemabinding -- обязательное требование к классификатору
as begin
    declare
        @grp_name as sysname
    
    if (suser_name() = 'user1')
        -- Нагрузка всех сессий user1 будет направлена в Group1
        set @grp_name = 'Group1'

    else if (suser_name() = 'user2')
        -- Нагрузка всех сессий user2 будет направлена в Group2
        set @grp_name = 'Group2'

    else if (suser_name() = 'user3')
        -- Нагрузка всех сессий user3 будет направлена в Group3
        set @grp_name = 'Group3'
        
    -- Остальные сессии будут работать в контексте Default
    
    return @grp_name
end
go

Зарегистрируйте вашу функцию.

alter resource governor
with (classifier_function = dbo.rg_class_simple)
go

Проверьте метаданные RG.

select
  object_name(classifier_function_id) as classifier_function
  , is_enabled -- если RG выключен, здесь должен быть 0
from sys.resource_governor_configuration

classifier_function  is_enabled
-------------------- ----------
rg_class_simple      0

Проверьте конфигурацию пулов (в этом эксперименте нас интересует только распределение CPU).

select 
  pool_id
  , name
  , min_cpu_percent
  , max_cpu_percent
from sys.resource_governor_resource_pools

pool_id     name         min_cpu_percent max_cpu_percent
----------- ------------ --------------- ---------------
1           internal     0               100
2           default      0               100
260         Pool1        30              100
261         Pool2        50              70
262         Pool3        5               100

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

select
  group_id
  , a.name
  , a.pool_id
  , b.name as pool_name
from sys.resource_governor_workload_groups a
inner join sys.resource_governor_resource_pools b on a.pool_id = b.pool_id

group_id    name         pool_id     pool_name
----------- ------------ ----------- ------------
1           internal     1           internal
2           default      2           default
256         Group1       260         Pool1
257         Group2       261         Pool2
258         Group3       262         Pool3

Подтвердите транзакцию.

commit

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

select
  is_reconfiguration_pending 
from sys.dm_resource_governor_configuration

is_reconfiguration_pending
--------------------------
1

Дайте указание свыше следовать заветам Ильича.

-- Дорогой сервер, прими, пожалуйста, во внимание наши пожелания!
alter resource governor reconfigure

В каком случае сервер может отказать? Ну, например, если инструкцией DROP WORKLOAD GROUP вы удалили группу нагрузки из метаданных, и к этому времени в контексте удалённой группы работала одна или несколько сессий. Сервер откажет вам не только в удалении группы, но и во всех изменениях, которые были внесены в конфигурацию с момента предыдущего ALTER RG RECONFIGURE.

Хочу уберечь вас от искушения объединить инструкцию, выполненную на шаге 4, с ALTER RESOURCE GOVERNOR RECONFIGURE. Это две взаимоисключающие команды! К примеру, мы можем написать так:

alter resource governor
with (classifier_function = dbo.rg_class_simple)reconfigure

Синтаксический анализатор распознает в этом две независимые инструкции

alter resource governor with(…)
reconfigure (которая используется после вызова sp_configure).

Убедитесь, что настройки вступили в силу.

select
  object_name(classifier_function_id) as classifier_function
  , is_reconfiguration_pending –- обнуляется после успешного reconfigure
from sys.dm_resource_governor_configuration

-- Статистика и текущая конфигурация пулов
select * 
from sys.dm_resource_governor_resource_pools

-- Статистика и текущая конфигурация групп нагрузки
select *
from sys.dm_resource_governor_workload_groups

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

create function dbo.session_group_context()
returns sysname with execute as owner
as begin

declare @group_name sysname
select
  @group_name = g.name
from sys.dm_exec_sessions s
inner join sys.resource_governor_workload_groups g on s.group_id = g.group_id
where
  session_id = @@spid

  return @group_name
end
go

-- Даём права на запуск функции роли public
grant exec on object::dbo.session_group_context to public

Откройте три сессии под учётными записями user1, user2 и user3. В каждой из них выполните:

select dbo.session_group_context()

У вас должно получиться Group1, Group2 и Group3 соответственно.

Запустите Perfmon и выберите счётчик CPU usage % объекта SQLServer:Resource Pool Stats. Он покажет, как распределяется процессорное время между пулами. Отметьте в списке только экземпляры Pool1, Pool2 и Pool3.
В окне сессии user1 запустите скрипт, интенсивно использующий ресурсы CPU.

set nocount on
declare
  -- MS SQL Server 2008 допускает присвоение значений 
  -- в объявлении переменной
  @i    int = 1
  , @name  varchar(128)
  
while (@i != 500000) begin

  select 
    @name = name
  from sys.sysobjects
  order by newid()

  set @i += 1 -- Теперь так тоже можно ;)
end

Выждав несколько секунд, запустите скрипт с нагрузкой на CPU сначала в сессии user2, а затем user3. Результат, который должен получиться на двуядерной машине с привязкой SQL-сервера к одному логическому процессору (affinity mask = 1), изображён на рисунке 6 (я нарочно отключил один узел, чтобы не вдаваться сейчас в нюансы параллелизма).

Рисунок 6.

Для ответа на излюбленный вопрос психотерапевта «Что вы видите на этой картинке?» нам потребуется воскресить в памяти термин Effective MAX% (обсуждалось в первой части статьи) и, пока свежо преданье старины глубокой, сочинить вычисляющую его функцию.

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

-- Создаём табличный тип, чтобы использовать его в параметрах функции
create type PoolList as table (pool_id int)
go

create function dbo.pools_cpu_effective_max(
  @pools PoolList readonly
) returns table
  return (
    select
      pool_id
      , name
      , min_cpu_percent
      , max_cpu_percent
      , isnull(
        case
        when name = 'internal' then 100 
        when max_cpu_percent < (100 - sum_of_cpu_min) then max_cpu_percent
        else (100 - sum_of_cpu_mein)
        end
      , 100) as effective_cpu_max
    from (
      select
        pls.pool_id
        , name
        , min_cpu_percent
        , max_cpu_percent
        , (
          select
            sum(min_cpu_percent)
          from sys.resource_governor_resource_pools x
          inner join @pools tp on x.pool_id = tp.pool_id
          where x.pool_id != pls.pool_id
        ) as sum_of_cpu_min
      from sys.resource_governor_resource_pools pls
      inner join @pools tp on pls.pool_id = tp.pool_id
    )pls
  )  
go

Воспроизведём события в хронологическом порядке.

В точке (A) начал выполнение нагрузочный скрипт сессии user1, которая через группу Group1 потребляет ресурсы Pool1 (чёрная ветка на графике). Так как потребители других пулов к этому моменту в CPU не нуждаются, Resource Governor отдаёт user1 всё, чем располагает сам. А располагает он только временем одного ядра (второе мы у него отобрали, задав affinity). Таким образом, SQL-сервер может использовать только 50% от ресурса машины, что и составит 100% ресурса, доступного пулам.

Как мы можем убедиться, расчёт Pool1 Effective MAX% не входит в противоречие с поведением сервера (пул может занять от 30% до 100%).

declare
  @pools PoolList

insert @pools values (260) –- Идентификатор Pool1
select * from dbo.pools_cpu_effective_max(@pools)

pool_id     name         min_cpu_percent max_cpu_percent effective_cpu_max
----------- ------------ --------------- --------------- -----------------
260         Pool1        30              100             100

В точке (B) появляется потребитель Pool2, олицетворяемый сессией user2. Resource Governor корректирует распределение времени CPU в рамках изменившихся фактических максимумов, выделив каждому пулу по 50% (или 25%, если принимать во внимание неиспользуемое SQL-сервером ядро процессора).

declare
  @pools PoolList

insert @pools values (260), (261) –- Идентификаторы Pool1 и Pool2 соотв.
select * from dbo.pools_cpu_effective_max(@pools)

pool_id     name         min_cpu_percent max_cpu_percent effective_cpu_max
----------- ------------ --------------- --------------- -----------------
260         Pool1        30              100             50
261         Pool2        50              70              70

Ещё раз перераспределение происходит в точке (С), когда сессия user3 начинает выполнять свою работу. Её доля процессорного времени задана в параметрах Pool3.

Новые значения Effective MAX% вступают в силу, что тут же сказывается на Pool1 (Pool2 ещё на предыдущем этапе «упёрся» в свой минимум, поэтому экспроприировать время можно только у Pool1).

declare
  @pools PoolList

insert @pools select pool_id from sys.resource_governor_resource_pools
select * from dbo.pools_cpu_effective_max(@pools)

pool_id     name         min_cpu_percent max_cpu_percent effective_cpu_max
----------- ------------ --------------- --------------- -----------------
1           internal     0               100             100
2           default      0               100             15
260         Pool1        30              100             45
261         Pool2        50              70              65
262         Pool3        5               100             20

Не останавливая нагрузочные скрипты, измените в административной сессии настройки Pool3. Напомню, что ранее MAX CPU% у этого пула был равен 100, а используемое значение в точке (С) составляло 20%.

Увеличение MAX% до уровня выше используемого на текущий момент никакого влияния на распределение времени CPU между пулами не оказывает. Тогда как уменьшение до величины ниже используемой приведёт к очередному переделу (опять же в рамках от MIN% до Effective MAX%).

alter resource pool Pool3
with (max_cpu_percent = 10)
go

alter resource governor reconfigure
go

Pool3 высвободил 10% ресурса, которые достались самому обделённому пулу, а именно Pool1 (точка (D) на рис.7). Вообще говоря, сервер всегда пытается «помогать» наиболее ограниченным пулам, отдавая им предпочтение при раздаче освободившихся ресурсов. Так может продолжаться до тех пор, пока они не достигнут своего Effective Max% (Pool3 уже достиг своего максимума в 10% и Resource Governor не позволит ему сейчас взять больше).

Уменьшите MIN% для Pool2 до 40%. Излишек будет поделён поровну между Pool1 и Pool2 (точка (E) на рисунке 7). Любое изменение MIN% приводит к перерасчёту Effective MAX% и Shared%.

alter resource pool Pool2
with (min_cpu_percent = 40)
go

alter resource governor reconfigure
go

Рисунок 7.

Не останавливая работу скриптов, выключите Resource Governor. Обратите внимание, что сервер применил для всех пулов настройки по умолчанию (MIN = 0, MAX = 100). На графике вы увидите, как все пулы заняли по ~33% (100% / [кол-во пулов]). При этом метаданные в представлениях sys.resource_governor_* остались неизменны.

alter resource governor disable
go

select
  name
  , min_cpu_percent
  , max_cpu_percent 
from sys.dm_resource_governor_resource_pools

name         min_cpu_percent max_cpu_percent
------------ --------------- ---------------
internal     0               100
default      0               100
Pool1        0               100
Pool2        0               100
Pool3        0               100

Все новые подключения, которые вы будете выполнять от имени любой учётной записи (включая user1, user2 и user3), попадут уже в группу Default.

Фарш невозможно провернуть назад

Жизненный опыт мне подсказывает, что обратить фарш в исходное состояние – задача не из лёгких и вряд ли под силу даже лучшим умам микрохирургии. Такова реальность. Но, несмотря на все уговоры здравого смысла, иногда так хочется предпринять одну, пусть и маленькую, но всё-таки попытку вернуть былое и начать с чистого листа.

Если в этом месте вы подумали, что меня давно нужно облачить в смирительную рубашку, изолировать от общества и прописать седативные препараты (50 мг галоперидола как самому буйному автору журнала), то не стоит беспокоиться — рассудком я не помутился. Речь по-прежнему идёт о Resource Governor.

Не следует заблуждаться на предмет того, что вы с первого раза создадите идеальную конфигурацию RG. Да, возможно сначала она покажется пределом совершенства. Затем последует череда неутешительных экспериментов, и вот уже второй час как вы прокручиваете фарш против часовой стрелки, пытаясь избавиться от своих же собственных настроек. Мир SQL Server устроен так, что делать это нужно в строго заданной последовательности, иначе все попытки будут сопровождаться возгласами «Мама, роди меня обратно!».

Чтобы как-то помочь (в первую очередь себе), я написал процедуру reset_rg, которая возвращает конфигурацию Resource Governor в первозданный вид, каким он был при установке сервера. Процедура не предназначена для участия в конкурсе красоты, так что не обессудьте.

Алгоритм reset_rg

Отключить Resource Governor, предотвратив тем самым появление новых подключений в пользовательских группах.
Закрыть командой kill все сессии, существующие в пользовательских группах нагрузки.
Удалить все пользовательские группы из метаданных.
Восстановить значения параметров группы default по умолчанию.
Удалить все пользовательские пулы ресурсов.
Восстановить значения параметров пула default по умолчанию.
Отказаться от использования функции, указанной в конфигурации RG, в качестве классификатора.
Применить сделанные изменения, выполнив реконфигурацию RG.

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

create proc dbo.reset_rg (
  @kill_sessions    bit = 1  -- Принудительно завершать открытые сессии
)
as begin

set nocount on

declare
  -- Буфер для динамических вызовов
  @str_buffer    varchar(500)      
  
  --======= Переменные для работы с сессиями =======--
  -- Текущая сессия в цикле
  , @spid        int
  -- Максимальное время ожидания на spin-блокировке в секундах    
  , @spin_timeout    int      = 30
  -- Кол-во раундов spin-блокировки до "засыпания"  
  , @spin_count_limit  tinyint    = 5  
  -- Кол-во прошедших раундов spin-блокировки  
  , @spin_count    tinyint    = 0  
  -- Время входа в spin-блокировку  
  , @start_spining  datetime
  -- Признак того, что для тек. сессии уже была вызвана команда kill  
  , @kill_pending    bit          

  --======= Переменные для работы с группами =======--
  -- Идентификатор текущей группы нагрузки
  , @group_id      int    
  -- Имя текущей группы нагрузки      
  , @group_name    sysname        
  
  --======= Переменные для работы с пулами =======--
  -- Идентификатор текущего пула
  , @pool_id      int          
  -- Имя текущего пула
  , @pool_name    sysname        
  
  

/* 
  Так как мы собираемся избавиться от пользовательских групп,
  то сами должны работать либо в default, либо в internal.
*/
if (
  not exists(
    select *
    from sys.dm_exec_sessions
    where 
      session_id = @@SPID
      and (group_id = 1 or group_id = 2)
    )
)begin
  raiserror (
    N'Процедура запущена в сессии пользовательской группы нагрузки.', 16, 1)
  goto the_end  
end

-- Выключаем Resource Governor, чтобы новые подключения уже не могли
-- попасть в пользовательские группы нагрузки

alter resource governor disable

-- Если флажок @kill_sessions выставлен, 
-- отправим в кромешный Адлер все открытые соединения, 
-- существующие в пользовательских группах

-- Если @kill_sessions = 0 и сессии есть, то разразимся ошибкой.

while (1 = 1) begin

  set @spid = null

  select
    @spid = s.session_id
  from sys.dm_exec_sessions s
  inner join sys.resource_governor_workload_groups g on s.group_id = g.group_id
  where
    g.group_id != 1 and g.group_id != 2
  
  if ((@spid is not null) 
    and (@kill_sessions = 0)) 
    begin
    raiserror (N'В пользовательских группах есть сессии.', 16, 1)
    goto the_end
  end
  
  if (@spid is null) goto reset_metadata

  select
    @start_spining = getdate()
    , @kill_pending = 0
    , @spin_count = 0
    
  -- Принудительно завершаем текущую сессию 
  -- и ждём на spin-блокировке, пока она удалится
  while (exists(
    select * 
      from sys.dm_exec_sessions 
      where session_id = @spid)) begin
  
    set @spin_count+=1
    
    if (@kill_pending = 0) begin  
      set @str_buffer = 'kill ' + cast(@spid as varchar)
      exec (@str_buffer)
      set @kill_pending = 1
      print '[' + cast(@spid as varchar) + N'] произведён контрольный выстрел'
    end 
    
    print '[' + cast(@spid as varchar) + N'] сессия всё ещё жива, пульс в норме'
    
    if (@spin_count > @spin_count_limit)
      waitfor delay '00:00:01'
    
    if (DATEDIFF(ss, @start_spining, GETDATE()) > @spin_timeout) begin
      raiserror (
        N'Время ожидания истекло, сессия [%d] так и не умерла', 16, 1, @spid)
      goto the_end
    end
      
  end
  
  print '[' + cast(@spid as varchar) + N'] сессия отправилась в мир иной ' + char(134)
  
end

reset_metadata:

  begin tran
   
    while (1 = 1) begin    
    
      select
        @group_id = null
    
      select top 1
        @group_id = group_id
        , @group_name = name
      from sys.resource_governor_workload_groups
      where
        name != 'internal' 
        and name != 'default'
        
      
      if (@group_id is not null) begin
      
        set @str_buffer = 'drop workload group ' + quotename(@group_name)
        exec (@str_buffer)
        
        if @@error != 0 begin
          rollback
          goto the_end
        end
          
        print 
          quotename(@group_name) + N' группа нагрузки уничтожена ' + char(134) 
      end else
        break
    end  

    -- Восстанавливаем значения по умолчанию для группы default
    
    alter workload group [default]
    with (
      importance = medium
      , request_max_memory_grant_percent = 25
      , request_max_cpu_time_sec = 0
      , request_memory_grant_timeout_sec = 0
      , max_dop = 0
      , group_max_requests = 0
    )
    

    while (1 = 1) begin    
    
      select
        @pool_id = null
    
      select top 1
        @pool_id = pool_id
        , @pool_name = name
      from sys.resource_governor_resource_pools
      where
        name != 'internal' and name != 'default'
        
      if (@pool_id is not null) begin
      
        set @str_buffer = 'drop resource pool ' + quotename(@pool_name)
        exec (@str_buffer)
        
        if @@error != 0 begin
          rollback
          goto the_end
        end
        
        print quotename(@pool_name) + N' пул ресурсов уничтожен ' + char(134) 
      end else
        break
    end    

    -- Восстанавливаем значения по умолчанию для пула default    
    alter resource pool [default]
    with (
      min_cpu_percent = 0
      , max_cpu_percent = 100
      , min_memory_percent = 0
      , max_memory_percent = 100
    )

    -- Сбрасываем функцию-классификатор
    alter resource governor
    with (classifier_function = null)  
    
  commit
  
  -- Применяем настройки и включаем Resource Governor  
  alter resource governor reconfigure

the_end:
  -- Парадный выход
end
go

Пример вызова для конфигурации из эксперимента 1 с двумя активными сессиями:

exec dbo.reset_rg 1

[57] произведён контрольный выстрел
[57] сессия всё ещё жива, пульс в норме
[57] сессия всё ещё жива, пульс в норме
[57] сессия всё ещё жива, пульс в норме
[57] сессия всё ещё жива, пульс в норме
[57] сессия всё ещё жива, пульс в норме
[57] сессия всё ещё жива, пульс в норме
[57] сессия отправилась в мир иной †
[56] произведён контрольный выстрел
[56] сессия отправилась в мир иной †
[Group1] группа нагрузки уничтожена †
[Group2] группа нагрузки уничтожена †
[Group3] группа нагрузки уничтожена †
[Pool1] пул ресурсов уничтожен †
[Pool2] пул ресурсов уничтожен †
[Pool3] пул ресурсов уничтожен †

Первое слово дороже второго

Для Resource Governor это отнюдь не так. Обратите внимание на параметр min_memory_percent.

alter resource pool [default]
with (
  min_cpu_percent = 0
  , max_cpu_percent = 100
  , min_memory_percent = 0
  , max_memory_percent = 100
  , min_memory_percent = 5
  , min_memory_percent = 10
  , min_memory_percent = 20

)
go

select * 
  from sys.resource_governor_resource_pools


pool_id name  min_cpu_percent max_cpu_percent min_memory_percent max_memory_percent
------- --------- --------------- --------------- ------------------ ----------
1      internal 0               100             0                  100
2      default  0               100             20                 100

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

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