SQL Server - Usando Resource Governor para un Mayor Control de los Recursos del Servidor

Resource Governor no es más que una característica disponible desde SQL Server 2008 Enterprise que nos brinda la posibilidad de gestionar la carga de trabajo y el consumo de recursos del servidor, especificando límites en la cantidad de CPU y memoria que pueden utilizar las solicitudes entrantes, según un perfil a definir (basado en nombre de host, nombre de usuario, grupo AD, software utilizado, etc.), evitando que estos usuarios causen impactos importantes en el medio ambiente en su conjunto.

Una observación importante a decir es que Resource Governor recibió una actualización importante de SQL Server 2014, que es la posibilidad de controlar y administrar la carga de E/S de las solicitudes recibidas, lo que le permite limitar la cantidad máxima de IOPS utilizadas para ciertos perfiles.

No solo para determinar las cantidades máximas, el Resource Governor también se puede utilizar para definir cantidades mínimas de recursos que utilizará el perfil (Pool) y garantizar que estas cantidades definidas estén siempre disponibles para estas sesiones.

Resource Governor se usa ampliamente para limitar ciertos perfiles de acceso a bases de datos y evitar que las sesiones de diferentes instancias tengan que competir entre sí por los recursos del servidor. Algunos ejemplos donde se puede implementar el uso de Resource Governor:

• limitar el uso máximo de IOPS de consultas de usuarios ad-hoc
• limitar el uso máximo de CPU de los trabajos de instancia
• limitar la cantidad máxima de memoria utilizada en un sistema específico
• limitar el uso máximo de IOPS de los usuarios que forman parte de un determinado grupo de AD
• establecer la cantidad máxima de paralelismo de consultas para un perfil determinado

El funcionamiento interno de Resource Governor se reduce a tres componentes principales (función clasificadora, grupo de carga de trabajo y grupo de recursos) y actúan de la siguiente manera:

• Se crea una nueva conexión y esta sesión realiza una solicitud (Sesión 1 de n).
• La sesión se clasifica según criterios predefinidos mediante la función Clasificador. Estos criterios pueden ser el nombre de usuario, el grupo AD del que forma parte, la función del sistema o la función de la base de datos del que forma parte, el nombre del software utilizado, etc.
• La solicitud de sesión se enruta a un grupo de cargas de trabajo según el retorno de la función de clasificación, por ejemplo, Grupo 4.
• El grupo de cargas de trabajo utiliza el grupo de recursos al que está asociado, por ejemplo, el grupo 2.
• El grupo de recursos proporciona y limita los recursos requeridos por la aplicación, por ejemplo, la Aplicación 3.

Referencia: Inglés – portugués

Las funciones de clasificación o Classifier Functions son funciones creadas por el usuario en la base de datos “maestra”, que se aplicarán automáticamente a todas las nuevas conexiones realizadas a la instancia que tenga habilitado el Resource Governor.

Estas funciones devolverán el nombre del grupo de carga de trabajo que utilizará esta nueva conexión y en función de la lógica implementada por ella, que puede utilizar funciones nativas en SQL Server como HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER() e IS_MEMBER() para determinar criterios para clasificar la sesión y determinar qué grupo de carga de trabajo se utilizará cuando program_name como '%Management Studio%', por ejemplo.

Por este motivo, es importante tener en cuenta que una función mal implementada o que utilice muchos recursos para asignar sesiones puede terminar provocando un problema importante de rendimiento en la instancia. Por este motivo, es una buena práctica habilitar la conexión DAC para permitir la conexión a la instancia sin pasar por el uso de la función de clasificación. Si no haces esto y necesitas

Si no ha creado una función de clasificación, o no ha asignado la función al Gobernador de recursos o la función devuelve "predeterminado", NULL o el nombre de un grupo devuelto por la función no existe, esta sesión se enrutará al grupo de carga de trabajo "predeterminado".

