sp_server_diagnostics (Transact-SQL)
Consente di acquisire dati diagnostici e informazioni sull'integrità di SQL Server per rilevare possibili errori. La routine viene eseguita in modalità ripetizione e invia periodicamente risultati. Può essere richiamata da una connessione normale o di applicazione livello dati.
.gif "Icona di collegamento a un argomento")
Convenzioni della sintassi Transact-SQL
Sintassi
sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'
Argomenti
[ @repeat_interval =] 'repeat_interval_in_seconds'
Indica l'intervallo di tempo in cui la stored procedure verrà eseguita ripetutamente per inviare informazioni di integrità.repeat_interval_in_seconds è di tipo int e il valore predefinito è 0. I valori di parametro validi sono 0 oppure 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 viene specificato o se il valore specificato è 0, la stored procedure restituirà i dati una sola volta, quindi verrà chiusa.
Se il valore specificato è inferiore al valore minimo, verrà generato un errore e non verrà restituito nulla.
Se il valore specificato è uguale o maggiore di 5, la stored procedure viene eseguita ripetutamente per restituire lo stato di integrità finché non verrà annullato manualmente.
Valori di codice restituiti
0 (esito positivo) o 1 (esito negativo)
Set di risultati
sp_server_diagnostics restituisce le informazioni seguenti
Colonna |
Tipo di dati |
Descrizione |
|---|---|---|
creation_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 dell'istanza di SQL Server o per un gruppo di disponibilità AlwaysOn:
|
component_name |
Sysname |
Indica il nome del componente o il nome del gruppo di disponibilità:
|
state |
Int |
Indica lo stato di integrità del componente:
|
state_desc |
Sysname |
Descrive la colonna contenente gli stati. Le descrizioni che corrispondono ai valori nella colonna contenente gli stati sono:
|
data |
Varchar (max) |
Indica dati specifici del componente. |
Di seguito sono riportate le descrizioni dei cinque componenti:
system: Raccoglie dati da una prospettiva di sistema su spinlock, condizioni gravi di elaborazione, attività che non cedono il controllo, errori di pagina e utilizzo della CPU. Queste informazioni producono un'indicazione dello stato di integrità complessiva.
resource: Raccoglie dati da una prospettiva della risorsa su 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 dati da una prospettiva di elaborazione di query su thread di lavoro, attività dei tipi di attesa, sessioni intensive della CPU e attività di blocco. Queste informazioni producono un'indicazione dello stato di integrità complessiva.
io_subsystem: Raccoglie dati in IO. Oltre ai dati diagnostici, questo componente produce uno stato di integrità di avviso o integro e pulito solo per un sottosistema di IO.
events: Raccoglie dati e superfici tramite la stored procedure su errori ed eventi di interesse registrati dal server, inclusi i dettagli sulle eccezioni del buffer circolare, gli eventi del buffer circolare sul broker di memoria, la memoria insufficiente, il sistema di monitoraggio dell'utilità di pianificazione, il pool di buffer, gli spinlock, la sicurezza e la connettività. Gli eventi avranno sempre 0 come stato.
<name of the availability group>: Raccoglie dati per il gruppo di disponibilità specificato (se component_type = "alwaysOn:AvailabilityGroup").
Osservazioni
Da una prospettiva di errore, i componenti di elaborazione di query, risorsa e sistema verranno utilizzati per il rilevamento dell'errore mentre i componenti di eventi e io_subsystem verranno utilizzati solo per gli scopi diagnostici.
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 |
||
eventi |
x |
La (x) in ogni riga rappresenta gli stati di integrità validi per il componente. Ad esempio, lo stato di io_subsystem potrà essere pulito o avviso. Non verranno visualizzati gli stati di errore.
Autorizzazioni
È richiesta l'autorizzazione VIEW SERVER STATE nel server.
Esempi
È consigliabile utilizzare le sessioni estese per acquisire le informazioni di integrità e salvarle in un file che si trova fuori da SQL Server. Pertanto, è ancora possibile accedervi se si verifica un errore. 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
Nella query di esempio riportata di seguito viene letto il file di log della sessione estesa:
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\MSSQL11.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
)
AS XEventData
ORDER BY time
Nell'esempio seguente viene acquisito l'output di sp_server_diagnostics in una tabella in modalità non di ripetizione:
CREATE TABLE SpServerDiagnosticsResult
(
create_time DateTime,
component_type sysname,
component_name sysname,
state int,
state_desc sysname,
data nvarchar(max)
)
INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics