sys.dm_exec_query_profiles (Transact-SQL)

Artigo
07/05/2024

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Monitora o progresso da consulta em tempo real, enquanto a consulta está em execução. Por exemplo, use este DMV para determinar que parte da consulta está executando lentamente. Adicione esse DMV com outros DMVs de sistema usando as colunas identificadas no campo de descrição. Ou, adicione esse DMV com outros contadores de desempenho (como o Monitor de Desempenho, xperf) usando colunas de carimbo de data/hora.

Tabela retornada

Os contadores retornados são por operador por thread. Os resultados são dinâmicos e não correspondem aos resultados das opções existentes, como SET STATISTICS XML ON as que só criam saída quando a consulta é concluída.

Nome da coluna	Tipo de dados	Descrição
session_id	smallint	Identifica a sessão na qual esta consulta é executada. Referencia dm_exec_sessions.session_id.
request_id	int	Identifica a solicitação de destino. Referencia dm_exec_sessions.request_id.
sql_handle	varbinary(64)	É um token que identifica exclusivamente o lote ou o procedimento armazenado de que a consulta faz parte. Referencia dm_exec_query_stats.sql_handle.
plan_handle	varbinary(64)	É um token que identifica exclusivamente um plano de execução de consulta de um lote que foi executado e o plano reside no cache de plano ou está em execução no momento. Referências dm_exec_query_stats.plan_handle.
physical_operator_name	nvarchar(256)	Nome do operador físico.
node_id	int	Identifica um nó do operador na árvore de consulta.
thread_id	int	Distingue os threads (para uma consulta paralela) que pertencem ao mesmo nó do operador de consulta.
task_address	varbinary(8)	Identifica a tarefa do sistema operacional SQL que esse thread está usando. Referencia dm_os_tasks.task_address.
row_count	bigint	Número de linhas retornadas pelo operador até o momento.
rewind_count	bigint	Número de retrocessos até o momento.
rebind_count	bigint	Número de reassociações até o momento.
end_of_scan_count	bigint	Número de término de exames até o momento.
estimate_row_count	bigint	Número estimado de linhas. Pode ser útil comparar estimated_row_count com o row_count real.
first_active_time	bigint	A hora, em milissegundos, em que operador foi chamado primeiro.
last_active_time	bigint	A hora, em milissegundos, em que operador foi chamado por último.
open_time	bigint	Carimbo de data/hora quando aberto (em milissegundos).
first_row_time	bigint	Carimbo de data/hora quando a primeira linha foi aberta (em milissegundos).
last_row_time	bigint	Carimbo de data/hora quando a última linha foi aberta (em milissegundos).
close_time	bigint	Carimbo de data/hora quando fechado (em milissegundos).
elapsed_time_ms	bigint	Tempo total decorrido (em milissegundos) usado pelas operações do nó de destino até o momento.
cpu_time_ms	bigint	Tempo total de CPU (em milissegundos) usado pelas operações do nó de destino até o momento.
database_id	smallint	ID do banco de dados que contém o objeto no qual as leituras e gravações estão sendo realizadas.
object_id	int	O identificador do objeto no qual as leituras e gravações estão sendo realizadas. Referências sys.objects.object_id.
index_id	int	O índice (se houver) no qual o conjunto de linhas é aberto.
scan_count	bigint	Número de verificações de tabela/índice até o momento.
logical_read_count	bigint	Número de leituras lógicas até o momento.
physical_read_count	bigint	Número de leituras físicas até o momento.
read_ahead_count	bigint	Número de read-aheads até o momento.
write_page_count	bigint	Número de gravações de página até o momento devido ao derramamento.
lob_logical_read_count	bigint	Número de leituras lógicas LOB até o momento.
lob_physical_read_count	bigint	Número de leituras físicas LOB até o momento.
lob_read_ahead_count	bigint	Número de read-aheads LOB até o momento.
segment_read_count	int	Número de read-aheads de segmento até o momento.
segment_skip_count	int	Número de segmentos ignorados até o momento.
actual_read_row_count	bigint	Número de linhas lidas por um operador antes da aplicação do predicado residual.
estimated_read_row_count	bigint	Aplica-se a: a partir do SQL Server 2016 (13.x) SP1. Número de linhas estimadas para serem lidas por um operador antes da aplicação do predicado residual.

