Einschränken der CPU-Nutzung durch die Sicherungskomprimierung mithilfe der Ressourcenkontrolle (Transact-SQL)

Gilt für: SQL Server

Standardmäßig steigt die CPU-Nutzung durch die Sicherung mit Komprimierung erheblich, und die bei der Komprimierung zusätzlich benötigten CPU-Ressourcen können sich negativ auf gleichzeitig ausgeführte Vorgänge auswirken. Daher sollten Sie in einer Sitzung, bei der die CPU-Nutzung durch den Resource Governor eingeschränkt ist, eine komprimierte Sicherung mit niedriger Priorität erstellen, wenn CPU-Konflikte bestehen. In diesem Thema wird ein Szenario dargestellt, in dem die Sitzungen eines bestimmten SQL Server-Benutzers klassifiziert werden, indem diese einer Arbeitsauslastungsgruppe des Resource Governors zugeordnet werden, der die CPU-Nutzung in solchen Fällen einschränkt.

Wichtig

In einem Szenario für Ressourcenkontrolle kann die Klassifizierung der Sitzungen auf einem Benutzernamen, einem Anwendungsnamen oder einem beliebigen anderen Element basieren, anhand dessen Verbindungen voneinander unterschieden werden können. Weitere Informationen finden Sie unter Resource Governor Classifier Function und Resource Governor Workload Group.

Dieses Thema enthält die folgenden Szenarien, die nacheinander dargestellt werden:

1. Einrichten einer Anmeldung und eines Benutzers für Vorgänge mit niedriger Priorität

2. Konfigurieren der Ressourcenkontrolle zum Einschränken der CPU-Nutzung

3. Überprüfen der Klassifizierung der aktuellen Sitzung (Transact-SQL)

4. Komprimieren von Sicherungen in einer Sitzung mit CPU-Grenzwert

Einrichten einer Anmeldung und eines Benutzers für Vorgänge mit niedriger Priorität

Für das Szenario in diesem Thema sind eine SQL Server-Anmeldung und ein zugehöriger Benutzer für Vorgänge mit niedriger Priorität erforderlich. Mithilfe des Benutzernamens werden die in der Anmeldung ausgeführten Sitzungen klassifiziert und an eine Arbeitsauslastungsgruppe der Ressourcenkontrolle weitergeleitet, die die CPU-Nutzung einschränkt.

Im folgenden Verfahren werden die Schritte zum Einrichten einer Anmeldung und eines Benutzers zu diesem Zweck beschrieben. Darauf folgt das Transact-SQL-Beispiel "Beispiel A: Einrichten einer Anmeldung und eines Benutzers (Transact-SQL)."

So richten Sie eine Anmeldung und einen Datenbankbenutzer zum Klassifizieren von Sitzungen ein

1. Erstellen Sie eine SQL Server-Anmeldung zum Erstellen komprimierter Sicherungen mit niedriger Priorität.

   So erstellen Sie eine Anmeldung

   • Erstellen eines Anmeldenamens

   • CREATE LOGIN (Transact-SQL)

2. Sie können dieser Anmeldung die VIEW SERVER STATE-Berechtigung erteilen.

   • GRANT (Berechtigungen für Systemobjekte) (Transact-SQL)

   Weitere Informationen finden Sie unter GRANT Berechtigungen für Datenbankprinzipal (Transact-SQL).

3. Erstellen Sie einen SQL Server-Benutzer für diese Anmeldung.

   So erstellen Sie einen Benutzer

   • Erstellen eines Datenbankbenutzers

   • CREATE USER (Transact-SQL)

4. Fügen Sie den Benutzer der Datenbankrolle db_backupoperator der Datenbank hinzu, damit für die Anmeldung Sitzungen möglich sind und der Benutzer eine Datenbank sichern kann. Wiederholen Sie diesen Schritt für alle Datenbanken, die gesichert werden sollen. Sie können den Benutzer auch anderen festen Datenbankrollen hinzufügen.

   So fügen Sie einen Benutzer einer festen Datenbankrolle hinzu

   • sp_addrolemember (Transact-SQL)

   Weitere Informationen finden Sie unter GRANT Berechtigungen für Datenbankprinzipal (Transact-SQL).

Beispiel A: Einrichten einer Anmeldung und eines Benutzers (Transact-SQL)

Das folgende Beispiel ist nur dann relevant, wenn Sie eine neue SQL Server-Anmeldung und einen zugehörigen Benutzer für Sicherungen mit niedriger Priorität erstellen möchten. Alternativ können Sie eine vorhandene Anmeldung und einen vorhandenen Benutzer verwenden, wenn entsprechende vorhanden sind.

