Usar el regulador de recursos para limitar el uso de CPU mediante compresión de copia de seguridad (Transact-SQL)

Se aplica a:SQL Server

Por defecto, hacer una copia de seguridad mediante compresión aumenta significativamente el uso de la CPU, y la CPU adicional consumida por el proceso de compresión puede afectar negativamente a las operaciones concurrentes. Por consiguiente, podría ser conveniente crear una copia de seguridad comprimida de prioridad baja en una sesión en que el uso de CPU esté limitado por Resource Governor cuando se produce la contención por la CPU. En este tema se presenta un escenario en el que se clasifican las sesiones de un usuario de SQL Server determinado asignándolas a un grupo de cargas de trabajo del regulador de recursos que limita el uso de CPU en tales casos.

Este tema contiene el conjunto siguiente de escenarios, que se presentan en secuencia:

1. Configurar un inicio de sesión y un usuario para operaciones de prioridad baja

2. Configurar el regulador de recursos para limitar el uso de CPU

3. Comprobar la clasificación de la sesión actual (Transact-SQL)

4. Comprimir las copias de seguridad utilizando una sesión con CPU limitada

Configurar un inicio de sesión y un usuario para operaciones de prioridad baja

El escenario de este tema requiere un usuario y un inicio de sesión de SQL Server de prioridad baja. El nombre de usuario se utilizará para clasificar las sesiones que se ejecutan en el inicio de sesión y enrutarlas a un grupo de cargas de trabajo del regulador de recursos que limita el uso de CPU.

En el procedimiento siguiente se describen los pasos para configurar un inicio de sesión y un usuario con este propósito, además de un ejemplo de Transact-SQL , "Ejemplo A: configurar un inicio de sesión y un usuario (Transact-SQL)".

Para configurar un inicio de sesión y un usuario de la base de datos para clasificar las sesiones

1. Cree un inicio de sesión de SQL Server para crear copias de seguridad comprimidas de prioridad baja.

   Para crear un inicio de sesión

   • Crear un inicio de sesión

   • CREATE LOGIN (Transact-SQL)

2. Opcionalmente, conceda VIEW SERVER STATE a este inicio de sesión.

   • GRANT Permisos de objeto del sistema (Transact-SQL)

   Para más información, vea GRANT Permisos de entidad de seguridad de base de datos (Transact-SQL).

3. Cree un usuario de SQL Server para este inicio de sesión.

   Para crear un usuario

   • Crear un usuario de base de datos

   • CREATE USER (Transact-SQL)

4. Para que las sesiones de este inicio de sesión y este usuario puedan realizar una copia de seguridad de una base de datos determinada, agregue el usuario al rol de base de datos db_backupoperator de esa base de datos. Haga esto con cada base de datos de la que este usuario vaya a hacer una copia de seguridad. Si lo desea, puede agregar el usuario a los demás roles fijos de base de datos.

   Para agregar un usuario a un rol fijo de base de datos

   • sp_addrolemember (Transact-SQL)

   Para más información, vea GRANT Permisos de entidad de seguridad de base de datos (Transact-SQL).

Ejemplo A: configurar un inicio de sesión y un usuario (Transact-SQL)

El ejemplo siguiente solo es pertinente si decide crear un usuario y un inicio de sesión de SQL Server nuevos para las copias de seguridad de prioridad baja. Por otro lado, puede usar un inicio de sesión y un usuario existentes, si hay alguno adecuado.

En este ejemplo, se crea un inicio de sesión para la cuenta de Windows domain_name\MAX_CPU y, a continuación, se otorga el permiso SERVER STATE al inicio de sesión VIEW. Este permiso permite comprobar la clasificación que hace el regulador de recursos de las sesiones del inicio de sesión. A continuación, el ejemplo crea un usuario para domain_name\MAX_CPU y lo agrega al rol fijo de base de datos db_backupoperator para la base de datos de ejemplo AdventureWorks2025. La función clasificadora del regulador de recursos usará este nombre de usuario.

-- Create a SQL Server login for low-priority operations  
USE master;  
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;  
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];  
GO  
-- Create a SQL Server user in AdventureWorks2022 for this login  
USE AdventureWorks2022;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO  

[Inicio]

Configurar el regulador de recursos para limitar el uso de CPU

En este escenario en el que se usa el regulador de recursos, la configuración comprende los pasos básicos siguientes:

1. Cree y configure un grupo de recursos de Resource Governor que limite el ancho de banda promedio máximo de CPU que se asignará a las solicitudes del grupo de recursos cuando se produzca contención de CPU.

2. Crear y configurar un grupo de carga de trabajo de Resource Governor que use este grupo de recursos.

3. Cree una función clasificadora; se trata de una función definida por el usuario (UDF) que devuelve unos valores que el regulador de recursos usa para clasificar las sesiones de modo que se enruten al grupo de cargas de trabajo adecuado.

4. Registre la función clasificadora con Resource Governor.

5. Aplique los cambios a la configuración en memoria del regulador de recursos.

Las instrucciones de Transact-SQL para estos pasos se describen en el procedimiento "Para configurar el regulador de recursos de modo que se limite el uso de CPU”. Después, se muestra un ejemplo de Transact-SQL del procedimiento.

Para configurar el regulador de recursos (SQL Server Management Studio)

• Configurar el regulador de recursos mediante una plantilla

• Crear un grupo de recursos

• Crear un grupo de cargas de trabajo

Para configurar el regulador de recursos de modo que se limite el uso de CPU (Transact-SQL)

