sys.dm_exec_text_query_plan (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Devolve o Showplan em formato de texto para um lote Transact-SQL ou para uma instrução específica dentro do lote. O plano de consulta especificado pelo handle de plano pode ser armazenado em cache ou estar a ser executado atualmente. Esta função com valores de tabela é semelhante à sys.dm_exec_query_plan (Transact-SQL), mas apresenta as seguintes diferenças:

• A saída do plano de consulta é devolvida em formato de texto.
• A saída do plano de consulta não é limitada em tamanho.
• Extratos individuais dentro do lote podem ser especificados.

Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) e posteriores), Azure SQL Database.

Transact-SQL convenções de sintaxe

Sintaxe

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

Arguments

plan_handle
É um token que identifica de forma única um plano de execução de consulta para um lote que foi executado e cujo plano reside na cache do plano, ou que está atualmente a ser executado. plan_handle é varbinary(64).

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

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

statement_start_offset | 0 | PADRÃO
Indica, em bytes, a posição inicial da consulta que a linha descreve dentro do texto do seu objeto em lote ou persistido. statement_start_offset é inteligência. Um valor de 0 indica o início do lote. O valor padrão é 0.

O deslocamento inicial da instrução pode ser obtido a partir dos seguintes objetos de gestão dinâmica:

• sys.dm_exec_query_stats

• sys.dm_exec_requests

statement_end_offset | -1 | PADRÃO
Indica, em bytes, a posição final da consulta que a linha descreve dentro do texto do seu objeto em lote ou persistido.

statement_start_offset é inteligência.

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

Tabela retornada

Nome da coluna	Tipo de dados	Description
dbid	smallint	ID do banco de dados de contexto que estava em vigor quando a instrução Transact-SQL correspondente a este plano foi compilada. Para instruções SQL ad hoc e preparadas, a ID do banco de dados onde as instruções foram compiladas. A coluna é anulável.
Objectid	int	ID do objeto (por exemplo, procedimento armazenado ou função definida pelo usuário) para este plano de consulta. Para lotes ad hoc e preparados, esta coluna é nula. A coluna é anulável.
number	smallint	Inteiro do procedimento armazenado numerado. Por exemplo, um grupo de procedimentos para a aplicação de ordens pode ser chamado orderproc; 1, orderproc; 2, e assim sucessivamente. Para lotes ad hoc e preparados, esta coluna é nula. A coluna é anulável.
encriptado	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 Showplan em tempo de compilação do plano de execução da consulta especificada com plan_handle. O Showplan está em formato de texto. Um plano é gerado para cada lote que contém, por exemplo, instruções Transact-SQL ad hoc, chamadas de procedimento armazenado e chamadas de função definidas pelo usuário. A coluna é anulável.

Observações

Sob as seguintes condições, nenhuma saída do Showplan é devolvida na coluna do plano da tabela devolvida para sys.dm_exec_text_query_plan:

• Se o plano de consulta especificado usando plan_handle foi expulso da cache do plano, a coluna query_plan da tabela devolvida é nula. Por exemplo, esta condição pode ocorrer se houver um atraso temporal entre o momento em que a alavanca do plano foi capturada e a sua utilização 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 com mais de 8 KB. Os showplans para tais instruções não podem ser recuperados usando sys.dm_exec_text_query_plan porque não existem na cache.

• Se um batch Transact-SQL ou procedimento armazenado contiver uma chamada a uma função definida pelo utilizador ou a SQL dinâmico, por exemplo usando EXEC (string), o Showplan XML compilado para a função definida pelo utilizador não é incluído na tabela devolvida por sys.dm_exec_text_query_plan para o batch ou procedimento armazenado. Em vez disso, deve fazer uma chamada separada para sys.dm_exec_text_query_plan para a plan_handle que corresponde à função definida pelo utilizador.

Quando uma consulta ad hoc utiliza parametrizaçãosimples ou forçada, a coluna query_plan contém apenas o texto da instrução e não o plano real da consulta. Para devolver o plano da consulta, chame sys.dm_exec_text_query_plan para o handle do plano da consulta parametrizada preparada. Pode determinar se a consulta foi parametrizada referenciando a coluna sql da vista sys.syscacheobjects ou a coluna de texto da vista de gestão dinâmica sys.dm_exec_sql_text .

Permissions

Para executar sys.dm_exec_text_query_plan, um utilizador deve ser membro do papel fixo de servidor administrador do sistema ou ter a permissão VIEW SERVER STATE no servidor.

Permissões para SQL Server 2022 e posterior

Requer a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Examples

A. Recuperar o plano de consulta em cache para uma consulta Transact-SQL ou batch de decorrer lentamente

Se uma consulta Transact-SQL ou batch correr durante muito tempo numa ligação específica ao SQL Server, recupere o plano de execução dessa consulta ou lote para descobrir o que está a causar o atraso. O exemplo seguinte 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 seu servidor.

Primeiro, recupere o ID da sessão (SPID) do processo que está a executar a consulta ou lote usando o sp_who procedimento armazenado:

USE master;  
GO  
EXEC sp_who;  
GO  

O conjunto de resultados que é devolvido por sp_who indica que o ID da sessão é 54. Pode usar o ID da sessão com a sys.dm_exec_requests vista de gestão dinâmica para recuperar o handle do plano usando a seguinte consulta:

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

A tabela devolvida por sys.dm_exec_requests indica que o handle de plano para a consulta ou lote de execução lenta é 0x06000100A27E7C1FA821B10600. O exemplo seguinte devolve o plano de consulta para o handle de plano especificado e utiliza os valores predefinidos 0 e -1 para devolver todas as instruções da consulta ou lote.

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

B. Recuperar todos os planos de consulta do cache do plano

Para obter um instantâneo de todos os planos de consulta que residem na cache de planos, recupere os handles de planos de todos os planos de consulta na cache consultando a sys.dm_exec_cached_plans vista de gestão dinâmica. As maçanetas do plano são armazenadas na plan_handle coluna de sys.dm_exec_cached_plans. Depois, use o operador CROSS APPLY para passar os handles do plano da sys.dm_exec_text_query_plan seguinte forma. A saída do Showplan para cada plano atualmente na cache do plano encontra-se na query_plan coluna da tabela que é devolvida.

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. Recuperar todos os planos de consulta para os quais o servidor recolheu estatísticas de consulta a partir da cache do plano

Para obter um instantâneo de todos os planos de consulta para os quais o servidor recolheu estatísticas que atualmente residem na cache do plano, recupere os handles de planos desses planos na cache consultando a sys.dm_exec_query_stats vista de gestão dinâmica. As maçanetas do plano são armazenadas na plan_handle coluna de sys.dm_exec_query_stats. Depois, use o operador CROSS APPLY para passar os handles do plano da sys.dm_exec_text_query_plan seguinte forma. A saída do Showplan para cada plano está na query_plan coluna da tabela que é devolvida.

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. Recuperação de informação sobre as cinco principais consultas por tempo médio de CPU

O exemplo seguinte devolve os planos de consulta e o tempo médio de CPU para as cinco principais consultas. A função sys.dm_exec_text_query_plan especifica os valores padrão 0 e -1 devolver todas as instruções do 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  

Ver também

sys.dm_exec_query_plan (Transact-SQL)