Поделиться через

Создать и проверить определяемую пользователем функцию-классификатор

  • Статья

В этом разделе описывается создание и проверка определяемой пользователем функции-классификатора (UDF). Шаги включают выполнение инструкций Transact-SQL в SQL Server Management Studio Редактор запросов.

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

Пример.

  • Пул ресурсов (pProductionProcessing) и группа рабочей нагрузки (gProductionProcessing) создаются для рабочей обработки в течение определенного диапазона времени.

  • Пул ресурсов (pOffHoursProcessing) и группа рабочей нагрузки (gOffHoursProcessing) создаются для управления соединениями, не удовлетворяющими требованиям рабочей обработки.

  • Таблица (TblClassificationTimeTable) создается в базе данных master для сохранения времени запуска и остановки, которые можно вычислить после времени входа в систему. Она должна быть создана в базе данных master, так как регулятор ресурсов использует для функций-классификаторов привязку к схеме.

    Примечание

    Рекомендуется хранить большие часто обновляемые таблицы за пределами базы данных master.

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

Создание определяемой пользователем функции-классификатора

  1. Создайте и настройте новые пулы ресурсов и группы рабочей нагрузки. Назначьте каждую группу рабочей нагрузки соответствующему пулу ресурсов.

    --- Create a resource pool for production processing  
--- and set limits.  
USE master  
GO  
CREATE RESOURCE POOL pProductionProcessing  
WITH  
(  
     MAX_CPU_PERCENT = 100,  
     MIN_CPU_PERCENT = 50  
)  
GO  
--- Create a workload group for production processing  
--- and configure the relative importance.  
CREATE WORKLOAD GROUP gProductionProcessing  
WITH  
(  
     IMPORTANCE = MEDIUM  
)  
--- Assign the workload group to the production processing  
--- resource pool.  
USING pProductionProcessing  
GO  
--- Create a resource pool for off-hours processing  
--- and set limits.  

CREATE RESOURCE POOL pOffHoursProcessing  
WITH  
(  
     MAX_CPU_PERCENT = 50,  
     MIN_CPU_PERCENT = 0  
)  
GO  
--- Create a workload group for off-hours processing  
--- and configure the relative importance.  
CREATE WORKLOAD GROUP gOffHoursProcessing  
WITH  
(  
     IMPORTANCE = LOW  
)  
--- Assign the workload group to the off-hours processing  
--- resource pool.  
USING pOffHoursProcessing  
GO

  2. Обновите конфигурацию, хранимую в памяти.

    ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

  3. Создайте таблицу и определите время запуска и остановки диапазона времени рабочей обработки.

    USE master  
GO  
CREATE TABLE tblClassificationTimeTable  
(  
     strGroupName     sysname          not null,  
     tStartTime       time              not null,  
     tEndTime         time              not null  
)  
GO  
--- Add time values that the classifier will use to  
--- determine the workload group for a session.  
INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM')  
go

  4. Создайте функцию-классификатор, использующую функции и значения времени, которые можно оценить со временем в таблице подстановки. Дополнительные сведения об использовании таблиц подстановки в функции-классификаторе см. в разделе "Рекомендации по использованию таблиц подстановки в функции-классификаторе" этой статьи.

    Примечание

    SQL Server 2008 году появился расширенный набор типов данных и функций даты и времени. Дополнительные сведения см. в разделе Типы данных и время и функции (Transact-SQL).

    CREATE FUNCTION fnTimeClassifier()  
RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
     DECLARE @strGroup sysname  
     DECLARE @loginTime time  
     SET @loginTime = CONVERT(time,GETDATE())  
     SELECT TOP 1 @strGroup = strGroupName  
          FROM dbo.tblClassificationTimeTable  
          WHERE tStartTime <= @loginTime and tEndTime >= @loginTime  
     IF(@strGroup is not null)  
     BEGIN  
          RETURN @strGroup  
     END  
--- Use the default workload group if there is no match  
--- on the lookup.  
     RETURN N'gOffHoursProcessing'  
END  
GO

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

    ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)  
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

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

  1. Получите пул ресурсов и настройку группы рабочей нагрузки при помощи следующего запроса.

    USE master  
SELECT * FROM sys.resource_governor_resource_pools  
SELECT * FROM sys.resource_governor_workload_groups  
GO

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

    --- Get the classifier function Id and state (enabled).  
SELECT * FROM sys.resource_governor_configuration  
GO  
--- Get the classifer function name and the name of the schema  
--- that it is bound to.  
SELECT   
      object_schema_name(classifier_function_id) AS [schema_name],  
      object_name(classifier_function_id) AS [function_name]  
FROM sys.dm_resource_governor_configuration

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

    SELECT * FROM sys.dm_resource_governor_resource_pools  
SELECT * FROM sys.dm_resource_governor_workload_groups  
GO

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

    SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))  
          FROM sys.dm_exec_sessions s  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
          ON g.group_id = s.group_id  
ORDER BY g.name  
GO

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

    SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text   
           FROM sys.dm_exec_requests r  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
            ON g.group_id = r.group_id  
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t  
ORDER BY g.name  
GO

  6. С помощью следующего запроса выясните, какие запросы выполняются в классификаторе.

    SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name   
           FROM sys.dm_exec_sessions s  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
           ON g.group_id = s.group_id  
                 AND 'preconnect' = s.status  
ORDER BY g.name  
GO  

SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text   
           FROM sys.dm_exec_requests r  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
           ON g.group_id = r.group_id  
                 AND 'preconnect' = r.status  
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t  
ORDER BY g.name  
GO

Рекомендации по использованию таблиц подстановки в функции-классификаторе

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

  2. Ограничьте ввод и вывод данных, выполняемые для таблиц подстановки.

    1. Воспользуйтесь инструкцией TOP 1, чтобы вернуть только одну строку.

    2. Сведите к минимуму количество строк в таблице.

    3. Сделайте так, чтобы все строки таблицы были на одной странице или на небольшом количестве страниц.

    4. Убедитесь, что строки, найденные с помощью операций Index Seek, используют как можно больше столбцов поиска.

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

  3. Запретите блокирование таблицы подстановки.

    1. Воспользуйтесь указанием NOLOCK для предотвращения блокирования или оператором SET LOCK_TIMEOUT в функции с максимальным значением 1000 миллисекунд.

    2. Таблицы должны существовать в базе данных master. (Когда подключение устанавливают клиентские компьютеры, можно гарантировать восстановление только базы данных master.)

    3. Всегда указывайте полное имя таблицы со схемой. Имя базы данных не требуется, так как это должна быть база данных master.

    4. Не используйте триггеры для таблицы.

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

    6. По возможности отключите функцию-классификатор при изменении содержимого таблицы.

      Предупреждение

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

См. также:

Регулятор ресурсов
Включение регулятора ресурсов
Пул ресурсов регулятора ресурсов
Группа рабочей нагрузки регулятора ресурсов
Настройка регулятора ресурсов с помощью шаблона
Просмотр свойств регулятора ресурсов
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)