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

Создание и проверка функции классификатора User-Defined

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

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

В нашем примере:

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

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

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

    Замечание

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

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

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

  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

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

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

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

    1. Используйте top 1, чтобы вернуть только одну строку.

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

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

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

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

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

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

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

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

    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)