Usar el regulador de recursos para limitar el uso de CPU mediante 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 adversamente a las operaciones simultáneas. Por consiguiente, podría querer crear una copia de seguridad comprimida de prioridad baja en una sesión en la que el uso de CPU esté limitado por elregulador de recursos 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.

Importante

En un escenario determinado en el que se use el regulador de recursos, la clasificación de la sesión podría basarse en un nombre de usuario, un nombre de aplicación u otra característica 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 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 para este fin, seguido 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. Si lo desea, puede conceder el permiso VIEW SERVER STATE a este inicio de sesión.

   • GRANT (permisos de objeto de sistema de Transact-SQL)

   Para obtener 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 obtener 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.

Importante

En el ejemplo siguiente se usa un inicio de sesión y un nombre de usuario de ejemplo, nombre_de_dominio\MAX_CPU. Reemplácelos por los nombres del usuario y el inicio de sesión de SQL Server que piensa usar al crear las copias de seguridad comprimidas de prioridad baja.

En este ejemplo se crea un inicio de sesión para la cuenta de Windows nombre_de_dominio\MAX_CPU y, después, se concede el permiso VIEW SERVER STATE al inicio de sesión. Este permiso permite comprobar la clasificación que hace el regulador de recursos de las sesiones del inicio de sesión. Después, en el ejemplo se crea un usuario para nombre_de_dominio\MAX_CPU y se agrega al rol fijo de base de datos db_backupoperator para la base de datos de ejemplo AdventureWorks2012 . 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 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]

Configurar el regulador de recursos para limitar el uso de CPU

Nota:

Asegúrese de que el regulador de recursos está habilitado. Para obtener más información, vea Habilitar el regulador de recursos.

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 servidor del regulador de recursos que limite el ancho de banda máximo medio de CPU que se concederá a las solicitudes en el grupo de recursos de servidor cuando se produzca la contención por la CPU.

2. Cree y configure un grupo de cargas de trabajo del regulador de recursos que utiliza este grupo.

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 el regulador de recursos.

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 servidor del regulador de recursos, los grupos de cargas de trabajo y la clasificación, vea Regulador de recursos.

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 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 de servidor

• 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. Emita una instrucción CREATE RESOURCE POOL para crear un grupo de recursos de servidor. En el ejemplo de este procedimiento se utiliza la sintaxis siguiente:

   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

   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. Para ilustrarlo, el ejemplo de 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 las operaciones de prioridad baja cuyo uso de CPU quiera regular. En el ejemplo de este procedimiento se utiliza la sintaxis siguiente:

   CREATE WORKLOAD GROUP group_name USING 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 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

   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 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 instrucción ALTER RESOURCE GOVERNOR para registrar la función clasificadora con el regulador de recursos. 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 en memoria del regulador de recursos de la siguiente manera:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Ejemplo B: Configurar Resource Governor (Transact-SQL)

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

1. Crea el grupo de recursos de servidor 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 clasificadora con el regulador de recursos.

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 ejemplo de SQL Server creado en "Ejemplo A: Configurar un inicio de sesión y un usuario (Transact-SQL)", nombre_de_dominio\MAX_CPU. Reemplace esto por el nombre del usuario del inicio de sesión que piensa 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]

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.

Nota:

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

[Top]

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 de base de datos comprimida, consulte Creación de una copia de seguridad de base de datos completa (SQL Server).

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

En el ejemplo BACKUP siguiente se crea una copia de seguridad completa comprimida de la base de datos AdventureWorks2012 en un archivo de copia de seguridad al que se ha dado formato recientemente, 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]

Consulte también

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