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.

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

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.

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

Vedere anche

Concetti

Criteri di failover per istanze del cluster di failover