sp_server_diagnostics (Transact-SQL)

Článek
2025-03-11

platí pro:SQL Server

Zaznamenává diagnostická data a informace o stavu SQL Serveru za účelem zjištění potenciálních selhání. Procedura se spouští v režimu opakování a pravidelně odesílá výsledky. Lze ji vyvolat buď z běžného připojení, nebo vyhrazené připojení správce.

Transact-SQL konvence syntaxe

Syntax

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

Argumenty

Důležitý

Argumenty rozšířených uložených procedur musí být zadány v určitém pořadí, jak je popsáno v části Syntaxe. Pokud jsou parametry zadány mimo pořadí, dojde k chybové zprávě.

[ @repeat_interval = ] 'repeat_interval'

Označuje časový interval, ve kterém se uložená procedura spouští opakovaně, aby se odeslaly informace o stavu.

@repeat_interval je int s výchozím nastavením 0. Platné hodnoty parametrů jsou 0nebo jakákoli hodnota rovna nebo více než 5. Uložená procedura musí běžet alespoň 5 sekund, aby se vrátila úplná data. Minimální hodnota uložené procedury, která se má spustit v režimu opakování, je 5 sekund.

Pokud tento parametr není zadaný nebo pokud je zadaná hodnota 0, uložená procedura vrátí data jednou a pak ukončete.

Pokud je zadaná hodnota menší než minimální hodnota, vyvolá chybu a nevrátí nic.

Pokud je zadaná hodnota rovna nebo více než 5, uložená procedura se opakovaně spustí, aby vrátila stav, dokud nebude ručně zrušena.

Návratové hodnoty kódu

0 (úspěch) nebo 1 (selhání).

Sada výsledků

sp_server_diagnostics vrátí následující informace.

Sloupec	Datový typ	Popis
`create_time`	data a času	Označuje časové razítko vytvoření řádku. Každý řádek v jedné sadě řádků má stejné časové razítko.
`component_type`	sysname	Určuje, jestli řádek obsahuje informace pro komponentu na úrovni instance SQL Serveru nebo pro skupinu dostupnosti AlwaysOn: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Označuje název komponenty nebo název skupiny dostupnosti: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Označuje stav komponenty. Může to být jedna z následujících hodnot: `0`, `1`, `2`nebo `3`
`state_desc`	sysname	Popisuje sloupec stavu. Popisy, které odpovídají hodnotám ve sloupci stavu, jsou: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Určuje data specifická pro komponentu.

Tady jsou popisy pěti součástí:

systémová: Shromažďuje data z hlediska systému na otočných zámcích, závažných podmínkách zpracování, nevolných úloh, chyb stránky a využití procesoru. Tyto informace vytvářejí doporučení celkového stavu.
prostředku: Shromažďuje data z hlediska prostředků z hlediska fyzické a virtuální paměti, fondů vyrovnávací paměti, stránek, mezipaměti a dalších paměťových objektů. Tyto informace vytvářejí doporučení celkového stavu.
query_processing: Shromažďuje data z pohledu zpracování dotazů na pracovní vlákna, úlohy, typy čekání, relace náročné na procesor a blokující úlohy. Tyto informace vytvářejí doporučení celkového stavu.
io_subsystem: Shromažďuje data o vstupně-výstupních operacích. Kromě diagnostických dat tato komponenta vytváří čistý stav stavu v pořádku nebo upozornění pouze pro subsystém vstupně-výstupních operací.
události: Shromažďuje data a povrchy prostřednictvím uložené procedury týkající se chyb a událostí zájmu zaznamenaných serverem, včetně podrobností o výjimkách vyrovnávací paměti okruhu, událostech vyrovnávací paměti zprostředkovatelů paměti, monitorování plánovače, fondu vyrovnávacích pamětí, spinlocků, zabezpečení a připojení. Události vždy zobrazují 0 jako stav.
<název skupiny dostupnosti>: Shromažďuje data pro zadanou skupinu dostupnosti (pokud component_type = "Always On:AvailabilityGroup").

Poznámky

Z hlediska selhání se komponenty system, resourcea query_processing používají k detekci selhání, zatímco komponenty io_subsystem a events se používají pouze pro diagnostické účely.

Následující tabulka mapuje komponenty na jejich přidružené stavy.

Součásti	Čisté (1)	Upozornění (2)	Chyba (3)	Neznámé (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

x v každém řádku představuje platné stavy komponenty. Například io_subsystem se zobrazí jako clean nebo warning. Nezobrazuje se chybové stavy.

Poznámka

Interní postup sp_server_diagnostics se implementuje v preemptivním vlákně s vysokou prioritou.

Dovolení

Vyžaduje oprávnění VIEW SERVER STATE na serveru.

Příklady

Osvědčeným postupem je použít relace rozšířených událostí k zachycení informací o stavu a jejich uložení do souboru, který se nachází mimo SQL Server. Pokud dojde k selhání, můžete k němu přesto získat přístup.

A. Uložení výstupu z relace rozšířených událostí do souboru

Následující příklad uloží výstup z relace události do souboru:

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. Čtení protokolu relací rozšířených událostí

Následující dotaz načte soubor protokolu relace rozšířených událostí na SQL Serveru 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. Zachycení výstupu `sp_server_diagnostics` do tabulky

Následující příklad zachycuje výstup sp_server_diagnostics do tabulky v režimu bez opakování:

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;

Následující dotaz přečte souhrnný výstup z ukázkové tabulky:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Čtení podrobného výstupu z jednotlivých komponent

Následující příklady dotazů čtou některé podrobné výstupy z jednotlivých komponent v tabulce vytvořené v předchozím příkladu.

systém :

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

nepreemptivní čekání:

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

čekání preemptive:

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

požadavky náročné na procesor:

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

sestava blokovaného procesu :

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

vstup/výstup:

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

Informace o události :

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

zásady převzetí služeb při selhání pro instance clusteru s podporou převzetí služeb při selhání

Sdílet prostřednictvím