sp_server_diagnostics (Transact-SQL)

Artikel
2025-03-11

gäller för:SQL Server

Samlar in diagnostikdata och hälsoinformation om SQL Server för att identifiera potentiella fel. Proceduren körs i upprepat läge och skickar resultat med jämna mellanrum. Det kan anropas från antingen en vanlig anslutning eller en dedikerad administratörsanslutning.

Transact-SQL syntaxkonventioner

Syntax

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

Argument

Viktig

Argument för utökade lagrade procedurer måste anges i den specifika ordning som beskrivs i avsnittet Syntax. Om parametrarna anges i fel ordning visas ett felmeddelande.

[ @repeat_interval = ] "repeat_interval"

Anger det tidsintervall med vilket den lagrade proceduren körs upprepade gånger för att skicka hälsoinformation.

@repeat_interval är int med standardvärdet 0. Giltiga parametervärden är 0, eller ett värde som är lika med eller mer än 5. Den lagrade proceduren måste köras minst 5 sekunder för att returnera fullständiga data. Det minsta värdet för den lagrade proceduren som ska köras i upprepningsläget är 5 sekunder.

Om den här parametern inte har angetts eller om det angivna värdet är 0returnerar den lagrade proceduren data en gång och avslutar sedan.

Om det angivna värdet är mindre än minimivärdet uppstår ett fel och returnerar ingenting.

Om det angivna värdet är lika med eller mer än 5körs den lagrade proceduren upprepade gånger för att returnera hälsotillståndet tills det avbryts manuellt.

Returnera kodvärden

0 (lyckades) eller 1 (fel).

Resultatuppsättning

sp_server_diagnostics returnerar följande information.

Spalt	Datatyp	Beskrivning
`create_time`	datetime	Anger tidsstämpeln för radskapande. Varje rad i en enskild raduppsättning har samma tidsstämpel.
`component_type`	sysname	Anger om raden innehåller information för komponenten på SQL Server-instansnivå eller för en AlwaysOn-tillgänglighetsgrupp: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Anger namnet på komponenten eller namnet på tillgänglighetsgruppen: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Anger komponentens hälsostatus. Kan vara något av följande värden: `0`, `1`, `2`eller `3`
`state_desc`	sysname	Beskriver tillståndskolumnen. Beskrivningar som motsvarar värdena i delstatskolumnen är: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Anger data som är specifika för komponenten.

Här följer beskrivningarna av de fem komponenterna:

system: Samlar in data från ett systemperspektiv på spinlocks, svåra bearbetningsförhållanden, icke-givande uppgifter, sidfel och CPU-användning. Den här informationen genererar en allmän hälsotillståndsrekommendation.
resurs: Samlar in data från ett resursperspektiv på fysiskt och virtuellt minne, buffertpooler, sidor, cacheminne och andra minnesobjekt. Den här informationen genererar en allmän hälsotillståndsrekommendation.
query_processing: Samlar in data från ett frågebearbetningsperspektiv på arbetstrådar, uppgifter, väntetyper, PROCESSORintensiva sessioner och blockerande uppgifter. Den här informationen genererar en allmän hälsotillståndsrekommendation.
io_subsystem: Samlar in data om I/O. Förutom diagnostikdata genererar den här komponenten endast ett felfritt hälsotillstånd eller varningstillstånd för ett I/O-undersystem.
händelser: Samlar in data och ytor genom den lagrade proceduren om fel och händelser av intresse som registrerats av servern, inklusive information om undantag för ringbuffert, ringbufferthändelser om minnesutjämning, slut på minne, schemaläggarens övervakare, buffertpool, spinlocks, säkerhet och anslutning. Händelser visar alltid 0 som tillstånd.
<namnet på tillgänglighetsgruppen>: Samlar in data för den angivna tillgänglighetsgruppen (om component_type = "Always On:AvailabilityGroup").

Anmärkningar

Ur ett felperspektiv används komponenterna system, resourceoch query_processing för felidentifiering medan komponenterna io_subsystem och events endast används för diagnostikändamål.

I följande tabell mappas komponenterna till deras associerade hälsotillstånd.

Komponenter	Ren (1)	Varning (2)	Fel (3)	Okända (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

x på varje rad representerar giltiga hälsotillstånd för komponenten. Till exempel visas io_subsystem antingen som clean eller warning. Det visar inte feltillstånden.

Not

Den sp_server_diagnostics interna proceduren implementeras på en förebyggande tråd med hög prioritet.

Behörigheter

Kräver VIEW SERVER STATE behörighet på servern.

Exempel

Det är bästa praxis att använda extended events-sessioner för att samla in hälsoinformationen och spara den i en fil som finns utanför SQL Server. Därför kan du fortfarande komma åt den om det uppstår ett fel.

A. Spara utdata från en extended events-session till en fil

I följande exempel sparas utdata från en händelsesession till en fil:

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. Läs sessionsloggen För utökade händelser

Följande fråga läser sessionsloggfilen extended events på 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. Avbilda `sp_server_diagnostics` utdata till en tabell

I följande exempel avbildas utdata från sp_server_diagnostics till en tabell i icke-upprepat läge:

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;

Följande fråga läser sammanfattningsutdata från exempeltabellen:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Läsa detaljerade utdata från varje komponent

Följande exempelfrågor läser några av de detaljerade utdata från varje komponent i tabellen som skapades i föregående exempel.

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

Resursövervakare:

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

Inaktuella väntetider:

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

Förebyggande väntetider:

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

processorintensiva begäranden:

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 över blockerade processer:

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

indata/utdata:

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

Händelseinformation:

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

redundansprincip för redundansklusterinstanser

Dela via