sp_server_diagnostics (Transact-SQL)

Artigo
03/11/2025

Aplica-se a:SQL Server

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

Transact-SQL convenções de sintaxe

Sintaxe

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

Argumentos

Importante

Os argumentos para procedimentos armazenados estendidos devem ser inseridos na ordem específica, conforme descrito na seção de sintaxe. Se os parâmetros forem inseridos fora de ordem, ocorrerá uma mensagem de erro.

[ @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âmetros válidos são 0, ou qualquer valor igual ou superior a 5. O procedimento armazenado deve ser executado pelo menos 5 segundos para retornar dados completos. O valor mínimo para que o procedimento armazenado seja executado 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, em seguida, 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 superior a 5, o procedimento armazenado será executado repetidamente para retornar o estado de integridade até que seja cancelado manualmente.

Valores de código de retorno

0 (sucesso) ou 1 (fracasso).

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 da criação de linhas. 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 do 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 de integridade do componente. Pode ser um dos seguintes valores: `0`, `1`, `2`ou `3`
`state_desc`	sysname	Descreve a coluna de estado. As 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 os dados específicos do componente.

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

sistema: Coleta dados de uma perspetiva do sistema sobre spinlocks, condições de processamento severas, tarefas não produtivas, falhas de página e uso da CPU. Esta informação produz uma recomendação geral do estado de saúde.
de recursos : Coleta dados de uma perspetiva de recursos sobre memória física e virtual, pools de buffers, páginas, cache e outros objetos de memória. Esta informação produz uma recomendação geral do estado de saúde.
query_processing: Coleta dados de uma perspetiva de processamento de consultas sobre os threads de trabalho, tarefas, tipos de espera, sessões intensivas de CPU e tarefas de bloqueio. Esta informação produz uma recomendação geral do estado de saúde.
io_subsystem: Recolhe dados sobre IO. Além dos dados de diagnóstico, esse componente produz um estado de integridade limpa ou de aviso apenas para um subsistema de E/S.
eventos: Coleta dados e superfícies através 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, falta de memória, monitor do agendador, pool de buffers, spinlocks, segurança e conectividade. Os eventos sempre mostram 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

Do ponto de vista da falha, os componentes system, resourcee query_processing são usados para deteção de falhas, enquanto os componentes io_subsystem 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	Limpo (1)	Advertência (2)	Erro (3)	Desconhecidos (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 é exibido como clean ou warning. Ele não mostra os estados de erro.

Observação

O procedimento interno sp_server_diagnostics é implementado em um thread preventivo com alta prioridade.

Permissões

Requer permissão VIEW SERVER STATE no servidor.

Exemplos

É uma prática recomendada 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.

Um. 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. Ler o registo de sessões de Eventos Alargados

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 para 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

Espera não preventiva:

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

Espera preventiva:

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 de 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

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

Partilhar via

sp_server_diagnostics (Transact-SQL)

Sintaxe

Argumentos

[ @repeat_interval = ] 'repeat_interval'

Valores de código de retorno

Conjunto de resultados

Comentários

Permissões

Exemplos

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

B. Ler o registo de sessões de Eventos Alargados

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

D. Leia a saída detalhada de cada componente

Comentários

Recursos adicionais

Partilhar via

sp_server_diagnostics (Transact-SQL)

Sintaxe

Argumentos

[ @repeat_interval = ] 'repeat_interval'

Valores de código de retorno

Conjunto de resultados

Comentários

Permissões

Exemplos

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

B. Ler o registo de sessões de Eventos Alargados

C. Capturar sp_server_diagnostics saída para uma tabela

D. Leia a saída detalhada de cada componente

Conteúdo relacionado

Comentários

Recursos adicionais

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