Szenarien für die Arbeitsauslastungsverwaltung mit der Ressourcenkontrolle

Sie können mit der Ressourcenkontrolle die Arbeitsauslastung in Ihrem SQL Server-System auf verschiedene Weise überwachen und verwalten. In diesem Thema erhalten Sie einen Überblick über die Konfiguration der Ressourcenkontrolle. Außerdem wird gezeigt, wie Sie die Ressourcenkontrolle verwenden können. Die vorgestellten Szenarien enthalten Transact-SQL-Codebeispiele zum Erstellen und Ändern von Arbeitsauslastungsgruppen und Ressourcenpools.

Konfigurieren der Ressourcenkontrolle

Nachdem Sie SQL Server 2008 installiert haben, ist die Ressourcenkontrolle zur Verwendung bereit, aber noch nicht aktiviert. Die interne Arbeitsauslastungsgruppe (Internal) und die Standardarbeitsauslastungsgruppe (Default) sowie die entsprechenden Ressourcenpools sind vorhanden.

Gehen Sie wie folgt vor, um Ihre eigenen Ressourcenpools und Arbeitsauslastungsgruppen zu erstellen und zu verwenden:

• Erstellen Sie einen Ressourcenpool mit den von Ihnen gewünschten Grenzwerten.

• Erstellen Sie eine Arbeitsauslastungsgruppe mit den von Ihnen gewünschten Grenzwerten und Richtlinien, und weisen Sie der Arbeitsauslastungsgruppe einen Ressourcenpool zu.

• Definieren und aktivieren Sie eine Klassifizierungsfunktion, die für eingehende Anforderungen verwendet werden soll.

Nach Abschluss dieser Schritte sehen Sie die aktive Konfiguration für die Ressourcenkontrolle und den Status aller aktiven Anforderungen, die klassifiziert wurden.

Ermitteln der Anforderungen für Ressourcenpool und Arbeitsauslastungsgruppe

Die Herausforderung, die sich Ihnen stellt, ist, die Konfiguration für Arbeitsauslastungsgruppe und Ressourcenpool zu ermitteln, die Sie für Ihre SQL Server-Umgebung verwenden möchten. Hierfür müssen Sie wissen, wie die SQL Server-Ressourcen derzeit von einer Anwendung belegt werden.

Erstellen Sie zu diesem Zweck eine Arbeitsauslastungsgruppe im Standardressourcenpool. Führen Sie die Anwendung mehrere Tage lang aus, und beantworten Sie anhand der dadurch gewonnenen Informationen die folgenden Fragen:

• Welche Voraussetzungen gelten für die CPU?
  Ermitteln Sie die geschätzte CPU-Brandbreite, indem Sie die durchschnittliche CPU-Gesamtnutzung pro Anforderung mit der durchschnittlichen Anzahl von Anforderungen pro Sekunde multiplizieren.

  Anhand der maximalen, durchschnittlichen und standardmäßigen Abweichung bei der CPU-Nutzung können Sie ermitteln, ob ein maximaler CPU-Grenzwert erforderlich ist.

• Welche Anzahl gleichzeitiger Anforderungen liegt vor?
  Verwenden Sie die Statistiken für die maximale und die durchschnittliche Anzahl gleichzeitiger Anforderungen, um den minimalen Parallelitätsfaktor zu ermitteln.

• Wie viel Gesamtarbeitsspeicher wird benötigt?
  Ermitteln Sie anhand der Werte für die maximale und die durchschnittliche Arbeitsspeicherbelegung die Anforderungen an den Arbeitsspeicher.

• Wie viel Arbeitsspeicher wird für eine einzelne Abfrage benötigt?
  Ermitteln Sie anhand der Statistiken für den maximalen und den durchschnittlichen Arbeitsspeicher pro Abfrage, wie viel Arbeitsspeicher benötigt wird.

• Werden Abfragen mangels Arbeitsspeicher nicht ausgeführt?
  Ermitteln Sie anhand der Warteschlangenstatistik für die durchschnittliche Wartezeit auf Arbeitsspeicher, ob Abfragen aufgrund nicht verfügbaren Speicherplatzes blockiert werden.

Szenarien

Im Folgenden finden Sie sieben Beispielszenarien. Die Szenarien 1 bis 5 bauen aufeinander auf. Szenario 6 ist unabhängig von den vorangehenden Szenarien, Szenario 7 baut jedoch wiederum auf Szenario 6 auf.

Szenario 1

F: Ich habe gerade eine neue Version von SQL Server installiert und möchte die Ressourcenkontrolle verwenden. Wie kann ich die Ressourcenkontrolle in meiner Umgebung verwenden?

A: Ziehen Sie die Ressourcenkontrolle in Erwägung, um zu überwachen, wie viele Ressourcen von den Arbeitsauslastungen belegt werden. Führen Sie die folgenden Schritte durch, um eine Umgebung mit Ressourcenkontrolle einzurichten. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Erstellen Sie Arbeitsauslastungsgruppen für Ihre Arbeitsauslastungen.

2. Erstellen Sie eine Klassifizierungsfunktion.

3. Registrieren Sie die Klassifizierungsfunktion bei der Ressourcenkontrolle.

4. Aktivieren Sie die Ressourcenkontrolle.

5. Überwachen Sie die Leistungsindikatoren der Ressourcenkontrolle, und fragen Sie die dynamischen Verwaltungssichten ab, die Ihnen Informationen zum Ressourcenverbrauch für eine Arbeitsauslastungsgruppe liefern.

Beispiel

