sp_server_diagnostics (Transact-SQL)sp_server_diagnostics (Transact-SQL)

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions)

Erfasst Diagnosedaten und Zustandsinformationen zu SQL ServerSQL Server, um potenzielle Fehler zu erkennen.Captures diagnostic data and health information about SQL ServerSQL Server to detect potential failures. Die Prozedur wird im Wiederholungsmodus ausgeführt und sendet regelmäßig Ergebnisse.The procedure runs in repeat mode and sends results periodically. Sie kann über eine reguläre oder eine DAC-Verbindung aufgerufen werden.It can be invoked from either a regular or a DAC connection.

Gilt für: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) und höher).Applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later).

Symbol für Themenlink Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'   

ArgumenteArguments

[ @repeat_interval = ] 'repeat_interval_in_seconds'Gibt das Zeitintervall an, in dem die gespeicherte Prozedur wiederholt ausgeführt wird, um Integritäts Informationen zu senden.[ @repeat_interval = ] 'repeat_interval_in_seconds' Indicates the time interval at which the stored procedure will run repeatedly to send health information.

repeat_interval_in_seconds ist vom Datentyp int und hat den Standardwert 0.repeat_interval_in_seconds is int with the default of 0. Die gültigen Parameterwerte sind 0 sowie alle Werte größer oder gleich 5.The valid parameter values are 0, or any value equal to or more than 5. Die gespeicherte Prozedur muss mindestens 5 Sekunden lang ausgeführt werden, um vollständige Daten zurückzugeben.The stored procedure has to run at least 5 seconds to return complete data. Der minimale Wert für die Ausführung der gespeicherten Prozedur im Wiederholungsmodus beträgt 5 Sekunden.The minimum value for the stored procedure to run in the repeat mode is 5 seconds.

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.If this parameter is not specified, or if the specified value is 0, the stored procedure will return data one time and then exit.

Wenn der angegebene Wert kleiner als der minimale Wert ist, wird ein Fehler ausgelöst und kein Wert zurückgegeben.If the specified value is less than the minimum value, it will raise an error and return nothing.

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.If the specified value is equal to or more than 5, the stored procedure runs repeatedly to return the health state until it is manually canceled.

RückgabecodewerteReturn Code Values

„0“ (erfolgreich) oder „1“ (fehlerhaft)0 (success) or 1 (failure)

ResultsetsResult Sets

sp_server_diagnostics gibt die folgenden Informationen zurück.sp_server_diagnostics returns the following information

ColumnColumn DatentypData type BESCHREIBUNGDescription
create_timecreate_time datetimedatetime Gibt den Zeitstempel der Zeilenerstellung an.Indicates the time stamp of row creation. Jede Zeile in einem einzelnen Rowset weist denselben Zeitstempel auf.Each row in a single rowset has the same time stamp.
component_typecomponent_type sysnamesysname Gibt an, ob die Zeile Informationen für die SQL ServerSQL Server Komponente auf Instanzebene oder für eine Always on Verfügbarkeits Gruppe enthält:Indicates whether the row contains information for the SQL ServerSQL Server instance level component or for an Always On availability group:

instanceinstance

Always on: availabilitygroupAlways On:AvailabilityGroup
component_namecomponent_name sysnamesysname Gibt den Namen der Komponente oder den Namen der Verfügbarkeitsgruppe an:Indicates the name of component or the name of the availability group:

Systemsystem

resourceresource

query_processingquery_processing

io_subsystemio_subsystem

eventsevents

<name of the availability group>
statestate intint Gibt den Integritätsstatus der Komponente an:Indicates the health status of the component:

00

11

22

33
state_descstate_desc sysnamesysname Beschreibt die Zustandsspalte.Describes the state column. Folgende Beschreibungen entsprechen den Werten in der Statusspalte:Descriptions that correspond to the values in the state column are:

0: Unbekannt.0: Unknown

1: Bereinigen1: clean

2: Warnung2: warning

3: Fehler3: error
Datendata varchar (max)varchar (max) Gibt Daten an, die für die Komponente spezifisch sind.Specifies data that is specific to the component.

