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


Как создать и проверить определяемую пользователем функцию-классификатор (Transact-SQL)

В этом разделе описывается создание и проверка определяемой пользователем функции-классификатора (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