Vale la pena mencionar que el Gobernador de recursos solo puede usar una función de clasificación y, como la función se crea usando el parámetro CON SCHEMABINDING, solo se puede eliminar o cambiar si elimina la asociación de la función con el Gobernador de recursos, usando el comando
ALTERAR EL GOBERNADOR DE RECURSOS CON (CLASSIFIER_FUNCTION=NULL).

Ejemplo de creación

CREATE FUNCTION fncClassifica_ResourceGovernor() 
RETURNS SYSNAME 
WITH SCHEMABINDING 
AS 
BEGIN 
        
    DECLARE @grp_name AS SYSNAME
    
    IF (SUSER_NAME() = 'Report_User') 
        SET @grp_name = 'Group1'

    IF (APP_NAME() LIKE '%Management Studio%')
        SET @grp_name = 'Group2'
    
    RETURN @grp_name 

END 
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fncClassifica_ResourceGovernor)
ALTER RESOURCE GOVERNOR RECONFIGURE

Referencia: Inglés – portugués

Los grupos de cargas de trabajo son como contenedores para solicitudes con criterios similares. Cada grupo de cargas de trabajo está asociado con un grupo de recursos y un grupo de recursos se puede asociar con N grupos de cargas de trabajo.

El grupo de cargas de trabajo de una sesión se define a través de la función Clasificador y tiene algunas funciones y limitaciones muy importantes, como definir la importancia de las solicitudes que forman parte del grupo de cargas de trabajo.

A través del parámetro IMPORTANCIA = { BAJA | MEDIO (predeterminado) | ALTO }, puede definir que las solicitudes de este grupo de cargas de trabajo tendrán prioridad sobre las conexiones de otro grupo de cargas de trabajo que utilicen el mismo grupo de recursos (este parámetro no afecta las solicitudes que utilicen otro grupo de recursos). Este valor se utiliza para definir el orden de la lista de solicitudes del mismo planificador. Si tiene otros programadores disponibles, 1 consulta con BAJA importancia y 1 consulta con ALTA importancia, las 2 consultas se pueden ejecutar simultáneamente.

El parámetro REQUEST_MAX_MEMORY_GRANT_PERCENT especifica la cantidad máxima (en %) de memoria que una conexión puede tomar del grupo. Tenga en cuenta que especificar este valor en 0 evitará que se ejecuten consultas que utilizan SORT y HASH JOIN.

El parámetro REQUEST_MAX_CPU_TIME_SEC le permite definir el tiempo máximo, en segundos, que puede ejecutarse una consulta (el valor predeterminado es 0, es decir, sin límite). Si la solicitud excede el límite de tiempo, se generará un evento de seguimiento. Hasta la versión SQL Server 2017 CU3 la sesión no se interrumpía al alcanzar este límite, pero a partir de esta versión esto se puede configurar usando traceflag 2422.

El parámetro MAX_DOP le permite definir la cantidad máxima de paralelismo (grado máximo de paralelismo – MAXDOP) de las solicitudes realizadas en el grupo de cargas de trabajo, donde el valor predeterminado es 0 y los valores permitidos están entre 0 y 64. Esta configuración anula el parámetro sp_configure 'grado máximo de paralelismo' y también la sugerencia de consulta OPCIÓN (MAXDOP N).

El parámetro GROUP_MAX_REQUESTS define el número máximo de solicitudes simultáneas ejecutadas por el grupo de cargas de trabajo. El valor predeterminado es 0, es decir, sin limitación. Cuando se alcance este número, las nuevas conexiones estarán en estado de espera hasta que el número de solicitudes simultáneas caiga por debajo del máximo.

Ejemplo de creación

CREATE WORKLOAD GROUP [Group1] 
WITH (
    GROUP_MAX_REQUESTS=0, 
    IMPORTANCE=LOW, 
    REQUEST_MAX_CPU_TIME_SEC=2, 
    REQUEST_MAX_MEMORY_GRANT_PERCENT=25, 
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0, 
    MAX_DOP=0
) USING [PoolA]
GO

