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

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 ist es u.U. sinnvoll, in einer Sitzung, bei der die CPU-Nutzung durch denResource Governor eingeschränkt ist, eine komprimierte Sicherung mit niedriger Priorität zu 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 der Ressourcenkontrolle zugeordnet werden, die die CPU-Nutzung in solchen Fällen einschränkt.

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.

Das folgende Verfahren beschreibt die Schritte zum Einrichten einer Anmeldung und eines Benutzers für diesen Zweck, gefolgt von einem 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 Database Principal Permissions (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 Database Principal Permissions (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.

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

Konfigurieren der Ressourcenkontrolle zum Einschränken der CPU-Nutzung

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.

Die Transact-SQL-Anweisungen für diese Schritte werden in der Prozedur "So konfigurieren Sie Resource Governor zur Begrenzung der CPU-Auslastung" beschrieben, gefolgt von einem Transact-SQL-Beispiel der Prozedur.

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

   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 verwendet das Beispiel in diesem Thema 20 % Prozent (MAX_CPU_PERCENT = 20).)

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

   DEKLARIEREN SIE @workload_group_name ALS sysname

   IF (SUSER_NAME() = 'user_of_low_priority_login')

   SET @workload_group_name = 'workload_group_name'

   RÜCKGABEWERT @workload_group_name

   ENDE

   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 des Resource Governor (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.

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

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

[Top]

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 im Sicherungsbefehl WITH COMPRESSION (Transact-SQL) an, oder wählen Sie Sicherung komprimieren (SQL Server Management Studio) aus. 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 AdventureWorks2012 -Datenbank in der neu formatierten Sicherungsdatei Z:\SQLServerBackups\AdvWorksData.bakerstellt.

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

Weitere Informationen

Erstellen und Testen einer benutzerdefinierten Klassifizierungsfunktion
Ressourcenkontrolle