sp_server_diagnostics (Transact-SQL)

Articolo
04/10/2024

Si applica a: SQL Server

Acquisisce dati diagnostici e informazioni di integrità su SQL Server per rilevare potenziali errori. La procedura viene eseguita in modalità di ripetizione e i risultati vengono inviati periodicamente. Può essere richiamato da una connessione normale o da una connessione amministrativa dedicata.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

[ @repeat_interval = ] 'repeat_interval'

Indica l'intervallo di tempo in cui la stored procedure viene eseguita ripetutamente per inviare informazioni sull'integrità.

@repeat_interval è int con l'impostazione predefinita .0 I valori dei parametri validi sono 0o qualsiasi valore uguale o maggiore di 5. È necessario eseguire la stored procedure per almeno 5 secondi per restituire i dati completi. Il valore minimo per l'esecuzione della stored procedure in modalità di ripetizione è 5 secondi.

Se questo parametro non è specificato o se il valore specificato è 0, la stored procedure restituisce i dati una volta e quindi esci.

Se il valore specificato è minore del valore minimo, genera un errore e non restituisce nulla.

Se il valore specificato è uguale o maggiore di 5, la stored procedure viene eseguita ripetutamente per restituire lo stato di integrità fino a quando non viene annullata manualmente.

Valori del codice restituito

0 (esito positivo) o 1 (errore).

Set di risultati

sp_server_diagnostics restituisce le informazioni seguenti.

Colonna	Tipo di dati	Descrizione
`create_time`	datetime	Indica il timestamp della creazione della riga. Ogni riga di un singolo set di righe dispone dello stesso timestamp.
`component_type`	sysname	Indica se la riga contiene informazioni per il componente a livello di istanza di SQL Server o per un gruppo di disponibilità AlwaysOn: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Indica il nome del componente o il nome del gruppo di disponibilità: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Indica lo stato di integrità del componente. Può essere uno dei valori seguenti: `0`, `1`, `2`o `3`
`state_desc`	sysname	Descrive la colonna contenente gli stati. Le descrizioni che corrispondono ai valori nella colonna contenente gli stati sono: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Indica dati specifici del componente.

Di seguito sono riportate le descrizioni dei cinque componenti:

system: raccoglie i dati dal punto di vista del sistema su spinlock, condizioni di elaborazione gravi, attività che non producono, errori di pagina e utilizzo della CPU. Queste informazioni producono un'indicazione dello stato di integrità complessiva.
risorsa: raccoglie i dati dal punto di vista delle risorse in memoria fisica e virtuale, pool di buffer, pagine, cache e altri oggetti memoria. Queste informazioni producono un'indicazione dello stato di integrità complessiva.
query_processing: raccoglie i dati dal punto di vista dell'elaborazione delle query sui thread di lavoro, sulle attività, sui tipi di attesa, sulle sessioni a elevato utilizzo della CPU e sulle attività di blocco. Queste informazioni producono un'indicazione dello stato di integrità complessiva.
io_subsystem: raccoglie i dati in I/O. Oltre ai dati diagnostici, questo componente produce uno stato di integrità di avviso o integro e pulito solo per un sottosistema di IO.
eventi: raccoglie dati e superfici tramite la stored procedure sugli errori e sugli eventi di interesse registrati dal server, inclusi i dettagli sulle eccezioni del buffer circolare, gli eventi del buffer circolare relativi al broker di memoria, memoria insufficiente, monitoraggio dell'utilità di pianificazione, pool di buffer, spinlock, sicurezza e connettività. Gli eventi mostrano 0 sempre come stato.
<nome del gruppo> di disponibilità: raccoglie i dati per il gruppo di disponibilità specificato (se component_type = "Always On:AvailabilityGroup").

Osservazioni:

Dal punto di vista degli errori, i systemcomponenti , resourcee query_processing vengono usati per il rilevamento degli errori mentre i io_subsystem componenti e events vengono usati solo a scopo diagnostico.

Nella tabella seguente viene eseguito il mapping dei componenti agli stati di integrità associati.

Componenti	Pulito (1)	Avviso (2)	Errore (3)	Sconosciuto (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

In x ogni riga rappresenta gli stati di integrità validi per il componente. Ad esempio, io_subsystem viene visualizzato come clean o warning. Non mostra gli stati di errore.

Nota

La sp_server_diagnostics procedura interna viene implementata in un thread preemptive con priorità alta.

Autorizzazioni

È richiesta l'autorizzazione VIEW SERVER STATE per il server.

Esempi

È consigliabile usare sessioni eventi estesi per acquisire le informazioni sull'integrità e salvarle in un file che si trova all'esterno di SQL Server. Pertanto, è comunque possibile accedervi in caso di errore.

R. Salvare l'output da una sessione eventi estesi in un file

Nell'esempio seguente viene salvato l'output da una sessione eventi in un file:

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. Leggere il log delle sessioni degli eventi estesi

La query seguente legge il file di log delle sessioni degli eventi estesi in 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. Acquisire l'output `sp_server_diagnostics` in una tabella

L'esempio seguente acquisisce l'output di sp_server_diagnostics in una tabella in modalità non ripetuta:

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;

La query seguente legge l'output di riepilogo dalla tabella di esempio:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Leggere l'output dettagliato da ogni componente

Nell'esempio seguente viene eseguita una query per leggere parte dell'output dettagliato di ogni componente, nella tabella creata nell'esempio precedente.

Sistema:

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

Monitoraggio risorse:

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

Attese nonpreemptive:

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

Attese 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

Richieste a elevato utilizzo di CPU:

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

Report processo bloccato:

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

Input/output:

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

Informazioni sugli eventi:

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

Criteri di failover per istanze del cluster di failover

Condividi tramite

sp_server_diagnostics (Transact-SQL)

Sintassi

Argomenti

[ @repeat_interval = ] 'repeat_interval'

Valori del codice restituito

Set di risultati

Osservazioni:

Autorizzazioni

Esempi

R. Salvare l'output da una sessione eventi estesi in un file

B. Leggere il log delle sessioni degli eventi estesi

C. Acquisire l'output `sp_server_diagnostics` in una tabella

D. Leggere l'output dettagliato da ogni componente

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

sp_server_diagnostics (Transact-SQL)

Sintassi

Argomenti

[ @repeat_interval = ] 'repeat_interval'

Valori del codice restituito

Set di risultati

Osservazioni:

Autorizzazioni

Esempi

R. Salvare l'output da una sessione eventi estesi in un file

B. Leggere il log delle sessioni degli eventi estesi

C. Acquisire l'output sp_server_diagnostics in una tabella

D. Leggere l'output dettagliato da ogni componente

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive

C. Acquisire l'output `sp_server_diagnostics` in una tabella