Referencia: Inglés – portugués

El grupo de recursos representa los recursos físicos del servidor. Puede pensar en el grupo como una instancia virtual de SQL Server dentro de una instancia de SQL Server. Se crean dos grupos de recursos (interno y predeterminado) cuando se instala SQL Server y usted puede crear más grupos según sus necesidades (máximo 64 grupos de usuarios).

El grupo interno se utiliza para procesos en segundo plano de SQL Server y tiene prioridad sobre cualquier otro grupo. Si necesita todos los recursos disponibles en la instancia, los utilizará. Además del pool interno, también existe el pool predeterminado, que se utiliza para el resto de conexiones que no han sido clasificadas para usar el pool interno o los pools personalizados del usuario.

Cuando crea un grupo, debe especificar los límites máximo y mínimo de CPU, memoria y E/S (IOPS).

El parámetro MIN_CPU_PERCENT le permite especificar el valor mínimo de CPU utilizado por todas las solicitudes del grupo y es especialmente útil cuando se produce contención de CPU, ya que SQL Server garantizará que este valor esté disponible para que lo utilicen las conexiones de este grupo. Si el grupo no está en uso, esta CPU reservada estará disponible para que otros grupos la utilicen hasta que el grupo en cuestión vuelva a tener conexiones.

El parámetro MAX_CPU_PERCENT le permite especificar el porcentaje máximo de CPU que las solicitudes del grupo pueden usar en la instancia. Tenga en cuenta que el porcentaje máximo de CPU es un máximo oportunista. Si hay capacidad de CPU disponible, la carga de trabajo la utilizará hasta el 100%. El valor máximo solo se aplicará cuando haya competencia por los recursos de la CPU.

El parámetro CAP_CPU_PERCENT define un límite máximo "verdadero" de uso de CPU por parte del grupo. Las cargas de trabajo asociadas con el grupo pueden usar la capacidad de CPU por encima del valor MAX_CPU_PERCENT cuando esté disponible, pero no por encima del valor CAP_CPU_PERCENT.

El parámetro MIN_MEMORY_PERCENT le permite especificar y reservar una cantidad mínima de memoria que siempre será preasignada en la instancia para el grupo, independientemente de si el grupo se está utilizando o no.

Vale la pena recordar que la suma del valor mínimo de todos los pools no puede ser mayor que el 100% y el valor del parámetro MAX_CPU_PERCENT del pool debe ser mayor que el valor del parámetro MIN_CPU_PERCENT.

Los parámetros MIN_IOPS_PER_VOLUME y MAX_IOPS_PER_VOLUME se utilizan para determinar las cantidades mínima y máxima de IOPS utilizadas por las conexiones del grupo.

Ejemplo de creación

CREATE RESOURCE POOL [PoolA] 
WITH (
    MIN_CPU_PERCENT=0, 
    MAX_CPU_PERCENT=20, 
    CAP_CPU_PERCENT=20,
    MIN_MEMORY_PERCENT=0, 
    MAX_MEMORY_PERCENT=30, 
    AFFINITY SCHEDULER = AUTO, 
    MIN_IOPS_PER_VOLUME=0, 
    MAX_IOPS_PER_VOLUME=300
)
GO

Referencia: Inglés – portugués

Para verificar el estado del gobernador de recursos, simplemente consulte la vista sys.resource_governor_configuration y analice la columna. está_enabled.

A continuación se muestra la lista de vistas del gobernador de recursos:

• sys.resource_governor_configuration
• sys.resource_governor_external_resource_pool_affinity
• sys.resource_governor_external_resource_pools
• sys.resource_governor_resource_pool_affinity
• sys.resource_governor_resource_pools
• sys.resource_governor_workload_groups

A continuación se muestra la lista del DMV del gobernador de recursos