Im Folgenden finden Sie die Beschreibungen der fünf Komponenten:Here are the descriptions of the five components:

  • System: sammelt Daten aus einer Systemperspektive für Spinlocks, schwerwiegende Verarbeitungsbedingungen, nicht zustellenden Aufgaben, Seiten Fehler und CPU-Auslastung.system: Collects data from a system perspective on spinlocks, severe processing conditions, non-yielding tasks, page faults, and CPU usage. Diese Informationen ergeben eine allgemeine Empfehlung zum Integritätsstatus.This information is produces an overall health state recommendation.

  • Ressource: sammelt Daten aus einer ressourcenperspektive für physischen und virtuellen Arbeitsspeicher, Pufferpools, Seiten, Cache und andere Speicher Objekte.resource: Collects data from a resource perspective on physical and virtual memory, buffer pools, pages, cache and other memory objects. Diese Informationen erzeugen eine allgemeine Integritäts Zustands Empfehlung.This information produces an overall health state recommendation.

  • query_processing: sammelt Daten aus einer Abfrage Verarbeitungs Perspektive für Arbeitsthreads, Tasks, warte Typen, CPU-intensive Sitzungen und blockierende Tasks.query_processing: Collects data from a query-processing perspective on the worker threads, tasks, wait types, CPU intensive sessions, and blocking tasks. Diese Informationen erzeugen eine allgemeine Integritäts Zustands Empfehlung.This information produces an overall health state recommendation.

  • io_subsystem: sammelt Daten über e/a.io_subsystem: Collects data on IO. Zusätzlich zu den Diagnosedaten erzeugt diese Komponente nur für ein EA-Subsystem einen komplett fehlerfreien oder einen Warnzustand.In addition to diagnostic data, this component produces a clean healthy or warning health state only for an IO subsystem.

  • Ereignisse: sammelt Daten und über die gespeicherte Prozedur auf den vom Server aufgezeichneten Fehlern und Ereignissen, einschließlich Details zu Ringpuffer Ausnahmen, Ringpuffer Ereignissen zum Speicher Broker, nicht genügend Arbeitsspeicher, Scheduler-Monitor, Pufferpool, Spinlocks, Sicherheit und Konnektivität.events: Collects data and surfaces through the stored procedure on the errors and events of interest recorded by the server, including details about ring buffer exceptions, ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool, spinlocks, security, and connectivity . Ereignisse zeigen als Status immer 0 an.Events will always show 0 as the state.

  • <name of the availability group>: Sammelt Daten für die angegebene Verfügbarkeits Gruppe (wenn component_type = "Always on: availabilitygroup").<name of the availability group>: Collects data for the specified availability group (if component_type = "Always On:AvailabilityGroup").

HinweiseRemarks

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.From a failure perspective, the system, resource, and query_processing components will be leveraged for failure detection while the io_subsystem and events components will be leveraged for diagnostic purposes only.

In der folgenden Tabelle sind die Komponenten den jeweils zugeordneten Integritätszuständen zugeordnet.The following table maps the components to their associated health states.

KomponentenComponents Clean (1)Clean (1) Warning (2)Warning (2) Error (3)Error (3) Unknowns (0)Unknowns (0)
Systemsystem xx xx xx
resourceresource xx xx xx
query_processingquery_processing xx xx xx
io_subsystemio_subsystem xx xx
eventsevents xx

Das (x) in jeder Zeile steht für gültige Zustände für die Komponente.The (x) in each row represents valid health states for the component. Im Beispiel wird io_subsystem als fehlerfrei oder Warnung angezeigt.For example, io_subsystem will either show as clean or warning. Der Fehlerstatus wird nicht angezeigt.It will not show the error states.

Hinweis

Die Ausführung sp_server_diagnostics internen Prozedur wird in einem präemptiven Thread mit hoher Priorität implementiert.Execution of sp_server_diagnostics internal procedure is implemented on a preemptive thread at high priority.

BerechtigungenPermissions

Erfordert die VIEW SERVER STATE-Berechtigung auf dem Server.Requires VIEW SERVER STATE permission on the server.

BeispieleExamples

Es ist empfehlenswert, die Zustandsinformationen in erweiterten Sitzungen aufzuzeichnen und in einer Datei zu speichern, die sich außerhalb von SQL Server befindet.It is best practice to use the extended sessions to capture the health information and save it to a file that is located outside of SQL Server. In diesem Fall können Sie auch bei einem Fehler auf diese zugreifen.Therefore, you can still access it if there is a failure. Im folgenden Beispiel wird die Ausgabe einer Ereignissitzung in einer Datei gespeichert:The following example saves the output from an event session to a file:

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:The example query below reads the extended session log file:

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;  

Im folgenden Beispiel wird die Ausgabe von sp_server_diagnostics an eine Tabelle in einem anderen als dem Wiederholungsmodus aufgezeichnet:The following example captures the output of sp_server_diagnostics to a table in a non-repeat mode:

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 nachstehende Beispielabfrage liest die Zusammenfassungsausgabe aus der Tabelle:The example query below reads the summary output from the table:

SELECT create_time,
       component_name,
       state_desc 
FROM SpServerDiagnosticsResult;  

Die nachstehende Beispielabfrage liest einige Bestandteile der ausführlichen Ausgabe aus jeder Komponente in der Tabelle:The example query below reads some of the detailed output from the each component in the table:

-- 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

-- Resource Monitor
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

-- Nonpreemptive waits
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 waits
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 requests
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

-- Blocked Process Report
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

-- IO
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

-- Event information
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  

Weitere InformationenSee Also

Failoverrichtlinie für FailoverclusterinstanzenFailover Policy for Failover Cluster Instances