sp_server_diagnostics (Transact-SQL)

Artikel
03/11/2014

Erfasst Diagnosedaten und Zustandsinformationen zu SQL Server, um potenzielle Fehler zu erkennen. Die Prozedur wird im Wiederholungsmodus ausgeführt und sendet regelmäßig Ergebnisse. Sie kann über eine reguläre oder eine DAC-Verbindung aufgerufen werden.

Gilt für: SQL Server (SQL Server 2012 bis aktuelle Version).

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

Argumente

[ @repeat_interval =] 'repeat_interval_in_seconds'
Gibt das Zeitintervall an, in dem die gespeicherte Prozedur ausgeführt wird, um erneut Zustandsinformationen zu senden.

repeat_interval_in_seconds ist vom Datentyp int. Der Standardwert ist 0. Die gültigen Parameterwerte sind 0 sowie alle Werte größer oder gleich 5. Die gespeicherte Prozedur muss mindestens 5 Sekunden lang ausgeführt werden, um vollständige Daten zurückzugeben. Der minimale Wert für die Ausführung der gespeicherten Prozedur im Wiederholungsmodus beträgt 5 Sekunden.

Wenn dieser Parameter nicht angegeben ist oder der angegebene Wert 0 beträgt, gibt die gespeicherte Prozedur einmal Daten zurück und wird dann beendet.

Wenn der angegebene Wert kleiner als der minimale Wert ist, wird ein Fehler ausgelöst und kein Wert zurückgegeben.

Wenn der angegebene Wert größer oder gleich 5 ist, wird die gespeicherte Prozedur wiederholt ausgeführt, um den Zustand zurückzugeben, bis sie manuell abgebrochen wird.

Rückgabecodewerte

0 (Erfolg) oder 1 (Fehler)

Resultsets

sp_server_diagnostics gibt die folgenden Informationen zurück:

Spalte	Datentyp	Beschreibung
creation_time	datetime	Gibt den Zeitstempel der Zeilenerstellung an. Jede Zeile in einem einzelnen Rowset weist denselben Zeitstempel auf.
component_type	sysname	Gibt an, ob die Zeile Informationen für die Komponente der SQL Server-Instanzebene oder für eine AlwaysOn-Verfügbarkeitsgruppe enthält: Instanz alwaysOn:AvailabilityGroup
component_name	sysname	Gibt den Namen der Komponente oder den Namen der Verfügbarkeitsgruppe an: system resource query_processing io_subsystem Ereignisse <name of the availability group>
state	int	Gibt den Integritätsstatus der Komponente an: 0 1 2 3
state_desc	sysname	Beschreibt die Zustandsspalte. Folgende Beschreibungen entsprechen den Werten in der Statusspalte: 0:Unknown 1:clean 2:warning 3:error
Daten	varchar (max)	Gibt Daten an, die für die Komponente spezifisch sind.

Im Folgenden finden Sie die Beschreibungen der fünf Komponenten:

system: Erfasst aus Systemperspektive Daten zu Spinlocks, Verarbeitungsbedingungen mit hohem Schweregrad, Tasks ohne Ergebnis, Seitenfehlern und CPU-Verwendung. Diese Informationen ergeben eine allgemeine Empfehlung zum Integritätsstatus.
resource: Erfasst aus Ressourcenperspektive Daten zum physischen und virtuellen Arbeitsspeicher, zu Pufferpools, Seiten, zum Cache und zu anderen Arbeitsspeicherobjekten. Diese Informationen ergeben eine allgemeine Empfehlung zum Integritätsstatus.
query_processing: Erfasst aus Abfrageverarbeitungsperspektive Daten zu Arbeitsthreads, Tasks, Wartetypen, CPU-intensiven Sitzungen und blockierenden Tasks. Diese Informationen ergeben eine allgemeine Empfehlung zum Integritätsstatus.
io_subsystem: Erfasst Daten zu EA. Zusätzlich zu den Diagnosedaten erzeugt diese Komponente nur für ein EA-Subsystem einen komplett fehlerfreien oder einen Warnzustand.
events: Erfasst über die gespeicherte Prozedur Daten und Oberflächen zu den relevanten, vom Server aufgezeichneten Fehlern und Ereignissen, u. a. Details zu Ringpufferausnahmen, Ringpufferereignisse zum Speicherbroker, ungenügender Arbeitsspeicher, Zeitplanungsmodul-Überwachung, Pufferpool, Spinlocks, Sicherheit und Konnektivität. Ereignisse zeigen als Status immer 0 an.
<name of the availability group>: Erfasst Daten für die angegebene Verfügbarkeitsgruppe (wenn component_type = "alwaysOn:AvailabilityGroup").

Hinweise

Die Komponenten system, resource und query_processing werden zur Fehlererkennung aus Fehlerperspektive genutzt, während die Komponenten io_subsystem und events nur zu Diagnosezwecken genutzt werden.

In der folgenden Tabelle sind die Komponenten den jeweils zugeordneten Integritätszuständen zugeordnet.

Komponenten	Clean (1)	Warning (2)	Error (3)	Unknowns (0)
System	x	x	x
Ressource	x	x	x
query_processing	x	x	x
io_subsystem	x	x
Ereignisse				x

Das (x) in jeder Zeile steht für gültige Zustände für die Komponente. Im Beispiel wird io_subsystem als fehlerfrei oder Warnung angezeigt. Der Fehlerstatus wird nicht angezeigt.

Berechtigungen

Erfordert die VIEW SERVER STATE-Berechtigung auf dem Server.

Beispiele

Es ist empfehlenswert, die Zustandsinformationen in erweiterten Sitzungen aufzuzeichnen und in einer Datei zu speichern, die sich außerhalb von SQL Server befindet. In diesem Fall können Sie auch bei einem Fehler auf diese zugreifen. Im folgenden Beispiel wird die Ausgabe einer Ereignissitzung in einer Datei gespeichert:

CREATE EVENT SESSION [diag]
ON SERVER
           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
           ADD TARGET [asynchronous_file_target] (set filename='c:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
      ON SERVER STATE = start;
GO

In der unten angegebenen Beispielabfrage wird die Protokolldatei der erweiterten Sitzung gelesen:

SELECT
    xml_data.value('(/event/@name)[1]','varchar(max)') AS Name
  , xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package
  , xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time'
  , xml_data.value('(/event/data[@name=''component_type'']/value)[1]','sysname') AS Sysname
  , xml_data.value('(/event/data[@name=''component_name'']/value)[1]','sysname') AS Component
  , xml_data.value('(/event/data[@name=''state'']/value)[1]','int') AS State
  , xml_data.value('(/event/data[@name=''state_desc'']/value)[1]','sysname') AS State_desc
  , xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM 
(
      SELECT
                        object_name as event
                        ,CONVERT(xml, event_data) as xml_data
       FROM  
      sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) 
AS XEventData
ORDER BY time;

Im folgenden Beispiel wird die Ausgabe von sp_server_diagnostics an eine Tabelle in einem anderen als dem Wiederholungsmodus aufgezeichnet:

CREATE TABLE SpServerDiagnosticsResult
(
      create_time DateTime,
      component_type sysname,
      component_name sysname,
      state int,
      state_desc sysname,
      data nvarchar(max)
);
INSERT INTO SpServerDiagnosticsResult ;
EXEC sp_server_diagnostics;

Siehe auch

Konzepte

Failoverrichtlinie für Failoverclusterinstanzen

Freigeben über