Wichtig

Im folgenden Beispiel werden eine Beispielanmeldung und ein Beispielbenutzername, domain_name\MAX_CPU, verwendet. Ersetzen Sie diese durch die Namen der SQL Server-Anmeldung und des zugehörigen Benutzers, die beim Erstellen komprimierter Sicherungen mit niedriger Priorität verwendet werden sollen.

In diesem Beispiel wird eine Anmeldung für das Windows-Konto domain_name\MAX_CPU erstellt, und dieser wird anschließend die VIEW SERVER STATE-Berechtigung erteilt. Mit dieser Berechtigung können Sie die Klassifizierung der Sitzungen der Anmeldung durch die Ressourcenkontrolle überprüfen. Im Beispiel wird anschließend ein Benutzer für domain_name\MAX_CPU erstellt, und dieser wird der festen Datenbankrolle db_backupoperator für die AdventureWorks2022-Beispieldatenbank hinzugefügt. Dieser Benutzername wird von der Klassifizierungsfunktion der Ressourcenkontrolle verwendet.

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

[Nach oben]

Konfigurieren der Ressourcenkontrolle zum Einschränken der CPU-Nutzung

Hinweis

Stellen Sie sicher, dass die Ressourcenkontrolle aktiviert ist. Weitere Informationen finden Sie unter Aktivieren der Ressourcenkontrolle.

In diesem Szenario für Ressourcenkontrolle besteht die Konfiguration aus den folgenden grundlegenden Schritten:

1. Erstellen und konfigurieren Sie einen Ressourcenpool für die Ressourcenkontrolle zum Einschränken der maximalen durchschnittlichen CPU-Bandbreite für alle Anforderungen im Ressourcenpool, wenn CPU-Konflikte bestehen.

2. Erstellen und konfigurieren Sie eine Arbeitsauslastungsgruppe der Ressourcenkontrolle, die diesen Pool verwendet.

3. Erstellen Sie eine Klassifizierungsfunktion. Dabei handelt es sich um eine benutzerdefinierte Funktion (User-Defined Function, UDF), deren Rückgabewerte von Resource Governor verwendet wird, um Sitzungen zu klassifizieren, sodass diese an die entsprechende Arbeitsauslastungsgruppe weitergeleitet werden.

4. Registrieren Sie die Klassifizierungsfunktion bei der Ressourcenkontrolle.

5. Wenden Sie die Änderungen auf die Konfiguration im Arbeitsspeicher der Ressourcenkontrolle an.

Hinweis

Informationen zu Ressourcenpools der von Resource Governor, Arbeitsauslastungsgruppen und Klassifizierung finden Sie unter Ressourcenkontrolle.

Die Transact-SQL-Anweisungen für diese Schritte werden unter "So konfigurieren Sie die Ressourcenkontrolle zum Einschränken der CPU-Nutzung" beschrieben, worauf ein Transact-SQL-Beispiel für die Vorgehensweise folgt.

So konfigurieren Sie die Ressourcenkontrolle (SQL Server Management Studio)

• Konfigurieren der Ressourcenkontrolle mit einer Vorlage

• Erstellen eines Ressourcenpools

• Erstellen einer Arbeitsauslastungsgruppe

So konfigurieren Sie die Ressourcenkontrolle zum Einschränken der CPU-Nutzung (Transact-SQL)

1. Geben Sie eine CREATE RESOURCE POOL -Anweisung aus, um einen Ressourcenpool zu erstellen. Im Beispiel für diese Vorgehensweise wird die folgende Syntax verwendet:

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

   Value ist ein ganzzahliger Wert zwischen 1 und 100, der den Prozentsatz der maximalen durchschnittlichen CPU-Bandbreite angibt. Der korrekte Wert hängt von der Umgebung ab. Zur Veranschaulichung wird im Beispiel in diesem Thema der Wert 20 % (MAX_CPU_PERCENT = 20) verwendet.

2. Geben Sie eine CREATE WORKLOAD GROUP -Anweisung aus, um eine Arbeitsauslastungsgruppe für Vorgänge mit niedriger Priorität zu erstellen, deren CPU-Nutzung kontrolliert werden soll. Im Beispiel für diese Vorgehensweise wird die folgende Syntax verwendet:

   CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];
   

