sys.dm_exec_text_query_plan (Transact-SQL)

  • Artigo

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

Retorna o plano de execução em formato de texto de um lote Transact-SQL ou de uma instrução específica dentro do lote. O plano de consulta especificado pelo identificador de plano pode ser armazenado em cache ou em execução no momento. Esta função com valor de tabela é semelhante a sys.dm_exec_query_plan (Transact-SQL), mas com as seguintes diferenças:

  • A saída do plano de consulta é retornada em formato de texto.
  • A saída do plano de consulta não é limitada em tamanho.
  • Instruções individuais podem ser especificadas dentro do lote.

Aplica-se a: SQL Server [SQL Server 2008 (10.0.x) e posterior] e Banco de Dados SQL do Azure.

Convenções de sintaxe de Transact-SQL

Sintaxe

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)

Argumentos

plan_handle
É 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. plan_handle é varbinary(64).

O plan_handle pode ser obtido dos seguintes objetos de gerenciamento dinâmico:

statement_start_offset | 0 | DEFAULT
Indica, em bytes, a posição inicial da consulta que a linha descreve dentro do texto de seu lote ou objeto persistente. statement_start_offset é int. O valor 0 indica o início do lote. O valor padrão é 0.

O deslocamento de início da instrução pode ser obtido dos seguintes objetos de gerenciamento dinâmico:

statement_end_offset | -1 | DEFAULT
Indica, em bytes, a posição final da consulta que a linha descreve dentro do texto de seu lote ou objeto pesistente.

statement_start_offset é int.

Um valor de -1 indica o fim do lote. O valor padrão é -1.

Tabela retornada

Nome da coluna Tipo de dados Descrição
dbid smallint A ID do banco de dados de contexto em vigor quando a instrução Transact-SQL correspondente a esse plano foi compilada. Para instruções SQL preparadas e ad hoc, a ID do banco de dados no qual as instruções foram compiladas.

A coluna é anulável.
objectid int A identificação do objeto (por exemplo, procedimento armazenado ou função definida pelo usuário) para este plano de consulta. Para lotes assistemáticos e preparados, essa coluna é nula.

A coluna é anulável.
number smallint Inteiro de procedimento armazenado numerado. Por exemplo, um grupo de procedimentos para o aplicativo orders pode ser denominado orderproc;1, orderproc;2 e assim por diante. Para lotes assistemáticos e preparados, essa coluna é nula.

A coluna é anulável.
encrypted bit Indica se o procedimento armazenado correspondente está criptografado.

0 = não criptografado

1 = criptografado

A coluna não é anulável.
query_plan nvarchar(max) Contém a representação do plano de execução em tempo de compilação do plano de execução de consulta especificado com plan_handle. O Showplan está em formato de texto. 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.

A coluna é anulável.

Comentários

Nas seguintes condições, nenhuma saída do Plano de Execução é retornada na coluna do plano da tabela retornada para sys.dm_exec_text_query_plan:

  • Se o plano de consulta especificado usando plan_handle for retirado do cache de planos, a coluna query_plan da tabela retornada será nula. Por exemplo, essa condição pode ocorrer se houver um atraso de tempo entre o momento em que o identificador de plano foi capturado e quando ele foi usado com sys.dm_exec_text_query_plan.

  • Algumas instruções Transact-SQL não são armazenadas em cache, como instruções de operação em massa ou instruções que contêm literais de cadeia de caracteres com mais de 8 KB. Os planos de execução para essas instruções não podem ser recuperados usando sys.dm_exec_text_query_plan porque eles não existem no cache.

  • Se um lote ou procedimento armazenado Transact-SQL contiver uma chamada a uma função definida pelo usuário ou uma chamada para o SQL dinâmico, usando EXEC (cadeia de caracteres) por exemplo, o plano de execução XML compilado para a função definida pelo usuário não será incluído na tabela retornada por sys.dm_exec_text_query_plan para o lote ou o procedimento armazenado. Nesse caso, você precisa fazer uma chamada separada para sys.dm_exec_text_query_plan do plan_handle que corresponde à função definida pelo usuário.

