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

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 daResource Governor nel caso in cui si verifichi una contesa di CPU. Questo argomento presenta uno scenario che classifica le sessioni di un determinato utente SQL Server mapping a un gruppo di carico di lavoro Resource Governor che limita l'utilizzo della CPU in questi 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

Lo scenario in questo argomento richiede una priorità bassa SQL Server account di accesso e utente. 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.

La procedura seguente descrive i passaggi per configurare un account di accesso e un utente per questo scopo, seguito da un esempio Transact-SQL, "Esempio A: Configurazione 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. Creare un account di accesso 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 Database Principal Permissions (Transact-SQL).

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

   Per creare un utente

   • Creazione di 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 Database Principal Permissions (Transact-SQL).

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

L'esempio seguente è rilevante solo se si sceglie di creare un nuovo account di accesso SQL Server e l'utente per i 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 AdventureWorks2012. 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 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]

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 sono descritte nella procedura "Per configurare Resource Governor per limitare l'utilizzo della CPU", seguito da un esempio Transact-SQL della procedura.

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 nome_pool WITH ( MAX_CPU_PERCENT = valore );

   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. Per lo scopo dell'illustrazione, l'esempio in questo argomento usa il 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 nome_gruppo USING nome_pool;

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 [nome_schema.]nome_funzione() RETURNS sysname

   WITH SCHEMABINDING

   AS

   BEGIN

   DECLARE @workload_group_name AS sysname

   IF (SUSER_NAME() = 'utente_accesso_bassa_priorità')

   SET @workload_group_name = 'nome_gruppo_carico_di_lavoro'

   RETURN @workload_group_name

   FINE

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

   • 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 = nome_schema.nome_funzione);

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

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 Compress backup (SQL Server Management Studio). Per creare un backup compresso del database, vedere Creare un backup completo del database (SQL Server).

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

Nell'esempio backup seguente viene creato un backup completo compresso del database AdventureWorks2012 in un file di backup appena formattato, 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]

Vedere anche

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