1. Ejecute una CREATE RESOURCE POOL sentencia para crear un grupo de recursos. En el ejemplo de este procedimiento se utiliza la sintaxis siguiente:

   CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );
   

   Value es un número entero comprendido entre 1 y 100 que indica el porcentaje máximo medio de ancho banda de CPU. El valor adecuado depende del entorno. A efectos de ilustración, en el ejemplo de este tema se utiliza un porcentaje del 20% (MAX_CPU_PERCENT = 20.)

2. Emita una CREATE WORKLOAD GROUP instrucción para crear un grupo de cargas de trabajo para operaciones de prioridad baja cuyo uso de CPU quiera controlar. En el ejemplo de este procedimiento se utiliza la sintaxis siguiente:

   CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];
   

3. Emita una CREATE FUNCTION instrucción para crear una función clasificadora que asigne el grupo de cargas de trabajo creado en el paso anterior al usuario del inicio de sesión de prioridad baja. En el ejemplo de este procedimiento se utiliza la sintaxis siguiente:

   CREATE FUNCTION [<schema_name>].[<function_name>]() RETURNS sysname  
   WITH SCHEMABINDING  
   AS  
   BEGIN  
       DECLARE @workload_group_name AS [<sysname>]  
       IF (SUSER_NAME() = '<user_of_low_priority_login>')  
       SET @workload_group_name = '<workload_group_name>'  
       RETURN @workload_group_name  
   END;
   

   Para obtener más información sobre los componentes de esta instrucción CREATE FUNCTION, vea:

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Importante

     SUSER_NAME es solo una de las diversas funciones del sistema que se pueden utilizar en una función clasificadora. Para obtener más información, vea Crear y probar una función clasificadora definida por el usuario.

   • SET @local_variable (Transact-SQL).

4. Emita una ALTER RESOURCE GOVERNOR instrucción para registrar la función clasificadora con Resource Governor. En el ejemplo de este procedimiento se utiliza la sintaxis siguiente:

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
   

5. Emita una segunda instrucción ALTER RESOURCE GOVERNOR para aplicar los cambios a la configuración de Resource Governor en memoria, como se indica a continuación:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Ejemplo B: configurar el regulador de recursos (Transact-SQL)

En el ejemplo siguiente se realizan los pasos dentro de una única transacción:

1. Crea el grupo de recursos pMAX_CPU_PERCENT_20.

2. Crea el grupo de cargas de trabajo gMAX_CPU_PERCENT_20 .

3. Crea la función clasificadora rgclassifier_MAX_CPU() , que utiliza el nombre de usuario creado en el ejemplo anterior.

4. Registra la función de clasificación con el Resource Governor.

Después de confirmar la transacción, el ejemplo aplica los cambios de configuración solicitados en las instrucciones ALTER WORKLOAD GROUP o ALTER RESOURCE POOL.

-- Configure Resource Governor.  
USE master;  
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%.   
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20  
   WITH  
      (MAX_CPU_PERCENT = 20);  
GO  

-- Create a workload group to use this pool.   
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20  
USING pMAX_CPU_PERCENT_20;  
GO  

-- Create a classification function.  
-- Note that any request that does not get classified goes into   
-- the 'Default' group.  
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname   
WITH SCHEMABINDING  
AS  
BEGIN  
    DECLARE @workload_group_name AS sysname  
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')  
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'  
    RETURN @workload_group_name  
END;  
GO  
  
-- Register the classifier function with Resource Governor.  
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);  
COMMIT TRAN;  
GO  

-- Start Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO    

[Inicio]

Comprobar la clasificación de la sesión actual (Transact-SQL)

Si lo desea, inicie sesión como el usuario que especificó en la función clasificadora y compruebe la clasificación de la sesión emitiendo la instrucción SELECT siguiente en el Explorador de objetos:

USE master;  
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name   
FROM sys.dm_exec_sessions AS sess   
JOIN sys.dm_resource_governor_workload_groups AS grps   
    ON sess.group_id = grps.group_id  
WHERE session_id > 50;  
GO  

En el panel de resultados, la columna name debe enumerar una o varias sesiones para el nombre del grupo de cargas de trabajo que especificó en la función clasificadora.

[Inicio]

Comprimir las copias de seguridad utilizando una sesión con CPU limitada

Para crear una copia de seguridad comprimida en una sesión con el uso máximo de CPU limitado, inicie sesión como el usuario especificado en la función clasificadora. En el comando de copia de seguridad, especifique WITH COMPRESSION (Transact-SQL) o seleccione Comprimir copia de seguridad (SQL Server Management Studio). Para crear una copia de seguridad comprimida de la base de datos, consulte Crear una copia de seguridad completa de base de datos (SQL Server).

Ejemplo C: crear una copia de seguridad comprimida (Transact-SQL)

En el ejemplo siguiente BACKUP se crea una copia de seguridad completa comprimida de la base de datos AdventureWorks2025 en un archivo de copia de seguridad recién formateado, Z:\SQLServerBackups\AdvWorksData.bak.

--Run backup statement in the gBackup session.  
BACKUP DATABASE AdventureWorks2022 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'   
WITH   
   FORMAT,   
   MEDIADESCRIPTION='AdventureWorks2022 Compressed Data Backups',
   DESCRIPTION='First database backup on AdventureWorks2022 Compressed Data Backups media set',
   COMPRESSION;  
GO  

[Inicio]

Contenido relacionado

• Tutorial: Ejemplos de configuración del regulador de recursos y procedimientos recomendados
• Gobernador de Recursos