Utilizzo di Resource Governor per limitare l'utilizzo della CPU da parte della compressione dei backup (Transact-SQL)Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

In questo argomento si applica a: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Per impostazione predefinita, l'esecuzione di backup mediante la compressione aumenta in modo significativo l'utilizzo della CPU e la CPU aggiuntiva utilizzata dal processo di compressione può avere un impatto negativo sulle operazioni simultanee.By default, backing up using compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations. È necessario quindi creare un backup compresso con priorità bassa in una sessione con utilizzo della CPU limitato daResource Governor nel caso in cui si verifichi una contesa di CPU.Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited byResource Governor when CPU contention occurs. In questo argomento viene presentato uno scenario che classifica le sessioni di un particolare utente di SQL ServerSQL Server eseguendone mapping a un gruppo del carico di lavoro di Resource Governor che limita l'utilizzo della CPU in tali casi.This topic presents a scenario that classifies the sessions of a particular SQL ServerSQL Server user by mapping them to a Resource Governor workload group that limits CPU usage in such cases.

Importante

In uno scenario di Resource Governor specifico la classificazione della sessione potrebbe essere basata su un nome utente, un nome di applicazione o qualsiasi altro elemento in grado di differenziare una connessione.In a given Resource Governor scenario, session classification might be based on a user name, an application name, or anything else that can differentiate a connection. Per ulteriori informazioni, vedere Resource Governor Classifier Function e Resource Governor Workload Group.For more information, see Resource Governor Classifier Function and Resource Governor Workload Group.

In questo argomento è contenuto il set seguente di scenari, che vengono presentati in sequenza:This topic contains the following set of scenarios, which are presented in sequence:

  1. Impostazione di un account di accesso e di un utente per operazioni con priorità bassaSetting Up a Login and User for Low-Priority Operations

  2. Configurazione di Resource Governor per limitare l'utilizzo di CPUConfiguring Resource Governor to Limit CPU Usage

  3. Verifica della classificazione della sessione corrente (Transact-SQL)Verifying the Classification of the Current Session (Transact-SQL)

  4. Compressione di backup utilizzando una sessione con utilizzo della CPU limitatoCompressing Backups Using a Session with Limited CPU

Impostazione di un account di accesso e di un utente per operazioni con priorità bassaSetting Up a Login and User for Low-Priority Operations

Per lo scenario presentato in questo argomento sono necessari un account di accesso e un utente di SQL ServerSQL Server con priorità bassa.The scenario in this topic requires a low-priority SQL ServerSQL Server login and user. Il nome utente verrà utilizzato per classificare sessioni in esecuzione con l'account di accesso e per indirizzarle a un gruppo del carico di lavoro di Resource Governor che limita l'utilizzo della CPU.The user name will be used to classify sessions running in the login and route them to a Resource Governor workload group that limits CPU usage.

Nella procedura seguente vengono descritte le operazioni necessarie per impostare un account di accesso e un utente a tale scopo. Viene quindi illustrato l'esempio Transact-SQLTransact-SQL "Esempio A. Impostazione di un account di accesso e di un utente (Transact-SQL)".The following procedure describes the steps for setting up a login and user for this purpose, followed by a Transact-SQLTransact-SQL example, "Example A: Setting Up a Login and User (Transact-SQL)."

Per impostare un account di accesso e un utente del database per classificare sessioniTo set up a login and database user for classifying sessions

  1. Definire un account di accesso SQL ServerSQL Server per la creazione di backup compressi con priorità bassa.Create a SQL ServerSQL Server login for creating low-priority compressed backups.

    Per creare un account di accessoTo create a login

  2. Facoltativamente, concedere l'autorizzazione VIEW SERVER STATE a tale account di accesso.Optionally, grant VIEW SERVER STATE to this login.

  3. Creare un utente di SQL ServerSQL Server per tale account di accesso.Create a SQL ServerSQL Server user for this login.

    Per creare un utenteTo create a user

  4. Per consentire alle sessioni relative agli account di accesso e all'utente creati di eseguire il backup di un database specifico, aggiungere l'utente al ruolo db_backupoperator del database.To enable sessions of this login and user to back up a given database, add the user to the db_backupoperator database role of that database. Effettuare questa operazione per ogni database di cui l'utente eseguirà il backup.Do this for each database that this user will back up. Facoltativamente, aggiungere l'utente agli altri ruoli predefiniti del database.Optionally, add the user to other fixed database roles.

    Per aggiungere un utente a un ruolo predefinito del databaseTo add a user to a fixed database role

