sp_server_diagnostics (Transact-SQL)

A következőkre vonatkozik:SQL Server

Diagnosztikai adatokat és állapotinformációkat rögzít az SQL Serverről a lehetséges hibák észleléséhez. Az eljárás ismétlődő módban fut, és rendszeresen küld eredményeket. Meghívható normál kapcsolatból vagy dedikált rendszergazdai kapcsolatból.

Transact-SQL szintaxis konvenciói

Szintaxis

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

Érvek

Fontos

A kiterjesztett tárolt eljárások argumentumait a Szintaxis szakaszban leírt sorrendben kell megadni. Ha a paraméterek sorrenden kívül vannak beírva, hibaüzenet jelenik meg.

[ @repeat_interval = ] 'repeat_interval'

Azt az időintervallumot jelzi, amikor a tárolt eljárás ismétlődően fut az állapotinformációk küldéséhez.

@repeat_intervalint alapértelmezett 0. Az érvényes paraméterértékek 0, vagy bármely olyan érték, amely 5vagy annál nagyobb. A tárolt eljárásnak legalább 5 másodpercet kell futnia a teljes adatok visszaadásához. A tárolt eljárás ismétlési módban való futtatásának minimális értéke 5 másodperc.

Ha ez a paraméter nincs megadva, vagy ha a megadott érték 0, a tárolt eljárás egyszer visszaadja az adatokat, majd kilép.

Ha a megadott érték kisebb, mint a minimális érték, hibát jelez, és semmit sem ad vissza.

Ha a megadott érték egyenlő vagy nagyobb, mint 5, a tárolt eljárás többször fut, hogy visszaadja az állapotot, amíg manuálisan nem törli.

Kódértékek visszaadása

0 (sikeres) vagy 1 (hiba).

Eredményhalmaz

sp_server_diagnostics a következő adatokat adja vissza.

Oszlop	Adattípus	Leírás
`create_time`	datetime	A sorlétrehozás időbélyegét jelzi. Egy sorhalmaz minden sora azonos időbélyeggel rendelkezik.
`component_type`	sysname	Azt jelzi, hogy a sor tartalmaz-e információt az SQL Server példányszintű összetevőjére vagy egy Always On rendelkezésre állási csoportra vonatkozóan: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Az összetevő nevét vagy a rendelkezésre állási csoport nevét jelzi: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`		Az összetevő állapotát jelzi. Az alábbi értékek egyike lehet: `0`, `1`, `2`vagy `3`
`state_desc`	sysname	Az állapotoszlopot ismerteti. Az állapotoszlop értékeinek megfelelő leírások a következők: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Az összetevőre jellemző adatokat adja meg.

Íme az öt összetevő leírása:

rendszer: Rendszer szempontjából gyűjt adatokat a spinlockokról, a súlyos feldolgozási feltételekről, a nem hozamot eredményező feladatokról, az oldalhibákról és a processzorhasználatról. Ez az információ általános állapotjavaslatot hoz létre.
erőforrás-: Adatokat gyűjt az erőforrás szempontjából a fizikai és virtuális memóriáról, a pufferkészletekről, a lapokról, a gyorsítótárról és más memóriaobjektumokról. Ez az információ általános állapotjavaslatot hoz létre.
query_processing: Lekérdezésfeldolgozási szempontból gyűjt adatokat a feldolgozói szálakról, a feladatokról, a várakozási típusokról, a processzorigényes munkamenetekről és a blokkolt tevékenységekről. Ez az információ általános állapotjavaslatot hoz létre.
io_subsystem: Adatokat gyűjt az IO-n. A diagnosztikai adatok mellett ez az összetevő csak egy I/O-alrendszer számára biztosít tiszta kifogástalan állapotot vagy figyelmeztető állapotot.
események: Adatokat és felületeket gyűjt a tárolt eljáráson keresztül a kiszolgáló által rögzített hibákról és eseményekről, beleértve a gyűrűpuffer kivételeiről, a memóriaközvetítő gyűrűpufferes eseményeiről, a memórián kívüli eseményekről, a ütemező monitorozásáról, a pufferkészletről, a spinlockokról, a biztonságról és a kapcsolatról. Az események mindig 0 állapotként jelennek meg.
<rendelkezésre állási csoport neve>: Adatokat gyűjt a megadott rendelkezésre állási csoportról (ha component_type = "Always On:AvailabilityGroup").

Megjegyzések

Hiba szempontjából a system, a resourceés a query_processing összetevőket használják a hibaészleléshez, míg a io_subsystem és a events összetevőket csak diagnosztikai célokra használják.

Az alábbi táblázat leképezi az összetevőket a hozzájuk tartozó állapotukra.

Összetevők	Tiszta (1)	Figyelmeztetés (2)	Hiba (3)	Ismeretlenek (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

Az egyes sorokban lévő x az összetevő érvényes állapotait jelzik. A io_subsystem például clean vagy warningjelenik meg. Nem jeleníti meg a hibaállapotokat.

Jegyzet

A sp_server_diagnostics belső eljárást egy magas prioritású, megelőző szálon hajtják végre.

Engedélyek

A kiszolgálón VIEW SERVER STATE engedély szükséges.

Példák

Ajánlott kiterjesztett események munkamenetek használatával rögzíteni az állapotinformációkat, és menteni azokat egy SQL Serveren kívüli fájlba. Ezért továbbra is elérheti, ha hiba történt.

Egy. Kimenet mentése kiterjesztett események munkamenetből fájlba

Az alábbi példa egy esemény-munkamenet kimenetét egy fájlba menti:

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. A Bővített események munkamenetnaplójának olvasása

A következő lekérdezés beolvassa a Bővített események munkamenet naplófájlját az SQL Server 2016-on (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. `sp_server_diagnostics` tábla kimenetének rögzítése

Az alábbi példa nem ismétlődő módban rögzíti a sp_server_diagnostics kimenetét egy táblában:

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;

Az alábbi lekérdezés a példatáblából olvassa be az összefoglaló kimenetet:

SELECT create_time,
       component_name,
       state_desc
FROM SpServerDiagnosticsResult;

D. Az egyes összetevők részletes kimenetének olvasása

Az alábbi példa lekérdezések az egyes összetevők részletes kimenetét olvasták be az előző példában létrehozott táblázatban.

rendszer:

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

Erőforrás-figyelő:

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

nem megelőző várakozások:

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

Megelőző várakozások:

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

processzorigényes kérelmek:

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

blokkolt folyamatjelentés:

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

bemenet/kimenet:

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

eseményinformációk:

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

feladatátvételi házirend feladatátvételi fürtpéldányokhoz

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2025-06-23