Crear y probar una función clasificadora definida por el usuario

Artículo
06/25/2013

En este tema se muestra cómo crear y probar una función clasificadora definida por el usuario (UDF). Los pasos implican ejecutar instrucciones de Transact-SQL en el Editor de consultas de SQL Server Management Studio.

El ejemplo mostrado en el procedimiento siguiente ilustra las posibilidades para crear una función clasificadora definida por el usuario bastante compleja.

En nuestro ejemplo:

Se crean un grupo de recursos de servidor (pProductionProcessing) y un grupo de cargas de trabajo (gProductionProcessing) para el procesamiento de producción durante un intervalo de tiempo especificado.
Se crean un grupo de recursos de servidor (pOffHoursProcessing) y un grupo de cargas de trabajo (gOffHoursProcessing) para administrar las conexiones que no cumplen los requisitos del procesamiento de producción.
Se crea una tabla (TblClassificationTimeTable) en la base de datos master para contener las horas de inicio y de finalización que se puede evaluar con una hora de inicio de sesión. Esto se debe crear en la base de datos maestra porque el regulador de recursos utiliza el enlace de esquemas para las funciones clasificadoras.

[!NOTA]

Como práctica recomendada, no debe almacenar en la base de datos master las tablas grandes que se actualizan con frecuencia.

La función clasificadora extiende el tiempo de inicio de sesión. Una función demasiado compleja puede hacer que los inicios de sesión agoten el tiempo de espera o ralenticen las conexiones rápidas.

Para crear la función clasificadora definida por el usuario

Cree y configure los grupos de recursos y grupos de cargas de trabajo nuevos. Asigne cada grupo de cargas de trabajo al grupo de recursos de servidor adecuado.

--- 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

Actualice la configuración en la memoria.
```
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
```

Cree una tabla y defina las horas de finalización e inicio para el intervalo de tiempo de proceso de producción.

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

Cree la función clasificadora que utiliza las funciones de hora y los valores que se pueden evaluar con las horas en la tabla de búsqueda. Para obtener información sobre cómo utilizar tablas de búsqueda en una función clasificadora, vea "Prácticas recomendadas para utilizar tablas de búsqueda en una función clasificadora" en este tema.

[!NOTA]

SQL Server 2008 incluía un conjunto expandido de funciones y tipos de datos de fecha y hora. Para obtener más información, vea Tipos de datos y funciones de fecha y hora (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

Registre la función clasificadora y actualice la configuración en memoria.

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

Para comprobar los grupos de recursos, los grupos de cargas de trabajo y la función clasificadora definida por el usuario

Obtenga la configuración del grupo de cargas de trabajo y del grupo de recursos de servidor con la consulta siguiente.
```
USE master
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups
GO
```

Compruebe que la función clasificadora existe y está habilitada con las consultas siguientes.

--- 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

Obtenga los datos de tiempo de ejecución actuales para los grupos de recursos y de cargas de trabajo con la consulta siguiente.
```
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups
GO
```

Averigüe qué sesiones están en cada grupo con la consulta siguiente.

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

Averigüe qué solicitudes están en cada grupo con la consulta siguiente.

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

Averigüe qué solicitudes está ejecutándose en la función clasificadora con la consulta siguiente.

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

Prácticas recomendadas para utilizar tablas de búsqueda en una función clasificadora

No utilice una tabla de búsqueda a menos que sea absolutamente necesario. Si necesita utilizar una tabla de búsqueda, puede estar codificada de forma rígida en la propia función; sin embargo, es necesario que esté en equilibrio con la complejidad y los cambios dinámicos de la función clasificadora.
Limite la E/S realizada para las tablas de búsqueda.
1. Utilice TOP 1 para que se devuelva solo una fila.
2. Minimice el número de filas de la tabla.
3. Haga que todas las filas de la tabla estén en una sola página o en un número reducido de páginas.
4. Confirme que las filas encontradas utilizando las operaciones de Index Seek usan tantas columnas de búsqueda como sea posible.
5. Desnormalice a una sola tabla si está pensando en utilizar varias tablas con combinaciones.

Evite el bloqueo en la tabla de búsqueda.

Utilice la sugerencia NOLOCK para evitar el bloqueo o utilice SET LOCK_TIMEOUT en la función con un valor máximo de 1000 milisegundos.
Debe haber tablas en la base de datos maestra. (La base de datos maestra es la única base de datos cuya recuperación está asegurada cuando los equipos clientes intentan la conexión).
Utilice siempre el nombre completo de la tabla con el esquema. El nombre de la base de datos no es necesario porque tiene que ser la base de datos maestra.
No debe haber desencadenadores en la tabla.
Si está actualizando el contenido de la tabla, asegúrese de utilizar una transacción de nivel de aislamiento de instantáneas para que los escritores no bloqueen los lectores. Observe que el uso de la sugerencia NOLOCK también debe mitigar este riesgo.

Si es posible, deshabilite la función clasificadora al cambiar el contenido de la tabla.

Advertencia
Se aconseja encarecidamente seguir estas prácticas recomendadas. Si hay problemas que impiden seguir las prácticas recomendadas, conviene ponerse en contacto con el servicio de soporte técnico de Microsoft para poder evitar de forma proactiva cualquier problema futuro.