sp_server_diagnostics (Transact-SQL)
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 5
ist, 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 system
Komponenten und query_processing
Die Komponenten resource
zur 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