• sys.dm_resource_governor_configuration
• sys.dm_resource_governor_external_resource_pool_affinity
• sys.dm_resource_governor_external_resource_pools
• sys.dm_resource_governor_resource_pool_affinity
• sys.dm_resource_governor_resource_pool_volumes
• sys.dm_resource_governor_resource_pools
• sys.dm_resource_governor_workload_groups

Ahora que hemos visto cómo funciona en teoría el Gobernador de Recursos, pongamos todo en práctica en el siguiente ejemplo:

USE [master]
GO

----------------------------------------------------------------------------------------------
-- "Limpeza" do Resource Governor
----------------------------------------------------------------------------------------------

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=NULL)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

ALTER RESOURCE GOVERNOR DISABLE
GO

IF (EXISTS(SELECT NULL FROM sys.resource_governor_workload_groups WHERE [name] = 'Grupo1')) DROP WORKLOAD GROUP [Grupo1] 
GO

IF (EXISTS(SELECT NULL FROM sys.resource_governor_resource_pools WHERE [name] = 'PoolA')) DROP RESOURCE POOL [PoolA] 
GO

IF (OBJECT_ID('dbo.fncClassifica_ResourceGovernor') IS NOT NULL) DROP FUNCTION dbo.fncClassifica_ResourceGovernor
GO


----------------------------------------------------------------------------------------------
-- Criação do Pool de Recursos
----------------------------------------------------------------------------------------------

CREATE RESOURCE POOL [PoolA] 
WITH (
    MIN_CPU_PERCENT=0, 
    MAX_CPU_PERCENT=20, 
    CAP_CPU_PERCENT=20,
    MIN_MEMORY_PERCENT=0, 
    MAX_MEMORY_PERCENT=30, 
    AFFINITY SCHEDULER = AUTO, 
    MIN_IOPS_PER_VOLUME=0, 
    MAX_IOPS_PER_VOLUME=300
)
GO


----------------------------------------------------------------------------------------------
-- Criação do Workload Group
----------------------------------------------------------------------------------------------

CREATE WORKLOAD GROUP [Grupo1] 
WITH (
    GROUP_MAX_REQUESTS=0, 
    IMPORTANCE=LOW, 
    REQUEST_MAX_CPU_TIME_SEC=2, 
    REQUEST_MAX_MEMORY_GRANT_PERCENT=25, 
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0, 
    MAX_DOP=0
) USING [PoolA]
GO


----------------------------------------------------------------------------------------------
-- Criação da função de classificação
----------------------------------------------------------------------------------------------

CREATE FUNCTION fncClassifica_ResourceGovernor() 
RETURNS SYSNAME 
WITH SCHEMABINDING 
AS 
BEGIN 
        
    DECLARE @grp_name AS SYSNAME, @Usuario VARCHAR(200) = SUSER_NAME(), @Programa VARCHAR(200) = APP_NAME()
    
    IF (@Usuario = 'Report_User') 
        SET @grp_name = 'Grupo1'
    ELSE IF (@Programa LIKE '%Management Studio%')
        SET @grp_name = 'Grupo2'

    
    RETURN @grp_name 

END 
GO


----------------------------------------------------------------------------------------------
-- Habilita o Resource Governor, aplica a função de classificação e confirma as alterações
----------------------------------------------------------------------------------------------

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fncClassifica_ResourceGovernor)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Resultado:

Si lo prefiere, también puede utilizar la interfaz (GUI) de Management Studio para configurar el grupo de recursos y el grupo de cargas de trabajo:

Y con eso, abra la interfaz de edición/creación del grupo de recursos y del grupo de cargas de trabajo (es la misma interfaz para ambos):

Vale la pena recordar que no todos los parámetros están disponibles en la interfaz de SQL Server Management Studio (probé en la versión 17.4), como, por ejemplo, los parámetros de E/S. Por lo tanto, le sugiero que utilice la línea de comando para administrar Resource Governor.

