Problembehandlung bei der Ressourcenkontrolle

Wenn für die Klassifizierung keine benutzerdefinierte Klassifizierungsfunktion verfügbar ist, werden alle Sitzungen automatisch der standardmäßigen Arbeitsauslastungsgruppe zugeordnet. Nachdem Sie eine benutzerdefinierte Klassifizierungsfunktion erstellt haben, müssen Sie sicherstellen, dass diese in der Ressourcenkontrolle registriert ist und dass die Konfiguration im Arbeitsspeicher aktualisiert wird.

Das Erstellen, Registrieren und Aktivieren einer benutzerdefinierten Klassifizierungsfunktion erfolgt in drei Schritten:

• Im ersten Schritt erstellen Sie die Funktion.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• Im zweiten Schritt registrieren Sie die Funktion in der Ressourcenkontrolle.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• Im dritten Schritt aktualisieren Sie die Konfiguration im Arbeitsspeicher der Ressourcenkontrolle.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

Bei der Problembehandlung von Klassifizierungen sollten Sie zunächst überprüfen, ob die erstellte Funktion in der Ressourcenkontrolle registriert ist und ob die Konfiguration aktualisiert wurde. Rufen Sie mithilfe der folgenden Abfrage den Namen des Schemas (schema_name) und der Klassifizierungsfunktion (function_name) für die derzeit von der Ressourcenkontrolle verwendete benutzerdefinierte Klassifizierungsfunktion ab.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

Die zuvor genannte Methode eignet sich zur Problembehandlung in Szenarios, in denen Sie die benutzerdefinierte Klassifizierungsfunktion geändert haben, die Ressourcenkontrolle jedoch noch die vorhergehende Funktionslogik zum Klassifizieren von Sitzungen verwendet. Dieses Verhalten deutet darauf hin, dass die von Ihnen vorgenommenen Änderungen nicht auf die Konfiguration im Arbeitsspeicher angewendet wurden.

Sie können komplexe Klassifizierungsfunktionen erstellen, die entweder nicht die erwarteten Ergebnisse liefern oder ressourcenintensiv sind. Nachdem Sie die Schritte der grundlegenden Problembehandlung durchgeführt haben, müssen Sie die Leistungsfähigkeit der Funktionslogik überprüfen. Im schlimmsten Fall führt der Codierungsfehler zu einer Endlosschleife oder Endlosabfrage.

Sie können für die Problembehandlung bei einer fehlerhaft geschriebenen Klassifizierungsfunktion eine dedizierte Administratorverbindung (Dedicated Administrator Connection, DAC) verwenden, da eine DAC der Klassifizierung nicht unterliegt und verwendet werden kann, während die Ressourcenkontrolle ausgeführt wird und eingehende Sitzungen klassifiziert. Weitere Informationen finden Sie unter Verwenden einer dedizierten Administratorverbindung.

Hinweis
Wenn keine DAC für die Problembehandlung verfügbar ist, können Sie das System im Einzelbenutzermodus neu starten. Obwohl der Einzelbenutzermodus nicht der Klassifizierung unterliegt, können Sie die Klassifizierung der Ressourcenkontrolle während der Ausführung nicht diagnostizieren.

Rufen Sie Informationen zur Klassifizierungsfunktion mithilfe der folgenden Abfrage ab:

• sys.dm_exec_query_stats. (enthält die Anweisungsinformationen, jedoch nicht die Funktion selbst)

• sys.dm_exec_sql_text (verwenden Sie dies zusammen mit sql_handle aus sys.dm_exec_query_stats)

• PreConnect:Starting-Ereignisklasse (gibt ID und Name der Klassifizierungsfunktion an)

In diesem Szenario berichtet ein Benutzer von Leistungseinbußen. Sie vermuten, dass die Anzahl von Anforderungen eingeschränkt ist.

Ermitteln Sie zunächst, ob die Einschränkung der Anforderungsanzahl für die Gruppe des Benutzers explizit konfiguriert wurde. Überprüfen Sie hierzu die Gruppenmitgliedschaft des Benutzers, und stellen Sie fest, ob die GROUP_MAX_REQUESTS-Einstellung aktiviert ist. Wenn GROUP_MAX_REQUESTS nicht aktiviert ist, besteht keine explizite Einschränkung der Anforderungsanzahl. In diesem Fall sollten Sie die Ursache mithilfe der folgenden Schritte analysieren.

• Fragen Sie sys.dm_os_waiting_tasks ab, um zu ermitteln, ob Anforderungen im RESMGR_THROTTLED-Wartetyp warten. Wenn dieser Wartetyp vorhanden ist, deutet dies auf eine Einschränkung der Anforderungsanzahl hin.

