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
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 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.
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:
Configurar un inicio de sesión y un usuario para operaciones de prioridad baja
Configurar el regulador de recursos para limitar el uso de CPU
Comprobar la clasificación de la sesión actual (Transact-SQL)
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
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
Si lo desea, puede conceder el permiso VIEW SERVER STATE a este inicio de sesión.
Para más información, consulte Permisos de entidad de seguridad de base de datos GRANT (Transact-SQL).
Cree un usuario de SQL Server para este inicio de sesión.
Para crear un usuario
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 más información, consulte Permisos de entidad de seguridad de base de datos GRANT (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 domain_name\MAX_CPU y se añade al rol fijo de base de datos db_backupoperator para la base de datos de muestra AdventureWorks2022. 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
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:
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.
Cree y configure un grupo de cargas de trabajo del regulador de recursos que utiliza este grupo.
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.
Registre la función clasificadora con el regulador de recursos.
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 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)
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 = /*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.)
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>];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 más información sobre los componentes de esta instrucción
CREATE FUNCTION, vea:-
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.
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>]);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:
Crea el grupo de recursos de servidor
pMAX_CPU_PERCENT_20.Crea el grupo de cargas de trabajo
gMAX_CPU_PERCENT_20.Crea la función clasificadora
rgclassifier_MAX_CPU(), que utiliza el nombre de usuario creado en el ejemplo anterior.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 muestra de SQL Server 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 piensa usar para crear copias de seguridad comprimidas de prioridad baja.
-- 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
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).
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 BACKUP siguiente se crea una copia de seguridad completa comprimida de la base de datos AdventureWorks2022 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 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
Consulte también
Crear y probar una función clasificadora definida por el usuario
Regulador de recursos