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

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

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]

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;
   

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]

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]