• Starten Sie den Systemmonitor, und erfassen Sie Daten mithilfe der Indikatoren Anforderungen in der Warteschlange und Aktive Anforderungen. Wenn die Anzahl von Anforderungen in der Warteschlange ungleich Null ist, deutet dies auf eine Einschränkung der Anforderungsanzahl hin.

• Überprüfen Sie, ob der Wert für Aktive Anforderungen mit der GROUP_MAX_REQUESTS-Einstellung übereinstimmt. Wenn der Wert für Aktive Anforderungen höher ist als die GROUP_MAX_REQUESTS-Einstellung, enthält die Gruppe möglicherweise Anforderungen, die nicht eingeschränkt werden können (z. B. offene Transaktionen).

• Wenn der Wert für Anforderungen in der Warteschlange Null beträgt, sollten Sie den Wert für Aktive Anforderungen von allen Arbeitsauslastungsgruppen überprüfen, die den gleichen Ressourcenpool verwenden. Möglicherweise ist der Pool aufgrund zu vieler Anforderungen überlastet.

Wenn Sie eine Richtlinie verwenden, die durch die Generierung von Ereignissen in der Ressourcenkontrolle gesteuert wird, können Sie das beim Erreichen des maximalen CPU-Grenzwerts generierte Ereignis nutzen.

In diesem Szenario ermitteln Sie, ob der maximale CPU-Grenzwert (REQUEST_MAX_CPU_TIME_SEC) zu niedrig ist, den Sie zum Erkennen von Abfragen mit zu hoher CPU-Auslastung konfiguriert haben.

Die folgenden Aktionen helfen Ihnen, die CPU-Grenzwerteinstellung zu überprüfen.

• Starten Sie eine SQL-Ablaufverfolgungssitzung, und erfassen Sie das Ereignis CPU Threshold Exceeded. Wenn eine Benutzeranforderung den maximalen CPU-Nutzungsgrenzwert erreicht, generiert der Server automatisch ein SQL-Ablaufverfolgungsereignis. Wenn die Einstellung zu niedrig ist, wird eine große Anzahl dieser Ereignisse generiert.

Hinweis
Dieses Ereignis wird auch als Serverereignisbenachrichtigung bereitgestellt. Sie können somit ein Skript schreiben, das auf dieses Ereignis reagiert.

• Starten Sie den Systemmonitor, und erfassen Sie Daten mithilfe des Indikators Maximale CPU-Zeit für Anforderungen (ms). Sie können den Wert dieses Indikators auch als Anhaltspunkt verwenden, um einen geeigneten Grenzwert für die Arbeitsauslastungsgruppe einzustellen.

In diesem Szenario vermuten Sie eine Einschränkung der CPU-Bandbreite, da der Leistungsindikator CPU-Verwendung in % den gleichen oder nahezu den gleichen Wert wie die MAX_CPU_PERCENT-Einstellung der Ressourcenkontrolle aufweist. Die folgende Abfrage gibt den Wert der CPU-Verwendung in Prozent für alle Arbeitsauslastungsgruppen und Ressourcenpools einer SQL Server-Instanz zurück.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

Weitere Informationen finden Sie unter sys.dm_os_performance_counters (Transact-SQL).

Sie ermitteln, ob die CPU-Bandbreite eingeschränkt ist, indem Sie in Ihrem System die folgenden Überprüfungen durchführen.

• Überprüfen Sie die CPU-Gesamtauslastung des Servers. Wenn eine andere Last als SQL Server aktiv ist, kann sich dies auf die Abfrage auswirken, für die Sie die Problembehandlung ausführen.

• Überprüfen Sie die Verteilung der CPU-Verwendung unter den Ressourcenpools. Ein Ressourcenpool kann eingeschränkt sein, wenn in einem anderen Pool ein hoher Mindestwert für die CPU-Verwendung konfiguriert ist. Vergleichen Sie den Indikator für die erwartete (berechnete) CPU-Auslastung mit dem für die tatsächliche CPU-Auslastung.

• Überprüfen Sie die dem betreffenden Ressourcenpool zugewiesenen Arbeitsauslastungsgruppen. Last von anderen Arbeitsauslastungsgruppen kann für Benutzer, die den gleichen Pool verwenden, Auswirkungen haben.