Hinweis
In der folgenden Konfiguration wird kein Ressourcenpool zur Verwendung durch die Arbeitsauslastungsgruppe angegeben. Daher verwenden die Arbeitsauslastungsgruppen standardmäßig den Standardpool.

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Szenario 2

F: Basierend auf den Überwachungsergebnissen aus Szenario 1 möchte ich, dass jedes Mal, wenn eine Abfrage in der Ad-Hoc-Gruppe (GroupAdhoc) länger als 30 Sekunden läuft, ein Ereignis ausgelöst wird.

A: Führen Sie die folgenden Schritte durch, um die aktuelle Konfiguration der Ressourcenkontrolle zu ändern. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Legen Sie für die Ad-Hoc-Gruppe einen Grenzwert für die CPU-Verwendung fest.

2. Überwachen Sie SQL-Ablaufverfolgungsereignisse (Ereignis für die Ressourcenkontrollen-Verwaltungsklasse).

3. Führen Sie eine Aktion für das Ereignis aus. Sie können zum Beispiel veranlassen, dass das Ereignis ignoriert, eine E-Mail-Nachricht oder Seite gesendet oder der KILL-Befehl für die Anforderung ausgeführt wird.

Beispiel

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Szenario 3

F: Ich möchte die Ad-Hoc-Gruppe weiter einschränken, sodass sie nicht mehr als 50 Prozent an CPU mit allen kumulierten Anforderungen belegt.

A: Da in den vorherigen Szenarien der Standardpool verwendet wird, müssen Sie einen neuen Ressourcenpool erstellen. Führen Sie die folgenden Schritte durch, um die aktuelle Konfiguration der Ressourcenkontrolle zu ändern. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Erstellen Sie einen neuen Ressourcenpool, und legen Sie die CPU-Grenzwerte fest.

2. Konfigurieren Sie die Ad-Hoc-Arbeitsauslastungsgruppe so, dass sie den neuen Ressourcenpool verwendet.

Beispiel

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Szenario 4

F: Jetzt möchte ich sicherstellen, dass der Admin-Gruppe immer Ressourcen zum Ausführen von Diagnoseabfragen zur Verfügung stehen, diese Abfragen sollten jedoch maximal zehn Prozent der Arbeitsspeicherressourcen auf dem Server belegen.

A: Zu diesem Zweck müssen Sie einen neuen Ressourcenpool erstellen. Führen Sie die folgenden Schritte durch, um die aktuelle Konfiguration der Ressourcenkontrolle zu ändern. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Erstellen Sie einen neuen Ressourcenpool, und legen Sie Grenzwerte für die Ressourcen fest.

2. Konfigurieren Sie die Admin-Arbeitsauslastungsgruppe so, dass sie den neuen Pool verwendet.

Beispiel

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Szenario 5

F: Da sich meine Berichte derzeit im Standardressourcenpool befinden, jedoch eine gesonderte Arbeitsauslastungsgruppe (GroupReports) verwenden, möchte ich sicherstellen, dass die Berichte in der Standardressourcengruppe die höchste Priorität für die Ressourcenbelegung erhalten.

A: Für diesen Zweck müssen Sie die IMPORTANCE-Einstellung in der Standardgruppe konfigurieren.

Hinweis
Sie können die Einstellungen für die Standardgruppe, nicht jedoch für den Standardpool ändern. Falls Sie glauben, dass für den Standardpool eine Änderung erforderlich ist, müssen Sie sehr wahrscheinlich einen neuen Ressourcenpool erstellen.

Führen Sie den folgenden Schritt durch, um die aktuelle Konfiguration der Ressourcenkontrolle zu ändern. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Ändern Sie die Einstellung für die Standardgruppe (Default).

Beispiel

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Szenario 6

F: Ich möchte eine neue Arbeitsauslastungsgruppe erstellen, die einen bestehenden Ressourcenpool verwendet. Dann möchte ich die Klassifizierungsfunktion so ändern, dass die Gruppe in einen neuen Ressourcenpool verschoben wird.

A: Führen Sie die folgenden Schritte durch, um diese neue Umgebung für die Ressourcenkontrolle einzurichten. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Erstellen Sie einen neuen Ressourcenpool mit den Standardeinstellungen.

2. Erstellen Sie eine neue Arbeitsauslastungsgruppe, die sich in einem bestehenden Pool befindet.

3. Erstellen und registrieren Sie eine neue Klassifizierungsfunktion zur Verarbeitung von Anforderungen.

Beispiel

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Szenario 7

F: Ich habe beschlossen, dass eine Arbeitsauslastungsgruppe (die in Szenario 6 erstellte) überflüssig geworden ist, daher möchte ich die Arbeitsauslastungsgruppe und den zugehörigen Ressourcenpool löschen.

A: Führen Sie die folgenden Schritte durch, um die Umgebung für die Ressourcenkontrolle zu ändern. Anschließend finden Sie ein Konfigurationsbeispiel.

1. Erstellen und registrieren Sie eine neue Klassifizierungsfunktion, die die Anforderungen auf die übrigen Arbeitsauslastungsgruppen verteilt.

2. Löschen Sie die Arbeitsauslastungsgruppe.

3. Löschen Sie den Ressourcenpool.

4. Übernehmen Sie die Konfigurationsänderungen.

Beispiel

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Siehe auch

Verweis

Katalogsichten der Ressourcenkontrolle (Transact-SQL)

Dynamische Verwaltungssichten in Verbindung mit der Ressourcenkontrolle (Transact-SQL)

Konzepte

Konzepte der Ressourcenkontrolle

DDL-Befehle und Systemsichten in der Ressourcenkontrolle

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