sp_server_diagnostics (Transact-SQL)
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.
.gif "Ícone de vínculo de tópico")
Convenções de 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:
|
component_name |
Sysname |
Indica o nome de componente ou o nome do grupo de disponibilidade:
|
estado |
Int |
Indica o status de integridade do componente:
|
state_desc |
Sysname |
Descreve a coluna de estado. Descrições que correspondem aos valores na coluna de estado são:
|
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\MSSQL11.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