Comentários gerais

Se o nó do plano de consulta não tiver nenhuma E/S, todos os contadores relacionados à E/S serão definidos como NULL.

Os contadores relacionados a E/S relatados por esse DMV são mais granulares do que os relatados pelas SET STATISTICS IO duas maneiras a seguir:

SET STATISTICS IO agrupa os contadores de todas as E/S de uma determinada tabela. Com essa DMV, você obterá contadores separados para cada nó no plano de consulta que executa E/S para a tabela.
Se houver uma varredura paralela, este DMV relata os contadores para cada um das threads paralelas que trabalham na varredura.

A partir do SQL Server 2016 (13.x) SP1, a infraestrutura de criação de perfil de estatísticas de execução de consulta padrão existe lado a lado com uma infraestrutura de criação de perfil de estatísticas de execução de consulta leve. SET STATISTICS XML ON e SET STATISTICS PROFILE ON sempre use a infraestrutura de criação de perfil de estatísticas de execução de consulta padrão. Para sys.dm_exec_query_profiles ser preenchida, uma das infraestruturas de criação de perfil de consulta deve estar habilitada. Para obter mais informações, confira Infraestrutura de Criação de Perfil de Consulta.

Observação

A consulta sob investigação deve ser iniciada após a ativação da infraestrutura de criação de perfil da consulta, habilitá-la após o início da consulta não produzirá resultados em sys.dm_exec_query_profiles. Para obter mais informações sobre como habilitar as infraestruturas de criação de perfil de consulta, consulte Infraestrutura de criação de perfil de consulta.

Permissões

No SQL Server e na Instância Gerenciada de SQL do Azure, requer VIEW DATABASE STATE permissão e associação da db_owner função de banco de dados.
Nas Camadas Premium do Banco de Dados SQL do Azure, requer a VIEW DATABASE STATE permissão no banco de dados.
Nos objetivos de serviço Básico, S0 e S1 do Banco de Dados SQL do Azure e para bancos de dados em pools elásticos, a conta de administrador do servidor ou a conta de administrador do Microsoft Entra é necessária. Em todos os outros objetivos de serviço do Banco de Dados SQL, a VIEW DATABASE STATE permissão é necessária no banco de dados.

Permissões do SQL Server 2022 e posteriores

Requer a permissão VIEW DATABASE PERFORMANCE STATE no banco de dados.

Exemplos

Etapa 1: Faça login em uma sessão na qual você planeja executar a consulta que analisará com sys.dm_exec_query_profileso . Para configurar a consulta para criação de perfil, use SET STATISTICS PROFILE ON. Execute a consulta nessa mesma sessão.

--Configure query for profiling with sys.dm_exec_query_profiles  
SET STATISTICS PROFILE ON;  
GO  

--Or enable query profiling globally under SQL Server 2016 SP1 or above (not needed in SQL Server 2019)  
DBCC TRACEON (7412, -1);  
GO 
  
--Next, run your query in this session, or in any other session if query profiling has been enabled globally

Etapa 2: Faça login em uma segunda sessão diferente da sessão em que sua consulta está sendo executada.

A instrução a seguir resume os progressos realizado pela consulta atualmente em execução na sessão 54. Para fazer isso, ela calcula o número total de linhas de saída de todos as threads para cada nó e o compara com o número estimado de linhas de saída para esse nó.

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT node_id,physical_operator_name, SUM(row_count) row_count, 
  SUM(estimate_row_count) AS estimate_row_count, 
  CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)  
FROM sys.dm_exec_query_profiles   
WHERE session_id=54
GROUP BY node_id,physical_operator_name  
ORDER BY node_id;

Confira também

Exibições e funções de gerenciamento dinâmico (Transact-SQL)
Funções e exibições de gerenciamento dinâmico relacionadas à execução (Transact-SQL)

Partilhar via