3. Geben Sie eine CREATE FUNCTION -Anweisung aus, um eine Klassifizierungsfunktion zu erstellen, die die im vorherigen Schritt erstellte Arbeitsauslastungsgruppe dem Benutzer mit der Anmeldung mit niedriger Priorität zuordnet. Im Beispiel für diese Vorgehensweise wird die folgende Syntax verwendet:

   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;
   

   Informationen zu den Komponenten dieser CREATE FUNCTION-Anweisung finden Sie unter:

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Wichtig

     SUSER_NAME ist nur eine von mehreren Systemfunktionen, die in einer Klassifizierungsfunktion verwendet werden können. Weitere Informationen finden Sie unter Erstellen und Testen einer benutzerdefinierten Klassifizierungsfunktion.

   • SET @local_variable (Transact-SQL).

4. Geben Sie eine ALTER RESOURCE GOVERNOR -Anweisung aus, um die Klassifizierungsfunktion bei der Ressourcenkontrolle zu registrieren. Im Beispiel für diese Vorgehensweise wird die folgende Syntax verwendet:

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
   

5. Geben Sie eine weitere ALTER RESOURCE GOVERNOR-Anweisung aus, um die Änderungen folgendermaßen auf die Konfiguration im Arbeitsspeicher der Ressourcenkontrolle anzuwenden:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Beispiel B: Konfigurieren der Ressourcenkontrolle (Transact-SQL)

Im folgenden Beispiel werden die folgenden Schritte in einer einzelnen Transaktion ausgeführt:

1. Der pMAX_CPU_PERCENT_20 -Ressourcenpool wird erstellt.

2. Die gMAX_CPU_PERCENT_20 -Arbeitsauslastungsgruppe wird erstellt.

3. Die rgclassifier_MAX_CPU() -Klassifizierungsfunktion, die den im vorherigen Beispiel erstellten Benutzernamen verwendet, wird erstellt.

4. Die Klassifizierungsfunktion wird bei der Ressourcenkontrolle registriert.

Nachdem für die Transaktion ein Commit ausgeführt wurde, werden im Beispiel alle Konfigurationsänderungen angewendet, die in der ALTER WORKLOAD GROUP-Anweisung oder der ALTER RESOURCE POOL-Anweisung angefordert wurden.

Wichtig

Im folgenden Beispiel wird der Benutzername des SQL Server-Beispielbenutzers domain_name\MAX_CPU verwendet, der unter "Beispiel A: Einrichten einer Anmeldung und eines Benutzers (Transact-SQL)" erstellt wurde. Ersetzen Sie diesen durch den Namen des Benutzers der Anmeldung, die zum Erstellen komprimierter Sicherungen mit niedriger Priorität verwendet werden soll.

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

[Nach oben]

Überprüfen der Klassifizierung der aktuellen Sitzung (Transact-SQL)

Melden Sie sich optional als der Benutzer an, den Sie in der Klassifizierungsfunktion angegeben haben, und überprüfen Sie die Sitzungsklassifizierung durch Ausgeben der folgenden SELECT -Anweisung im Objekt-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  

Im Ergebnisbereich sollte in der Spalte Name mindestens eine Sitzung für den Namen der Arbeitsauslastungsgruppe aufgeführt werden, den Sie in der Klassifizierungsfunktion angegeben haben.

Hinweis

Informationen zu den dynamischen Verwaltungssichten, die von dieser SELECT-Anweisung aufgerufen werden, finden Sie unter sys.dm_exec_sessions (Transact-SQL) und sys.dm_resource_governor_workload_groups (Transact-SQL).

[Nach oben]

Komprimieren von Sicherungen in einer Sitzung mit CPU-Grenzwert

Melden Sie sich zum Erstellen einer komprimierten Sicherung in einer Sitzung mit maximalem CPU-Grenzwert als der Benutzer an, der in der Klassifizierungsfunktion angegeben ist. Geben Sie in Ihrem Backup-Befehl entweder die Option MIT KOMPRIMIERUNG an (Transact-SQL) oder wählen Sie die Option Backup komprimieren (SQL Server Management Studio). Informationen zum Erstellen einer komprimierten Datenbanksicherung finden Sie unter Erstellen einer vollständigen Datenbanksicherung (SQL Server).

Beispiel C: Erstellen einer komprimierten Sicherung (Transact-SQL)

Im folgenden BACKUP-Beispiel wird eine komprimierte, vollständige Sicherung der AdventureWorks2022-Datenbank in der neu formatierten Sicherungsdatei Z:\SQLServerBackups\AdvWorksData.bak erstellt.

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

[Nach oben]

Weitere Informationen

Erstellen und Testen einer benutzerdefinierten Klassifizierungsfunktion
Ressourcenkontrolle