• Überprüfen Sie die Verteilung der CPU-Verwendung unter den Zeitplanungsmodulen. Die analysierte Abfrage wurde möglicherweise einem Zeitplanungsmodul zugeordnet, das Abfragen mit langer Ausführungszeit enthält. In diesem Fall kann es den Anschein erwecken, als wäre die Abfrage eingeschränkt. Das eigentliche Problem liegt jedoch in einer ungleichmäßigen Verteilung der Last unter den Zeitplanungsmodulen.

• Überprüfen Sie, ob die Arbeitsauslastung möglicherweise nicht durch die Einstellungen der Ressourcenkontrolle eingeschränkt, sondern durch andere Sitzungen blockiert wurde.

• Ermitteln Sie, wie viele Sitzungen aktuell Abfragen in dem System ausführen. Wenn viele Anforderungen gleichzeitig ausgeführt werden, versucht SQL Server, allen Anforderungen einen gewissen Anteil an der CPU-Zeit zuzuweisen. So soll sichergestellt werden, dass weiterhin auf die CPU zugegriffen werden kann.

In diesem Szenario vermuten Sie, dass eine Abfrage aufgrund der Größe des zugewiesenen Arbeitsspeichers zu langsam ausgeführt wird.

Die Ressourcenkontrolle erzwingt den maximalen Arbeitsspeichergrenzwert für Abfragen, indem die Arbeitsspeicherzuweisung reduziert wird. So erfüllen auch umfangreiche Abfragen den Grenzwert. Wenn eine Abfrage weniger als 100 % der Arbeitsspeicherzuweisung erhält, müssen die temporären Daten ggf. ausgelagert ("Überlauf") und auf einen Datenträger geschrieben werden. Dies wirkt sich spürbar auf die Leistung aus.

Sie müssen ermitteln, wie groß der Anteil umfangreicher Abfragen ist, um einen geeigneten Grenzwert für die maximale Abfragegröße festzulegen. Die folgenden Aktionen helfen Ihnen, die besten Einstellungen zu bestimmen:

• Fragen Sie sys.dm_exec_query_memory_grants ab, um den aktuellen Status der Arbeitsspeicherzuweisungen zu ermitteln. In der ideal_memory_kb-Spalte wird die ideale Menge auf Grundlage der Kardinalitätsschätzung angezeigt. Die requested_memory_kb-Spalte enthält die angefragte Menge. Diese wurde ggf. nach dem Erreichen des maximalen Abfragegrenzwerts reduziert. Wenn der Wert für requested_memory_kb deutlich niedriger ist als für ideal_memory_kb, kann es zu einem Überlauf der Abfrage kommen (vorausgesetzt die Kardinalitätsschätzung ist korrekt).

• Starten Sie den Systemmonitor, und erfassen Sie Daten mithilfe des Indikators Reduzierte Arbeitsspeicherzuweisungen/Sekunde. Der Wert dieses Indikators gibt die Anzahl der Arbeitsspeicherzuweisungen an, die weniger als die ideale Menge erhalten haben, nachdem der Grenzwert für die maximale Anforderungsgröße erreicht wurde. Große Abfragen werden deutlich langsamer ausgeführt als Abfragen mit der idealen Menge, da für sie ein Überlauf auf den Datenträger erfolgen muss, damit die Arbeitsspeichergrenzwerte eingehalten werden.

Zum Beheben eines Problems bei der Arbeitsspeicherzuweisung müssen Sie ggf. den Grenzwert für die Poolgröße oder den Grenzwert für die maximale Arbeitsspeichergröße erhöhen.

Hinweis
Wenn Sie nur die maximale Arbeitsspeichergröße erhöhen, können möglicherweise weniger große Abfragen gleichzeitig ausgeführt werden.

In diesem Szenario führt eine Abfrage aufgrund eines Timeoutfehlers bei der Arbeitsspeicherzuweisung zu einem Fehler.

Sowohl die Gesamtzahl der aktiven Anforderungen von Arbeitsspeicherzuweisungen als auch die Arbeitsspeichergrenzwerte, die in der Arbeitsauslastungsgruppe und im Ressourcenpool definiert sind, können zu einem Timeout der Arbeitsspeicherzuweisung führen. Wenn ein Ressourcenpool von mehreren Ressourcengruppen gemeinsam verwendet wird, kann sich die Anzahl gleichzeitiger Abfragen in anderen Gruppen ebenfalls auf die Timeouts bei der Arbeitsspeicherzuweisung auswirken.

Die folgenden Aktionen helfen Ihnen, die optimalen Einstellungen für den Ressourcenpool zu bestimmen:

• Fragen Sie sys.dm_exec_query_memory_grants ab, um die Anzahl von Arbeitsspeicherzuweisungen und Abfragen in der Warteschlange für diese Gruppe und diesen Pool zu ermitteln.

