sp_server_diagnostics (Transact-SQL)

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

[ @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.

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.

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.

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\MSSQL16.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
EXECUTE 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

Relaterat innehåll

• redundansprincip för redundansklusterinstanser