Procedura: 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. È pertanto possibile creare un backup compresso con priorità bassa in una sessione con utilizzo della CPU limitato da Resource Governor nel caso in cui si verifichi un conflitto 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.

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. Per ulteriori informazioni, vedere Considerazioni per la scrittura di una funzione di classificazione e Scenari di gestione del carico di lavoro di Resource Governor.

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 della 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 l'esempio 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 SQL Server per la creazione di backup compressi con priorità bassa. 

   Per creare un account di accesso

   • Procedura: Creazione di un account di accesso di SQL Server

   • 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 ulteriori informazioni, vedere Autorizzazioni dei ruoli predefiniti del server (Motore di database).

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

   Per creare un utente

   • Procedura: Creazione di un utente del 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 ulteriori informazioni, vedere Autorizzazioni dei ruoli predefiniti del database (Motore di database).

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

Importante
Nell'esempio seguente vengono utilizzati un account di accesso e un nome utente domain_name\MAX_CPU. Sostituire questi nomi con quelli dell'account di accesso e dell'utente di SQL Server che si intende utilizzare nella creazione dei backup compressi con priorità bassa.

In questo esempio viene creato un account di accesso per l'account di Windows domain_name\MAX_CPU, quindi a tale account viene concessa l'autorizzazione VIEW SERVER STATE che consente di verificare la classificazione di Resource Governor delle sessioni dell'account di accesso. Nell'esempio viene successivamente creato un utente per domain_name\MAX_CPU, che viene aggiunto al ruolo predefinito del database db_backupoperator per il database di esempio AdventureWorks. 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 AdventureWorks for this login
USE AdventureWorks;
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]

Configurazione di Resource Governor per limitare l'utilizzo della CPU

[!NOTA]

Verificare che Resource Governor sia abilitato. Per ulteriori informazioni, vedere Procedura: Abilitazione o abilitazione di Resource Governor (SQL Server Management Studio).

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 un conflitto 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 una funzione definita dall'utente i cui valori restituiti vengono utilizzati da Resource Governor per classificare le sessioni in modo da indirizzarle al gruppo del carico di lavoro appropriato.

4. Registrazione della funzione di classificazione con Resource Governor.

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

[!NOTA]

Per informazioni sui pool di risorse, i gruppi del carico di lavoro e la classificazione di Resource Governor, vedere Concetti 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 Transact-SQL.

Per configurare Resource Governor (SQL Server Management Studio)

• Procedura: Configurazione di Resource Governor utilizzando un modello (SQL Server Management Studio)

• Procedura: Creazione di un pool di risorse (SQL Server Management Studio)

• Procedura: Creazione di un gruppo del carico di lavoro (SQL Server Management Studio)

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 = value );

   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 del carico di lavoro per le operazioni con priorità bassa per cui si desidera 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 del 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 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 ulteriori informazioni, vedere Considerazioni per la scrittura di una funzione di classificazione.

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

Importante
Nell'esempio seguente viene utilizzato il nome dell'utente di SQL Server di esempio creato in "Esempio A. Impostazione di un account di accesso e di un utente (Transact-SQL)", ovvero nome_dominio\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.

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

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 Risultati devono essere elencate una o più sessioni per il gruppo del carico di lavoro specificato nella funzione di classificazione.

[!NOTA]

Per informazioni sulle viste a gestione dinamica chiamate da questa funzione SELECT, vedere sys.dm_exec_sessions (Transact-SQL) e sys.dm_resource_governor_workload_groups (Transact-SQL).

[Torna all'inizio]

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

• Procedura: Creazione di un backup completo del database (Transact-SQL)

• Procedura: Esecuzione del backup di un database (SQL Server Management Studio)

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

Nell'esempio seguente BACKUP viene creato un backup compresso completo del database AdventureWorks nel nuovo file di backup formattato Z:\SQLServerBackups\AdvWorksData.bak.

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

[Torna all'inizio]

Vedere anche

Attivitá

Procedura: Creazione e test di una funzione di classificazione definita dall'utente (Transact-SQL)

Concetti

Introduzione a Resource Governor

Concetti di Resource Governor

Scenari di gestione del carico di lavoro di Resource Governor

Risoluzione dei problemi relativi a Resource Governor

Altre risorse

Gestione di carichi di lavoro di SQL Server con Resource Governor

Procedure per Resource Governor