Usare Resource Governor per limitare l'utilizzo della CPU da parte della compressione dei backup (Transact-SQL)

Si applica a: SQL Server

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. È necessario quindi creare un backup compresso con priorità bassa in una sessione con utilizzo della CPU limitato da Resource Governor nel caso in cui si verifichi una contesa di CPU. In questo argomento viene presentato uno scenario che classifica le sessioni di un particolare utente di SQL Server eseguendone mapping a un gruppo del carico di lavoro di Resource Governor che limita l'utilizzo della CPU in tali casi.

In questo argomento è contenuto il set seguente di scenari, che vengono presentati in sequenza:

1. Impostazione di un account di accesso e di un utente per operazioni con priorità bassa

2. Configurazione di Resource Governor per limitare l'utilizzo di CPU

3. Verifica della classificazione della sessione corrente (Transact-SQL)

4. Compressione di backup utilizzando una sessione con utilizzo della CPU limitato

Impostazione di un account di accesso e di un utente per operazioni con priorità bassa

Per lo scenario presentato in questo argomento sono necessari un account di accesso e un utente di SQL Server con priorità bassa. 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.

Nella procedura seguente vengono descritte le operazioni necessarie per impostare un account di accesso e un utente a tale scopo. Viene quindi illustrato un esempio di Transact-SQL, "Esempio A. Impostazione di un account di accesso e di un utente (Transact-SQL)".

Per impostare un account di accesso e un utente del database per classificare sessioni

1. Definire un account di accesso di SQL Server per la creazione di backup compressi con priorità bassa.

   Per creare un account di accesso

   • Creare un account di accesso

   • CREATE LOGIN (Transact-SQL)

2. Facoltativamente, concedere l'autorizzazione VIEW SERVER STATE a tale account di accesso.

   • GRANT - autorizzazioni per oggetti di sistema (Transact-SQL)

   Per altre informazioni, vedere GRANT - autorizzazioni per entità di database (Transact-SQL).

3. Creare un utente di SQL Server per tale account di accesso.

   Per creare un utente

   • Creare un utente di database

   • CREATE USER (Transact-SQL)

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. Effettuare questa operazione per ogni database di cui l'utente eseguirà il backup. Facoltativamente, aggiungere l'utente agli altri ruoli predefiniti del database.

   Per aggiungere un utente a un ruolo predefinito del database

   • sp_addrolemember (Transact-SQL)

   Per altre informazioni, vedere GRANT - autorizzazioni per entità di database (Transact-SQL).

Esempio A. Impostazione di un accesso e di un utente (Transact-SQL)

L'esempio seguente è rilevante solo se si sceglie di creare un nuovo account di accesso e un nuovo utente di SQL Server per eseguire backup con priorità bassa. In alternativa, è possibile utilizzare un account di accesso e un utente esistenti, se appropriati.

Questo esempio crea un accesso per l'account di Windows domain_name\MAX_CPU e concede l'autorizzazione VIEW SERVER STATE all'accesso. che consente di verificare la classificazione di Resource Governor delle sessioni dell'account di accesso. 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 AdventureWorks2022. Tale nome utente verrà utilizzato dalla funzione di classificazione di Resource Governor.

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

[Top]

Configurazione di Resource Governor per limitare l'utilizzo di CPU

In questo scenario di Resource Governor la configurazione include i passaggi di base seguenti:

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.

2. Creazione e configurazione di un gruppo del carico di lavoro di Resource Governor che utilizza tale 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.

4. Registrazione della funzione di classificazione con Resource Governor.

5. Applicazione delle modifiche alla configurazione in memoria di Resource Governor.

Le istruzioni Transact-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 di Transact-SQL.

Per configurare Resource Governor (SQL Server Management Studio)

• Configurare Resource Governor usando un modello

• Creare un pool di risorse

• Creare un gruppo di carico di lavoro

Per configurare Resource Governor per limitare l'utilizzo della CPU (Transact-SQL)

1. Eseguire un'istruzione CREATE RESOURCE POOL per creare un pool di risorse. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:

   CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );
   

   Value è un integer compreso tra 1 e 100 che indica la percentuale del valore massimo della larghezza di banda media della CPU. Il valore appropriato dipende dall'ambiente. A scopo illustrativo, nell'esempio in questo argomento viene utilizzato il valore 20% (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. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:

   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. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:

   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;
   

   Per informazioni sui componenti di questa istruzione CREATE FUNCTION, vedere:

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Importante

     SUSER_NAME è solo una delle numerose funzioni di sistema che possono essere utilizzate in una funzione di classificazione. Per altre informazioni, vedere Creare e testare una funzione di classificazione definita dall'utente.

   • SET @local_variable (Transact-SQL).

4. Eseguire un'istruzione ALTER RESOURCE GOVERNOR per registrare la funzione di classificazione con Resource Governor. Nell'esempio relativo a questa procedura viene utilizzata la sintassi seguente:

   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:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

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

Nell'esempio seguente vengono effettuati i passaggi seguenti all'interno di un'unica transazione:

1. Creazione del pool di risorse pMAX_CPU_PERCENT_20 .

2. Creazione del gruppo del carico di lavoro gMAX_CPU_PERCENT_20 .

3. Creazione della funzione di classificazione rgclassifier_MAX_CPU() che utilizza il nome utente creato nell'esempio precedente.

4. Registrazione della funzione di classificazione con 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.

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

[Top]

Verifica della classificazione della sessione corrente (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:

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.

[Top]

Compressione di backup utilizzando una sessione con utilizzo della CPU limitato

Per creare un backup compresso in una sessione con un utilizzo massimo della CPU limitato, accedere come l'utente specificato nella funzione di classificazione. Nel comando di backup specificare WITH COMPRESSION (Transact-SQL) o selezionare Comprimi backup (SQL Server Management Studio). Per creare un backup compresso del database, vedere Creazione di un backup completo del database (SQL Server).

Esempio C. Creazione di un backup compresso (Transact-SQL)

Nell'esempio seguente BACKUP viene creato un backup compresso completo del database AdventureWorks2022 nel nuovo file di backup formattato, 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  

[Top]

Vedi anche

Creare e testare una funzione di classificazione definita dall'utente
Resource Governor