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

Artículo
06/13/2017

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

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

En el 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 maestra para contener la hora 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 debería almacenar en la base de datos maestra las tablas grandes que se actualicen 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 usar tablas de búsqueda en una función clasificadora, vea "Procedimientos recomendados para usar tablas de búsqueda en una función clasificadora" en este tema.

Nota

SQL Server 2008 introdujo un conjunto expandido de tipos y funciones de datos de fecha y hora. Para más información, consulte 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.
1. 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.
2. 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).
3. 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.
4. No debe haber desencadenadores en la tabla.
5. 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.
6. 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.

Consulte también

Regulador de recursos
Habilitar el regulador de recursos
Grupo de recursos de servidor del regulador de recursos
Grupos de cargas de trabajo del regulador de recursos
Configurar el regulador de recursos mediante una plantilla
Ver las propiedades del regulador de recursos
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Compartir a través de

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

Para crear la función clasificadora definida por el usuario

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

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

Consulte también

Recursos adicionales