• Artículo

Cómo 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 ser conveniente crear una copia de seguridad comprimida de prioridad baja en una sesión en que el uso de CPU esté limitado por el regulador 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.

Nota importanteImportante

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, vea Consideraciones para escribir una función clasificadora y Escenarios de administración de la carga de trabajo del regulador de recursos.

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

  2. Si lo desea, puede conceder el permiso VIEW SERVER STATE a este inicio de sesión.

    Para obtener más información, vea Permisos de los roles fijos de servidor (motor de base de datos).

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

    Para crear un usuario

  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

    Para obtener más información, vea Permisos de los roles fijos de base de datos (motor de base de datos).

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

El ejemplo siguiente sólo 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 utilizar un inicio de sesión y un usuario existentes, si hay alguno adecuado.

Nota importanteImportante

En el ejemplo siguiente se utiliza 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 utilizar 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, a continuación, 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. El ejemplo crea a continuación un usuario para nombre_de_dominio\MAX_CPU y lo agrega al rol fijo de base de datos db_backupoperator para la base de datos de ejemplo AdventureWorks2008R2. La función clasificadora del regulador de recursos utilizará 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 AdventureWorks2008R2 for this login
USE AdventureWorks2008R2;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[Principio]

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 Cómo habilitar o deshabilitar el regulador de recursos (SQL Server Management Studio).

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 Conceptos 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)

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. A efectos de ilustración, en el ejemplo de este tema se utiliza un porcentaje del 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 desea 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

    END

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

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

Nota importanteImportante

En el ejemplo siguiente se utiliza 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 utilizar 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

[Principio]

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 invocadas por esta instrucción SELECT, vea sys.dm_exec_sessions (Transact-SQL) y sys.dm_resource_governor_workload_groups (Transact-SQL).

[Principio]

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

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 AdventureWorks2008R2 en un archivo de copia de seguridad al que se ha dado formato recientemente, Z:\SQLServerBackups\AdvWorks2008R2Data.bak.

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

[Principio]