Freigeben über

  • Artikel

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

Standardmäßig steigt die CPU-Nutzung durch die Sicherung unter Verwendung der Komprimierung erheblich, und die bei der Komprimierung zusätzlich verbrauchten CPU-Ressourcen können sich negativ auf gleichzeitige 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.

Wichtiger HinweisWichtig

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 Überlegungen zum Schreiben einer Klassifizierungsfunktion und Szenarien für die Arbeitsauslastungsverwaltung mit 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

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

    Weitere Informationen finden Sie unter Berechtigungen der festen Serverrollen (Datenbankmodul).

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

    So erstellen Sie einen Benutzer

  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

    Weitere Informationen finden Sie unter Berechtigungen der festen Datenbankrollen (Datenbankmodul).

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.

Wichtiger HinweisWichtig

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, und dieser wird der festen Datenbankrolle db_backupoperator für die AdventureWorks2008R2-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 AdventureWorks2008R2 for this login
USE AdventureWorks2008R2;
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

HinweisHinweis

Stellen Sie sicher, dass die Ressourcenkontrolle aktiviert ist. Weitere Informationen finden Sie unter Vorgehensweise: Aktivieren oder Deaktivieren der Ressourcenkontrolle (SQL Server Management Studio).

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.

HinweisHinweis

Informationen zu Ressourcenpools der Ressourcenkontrolle, Arbeitsauslastungsgruppen und Klassifizierung finden Sie unter Konzepte der 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)

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:

  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.

Wichtiger HinweisWichtig

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

HinweisHinweis

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.

So erstellen Sie eine komprimierte Datenbanksicherung

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

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

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

[Nach oben]