• Fragen Sie sys.dm_exec_query_resource_semaphores ab, um den insgesamt zugewiesenen Arbeitsspeicher und die Zielwerte anzuzeigen.

Wenn die zugewiesene Arbeitsspeicherauslastung größer als der verfügbare Arbeitsspeicher ist, können Sie den Grenzwert für die Ressourcenpoolgröße erhöhen.

Eine Abfrage schlägt mit einem Fehler aufgrund von nicht genügend Arbeitsspeicher fehl.

Grundlegende Problembehandlung

Erweiterte Problembehandlung

Der Fehler Nicht genügend Arbeitsspeicher (701) ist ein allgemeiner Fehler. Er wird zurückgegeben, wenn Tasks versuchen, einen Speicherblock aus dem Speicher-Manager zuzuordnen und dieser Versuch nicht erfolgreich ist. Weitere Informationen finden Sie unter MSSQLSERVER_701.

Dieser Fehler wird durch die folgenden Bedingungen verursacht:

• Der Speicherpool hat den Gesamtgrenzwert erreicht.

Hinweis
Die Ressourcenkontrolle ist möglicherweise nicht die einzige Ursache dieser Bedingung. Auf dem Server werden ggf. weitere Anwendungen mit Speicherbedarf ausgeführt, die ebenfalls zu dieser Bedingung beitragen.

• Die Zuweisung von mehreren Seiten oder einem virtuellen Adressraum führt zu einem Fehler, da der virtuelle Adressraum über keinen freien Block verfügt, der für die erforderliche Reservierung groß genug ist. Dieser Fehler tritt vor allem in 32-Bit-Architekturen auf. In 64-Bit-Architekturen ist er eher unwahrscheinlich.

• Die Zuweisung von mehreren Seiten oder einem virtuellen Adressraum führt zu einem Fehler, da die Gesamtzahl der Commits die Commitgrenze erreicht hat. Dies gilt sowohl für 32-Bit- als auch für 64-Bit-Architekturen.

Wenn eine Fehlermeldung aufgrund von unzureichendem Arbeitsspeicher angezeigt wird, sollten Sie bei der Fehleranalyse mit dem Fehlerprotokoll beginnen. Das Protokoll enthält ähnliche Informationen wie im folgenden Beispiel:

2006-01-28 04:27:15.43 spid51 Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Folgende Fehler können im Fehlerprotokoll aufgezeichnet werden:

• FAIL_PAGE_ALLOCATION, gefolgt von der Anzahl von Seiten, die zugeordnet werden sollten

• FAIL_VIRTUAL_RESERVE, gefolgt von der Anzahl an Byte, die reserviert werden sollten

• FAIL_VIRTUAL_COMMIT, gefolgt von der Anzahl an Byte, für die ein Commit durchgeführt werden sollte

Beachten Sie, dass der Task, der den Fehler wegen unzureichendem Arbeitsspeicher ausgelöst hat, häufig nicht die Ursache des Fehlers ist. Sofern kein Endlostask vorliegt, ist der Fehler wegen unzureichendem Arbeitsspeicher häufig das Ergebnis mehrerer ausgeführter Tasks. Sie müssen daher bei der Fehleranalyse die Systemaktivität im Allgemeinen berücksichtigen, z. B. bei dem häufig auftretenden FAIL_PAGE_ALLOCATION-Fehler.

Eine weitere wichtige Information im Fehlerprotokoll sind die Angaben zum Arbeitsspeicherstatus. Je nach Fehler sollten Sie die Zahlen für einzelne Seiten, mehrere Seiten, virtuell reservierten Speicherplatz oder per Commit übergebenen Speicherplatz für individuelle Speicherclerks beachten. Die Identifizierung der wichtigsten Arbeitsspeicherconsumer ist ein wichtiger Schritt bei der Analyse der Fehlerursache. Die größten Arbeitsspeicherconsumer sind normalerweise die Folgenden:

• MEMORYCLERK_ * bedeutet, dass die Serverkonfiguration oder die Arbeitsauslastung eine bestimmte Arbeitsspeicherbelegung erfordert. Es sind entsprechende Arbeitsspeicherclerks für SQL Server-Komponenten vorhanden, und einzelne Komponenten können mehrere Arbeitsspeicherclerks aufweisen. Weitere Informationen finden Sie unter sys.dm_os_memory_clerks (Transact-SQL). Sie können die Arbeitsauslastung, die das Problem verursacht hat, teilweise anhand der Arbeitsspeicherclerks identifizieren. Zumeist müssen Sie jedoch die den Arbeitsspeicherclerks zugeordneten Arbeitsspeicherobjekte analysieren, um die Ursache der hohen Arbeitsspeicherauslastung zu ermitteln.