Esempio A. Impostazione di un accesso e di un utente (Transact-SQL)Example A: Setting Up a Login and User (Transact-SQL)

L'esempio seguente è rilevante solo se si sceglie di creare un nuovo account di accesso e un nuovo utente di SQL ServerSQL Server per eseguire backup con priorità bassa.The following example is relevant only if you choose to create a new SQL ServerSQL Server login and user for low-priority backups. In alternativa, è possibile utilizzare un account di accesso e un utente esistenti, se appropriati.Alternatively, you can use an existing login and user, if an appropriate one exists.

Importante

L'esempio seguente usa un accesso e un nome utente di esempio, domain_name\MAX_CPU.The following example uses a sample login and user name, domain_name\MAX_CPU. Sostituire questi nomi con quelli dell'account di accesso e dell'utente di SQL ServerSQL Server che si intende utilizzare nella creazione dei backup compressi con priorità bassa.Replace these with the names of the SQL ServerSQL Server login and user that you plan to use when creating your low-priority compressed backups.

Questo esempio crea un accesso per l'account di Windows domain_name\MAX_CPU e concede l'autorizzazione VIEW SERVER STATE all'accesso.This example creates a login for the domain_name\MAX_CPU Windows account and then grants VIEW SERVER STATE permission to the login. che consente di verificare la classificazione di Resource Governor delle sessioni dell'account di accesso.This permission enables you to verify the Resource Governor classification of sessions of the login. L'esempio crea quindi un utente per domain_name\MAX_CPU e lo aggiunge al ruolo predefinito del database db_backupoperator per il database di esempio AdventureWorks2012AdventureWorks2012 .The example then creates a user for domain_name\MAX_CPU and adds it to the db_backupoperator fixed database role for the AdventureWorks2012AdventureWorks2012 sample database. Tale nome utente verrà utilizzato dalla funzione di classificazione di Resource Governor.This user name will be used by the Resource Governor classifier function.

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

