sys.dm_exec_query_statistics_xml (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Retorna o plano de execução da consulta para solicitações em andamento. Use essa DMV para recuperar o XML do plano de execução com estatísticas transitórias.

Sintaxe

sys.dm_exec_query_statistics_xml(session_id)

Argumentos

session_id
É a ID da sessão que está executando o lote a ser pesquisado. session_id é smallint. session_id pode ser obtido nos seguintes objetos de gerenciamento dinâmico:

Tabela retornada

Nome da Coluna Tipo de Dados Descrição
session_id smallint ID da sessão. Não permite valor nulo.
request_id int ID da solicitação. Não permite valor nulo.
sql_handle varbinary(64) É um token que identifica exclusivamente o lote ou o procedimento armazenado de que a consulta faz parte. Anulável.
plan_handle varbinary(64) É um token que identifica exclusivamente um plano de execução de consulta para um lote em execução no momento. Anulável.
query_plan xml Contém a representação Showplan em tempo de execução do plano de execução da consulta que é especificado com plan_handle contendo estatísticas parciais. O Showplan está em formato XML. Um plano é gerado para cada lote que contém. Por exemplo, instruções ad hoc Transact-SQL, chamadas de procedimentos armazenados e chamadas de função definidas pelo usuário. Anulável.

Comentários

Importante

Devido a uma possível violação de acesso aleatório (AV) durante a execução de um procedimento armazenado de monitoramento com a DMV sys.dm_exec_query_statistics_xml, o atributo XML Showplan <ParameterList> de valor ParameterRuntimeValue foi removido no SQL Server 2017 (14.x) CU 26 e no SQL Server 2019 (15.x) CU 12. Esse valor pode ser útil na solução de problemas de procedimentos armazenados de execução prolongada.

A partir do SQL Server 2017 (14.x) CU 31 e do SQL Server 2019 (15.x) CU 19, a coleta do atributo XML Showplan <ParameterList> de valor ParameterRuntimeValue foi reabilitada com a inclusão do sinalizador de rastreamento 2446. Esse sinalizador de rastreamento permite a coleta do valor do parâmetro de tempo de execução às custas de introduzir uma sobrecarga adicional.

Aviso

O Sinalizador de Rastreamento 2446 não deve ser habilitado continuamente em um ambiente de produção, mas apenas para fins de solução de problemas com tempo limitado. O uso desse sinalizador de rastreamento introduzirá uma sobrecarga adicional e possivelmente significativa de CPU e memória, pois criaremos um fragmento XML do Plano de Execução com informações de parâmetro de runtime, quer a DMV do sys.dm_exec_query_statistics_xml seja chamada ou não.

Observação

A partir do SQL Server 2022 (16.x), do Banco de Dados SQL do Azure e da Instância Gerenciada de SQL do Azure, para fazer isso no nível do banco de dados, consulte a opção FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION em ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Essa função do sistema está disponível a partir do SQL Server 2016 (13.x) SP1. Consulte o artigo 3190871 da base de dados de conhecimento

Essa função do sistema funciona com as infraestruturas de criação de perfil de estatísticas de execução de consultas padrão e leve. Para obter mais informações, confira Infraestrutura de Criação de Perfil de Consulta.

Nas condições a seguir, nenhuma saída de Showplan é retornada na coluna query_plan da tabela retornada para sys.dm_exec_query_statistics_xml:

  • Se o plano de consulta que corresponde a session_id especificado não estiver mais sendo executado, a coluna query_plan da tabela retornada será nula. Por exemplo, essa condição poderá ocorrer se houver um atraso entre o momento em que o identificador do plano foi capturado e quando foi usado com sys.dm_exec_query_statistics_xml.

Devido a uma limitação no número de níveis aninhados permitidos no tipo de dados xml, sys.dm_exec_query_statistics_xml não pode retornar planos de consulta iguais ou superiores a 128 níveis de elementos aninhados. Em versões anteriores do SQL Server, essa condição impedia o retorno do plano de consulta e gerava o erro 6335. No SQL Server 2005 (9.x) Service Pack 2 e versões posteriores, a coluna query_plan retorna NULL.

Permissões

No SQL Server, requer a permissão VIEW SERVER STATE no servidor.
Nas camadas Premium do Banco de Dados SQL, a permissão VIEW DATABASE STATE é necessária no banco de dados. Nas camadas Standard e Basic do Banco de Dados SQL, é necessária uma conta de Administrador do servidor ou uma conta de administrador do Microsoft Entra.

Permissões do SQL Server 2022 e posteriores

É necessária a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Exemplos

R. Analisando o plano de consulta em tempo real e as estatísticas de execução de um lote em execução

O exemplo a seguir consulta sys.dm_exec_requests para localizar a consulta interessante e copia o endereço session_id da saída.

SELECT * FROM sys.dm_exec_requests;  
GO

Em seguida, para obter o plano de consulta em tempo real e as estatísticas de execução, use o session_id copiado com a função do sistema sys.dm_exec_query_statistics_xml.

--Run this in a different session than the session in which your query is running.
SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);  
GO

Ou combinado para todas as solicitações em andamento.

--Run this in a different session than the session in which your query is running.
SELECT 
	eqs.query_plan, 
	er.session_id, 
	er.request_id, 
	er.database_id,
	er.start_time,
	er.[status], 
	er.wait_type,
	er.wait_resource, 
	er.last_wait_type,
	(er.cpu_time/1000) AS cpu_time_sec,
	(er.total_elapsed_time/1000)/60 AS elapsed_time_minutes,
	(er.logical_reads*8)/1024 AS logical_reads_KB,
	er.granted_query_memory,
	er.dop,
	er.row_count, 
	er.query_hash, 
	er.query_plan_hash
FROM sys.dm_exec_requests er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO

Confira também

Sinalizadores de rastreamento
Exibições e funções de gerenciamento dinâmico (Transact-SQL)
Exibições de gerenciamento dinâmico relacionadas a bancos de dados (Transact-SQL)