Uso del regulador de recursos para limitar el uso de CPU por compresión de copia de seguridad (Transact-SQL)

De forma predeterminada, la copia de seguridad con compresión aumenta significativamente el uso de cpu y la CPU adicional consumida por el proceso de compresión puede afectar negativamente a las operaciones simultáneas. Por lo tanto, es posible que quiera crear una copia de seguridad comprimida de prioridad baja en una sesión cuyo uso de CPU esté limitado porresource Governor cuando se produzca la contención de CPU. En este tema se presenta un escenario que clasifica las sesiones de un usuario determinado de SQL Server mediante su asignación a un grupo de cargas de trabajo de Resource Governor que limita el uso de CPU en tales casos.

Importante

En un escenario determinado de Resource Governor, la clasificación de sesión podría basarse en un nombre de usuario, un nombre de aplicación o cualquier otra cosa que pueda diferenciar una conexión. Para obtener más información, consulte Resource Governor Classifier Function y Resource Governor Workload Group.

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

1. Configuración de acceso y usuario para las operaciones de Low-Priority

2. Configuración del regulador de recursos para limitar el uso de CPU

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

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

Configuración de un inicio de sesión y un usuario para operaciones de Low-Priority

El escenario de este tema requiere un inicio de sesión y un usuario de SQL Server de prioridad baja. El nombre de usuario se usará para clasificar las sesiones que se ejecutan en el inicio de sesión y enrutarlas a un grupo de cargas de trabajo de Resource Governor que limite el uso de la CPU.

En el procedimiento siguiente se describen los pasos para configurar un inicio de sesión y un usuario para este fin, seguido de un ejemplo de Transact-SQL, "Ejemplo A: Configuración de un inicio de sesión y un usuario (Transact-SQL)."

Para configurar un inicio de sesión y un usuario de base de datos para la clasificación de 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 de sistema de Transact-SQL)

   Para obtener más información, consulte GRANT permisos de principal de la 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

   • CREAR USUARIO (Transact-SQL)

4. Para permitir que las sesiones de este inicio de sesión y el usuario realicen 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 para cada base de datos de la que este usuario realizará una copia de seguridad. Opcionalmente, agregue el usuario a otros roles fijos de base de datos.

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

   • sp_addrolemember (Transact-SQL)

   Para obtener más información, consulte GRANT Permisos del principal de base de datos (Transact-SQL).

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

El ejemplo siguiente solo es relevante si decide crear un nuevo inicio de sesión de SQL Server y un usuario para las copias de seguridad de prioridad baja. Como alternativa, puede usar un inicio de sesión y un usuario existentes, si existe uno adecuado.

Importante

En el ejemplo siguiente se usa un nombre de usuario y un inicio de sesión de ejemplo, domain_name\MAX_CPU. Reemplace estos por los nombres del inicio de sesión y el usuario de SQL Server que planea usar al crear las copias de seguridad comprimidas de prioridad baja.

En este ejemplo se crea un inicio de sesión para la domain_name\MAX_CPU cuenta de Windows y, a continuación, se concede el permiso VIEW SERVER STATE al inicio de sesión. Este permiso le permite verificar la clasificación del Administrador de Recursos de las sesiones del inicio de sesión del usuario. 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 AdventureWorks2012. Esta 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 AdventureWorks2012 for this login  
USE AdventureWorks2012;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO  
  

[Top]

Configuración del regulador de recursos para limitar el uso de CPU

Nota:

Asegúrese de que Resource Governor está habilitado. Para obtener más información, consulte Habilitación del regulador de recursos.

En este escenario de Resource Governor, la configuración consta de los siguientes pasos básicos:

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

2. Cree y configure un grupo de cargas de trabajo de Resource Governor que use este grupo.

3. Cree una función clasificadora, que es una función definida por el usuario (UDF) cuyos valores devueltos los usa Resource Governor para clasificar las sesiones para 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.

Nota:

Para obtener información sobre los grupos de recursos de Resource Governor, los grupos de cargas de trabajo y la clasificación, consulte Resource Governor.

Las instrucciones Transact-SQL para estos pasos se describen en el procedimiento "Para configurar Resource Governor para limitar el uso de CPU", seguido de un Transact-SQL ejemplo del procedimiento.

Para configurar Resource Governor (SQL Server Management Studio)

• Configuración del regulador de recursos mediante una plantilla

• Crear un grupo de recursos de servidor

• Crear un grupo de cargas de trabajo

Para configurar el regulador de recursos para limitar el uso de CPU (Transact-SQL)

1. Emita una instrucción CREATE RESOURCE POOL para crear un grupo de recursos. En el ejemplo de este procedimiento se usa la sintaxis siguiente:

   CREAR POOL DE RECURSOS pool_name CON (MAX_CPU_PERCENT = valor);

   El valor es un entero de 1 a 100 que indica el porcentaje de ancho de banda de CPU promedio máximo. El valor adecuado depende de su entorno. Para ilustrar, el ejemplo en este tema usa el 20 %% (MAX_CPU_PERCENT = 20).

2. Emita una instrucción CREATE WORKLOAD GROUP 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 usa la sintaxis siguiente:

   CREAR GRUPO DE CARGA DE TRABAJO group_name USANDO pool_name;

3. Emita una instrucción CREATE FUNCTION 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 usa la sintaxis siguiente:

   CREATE FUNCTION [schema_name.]function_name() RETORNA sysname

   WITH SCHEMABINDING

   COMO

   INICIO

   DECLARE @workload_group_name AS sysname

   IF (SUSER_NAME() = "user_of_low_priority_login")

   SET @workload_group_name = "workload_group_name"

   DEVOLUCIÓN @workload_group_name

   FIN

   Para obtener 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 funciones del sistema que se pueden usar en una función clasificadora. Para obtener más información, vea Crear y probar un clasificador User-Defined Function.

   • SET @local_variable (Transact-SQL).

4. Emita una instrucción ALTER RESOURCE GOVERNOR para registrar la función clasificadora con Resource Governor. En el ejemplo de este procedimiento se usa 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 en memoria del Resource Governor, de la siguiente manera:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Ejemplo B: Configuración del regulador de recursos (Transact-SQL)

En el ejemplo siguiente se realizan los pasos siguientes dentro de una sola transacción:

1. Se crea el grupo de recursos pMAX_CPU_PERCENT_20.

2. Crea el grupo de carga de trabajo gMAX_CPU_PERCENT_20.

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

4. Registra la función clasificadora con 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.

Importante

En el ejemplo siguiente se usa el nombre de usuario del usuario de SQL Server de ejemplo creado en "Ejemplo A: Configurar un inicio de sesión y un usuario (Transact-SQL)," domain_name\MAX_CPU. Reemplace esto por el nombre del usuario del inicio de sesión que planea usar para crear copias de seguridad comprimidas de prioridad baja.

-- Configure Resource Governor.  
BEGIN TRAN  
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  
  

[Top]

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

Opcionalmente, inicie sesión como el usuario que especificó en la función clasificadora y compruebe la clasificación de sesión emitiendo la siguiente instrucción SELECT 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.

Nota:

Para obtener información sobre las vistas de administración dinámica a las que llama esta instrucción SELECT, vea sys.dm_exec_sessions (Transact-SQL) y sys.dm_resource_governor_workload_groups (Transact-SQL).

[Top]

Comprimir copias de seguridad mediante una sesión con CPU limitada

Para crear una copia de seguridad comprimida en una sesión con una CPU máxima limitada, 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 de base de datos comprimida, vea Crear una copia de seguridad completa de base de datos (SQL Server).

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

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

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

[Top]

Véase también

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