Para aquellos que nunca han usado o probado Resource Governor, pueden surgir las siguientes preguntas:
– ¿Y el rendimiento de las consultas? ¿Cambiará después de implementar esta característica?
– ¿Funciona realmente el gobernador de recursos?
– ¿Es realmente posible limitar recursos, como el disco, para determinadas sesiones?

Hagamos una prueba simple para demostrar esto:

• Cree un contador en Windows Perfmon (Monitor de rendimiento) para monitorear el contador de E/S de lectura de disco/s del objeto MSSQL$SQL2016:Resource Pool Stats.
• Cree un grupo de recursos, limitando la cantidad máxima de IOPS (MAX_IOPS_PER_VOLUME) a ​​200.
• Ejecute el comando DBCC CHECKDB (dirceuresende) CON NO_INFOMSGS
• Espere unos segundos y cambie el grupo de recursos, aumentando la cantidad máxima de IOPS en 2000.
• Ejecute el comando DBCC CHECKDB (dirceuresende ) CON NO_INFOMSGS nuevamente
• Recopila resultados y compara gráficos.

Después de realizar estos pasos, analicemos el resultado:

Como se ve en el gráfico, Resource Governor cumplió su función y limitó correctamente la cantidad de IOPS de disco utilizadas a 200 IOPS. Luego de aumentar el límite a 2000 IOPS pudimos observar que el disco logró llegar a 1000 IOPS y no pudo superar este valor por limitaciones de hardware, donde también se puede observar que debido al mayor límite de consumo de IOPS el tiempo para ejecutar checkdb fue menor.

Para deshabilitar Resource Governor en su instancia, simplemente use los siguientes comandos:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

ALTER RESOURCE GOVERNOR DISABLE;
GO

Recuerde que las conexiones actuales deben finalizar (o reiniciar la instancia); de lo contrario, el Gobernador de recursos seguirá activo y recibirá este mensaje de error:

Para excluir también grupos de cargas de trabajo y grupos de recursos, también puede utilizar los siguientes comandos:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO

DECLARE @Query VARCHAR(MAX) = ''


SELECT @Query += 'DROP WORKLOAD GROUP ' + QUOTENAME(name) + ';' 
FROM sys.resource_governor_workload_groups
WHERE [name] not in ('internal','default'); 

EXEC(@Query)

SET @Query = ''

SELECT @Query += 'DROP RESOURCE POOL ' + QUOTENAME([name]) + ';'
FROM sys.resource_governor_resource_pools
WHERE [name] NOT IN ('internal','DEFAULT'); 

EXEC(@Query)


ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

ALTER RESOURCE GOVERNOR DISABLE;
GO

Después de activar Resource Governor, puede monitorear el uso de CPU del grupo de recursos de un vistazo mediante la siguiente consulta:

SELECT
    A.[name] AS resource_pool,
    COALESCE(SUM(B.total_request_count), 0) AS total_request_count,
    COALESCE(SUM(B.total_cpu_usage_ms), 0) AS total_cpu_usage_ms,
    (CASE WHEN SUM(B.total_request_count) > 0 THEN SUM(B.total_cpu_usage_ms) / SUM(B.total_request_count) ELSE 0 END) AS avg_cpu_usage_ms
FROM
    sys.dm_resource_governor_resource_pools AS A
    LEFT OUTER JOIN sys.dm_resource_governor_workload_groups AS B ON A.pool_id = B.pool_id
GROUP BY
    A.[name]

Resultado:

O supervise todos los parámetros de CPU de los grupos y grupos de cargas de trabajo:

SELECT
    A.[name] AS resource_pool,
    B.[name] AS workload_group,
    A.total_cpu_usage_ms,
    A.min_cpu_percent,
    A.max_cpu_percent,
    A.cap_cpu_percent,
    A.total_cpu_delayed_ms,
    A.total_cpu_active_ms,
    A.total_cpu_violation_delay_ms,
    A.total_cpu_violation_sec,
    A.total_cpu_usage_preemptive_ms,
    B.total_cpu_limit_violation_count,
    B.total_cpu_usage_ms,
    B.max_request_cpu_time_ms,
    B.request_max_cpu_time_sec,
    B.total_cpu_usage_preemptive_ms