Quando uma consulta ad hoc usar parametrização simples ou forçada, a coluna query_plan conterá somente o texto de instrução e não o plano de consulta real. Para retornar o plano de consulta, chame sys.dm_exec_text_query_plan para o identificador de plano da consulta parametrizada preparada. Você pode determinar se a consulta foi parametrizada referenciando a coluna sql da exibição sys.syscacheobjects ou a coluna de texto da exibição de gerenciamento dinâmico sys.dm_exec_sql_text .

Permissões

Para executar sys.dm_exec_text_query_plan, um usuário deve ser membro da função de servidor fixa sysadmin ou ter a permissão VIEW SERVER STATE no servidor.

Permissões do SQL Server 2022 e posteriores

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

Exemplos

R. Recuperando o plano de consulta em cache para uma consulta ou lote Transact-SQL de execução lenta

Se uma consulta ou um lote Transact-SQL for executado por muito tempo em uma determinada conexão com o SQL Server, recupere o plano de execução dessa consulta ou lote para descobrir o que está causando o atraso. O exemplo a seguir mostra como recuperar o Showplan para uma consulta ou lote de execução lenta.

Observação

Para executar este exemplo, substitua os valores de session_id e plan_handle por valores específicos do servidor.

Primeiramente, recupere a identificação de processo do servidor (SPID) para o processo que está executando a consulta ou lote usando o procedimento armazenado sp_who:

USE master;  
GO  
EXEC sp_who;  
GO

O conjunto de resultados retornado por sp_who indica que o SPID é 54anos. Você pode usar o SPID com a exibição de gerenciamento dinâmico sys.dm_exec_requests para recuperar o identificador de plano, por meio da seguinte consulta:

USE master;  
GO  
SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO

A tabela retornada por sys.dm_exec_requests indica que o identificador de plano para a consulta ou lote de execução lenta é 0x06000100A27E7C1FA821B10600. O exemplo a seguir retorna o plano de consulta para o identificador de plano especificado e usa os valores padrão 0 e -1 para retornar todas as instruções na consulta ou lote.

USE master;  
GO  
SELECT query_plan   
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);  
GO

B. Recuperando todo o plano de consulta do cache de plano

Para recuperar um instantâneo de todos os planos de consulta residindo no cache de plano, recupere os identificadores de plano de todas as consultas no cachê, consultando a exibição de gerenciamento dinâmico sys.dm_exec_cached_plans. Os identificadores de plano são armazenados na coluna plan_handle de sys.dm_exec_cached_plans. Em seguida, use o operador CROSS APPLY para transmitir o identificador de plano a sys.dm_exec_text_query_plan, como se segue. A saída do plano de execução de cada plano que está no cache de planos no momento, fica na coluna query_plan da tabela retornada.

USE master;  
GO  
SELECT *   
FROM sys.dm_exec_cached_plans AS cp   
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);  
GO

C. Recuperando todo plano de consulta para o qual o servidor coletou estatísticas de consulta do cache de plano

Para recuperar um instantâneo de todos os planos de consulta para os quais o servidor reuniu estatísticas que residem atualmente no cache de plano, recupere os identificadores desses planos no cache consultando a exibição de gerenciamento dinâmico sys.dm_exec_query_stats. Os identificadores de plano são armazenados na coluna plan_handle de sys.dm_exec_query_stats. Em seguida, use o operador CROSS APPLY para transmitir o identificador de plano a sys.dm_exec_text_query_plan, como se segue. A saída de plano de execução de cada plano está na coluna query_plan da tabela retornada.

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
GO

D. Recuperando informações sobre as cinco principais consultas por tempo médio de CPU

O exemplo a seguir retorna os planos de consulta e o tempo médio de CPU das cinco principais consultas. A função sys.dm_exec_text_query_plan especifica os valores padrão 0 e -1 para retornar todas as instruções no lote no plano de consulta.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
Plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1)  
ORDER BY total_worker_time/execution_count DESC;  
GO

Confira também

sys.dm_exec_query_plan (Transact-SQL)