sp_server_diagnostics (Transact-SQL)

Artikel
04/10/2024

Gilt für: SQL Server

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 entweder über eine normale Verbindung oder über eine dedizierte Administratorverbindung aufgerufen werden.

Transact-SQL-Syntaxkonventionen

Syntax

sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]

Argumente

[ @repeat_interval = ] 'repeat_interval'

Gibt das Zeitintervall an, in dem die gespeicherte Prozedur wiederholt ausgeführt wird, um Integritätsinformationen zu senden.

@repeat_interval ist int mit der Standardeinstellung von 0. Die gültigen Parameterwerte sind 0, oder ein beliebiger Wert gleich oder mehr als 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 ist 0, gibt die gespeicherte Prozedur Daten einmal zurück und beendet dann.

Wenn der angegebene Wert kleiner als der Minimalwert ist, löst er einen Fehler aus und gibt nichts zurück.

Wenn der angegebene Wert gleich oder mehr 5ist, wird die gespeicherte Prozedur wiederholt ausgeführt, um den Integritätszustand zurückzugeben, bis er manuell abgebrochen wird.

Rückgabecodewerte

0 (erfolgreich) oder 1 Fehler.

Resultset

sp_server_diagnostics gibt die folgenden Informationen zurück.

Spalte	Datentyp	Beschreibung
`create_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 SQL Server-Instanzebene oder für eine Always On-Verfügbarkeitsgruppe enthält: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Gibt den Namen der Komponente oder den Namen der Verfügbarkeitsgruppe an: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Gibt den Integritätsstatus der Komponente an. Dabei kann es sich um einen der folgenden Werte handeln: `0`, , `1`, , `2`oder `3`
`state_desc`	sysname	Beschreibt die Zustandsspalte. Folgende Beschreibungen entsprechen den Werten in der Statusspalte: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Gibt Daten an, die für die Komponente spezifisch sind.

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

System: Sammelt Daten aus systembezogener Sicht auf Spinlocks, schwere Verarbeitungsbedingungen, nicht ertragende Vorgänge, Seitenfehler und CPU-Auslastung. Diese Informationen erzeugen eine allgemeine Empfehlung für den Gesundheitszustand.
Ressource: Sammelt Daten aus einer Ressourcenperspektive zu physischem und virtuellem Speicher, Pufferpools, Seiten, Cache und anderen Speicherobjekten. Diese Informationen erzeugen eine allgemeine Empfehlung für den Gesundheitszustand.
query_processing: Sammelt Daten aus einer Abfrageverarbeitungsperspektive für die Arbeitsthreads, Aufgaben, Wartetypen, CPU-intensive Sitzungen und Blockieren von Aufgaben. Diese Informationen erzeugen eine allgemeine Empfehlung für den Gesundheitszustand.
io_subsystem: Sammelt Daten über E/A. Zusätzlich zu den Diagnosedaten erzeugt diese Komponente nur für ein EA-Subsystem einen komplett fehlerfreien oder einen Warnzustand.
Ereignisse: Sammelt Daten und Oberflächen über die gespeicherte Prozedur zu den Fehlern und Ereignissen von Interesse, die vom Server aufgezeichnet werden, einschließlich Details zu Ringpufferausnahmen, Ringpufferereignissen zum Speicherbroker, nicht genügend Arbeitsspeicher, Planermonitor, Pufferpool, Spinlocks, Sicherheit und Konnektivität. Ereignisse werden immer als Zustand angezeigt 0 .
<Name der Verfügbarkeitsgruppe>: Sammelt Daten für die angegebene Verfügbarkeitsgruppe (wenn ).component_type = "Always On:AvailabilityGroup"

Hinweise

Aus Fehlerperspektive werden die systemKomponenten und query_processing Die Komponenten resourcezur Fehlererkennung verwendet, während die io_subsystem Komponenten events nur für Diagnosezwecke verwendet werden.

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

Komponenten	Clean (1)	Warning (2)	Fehler (3)	Unknowns (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

Die x in jeder Zeile stellt gültige Integritätszustände für die Komponente dar. Zeigt z. B. entweder io_subsystem als clean oder warning. Die Fehlerzustände werden nicht angezeigt.

Hinweis

Die sp_server_diagnostics interne Prozedur wird auf einem präemptiven Thread mit hoher Priorität implementiert.

Berechtigungen

Erfordert die VIEW SERVER STATE-Berechtigung auf dem Server.

Beispiele

Es empfiehlt sich, erweiterte Ereignissitzungen zu verwenden, um die Integritätsinformationen zu erfassen und in einer Datei zu speichern, die sich außerhalb von SQL Server befindet. Daher können Sie weiterhin darauf zugreifen, wenn ein Fehler auftritt.

A. Speichern der Ausgabe aus einer Erweiterten Ereignissitzung in einer Datei

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

B. Lesen des Sitzungsprotokolls für erweiterte Ereignisse

Die folgende Abfrage liest die Sitzungsprotokolldatei für erweiterte Ereignisse in SQL Server 2016 (13.x):

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\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY TIME;

C. Erfassen der `sp_server_diagnostics` Ausgabe in einer Tabelle

Im folgenden Beispiel wird die Ausgabe einer sp_server_diagnostics Tabelle in einem nicht wiederholten Modus erfasst:

CREATE TABLE SpServerDiagnosticsResult (
    create_time DATETIME,
    component_type SYSNAME,
    component_name SYSNAME,
    [state] INT,
    state_desc SYSNAME,
    [data] XML
);

INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics;

Die folgende Abfrage liest die Zusammenfassungsausgabe aus der Beispieltabelle:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D: Lesen der detaillierten Ausgabe jeder Komponente

Im folgenden Beispiel werden einige der detaillierten Ausgaben jeder Komponente in der tabelle gelesen, die im vorherigen Beispiel erstellt wurde.

System:

SELECT data.value('(/system/@systemCpuUtilization)[1]', 'bigint') AS 'System_CPU',
    data.value('(/system/@sqlCpuUtilization)[1]', 'bigint') AS 'SQL_CPU',
    data.value('(/system/@nonYieldingTasksReported)[1]', 'bigint') AS 'NonYielding_Tasks',
    data.value('(/system/@pageFaults)[1]', 'bigint') AS 'Page_Faults',
    data.value('(/system/@latchWarnings)[1]', 'bigint') AS 'Latch_Warnings',
    data.value('(/system/@BadPagesDetected)[1]', 'bigint') AS 'BadPages_Detected',
    data.value('(/system/@BadPagesFixed)[1]', 'bigint') AS 'BadPages_Fixed'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'system'
GO

Ressourcenmonitor:

SELECT data.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(max)') AS [Notification],
    data.value('(/resource/memoryReport/entry[@description=''Working Set'']/@value)[1]', 'bigint') / 1024 AS [SQL_Mem_in_use_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Paging File'']/@value)[1]', 'bigint') / 1024 AS [Avail_Pagefile_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Physical Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_Physical_Mem_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Virtual Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_VAS_MB],
    data.value('(/resource/@lastNotification)[1]', 'varchar(100)') AS 'LastNotification',
    data.value('(/resource/@outOfMemoryExceptions)[1]', 'bigint') AS 'OOM_Exceptions'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'resource'
GO

Nicht veraltete Wartezeiten:

SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
    waits.evt.value('(@waits)', 'bigint') AS 'Waits',
    waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
    waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO

Preemptive Wartezeiten:

SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
    waits.evt.value('(@waits)', 'bigint') AS 'Waits',
    waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
    waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO

CPU-intensive Anforderungen:

SELECT cpureq.evt.value('(@sessionId)', 'bigint') AS 'SessionID',
    cpureq.evt.value('(@command)', 'varchar(100)') AS 'Command',
    cpureq.evt.value('(@cpuUtilization)', 'bigint') AS 'CPU_Utilization',
    cpureq.evt.value('(@cpuTimeMs)', 'bigint') AS 'CPU_Time_ms'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS cpureq(evt)
WHERE component_name LIKE 'query_processing'
GO

Blockierter Prozessbericht:

SELECT blk.evt.query('.') AS 'Blocked_Process_Report_XML'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS blk(evt)
WHERE component_name LIKE 'query_processing'
GO

Eingabe/Ausgabe:

SELECT data.value('(/ioSubsystem/@ioLatchTimeouts)[1]', 'bigint') AS 'Latch_Timeouts',
    data.value('(/ioSubsystem/@totalLongIos)[1]', 'bigint') AS 'Total_Long_IOs'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'io_subsystem'
GO

Ereignisinformationen:

SELECT xevts.evt.value('(@name)', 'varchar(100)') AS 'xEvent_Name',
    xevts.evt.value('(@package)', 'varchar(100)') AS 'Package',
    xevts.evt.value('(@timestamp)', 'datetime') AS 'xEvent_Time',
    xevts.evt.query('.') AS 'Event Data'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS xevts(evt)
WHERE component_name LIKE 'events'
GO

Failover Policy for Failover Cluster Instances

Teilen über

sp_server_diagnostics (Transact-SQL)

Syntax

Argumente

[ @repeat_interval = ] 'repeat_interval'

Rückgabecodewerte

Resultset

Hinweise

Berechtigungen

Beispiele

A. Speichern der Ausgabe aus einer Erweiterten Ereignissitzung in einer Datei

B. Lesen des Sitzungsprotokolls für erweiterte Ereignisse

C. Erfassen der `sp_server_diagnostics` Ausgabe in einer Tabelle

D: Lesen der detaillierten Ausgabe jeder Komponente

Feedback

Zusätzliche Ressourcen

Teilen über

sp_server_diagnostics (Transact-SQL)

Syntax

Argumente

[ @repeat_interval = ] 'repeat_interval'

Rückgabecodewerte

Resultset

Hinweise

Berechtigungen

Beispiele

A. Speichern der Ausgabe aus einer Erweiterten Ereignissitzung in einer Datei

B. Lesen des Sitzungsprotokolls für erweiterte Ereignisse

C. Erfassen der sp_server_diagnostics Ausgabe in einer Tabelle

D: Lesen der detaillierten Ausgabe jeder Komponente

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen

C. Erfassen der `sp_server_diagnostics` Ausgabe in einer Tabelle