sp_server_diagnostics (Transact-SQL)

Artigo
04/10/2024

Aplica-se a:SQL Server

Captura dados de diagnóstico e informações de integridade sobre o SQL Server para detectar possíveis falhas. O procedimento é executado no modo de repetição e envia resultados periodicamente. Ele pode ser invocado a partir de uma conexão regular ou de uma conexão de administrador dedicada.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

@repeat_interval [ = ] 'repeat_interval'

Indica o intervalo de tempo no qual o procedimento armazenado é executado repetidamente para enviar informações de integridade.

@repeat_interval é int com o padrão de 0. Os valores de parâmetro válidos são 0, ou qualquer valor igual ou superior 5a . 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 sairá.

Se o valor especificado for menor que o valor mínimo, ele 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 cancelado manualmente.

Valores do código de retorno

0 (sucesso) ou 1 (falha).

Conjunto de resultados

sp_server_diagnostics retorna as seguintes informações.

Coluna	Tipo de dados	Descrição
`create_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 Always On: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Indica o nome de componente ou o nome do grupo de disponibilidade: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Indica o status da integridade do componente. Pode ser um dos seguintes valores: `0`, `1`, `2`ou `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`
`data`	varchar (máx.)	Especifica dados que são específicos do componente.

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

sistema: Coleta dados de uma perspectiva do sistema sobre spinlocks, condições severas de processamento, tarefas que não geram rendimento, falhas de página e uso da CPU. Essas informações produzem uma recomendação geral de estado de saúde.
resource: coleta dados de uma perspectiva de recurso sobre memória física e virtual, pools de buffer, páginas, cache e outros objetos de memória. Essas informações produzem uma recomendação geral de estado de saúde.
query_processing: Coleta dados de uma perspectiva de processamento de consulta sobre os threads de trabalho, tarefas, tipos de espera, sessões intensivas de CPU e tarefas de bloqueio. Essas informações produzem uma recomendação geral de estado de saúde.
io_subsystem: Coleta dados sobre E/S. 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.
eventos: Coleta dados e superfícies por meio do procedimento armazenado sobre os erros e eventos de interesse 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 do agendador, pool de buffers, spinlocks, segurança e conectividade. Os eventos sempre aparecem 0 como o estado.
<nome do grupo> de disponibilidade: coleta dados para o grupo de disponibilidade especificado (se component_type = "Always On:AvailabilityGroup").

Comentários

De uma perspectiva de falha, os systemcomponentes , resourcee query_processing são usados para detecção de falhas, enquanto os io_subsystem componentes e events são usados 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)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

O x em cada linha representa estados de integridade válidos para o componente. Por exemplo, io_subsystem mostra como clean ou warning. Ele não mostra os estados de erro.

Observação

O sp_server_diagnostics procedimento interno é implementado em um segmento preventivo de alta prioridade.

Permissões

Requer a permissão VIEW SERVER STATE no servidor.

Exemplos

É recomendável usar sessões de Eventos Estendidos para capturar as informações de integridade e salvá-las em um arquivo localizado fora do SQL Server. Portanto, você ainda pode acessá-lo se houver uma falha.

R. Salvar a saída de uma sessão de Eventos Estendidos em um arquivo

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

B. Leia o log da sessão de Eventos Estendidos

A consulta a seguir lê o arquivo de log de sessão de Eventos Estendidos no 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. Capturar `sp_server_diagnostics` saída para uma tabela

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

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;

A consulta a seguir lê a saída de resumo da tabela de exemplo:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Leia a saída detalhada de cada componente

As consultas de exemplo a seguir leem algumas das saídas detalhadas de cada componente, na tabela criada no exemplo anterior.

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

Monitor de Recursos:

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

Esperas não preemptivas:

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

Aguarda preventivamente:

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

Solicitações intensivas da 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

Relatório de processo bloqueado:

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

Entrada/saída:

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

Informações do evento:

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

Failover Policy for Failover Cluster Instances

sp_server_diagnostics (Transact-SQL)

Sintaxe

Argumentos

@repeat_interval [ = ] 'repeat_interval'

Valores do código de retorno

Conjunto de resultados

Comentários

Permissões

Exemplos

R. Salvar a saída de uma sessão de Eventos Estendidos em um arquivo

B. Leia o log da sessão de Eventos Estendidos

C. Capturar sp_server_diagnostics saída para uma tabela

D. Leia a saída detalhada de cada componente

Conteúdo relacionado

Comentários

Comentários

Recursos adicionais

C. Capturar `sp_server_diagnostics` saída para uma tabela