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

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 Klassifizierungsfunktion der Ressourcenkontrolle und Arbeitsauslastungsgruppe der Ressourcenkontrolle.

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. Um für eine Anmeldung Sitzungen und dem Benutzer das Sichern einer bestimmten Datenbank zu ermöglichen, fügen Sie den Benutzer zur db_backupoperator-Datenbankrolle dieser Datenbank hinzu. 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 dann 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 dann ein Benutzer für domain_name\MAX_CPU erstellt. 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

[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 die Ressourcenkontrolle verwendet, 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 Ressourcenkontrolle, 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 = 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 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 Beispiel-SQL Server-Benutzers verwendet, der unter "Beispiel A: Einrichten einer Anmeldung und eines Benutzers (Transact-SQL)" erstellt wurde (domain_name\MAX_CPU). 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.
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

[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 name-Spalte 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 im Sicherungsbefehl entweder 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.bak erstellt.

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

[Nach oben]

Siehe auch

Aufgaben

Erstellen und Testen einer benutzerdefinierten Klassifizierungsfunktion

Konzepte

Ressourcenkontrolle