[Torna all'inizio][Top]

Configurazione di Resource Governor per limitare l'utilizzo di CPUConfiguring Resource Governor to Limit CPU Usage

Nota

Verificare che Resource Governor sia abilitato.Ensure that Resource Governor is enabled. Per altre informazioni, vedere Abilitare Resource Governor.For more information, see Enable Resource Governor.

In questo scenario di Resource Governor la configurazione include i passaggi di base seguenti:In this Resource Governor scenario, configuration comprises the following basic steps:

  1. Creazione e configurazione di un pool di risorse di Resource Governor che limita il valore massimo della larghezza di banda media della CPU assegnata alle richieste nel pool di risorse quando si verifica una contesa di CPU.Create and configure a Resource Governor resource pool that limits the maximum average CPU bandwidth that will be given to requests in the resource pool when CPU contention occurs.

  2. Creazione e configurazione di un gruppo del carico di lavoro di Resource Governor che utilizza tale pool.Create and configure a Resource Governor workload group that uses this pool.

  3. Creazione di una funzione di classificazione, ovvero di una funzione definita dall'utente i cui valori restituiti vengono usati da Resource Governor per classificare le sessioni in modo da indirizzarle al gruppo di carico di lavoro appropriato.Create a classifier function, which is a user-defined function (UDF) whose return values are used by Resource Governor for classifying sessions so that they are routed to the appropriate workload group.

  4. Registrazione della funzione di classificazione con Resource Governor.Register the classifier function with Resource Governor.

  5. Applicazione delle modifiche alla configurazione in memoria di Resource Governor.Apply the changes to the Resource Governor in-memory configuration.

Nota

Per informazioni sui pool di risorse, i gruppi di carico di lavoro e la classificazione di Resource Governor, vedere Resource Governor.For information about Resource Governor resource pools, workload groups, and classification, see Resource Governor.

Le istruzioni Transact-SQLTransact-SQL per questi passaggi vengono descritte nella procedura "Per configurare Resource Governor per limitare l'utilizzo della CPU", al termine della quale viene illustrato un esempio Transact-SQLTransact-SQL .The Transact-SQLTransact-SQL statements for these steps are described in the procedure, "To configure Resource Governor for limiting CPU usage," which is followed by a Transact-SQLTransact-SQL example of the procedure.

Per configurare Resource Governor (SQL Server Management Studio)To configure Resource Governor (SQL Server Management Studio)

Per configurare Resource Governor per limitare l'utilizzo della CPU (Transact-SQL)To configure Resource Governor for limiting CPU usage (Transact-SQL)

  1. Eseguire un'istruzione CREATE RESOURCE POOL per creare un pool di risorse.Issue a CREATE RESOURCE POOL statement to create a resource pool. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:The example for this procedure uses the following syntax:

    CREATE RESOURCE POOL nome_pool WITH ( MAX_CPU_PERCENT = valore );CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

    Value è un integer compreso tra 1 e 100 che indica la percentuale del valore massimo della larghezza di banda media della CPU.Value is an integer from 1 to 100 that indicates the percentage of maximum average CPU bandwidth. Il valore appropriato dipende dall'ambiente.The appropriate value depends on your environment. A scopo illustrativo, nell'esempio in questo argomento viene utilizzato il valore 20% (MAX_CPU_PERCENT = 20).For the purpose of illustration, the example in this topic uses 20% percent (MAX_CPU_PERCENT = 20.)

  2. Eseguire un'istruzione CREATE WORKLOAD GROUP per creare un gruppo di carico di lavoro per le operazioni con priorità bassa per cui si vuole controllare l'utilizzo della CPU.Issue a CREATE WORKLOAD GROUP statement to create a workload group for low-priority operations whose CPU usage you want to govern. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:The example for this procedure uses the following syntax:

    CREATE WORKLOAD GROUP nome_gruppo USING nome_pool;CREATE WORKLOAD GROUP group_name USING pool_name;

  3. Eseguire un'istruzione CREATE FUNCTION per creare una funzione di classificazione che esegue il mapping del gruppo di carico di lavoro creato nel passaggio precedente all'utente relativo all'account di accesso con priorità bassa.Issue a CREATE FUNCTION statement to create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:The example for this procedure uses the following syntax:

    CREATE FUNCTION [nome_schema.]nome_funzione() RETURNS sysnameCREATE FUNCTION [schema_name.]function_name() RETURNS sysname

    WITH SCHEMABINDINGWITH SCHEMABINDING

    ASAS

    BEGINBEGIN

    DECLARE @workload_group_name AS sysnameDECLARE @workload_group_name AS sysname

    IF (SUSER_NAME() = 'utente_accesso_bassa_priorità')IF (SUSER_NAME() = 'user_of_low_priority_login')

    SET @workload_group_name = 'nome_gruppo_carico_di_lavoro'SET @workload_group_name = 'workload_group_name'

    RETURN @workload_group_nameRETURN @workload_group_name

    ENDEND

    Per informazioni sui componenti di questa istruzione CREATE FUNCTION, vedere i seguenti argomenti:For information about the components of this CREATE FUNCTION statement, see:

  4. Eseguire un'istruzione ALTER RESOURCE GOVERNOR per registrare la funzione di classificazione con Resource Governor.Issue an ALTER RESOURCE GOVERNOR statement to register the classifier function with Resource Governor. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:The example for this procedure uses the following syntax:

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = nome_schema.nome_funzione);ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

  5. Eseguire una seconda istruzione ALTER RESOURCE GOVERNOR per applicare le modifiche alla configurazione in memoria di Resource Governor, come descritto di seguito:Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:

    ALTER RESOURCE GOVERNOR RECONFIGURE;  
    

Esempio B. Configurazione di Resource Governor (Transact-SQL)Example B: Configuring Resource Governor (Transact-SQL)

