Escenarios de administración de la carga de trabajo del regulador de recursos

Su objetivo consiste en determinar la configuración del grupo de cargas de trabajo y el grupo de recursos de servidor que desee utilizar para su entorno SQL Server. Para ello, debe tener información sobre la forma en que una aplicación utiliza los recursos de SQL Server.

La solución consiste en crear un grupo de cargas de trabajo que esté en el grupo de recursos de servidor predeterminado. Ejecute la aplicación durante varios días y utilice la información que recopile para responder a las preguntas siguientes.

• ¿Cuáles son los requisitos de CPU?
  Haga una estimación del ancho banda de CPU multiplicando el uso medio total por solicitud de la CPU por el número medio de solicitudes por segundo.

  El máximo, la media y la desviación estándar del uso de la CPU se pueden utilizar para determinar si es necesario establecer un límite máximo para la CPU.

• ¿Cuál es el número de solicitudes simultáneas?
  Utilice las estadísticas del número máximo y medio de solicitudes simultáneas para determinar el factor mínimo de simultaneidad.

• ¿Cuál es la cantidad memoria total necesaria?
  Utilice las cantidades máxima y media de memoria total utilizada para determinar los requisitos de memoria.

• ¿Cuánta memoria se necesita para una sola consulta?
  Utilice la estadística de cantidades máxima y media de memoria por consulta para determinar cuánta memoria se necesita.

• ¿Hay alguna consulta que no se esté ejecutando por falta de memoria?
  Utilice la estadística del tiempo medio de espera en la cola de memoria para determinar si se bloquea alguna consulta debido a problemas de disponibilidad de la memoria.

P: Acabo de instalar una nueva versión de SQL Server y desearía utilizar el regulador de recursos. ¿Cómo puedo utilizarlo en mi entorno?

R: Considere utilizar el regulador de recursos para supervisar el consumo de recursos por parte de las cargas de trabajo. Siga los pasos siguientes para preparar un entorno del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Cree grupos de cargas de trabajo para sus cargas de trabajo.

2. Cree una función de clasificación.

3. Registre la función de clasificación en el regulador de recursos.

4. Habilite el regulador de recursos.

5. Supervise los contadores de rendimiento del regulador de recursos y haga una consulta de los DMVs que devuelva información sobre el uso de recursos por parte de un grupo de cargas de trabajo.

Ejemplo

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: En función de los resultados de supervisión obtenidos en el escenario 1, desearía ver un evento cada vez que una consulta del grupo ad hoc (GroupAdhoc) se ejecute durante más de 30 segundos.

R: Siga los pasos siguientes para modificar la actual configuración del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Establezca un límite sobre el uso de la CPU para el grupo ad hoc.

2. Supervise los eventos de seguimiento de SQL (evento de clase de administración del regulador de recursos).

3. Realice una acción sobre el evento. Por ejemplo, omita el evento, envíe un correo electrónico, envíe una página o ejecute el comando KILL sobre la solicitud.

Ejemplo

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Deseo restringir el grupo ad-hoc de manera que no supere el 50 por ciento del uso de la CPU cuando se acumulen todas las solicitudes.

R: Dado que los escenarios anteriores utilizaron el grupo predeterminado, deberá crear un nuevo grupo de recursos de servidor. Siga los pasos siguientes para modificar la actual configuración del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Cree un nuevo grupo de recursos de servidor y configure los límites de la CPU.

2. Configure el grupo de cargas de trabajo ad hoc para utilizar el nuevo grupo de recursos de servidor.

Ejemplo

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Ahora deseo garantizar que el grupo de administración disponga siempre de recursos para ejecutar consultas de diagnóstico, pero dichas consultas no deben ocupar más del 10 por ciento de los recursos de memoria del servidor.

R: Para ello debe crear un nuevo grupo de recursos de servidor. Siga los pasos siguientes para modificar la actual configuración del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Cree un nuevo grupo de recursos de servidor y establezca los límites sobre los recursos.

2. Configure el grupo de cargas de trabajo de administración para utilizar el nuevo grupo de recursos de servidor.

Ejemplo

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Puesto que mis informes se encuentran actualmente en el grupo de recursos de servidor predeterminado, aunque utilizan un grupo de cargas de trabajo independiente (GroupReports), me gustaría asegurarme de que todo lo que se encuentre en el grupo de recursos de servidor predeterminado tendrá una prioridad de uso de recursos menor que los informes.

R: Para ello debe configurar el parámetro IMPORTANCE en el grupo predeterminado.

Siga el siguiente paso para modificar la actual configuración del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Cambie el valor para el grupo predeterminado.

Ejemplo

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Deseo crear un nuevo grupo de cargas de trabajo que utilice un grupo de recursos de servidor existente. A continuación, deseo cambiar la función clasificadora de manera que traslade el grupo a un nuevo grupo de recursos de servidor.

R: Siga los pasos siguientes para preparar este nuevo entorno del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Cree un nuevo grupo de recursos de servidor con la configuración predeterminada.

2. Cree un nuevo grupo de cargas de trabajo que resida en un grupo de recursos de servidor existente.

3. Cree y registre una nueva función clasificadora para administrar solicitudes.

Ejemplo

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: He decidido que ya no requiero un grupo de cargas de trabajo (creado en el escenario 6) , por lo que deseo eliminar el grupo de cargas de trabajo y el grupo de recursos de servidor en él contenido.

R: Siga los pasos siguientes para cambiar el entorno del regulador de recursos. Se le proporciona un ejemplo de configuración.

1. Cree y registre una nueva función clasificadora que clasificará las solicitudes en el grupo de cargas de trabajo restante.

2. Quite el grupo de cargas de trabajo.

3. Quite el grupo de recursos de servidor.

4. Aplique los cambios en la configuración.

Ejemplo

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO