Грег Лоу
До выхода в свет версии 2008 процессор баз данных SQL Server стремился сбалансировать производительность всех работающих в данный момент пользователей, так что администратор практически не мог устанавливать приоритеты для тех или иных пользователей или рабочих нагрузок. Кроме того, не следует забывать о том, что во многих организациях выполняются сотни, а то и тысячи приложений. Администраторы баз данных имеют весьма смутное представление о том, какие приложения используются в данное время, и им трудно обращаться к базам данных этих приложений. Ситуация усугубляется тем, что большие группы пользователей и приложений работают с конкретными учетными записями, применяемыми при регистрации в системе.
В версии SQL Server 2008 реализован менеджер ресурсов Resource Governor, предоставляющий администратору определенные возможности контроля использования ресурсов. С помощью этого компонента можно предотвращать возникновение проблем, связанных с быстродействием, и научиться определять, какие приложения выполняются в тот или иной момент. Надо сказать, что на сегодня Resource Governor не свободен от ограничений, но его возможностей достаточно для решения многих типичных проблем, связанных с ресурсами. В данной статье речь пойдет о том, как управлять ресурсами процессора и памяти с помощью пулов ресурсов, как назначать пулам ресурсов приложения и как назначать сеансы группам рабочих нагрузок.
Создание пула ресурсов
Работа Resource Governor состоит в управлении ресурсами процессора и памяти, выделенными для определенного сеанса. Администратор может указать выраженную в процентах максимальную и минимальную долю ресурсов процессора и памяти, которые может использовать приложение, а также максимально допустимую степень параллелизма. Для этого нужно создать большие пулы ресурсов, по которым будут распределены сеансы, и установить для них минимальные и максимальные доли в процентах с помощью команды CREATE RESOURCE POOL. Например, команды в листинге 1 создают три пула ресурсов, ограниченные процентом использования времени процессора.
Проценты, указанные в этих командах, не являются жесткими ограничителями. Так, если в системе выполняется лишь одно приложение с низким приоритетом, этому приложению будут предоставлены все ресурсы процессора. Управление ресурсами по критерию процессорного времени осуществляется лишь тогда, когда к этому ресурсу параллельно обращается несколько конкурирующих программ.
Разработчики Resource Governor установили максимальное число пулов равным 20, причем 18 из них доступны для настройки. Из оставшихся двух пулов один выделяется для внутренних процессов, таких как операции по очистке, а второй является пулом по умолчанию. Он используется для сеансов, не привязанных к другим пулам. Следовательно, не стоит создавать слишком много пулов ресурсов на ранних этапах работы. Рекомендуется заранее продумать, какие пулы могут вам понадобиться.
Создание групп рабочей нагрузки
Теперь, когда мы сгруппировали имеющиеся ресурсы, можно приступать к группировке приложений, которые будут назначены для этих пулов ресурсов. Задача решается с помощью команды CREATE WORKLOAD GROUP. В листинге 2 представлено несколько примеров команд CREATE WORKLOAD GROUP.
В листинге 2 создается пять групп рабочей нагрузки, и эти группы назначаются трем пулам ресурсов. Приложения Access и Excel размещаются в раздельных пулах - если в будущем вы захотите выделить одному из этих приложений больше ресурсов, чем второму, у вас будет такая возможность. Слишком "напористым" приложениям Access и приложениям, которые мы не сможем идентифицировать, будут выделяться небольшие объемы ресурсов. Приложениям, которые мы идентифицировали как жизненно важные для нашего бизнеса, в отношении ресурсов будет предоставляться наивысший приоритет.
Просмотр настроек Resource Governor
После создания групп рабочей нагрузки и назначения их пулам ресурсов эти группы не начинают работать сразу же. Дабы определить, что именно необходимо настроить дополнительно для приведения Resource Governor в рабочее состояние, администратор может просмотреть текущую конфигурацию Resource Governor. Это можно сделать в новом динамическом административном представлении. Выполнив команду
SELECT * FROM sys.dm_resource_ governor_configuration;
вы получите выходные данные, представленные на экране 1.
Отметим, что эти выходные данные отображаются на вкладке Results в окне SQL Server Management Studio (SSMS). Значение 1 в левом столбце - это номер строки выходных данных.
Приведенные выходные данные указывают на два обстоятельства. Во-первых, флажок is_reconfiguration_pending означает, что необходимо выполнить команду ALTER RESOURCE GOVERNOR RECONFIGURE до того, как внесенные изменения вступят в силу. После выполнения этой команды значение is_reconfiguration_pending будет равно нулю. Во-вторых, значение нуль для classifier_function_id указывает на то, что классифицирующая функция не была назначена (хотя мне кажется, что для данного значения этот столбец должен возвращать не нуль, а значение NULL). Функция-классификатор используется для того, чтобы определить, в какой группе рабочей нагрузки должен быть размещен сеанс. Вопросом о том, как использовать функции-классификаторы, мы займемся в самое ближайшее время.
Далее можно просмотреть конфигурацию пула ресурсов, выполнив команду
SELECT * FROM sys.dm_ resource_governor_ resource_pools;
которая возвращает выходные данные, показанные на экране 2.
Обратите внимание: до тех пор, пока не будет выполнена команда перенастройки, в числе выходных данных будут указаны только внутренний пул и пул по умолчанию. Чтобы увидеть настройки пула рабочей нагрузки, нужно выполнить команду
SELECT * FROM sys.dm_resource_ governor_workload_groups;
которая возвращает выходные данные, показанные на экране 3.
Классификация приложений
Последний этап процедуры настройки Resource Governor - идентификация сеансов, которыми требуется управлять. Задачу можно решить с помощью функции-классификатора, назначающей сеанс конкретной группе рабочей нагрузки, как показано в листинге 3.
Функции-классификаторы должны возвращать значение типа SYSNAME, которое является псевдонимом для NVARCHAR (128). К какой группе рабочей нагрузки должен быть отнесен тот или иной сеанс, зависит от функции имени приложения (APP_NAME ()). Эта функция возвращает значение, которое было указано в строке соединения от данного приложения. Я питаю слабость к данному методу, поскольку он позволяет принимать решения по классификации в зависимости от того, какое приложение выполняется в данный момент. Существует и ряд других методов классификации сеансов, но о них речь пойдет позже.
Нередко приложения проектируются таким образом, что имя приложения не указывается в строке соединения. Однако важно добавлять имя приложения в строку соединения или указывать его значение в файлах настройки приложения. Это полезно делать и при решении задач, не связанных с менеджером Resource Governor, так как имя приложения появляется в трассировочных записях, созданных профилировщиком SQL Server Profiler, и может быть использовано для фильтрации событий внутри этих трассировочных записей. Кроме того, доступ к именам приложений с помощью строк соединения может пригодиться в случае выполнения трассировки доступа к SQL Server при попытке изолировать проблему, связанную с тем или иным приложением. Однако в некоторых не столь удачно спроектированных приложениях используются жестко запрограммированные строки соединения, с которыми нельзя работать описанными выше методами. Поэтому жесткого программирования строк соединения следует избегать.
Кроме того, я пришел к заключению, что классификация приложений по именам - это хорошая отправная точка для идентификации неизвестных приложений, имеющих соединение с сервером. Между прочим, вся прелесть использования менеджера ресурсов в подобных ситуациях состоит в том, что неизвестные приложения размещаются в пуле ресурсов с низким приоритетом, и его пользователи, по всей вероятности, быстро идентифицируют свою прикладную программу. Эта функция помогает администраторам определять, кто фактически использует их системы.
Итак, в листинге 3 была использована функция APP_NAME (). Но надо сказать, что для классификации сеансов могут применяться и многие другие функции, например HOST_NAME (), SUSER_NAME (), SUSER_SNAME (), IS_SRVROLEMEMBER () и IS_MEMBER (). Если вы решите использовать в качестве классифицирующей функцию HOST_NAME () или APP_NAME (), имейте в виду, что эти функции могут быть изменены пользователями. Впрочем, мой опыт свидетельствует о том, что на практике функция APP_NAME () работает очень хорошо.
При принятии решений по классификации в дополнение к указанным функциям вы можете использовать ряд свойств. Сейчас функция LOGINPROPERTY () включает в себя два свойства (DefaultDatabase и DefaultLanguage), которые можно использовать в функциях-классификаторах. Кроме того, функция CONNECTIONPROPERTY () обеспечивает доступ к сетевому транспорту и протоколу, используемому для данного соединения, а также содержит детали схемы аутентификации, локальный IP-адрес, TCP-порт и IP-адрес клиента. Полезна также функция ORIGINAL_DB_NAME (): она возвращает имя базы данных, которая была предоставлена при установлении сеанса вместо базы данных по умолчанию. Упомянутые свойства дают администратору возможность принимать решения относительно классификации на основе любого из данных значений. Так, вы можете назначить соединение группе рабочей нагрузки в зависимости от того, с какой базой данных обычно работает данный пользователь.
Перед тем как приступать к применению функции-классификатора, важно протестировать ее; в противном случае система может зависнуть. Так, можно протестировать функцию-классификатор APP_NAME (), выполнив команду
SELECT APP_NAME ();
которая возвращает выходные данные, показанные на экране 4.
Поскольку среда SSMS не вошла в список приложений, о которых "знает" функция-классификатор, не приходится удивляться, что при выполнении команды
SELECT dbo.UserClassifier ();
система возвращает выходные данные, показанные на экране 5.
Если вы активировали классифицирующую функцию без каких-либо изменений, среда SSMS будет выполняться в пуле ресурсов для приложений с низким приоритетом. Но можно и не прибегать к жесткому программированию имен всех приложений в функции-классификаторе, а предписать этой функции осуществлять поиск имени группы рабочей нагрузки в таблице базы данных. Как правило, обращение к таблице с последующим поиском группы рабочей нагрузки не приводит к существенному снижению быстродействия, поскольку таблица обычно невелика по размерам, быстро кэшируется, а решения по классификации, для обеспечения которых и вызывается эта функция, принимаются только при первичном установлении соединения.
Теперь вы можете реализовать функцию-классификатор с помощью следующей команды:
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
Далее нужно перенастроить Resource Governor, с тем чтобы ввести в действие подготовленную конфигурацию. Это можно сделать с помощью команды
ALTER RESOURCE GOVERNOR RECONFIGURE;
На этом настройка менеджера Resource Governor заканчивается. Теперь следует убедиться, что программа работает, как положено. Если вы плохо протестировали функцию-классификатор, система может перестать реагировать на новые учетные имена, используемые для регистрации, и вам для получения доступа к SQL Server придется задействовать выделенное административное соединение (dedicated administrator connection, DAC). Если же вы не настроили режим DAC, то можете перезапустить сервер в однопользовательском режиме. При работе в этом режиме менеджер ресурсов не управляет работой соединения, так что Resource Governor можно отключить до тех пор, пока положение не будет исправлено.
Если же у вас возникнет необходимость модифицировать классифицирующую функцию, придется сначала отключить Resource Governor, поскольку при использовании Resource Governor модификация функции невозможна. Отключение текущей функции-классификатора выполняется с помощью следующей команды:
ALTER RESOURCE
GOVERNOR
WITH (CLASSIFIER_ FUNCTION = NULL);
Как правило, модификация Resource Governor поручается самому главному администратору баз данных в организации; это объясняется масштабом проблем, которые могут возникнуть, если Resource Governor будет настроен неправильно. И именно по этой причине в настоящей статье я использовал для реализации сценарии T-SQL, хотя данные операции можно выполнять непосредственно в среде SSMS. Для изменения настроек требуется разрешение CONTROL SERVER, а для просмотра настроек - разрешение VIEW SERVER STATE. Сбои классифицирующей функции можно выявлять с помощью событий в журнале приложений на сервере.
Другие методы мониторинга и настройки ресурсов
Объем ресурсов, выделяемых системой SQL Server, можно отслеживать и с помощью инструмента System Monitor (perfmon.exe). Обратите внимание на то, что при добавлении счетчиков, которые планируется отслеживать, объект SQLServer: Resource Pool Stats отображает экземпляры каждого из настроенных пулов ресурсов, как показано на экране 6.
Аналогичные связанные с экземплярами выборки были добавлены к счетчику SQLServer: Workload Group Stats. Соответствующие значения можно получить также с помощью запроса к представлению sys.dm_os_performance_counters.
Для осуществления более детального мониторинга в SQL Server были добавлены дополнительные события, такие как CPU Threshold Exceeded, PreConnect: Starting и PreConnect: Completed. Если вы хотите управлять менеджером Resource Governor программным путем с помощью кода .NET, можете воспользоваться классом ResourceGovernor, который был включен в число объектов SQL Server Management Objects (SMO).
В данной статье при настройке пулов ресурсов я сосредоточил внимание на максимальных процентных значениях времени использования процессора, поскольку необходимо проявлять особую осторожность при конфигурировании минимальных процентных значений. Легко наложить значительные ограничения на использование ресурсов системы, установив слишком низкие минимальные значения; в первую очередь это касается минимальных значений по используемой памяти.
Возможные трудности
Вероятно, вы задумываетесь об ограничениях, о которых я упомянул ранее. Функция великолепна, но в чем тут подвох? Во-первых, пулы ресурсов и группы рабочей нагрузки нельзя расформировывать в то время, когда ими пользуются. Собственно, это неудивительно, правда, нужно иметь в виду, что в активно работающих системах указанные компоненты могут использоваться в течение довольно длительного времени. Во-вторых, вы, возможно, полагаете, что с помощью Resource Governor можно понизить приоритет того ужасного запроса, который, как только что выяснилось, "убивает" быстродействие системы. Однако вы не имеете возможности использовать Resource Governor с этой целью, поскольку как группы рабочей нагрузки, так и пулы ресурсов распределялись в момент начала сеанса. Далее, Resource Governor не позволяет распределять приоритеты среди приложений OLTP. Короткие запросы OLTP плохо поддаются подобному управлению. Менеджер Resource Governor вполне годится для работы с долго выполняющимися запросами, которые потребляют ресурсы в течение длительного времени.
Наконец, почти во всех местах, где я работаю, SQL Server ограничен системой ввода/вывода, а не ресурсами процессора либо памяти. Для связанных с процессором систем SQL Server характерен ограниченный набор проблем, таких как "загрязнение" кэша плана исполнения, что постоянно вызывает перекомпиляцию. И хотя, ограничивая память, мы можем "поставить на место" небольшие запросы к хранилищам данных, мне хотелось бы, чтобы разработчики реализовали функцию управления вводом/выводом файлов по схеме "файл за файлом". Но, несмотря на указанные недостатки, большинство администраторов, я думаю, согласятся с тем, что Resource Governor - замечательное дополнение к привычному набору инструментов и отличное средство, позволяющее вновь обрести контроль над системами.