sp_server_diagnostics (Transact-SQL)

Artigo
03/07/2014

Captura dados de diagnóstico e informações de integridade sobre o SQL Server para detectar falhas potenciais. O procedimento é executado em modo de repetição e envia resultados periodicamente. Pode ser invocado a partir de uma conexão comum ou DAC.

Aplica-se a: SQL Server (SQL Server 2012 à versão atual).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

Argumentos

[ @repeat_interval =] 'repeat_interval_in_seconds'
Indica o intervalo de tempo no qual o procedimento armazenado será executado repetidamente para enviar informações de integridade.

repeat_interval_in_seconds é int com o padrão de 0. Os valores de parâmetros válidos são 0 ou qualquer valor igual ou superior a 5. O procedimento armazenado deve ser executado por pelo menos 5 segundos para retornar dados completos. O valor mínimo de execução do procedimento armazenado no modo de repetição é de 5 segundos.

Se esse parâmetro não for especificado ou se o valor especificado for 0, o procedimento armazenado retornará dados uma vez e depois será encerrado.

Se o valor especificado for menor do que o valor mínimo, isso gerará um erro e não retornará nada.

Se o valor especificado for igual ou maior que 5, o procedimento armazenado será executado repetidamente para retornar o estado de integridade até que seja manualmente cancelado.

Valores de código de retorno

0 (êxito) ou 1 (falha)

Conjuntos de resultados

sp_server_diagnostics retorna as informações seguintes

Coluna	Tipo de dados	Descrição
creation_time	datetime	Indica o carimbo de data/hora de criação de linha. Cada linha em um único conjunto de linhas tem o mesmo carimbo de data/hora.
component_type	sysname	Indica se a linha contém informações para o componente de nível de instância do SQL Server ou para um grupo de disponibilidade AlwaysOn: instância alwaysOn:AvailabilityGroup
component_name	sysname	Indica o nome de componente ou o nome do grupo de disponibilidade: sistema recurso query_processing io_subsystem eventos <name of the availability group>
estado	int	Indica o status de integridade do componente: 0 1 2 3
state_desc	sysname	Descreve a coluna de estado. Descrições que correspondem aos valores na coluna de estado são: 0:Unknown 1:clean 2:warning 3:error
dados	varchar (max)	Especifica dados que são específicos do componente.

Aqui estão as descrições dos cinco componentes:

system: coleta dados de uma perspectiva de sistema em spinlocks, condições de processamento severas, tarefas não responsivas, falhas de página e uso de CPU. Essas informações produzem uma recomendação de estado de integridade geral.
resource: coleta dados de uma perspectiva de recurso na memória física e virtual, pools de buffers, páginas, cache e outros objetos de memória. Essas informações produzem uma recomendação de estado de integridade geral.
query_processing: coleta dados de uma perspectiva de processamento de consulta nos threads de trabalho, tarefas, tipos de espera, sessões intensivas de CPU e tarefas de bloqueio. Essas informações produzem uma recomendação de estado de integridade geral.
io_subsystem: coleta dados em IO. Além dos dados de diagnóstico, esse componente produz um estado de integridade limpo ou de integridade de aviso somente para um subsistema de IO.
events: coleta dados e superfícies por meio do procedimento armazenado nos erros e eventos interessantes registrados pelo servidor, incluindo detalhes sobre exceções de buffer de anel, eventos de buffer de anel sobre agente de memória, memória insuficiente, monitor de agendador, pool de buffer, spinlocks, segurança e conectividade. Eventos sempre mostrarão 0 como o estado.
<name of the availability group>: coleta dados para o grupo de disponibilidade especificado (se component_type = "alwaysOn:AvailabilityGroup").

Comentários

De uma perspectiva de falha, os componentes system, resource e query_processing serão aproveitados para detecção de falha, enquanto os componentes io_subsystem e eventos serão aproveitados apenas para fins de diagnóstico.

A tabela a seguir mapeia os componentes para seus estados de integridade associados.

Componentes	Clean (1)	Warning (2)	Erro (3)	Unknowns (0)
sistema	x	x	x
recurso	x	x	x
query_processing	x	x	x
io_subsystem	x	x
eventos				x

O (x) em cada linha representa estados de integridade válida para o componente. Por exemplo, io_subsystem mostrará como clean ou warning. Não mostrará os estados de erro.

Permissões

Requer a permissão VIEW SERVER STATE no servidor.

Exemplos

É prática recomendada usar sessões estendidas para capturar as informações de integridade e salvá-las em um arquivo localizado fora do SQL Server. Portanto, ainda será possível acessar isso em caso de falha. O exemplo a seguir salva a saída de uma sessão de evento em um arquivo:

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

A consulta de exemplo abaixo lê o arquivo de log de sessão estendida:

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\MSSQL12.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) 
AS XEventData
ORDER BY time;

O exemplo a seguir captura a saída de sp_server_diagnostics para uma tabela em um modo de não repetição:

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;

Consulte também

Conceitos

Política de failover para instâncias de cluster de failover

Compartilhar via