FROM
    sys.dm_resource_governor_resource_pools AS A
    LEFT OUTER JOIN sys.dm_resource_governor_workload_groups AS B ON A.pool_id = B.pool_id

También puede monitorear el uso de E/S de los grupos de recursos mediante la siguiente consulta:

SELECT
    pool_id,
    [name],
    min_iops_per_volume,
    max_iops_per_volume,
    read_io_queued_total,
    read_io_issued_total,
    read_io_completed_total,
    read_io_throttled_total,
    read_bytes_total,
    read_io_stall_total_ms,
    read_io_stall_queued_ms,
    io_issue_violations_total,
    io_issue_delay_total_ms
FROM
    sys.dm_resource_governor_resource_pools

Resultado:

También puede utilizar la siguiente consulta para comprobar qué grupo de cargas de trabajo utilizan las sesiones activas que se ejecutan actualmente:

SELECT
    B.[name],
    A.*
FROM 
    sys.dm_exec_sessions AS A WITH (NOLOCK)
    LEFT JOIN sys.dm_resource_governor_workload_groups B ON A.group_id = B.group_id
WHERE 
    A.session_id > 50
    AND A.session_id <> @@SPID
    AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))

Resultado:

Si desea una versión aún más completa de esta consulta, que prácticamente pueda replicar el comportamiento de sp_whoisactive, lea la publicación SQL Server: consulta para devolver consultas en ejecución (sp_WhoIsActive sin consumir TempDB).

A continuación, enumeraré algunas buenas prácticas a utilizar en Resource Governor para evitar problemas futuros al utilizar este recurso:

• Usando el CAD: Debido a que todas las conexiones nuevas pasan por la función de clasificación del Regulador de recursos, un problema en esta función puede dificultar el acceso a la instancia para solucionar el problema. Para solucionar esto fácilmente, puede habilitar y utilizar la DAC (Conexión de administrador dedicada) en su instancia y así poder iniciar sesión y cambiar la función de clasificación. Si no sabes cómo hacer esto, lee este post. Habilitación y uso de una conexión de administrador remoto (DAC) dedicada en SQL Server.
• Cuidado con la función de clasificación: Como se mencionó anteriormente, un error de codificación en la función de clasificación puede provocar efectos muy negativos en su instancia. Para ello, evita utilizar funciones o recursos que puedan aumentar al máximo el tiempo de respuesta de esta función. Si la función necesita consultar una tabla física, asegúrese de que el acceso sea lo suficientemente rápido y que los índices cumplan las condiciones de esta consulta.
• Grupo predeterminado y grupo de trabajo predeterminado: Una buena práctica al utilizar Resource Governor es dejar una parte de los recursos disponibles para el grupo predeterminado y el grupo de cargas de trabajo predeterminado. Esto es importante porque todas las sesiones que no sean procesos internos de SQL Server y no puedan clasificarse en ningún grupo personalizado formarán parte del grupo predeterminado. Si este grupo no tiene los recursos necesarios disponibles, estas solicitudes pueden experimentar problemas de desaceleración.
• Supervise siempre: Luego de implementar Resource Governor, siempre se debe estar atento y monitorear el flujo de solicitudes y validar que estén siendo dirigidas a los grupos de carga de trabajo correctos, además de monitorear posibles nuevas necesidades que requieran un cambio en la función de clasificación.
• Tenga cuidado con la configuración de la memoria: Como ya se mencionó en esta publicación, la configuración de la memoria se asigna previamente al crear el grupo, es decir, incluso si el grupo no tiene ninguna conexión activa y no está utilizando esta memoria preasignada, el grupo seguirá reservandola.