sp_server_diagnostics (Transact-SQL)

Artikel
2025-03-11

van toepassing op:SQL Server-

Legt diagnostische gegevens en statusgegevens vast over SQL Server om mogelijke fouten te detecteren. De procedure wordt uitgevoerd in de herhalingsmodus en verzendt periodiek resultaten. Het kan worden aangeroepen vanuit een normale verbinding of een toegewezen beheerdersverbinding.

Transact-SQL syntaxisconventies

Syntaxis

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

Argumenten

Belangrijk

Argumenten voor uitgebreide opgeslagen procedures moeten worden ingevoerd in de specifieke volgorde, zoals beschreven in de sectie Syntaxis. Als de parameters niet in de volgorde zijn ingevoerd, treedt er een foutbericht op.

[ @repeat_interval = ] 'repeat_interval'

Geeft het tijdsinterval aan waarop de opgeslagen procedure herhaaldelijk wordt uitgevoerd om statusgegevens te verzenden.

@repeat_interval is int met de standaardwaarde van 0. De geldige parameterwaarden zijn 0of een waarde die gelijk is aan of meer dan 5. De opgeslagen procedure moet ten minste 5 seconden worden uitgevoerd om volledige gegevens te retourneren. De minimale waarde voor de opgeslagen procedure die moet worden uitgevoerd in de herhalingsmodus is vijf seconden.

Als deze parameter niet is opgegeven of als de opgegeven waarde is 0, retourneert de opgeslagen procedure gegevens eenmalig en sluit deze af.

Als de opgegeven waarde kleiner is dan de minimumwaarde, wordt er een fout gegenereerd en wordt er niets geretourneerd.

Als de opgegeven waarde gelijk is aan of meer dan 5, wordt de opgeslagen procedure herhaaldelijk uitgevoerd om de status te retourneren totdat deze handmatig wordt geannuleerd.

Codewaarden retourneren

0 (geslaagd) of 1 (mislukt).

Resultatenset

sp_server_diagnostics retourneert de volgende informatie.

Kolom	Gegevenstype	Beschrijving
`create_time`	datum/tijd-	Geeft het tijdstempel aan van het maken van rijen. Elke rij in één rijset heeft hetzelfde tijdstempel.
`component_type`	sysname-	Geeft aan of de rij informatie bevat voor het sql Server-exemplaarniveauonderdeel of voor een AlwaysOn-beschikbaarheidsgroep: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname-	Geeft de naam van het onderdeel of de naam van de beschikbaarheidsgroep aan: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`		Geeft de status van het onderdeel aan. Kan een van de volgende waarden zijn: `0`, `1`, `2`of `3`
`state_desc`	sysname-	Beschrijft de statuskolom. Beschrijvingen die overeenkomen met de waarden in de kolom Status zijn: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Hiermee geeft u gegevens op die specifiek zijn voor het onderdeel.

Hier volgen de beschrijvingen van de vijf onderdelen:

systeem: verzamelt gegevens vanuit een systeemperspectief op spinlocks, ernstige verwerkingsomstandigheden, niet-opleverende taken, paginafouten en CPU-gebruik. Deze informatie produceert een algemene aanbeveling voor de status.
resource: verzamelt gegevens vanuit een resourceperspectief op fysiek en virtueel geheugen, buffergroepen, pagina's, cache en andere geheugenobjecten. Deze informatie produceert een algemene aanbeveling voor de status.
query_processing: verzamelt gegevens vanuit een queryverwerkingsperspectief op de werkrolthreads, taken, wachttypen, CPU-intensieve sessies en blokkerende taken. Deze informatie produceert een algemene aanbeveling voor de status.
io_subsystem: verzamelt gegevens over IO. Naast diagnostische gegevens produceert dit onderdeel alleen een schone status in orde of waarschuwingsstatus voor een IO-subsysteem.
gebeurtenissen: verzamelt gegevens en oppervlakken via de opgeslagen procedure op de fouten en gebeurtenissen van belang die door de server zijn vastgelegd, inclusief details over ringbufferuitzonderingen, ringbuffergebeurtenissen over geheugenbroker, uit het geheugen, scheduler-monitor, bufferpool, spinlocks, beveiliging en connectiviteit. Gebeurtenissen geven altijd 0 weer als de status.
<naam van de beschikbaarheidsgroep>: verzamelt gegevens voor de opgegeven beschikbaarheidsgroep (indien component_type = "Always On:AvailabilityGroup").

Opmerkingen

Vanuit een foutperspectief worden de system, resourceen query_processing onderdelen gebruikt voor foutdetectie, terwijl de io_subsystem- en events-onderdelen alleen worden gebruikt voor diagnostische doeleinden.

De volgende tabel wijst de onderdelen toe aan de bijbehorende statussen.

Onderdelen	Schoon (1)	Waarschuwing (2)	Fout (3)	Onbekenden (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

De x in elke rij vertegenwoordigt geldige statussen voor het onderdeel. io_subsystem wordt bijvoorbeeld weergegeven als clean of warning. De foutstatussen worden niet weergegeven.

Notitie

De sp_server_diagnostics interne procedure wordt uitgevoerd op een preemptive thread met hoge prioriteit.

Machtigingen

Vereist VIEW SERVER STATE machtiging op de server.

Voorbeelden

Het is raadzaam om uitgebreide gebeurtenissensessies te gebruiken om de statusgegevens vast te leggen en op te slaan in een bestand dat zich buiten SQL Server bevindt. Daarom kunt u deze nog steeds openen als er een fout opgetreden is.

Een. De uitvoer van een sessie met uitgebreide gebeurtenissen opslaan in een bestand

In het volgende voorbeeld wordt de uitvoer van een gebeurtenissessie opgeslagen in een bestand:

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. Het sessielogboek uitgebreide gebeurtenissen lezen

De volgende query leest het logboekbestand uitgebreide gebeurtenissensessie op 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. Uitvoer van `sp_server_diagnostics` vastleggen in een tabel

In het volgende voorbeeld wordt de uitvoer van sp_server_diagnostics vastgelegd in een tabel in een niet-herhalende modus:

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;

De volgende query leest de samenvattingsuitvoer uit de voorbeeldtabel:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Gedetailleerde uitvoer van elk onderdeel lezen

In het volgende voorbeeld worden enkele gedetailleerde uitvoer van elk onderdeel gelezen in de tabel die in het vorige voorbeeld is gemaakt.

Systeem:

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

niet-preventieve wachttijden:

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

voorlopige wachttijden:

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-intensieve aanvragen:

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

rapport geblokkeerd proces:

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

invoer/uitvoer:

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

gebeurtenisgegevens:

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

failoverbeleid voor exemplaren van failoverclusters

Delen via