sp_server_diagnostics (Transact-SQL)
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.
.gif "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:
|
component_name |
Sysname |
Gibt den Namen der Komponente oder den Namen der Verfügbarkeitsgruppe an:
|
state |
Int |
Gibt den Integritätsstatus der Komponente an:
|
state_desc |
Sysname |
Beschreibt die Zustandsspalte. Folgende Beschreibungen entsprechen den Werten in der Statusspalte:
|
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\MSSQL11.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