Nell'esempio seguente vengono effettuati i passaggi seguenti all'interno di un'unica transazione:The following example performs the following steps within a single transaction:

  1. Creazione del pool di risorse pMAX_CPU_PERCENT_20 .Creates the pMAX_CPU_PERCENT_20 resource pool.

  2. Creazione del gruppo del carico di lavoro gMAX_CPU_PERCENT_20 .Creates the gMAX_CPU_PERCENT_20 workload group.

  3. Creazione della funzione di classificazione rgclassifier_MAX_CPU() che utilizza il nome utente creato nell'esempio precedente.Creates the rgclassifier_MAX_CPU() classifier function, which uses the user name created in the preceding example.

  4. Registrazione della funzione di classificazione con Resource Governor.Registers the classifier function with Resource Governor.

    Dopo l'esecuzione del commit della transazione, nell'esempio vengono applicate le modifiche di configurazione richieste nell'istruzione ALTER WORKLOAD GROUP o ALTER RESOURCE POOL.After committing the transaction, the example applies the configuration changes requested in the ALTER WORKLOAD GROUP or ALTER RESOURCE POOL statements.

Importante

L'esempio seguente usa il nome dell'utente di SQL ServerSQL Server di esempio creato in "Esempio A. Impostazione di un accesso e di un utente (Transact-SQL)", ossia domain_name\MAX_CPU.The following example uses the user name of the sample SQL ServerSQL Server user created in "Example A: Setting Up a Login and User (Transact-SQL)," domain_name\MAX_CPU. Sostituire questo nome con quello dell'utente relativo all'account di accesso che si intende utilizzare per la creazione di backup compressi con priorità bassa.Replace this with the name of the user of the login that you plan to use for creating low-priority compressed backups.

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

[Torna all'inizio][Top]

Verifica della classificazione della sessione corrente (Transact-SQL)Verifying the Classification of the Current Session (Transact-SQL)

Facoltativamente, accedere come l'utente specificato nella funzione di classificazione e verificare la classificazione della sessione eseguendo l'istruzione SELECT seguente in Esplora oggetti:Optionally, log in as the user that you specified in your classifier function, and verify the session classification by issuing the following SELECT statement in Object Explorer:

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  

Nella colonna name del riquadro dei risultati dovrebbero essere elencate una o più sessioni per il nome del gruppo di carico di lavoro specificato nella funzione di classificazione.In the results pane, the name column should list one or more sessions for the workload-group name that you specified in your classifier function.

Nota

Per informazioni sulle viste a gestione dinamica chiamate da questa istruzione SELECT, vedere sys.dm_exec_sessions (Transact-SQL) e sys.dm_resource_governor_workload_groups (Transact-SQL).For information about the dynamic management views called by this SELECT statement, see sys.dm_exec_sessions (Transact-SQL) and sys.dm_resource_governor_workload_groups (Transact-SQL).

[Torna all'inizio][Top]

Compressione di backup utilizzando una sessione con utilizzo della CPU limitatoCompressing Backups Using a Session with Limited CPU

Per creare un backup compresso in una sessione con un utilizzo massimo della CPU limitato, accedere come l'utente specificato nella funzione di classificazione.To create a compressed backup in a session with a limited maximum CPU, log in as the user specified in your classifier function. Nel comando di backup specificare WITH COMPRESSION ( Transact-SQLTransact-SQL) o selezionare Comprimi backup ( SQL Server Management StudioSQL Server Management Studio).In your backup command, either specify WITH COMPRESSION ( Transact-SQLTransact-SQL) or select Compress backup ( SQL Server Management StudioSQL Server Management Studio). Per creare un backup compresso del database, vedere Creazione di un backup completo del database (SQL Server).To create a compressed database backup, see Create a Full Database Backup (SQL Server).

Esempio C. Creazione di un backup compresso (Transact-SQL)Example C: Creating a Compressed Backup (Transact-SQL)

Nell'esempio seguente BACKUP viene creato un backup compresso completo del database AdventureWorks2012AdventureWorks2012 nel nuovo file di backup formattato Z:\SQLServerBackups\AdvWorksData.bak.The following BACKUP example creates a compressed full backup of the AdventureWorks2012AdventureWorks2012 database in a newly formatted backup file, 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  

[Torna all'inizio][Top]

Vedere ancheSee Also

Creare e testare una funzione di classificazione definita dall'utente Create and Test a Classifier User-Defined Function
Resource GovernorResource Governor