• CACHESTORE_*, USERSTORE_*, OBJECTSTORE_* sind die Typen von Caches. Eine hohe Arbeitsspeicherauslastung durch einen Cache kann folgende Ursachen haben:

  • Arbeitsspeicher wird aus dem Cache zugewiesen, ist jedoch noch nicht als Eintrag eingefügt, der entfernt werden kann. Dies ist mit dem MEMORYCLERK-Fall weiter oben vergleichbar.

  • Alle Cacheeinträge sind in Gebrauch, sodass sie nicht entfernt werden können. Sie können dies überprüfen, indem Sie den Wert für sys.dm_os_memory_cache_counters anzeigen und die Werte in der Spalte entries_count mit denen in der Spalte entries_in_use_count vergleichen.

• MEMORYCLERK_SQLQERESERVATIONS zeigt an, wie viel Arbeitsspeicher von der Abfrageausführung (Query Execution, QE) reserviert wurde, um Abfragen mit Sortierungen/Verknüpfungen auszuführen. Wenn bei einer großen Anzahl von Reservierungen ein Fehler wegen unzureichendem Arbeitsspeicher ausgelöst wird, deutet dies zumeist auf einen Serverfehler hin.

Die Arbeitsspeicherstatusausgabe im Fehlerprotokoll zeigt zudem an, welcher Speicherpool verbraucht ist. Die Speicherbroker für jeden Pool geben die Speicherverteilung zwischen entnommenem (Kompilierung), zwischengespeichertem und reserviertem (zugewiesenem) Arbeitsspeicher an. Die Zahlen für die drei Broker entsprechen den vorausgehenden Arbeitsspeicherobjekten, die den Arbeitsspeicherclerks zugeordnet sind. Sie ermitteln, wie viel Arbeitsspeicher einem Pool von einem bestimmten Clerk oder Arbeitsspeicherobjekt zugewiesen ist, indem Sie die Informationen mithilfe eines benutzerdefinierten Skripts aus einem vollständigen Dump extrahieren. Die dynamische Verwaltungssicht sys.dm_os_memory_cache_entries zeigt die Pool-ID an, die jedem Eintrag zugeordnet ist.

Wenn Sie Microsoft Support Services in Anspruch nehmen möchten, halten Sie bitte die folgenden Informationen für unsere Supportmitarbeiter bereit:

• Das Fehlerprotokoll, in dem der Fehler wegen unzureichendem Arbeitsspeicher und der Arbeitsspeicherstatus zum Zeitpunkt des Fehlers aufgezeichnet wurden.

• Die Ausgabe der folgenden Anweisungen:

  dbcc memorystatus
  dbcc sqlperf(spinlockstats)
  select * from sys.dm_os_memory_clerks
  select * from sys.dm_os_wait_stats order by wait_type
  select * from sys.dm_os_waiting_tasks
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
  select * from sys.dm_os_memory_cache_clock_hands
  

• Ein mit T8004 erfasster Dump für den unzureichenden Arbeitsspeicher (optional). Dieser Minidump enthält wichtige Informationen, z. B. die Ringpuffer und den Spinlock-/Wartestatus. Der Dumpindikator für T8004 kann ohne Neustart des Servers zurückgesetzt werden, indem Sie T8004 deaktivieren und wieder aktivieren.

In diesem Szenario vermuten Sie, dass eine Abfrage aufgrund eines suboptimalen Abfrageplans zu langsam ausgeführt wird. Wenn dem Abfrageoptimierer aufgrund einer zu niedrigen Grenzwerteinstellung für einen Ressourcenpool nicht genügend Arbeitsspeicher zur Verfügung steht, generiert er ggf. einen suboptimalen Abfrageplan.

Die folgenden Aktionen helfen Ihnen, die optimalen Einstellungen für den Ressourcenpool zu bestimmen:

• Rufen Sie Daten aus dem Indikator Abfrageoptimierungen/Sekunde ab, und ermitteln Sie, ob die Arbeitsauslastungsgruppe eine hohe Anzahl von Abfragekompilierungen aufweist.

• Rufen Sie Daten aus dem Indikator Suboptimale Pläne/Sekunde ab, und ermitteln Sie, ob der Abfrageoptimierer häufig suboptimale Pläne erzeugt.

Wenn eine der zuvor genannten Bedingungen gegeben ist, sollten Sie den Arbeitsspeichergrenzwert des Ressourcenpools erhöhen.