Partilhar via

sys.dm_exec_query_plan (Transact-SQL)

  • Artigo

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

Retorna o plano de execução em formato XML para o lote especificado pelo identificador de plano. O plano especificado pelo identificador do plano pode estar em cache ou estar sendo executado.

O esquema XML para o Plano de Execução é publicado e está disponível neste site da Microsoft. Ele também está disponível no diretório em que o SQL Server está instalado.

Convenções de sintaxe de Transact-SQL

Sintaxe

sys.dm_exec_query_plan(plan_handle)

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:

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 xml 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 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.

A coluna é anulável.

Comentários

Nas seguintes condições, nenhuma saída do Plano de Execução é retornada na coluna query_plan da tabela retornada para sys.dm_exec_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 do plano foi capturado e o momento em que foi usado com sys.dm_exec_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 XML para essas instruções não podem ser recuperados usando sys.dm_exec_query_plan a menos que o lote esteja em execução no momento, pois eles não existem no cache.

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

Quando uma consulta ad hoc usa parametrização simples ou forçada, a coluna query_plan conterá apenas o texto da instrução e não o plano de consulta real. Para retornar o plano de consulta, chame sys.dm_exec_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 .

Observação

Devido a uma limitação no número de níveis aninhados permitidos no tipo de dados xml , sys.dm_exec_query_plan não pode retornar planos de consulta que atendam ou excedam 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.
Você pode usar a função de gerenciamento dinâmico sys.dm_exec_text_query_plan (Transact-SQL) para retornar a saída do plano de consulta em formato de texto.

Permissões

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

Permissões do SQL Server 2022 e posteriores

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

Exemplos

Os exemplos a seguir mostram como usar a exibição de gerenciamento dinâmico sys.dm_exec_query_plan .

Para exibir os Planos de Execução XML, execute as consultas a seguir no Editor de Consultas do SQL Server Management Studio e clique em ShowPlanXML na coluna query_plan da tabela retornada pelo sys.dm_exec_query_plan. O Plano de Execução XML é exibido no painel de resumo do Management Studio. Para salvar o Plano de Execução XML em um arquivo, clique com o botão direito do mouse em ShowPlanXML na coluna query_plan, clique em Salvar Resultados Como, nomeie o arquivo no formato< file_name.sqlplan>; por exemplo, MyXMLShowplan.sqlplan.

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

Os planos de consulta para vários tipos de lotes Transact-SQL, como lotes ad hoc, procedimentos armazenados e funções definidas pelo usuário, são armazenados em cache em uma área de memória chamada cache de planos. Cada plano de consulta em cache é identificado por um identificador exclusivo chamado de identificador de plano. Você pode especificar esse identificador de plano com a exibição de gerenciamento dinâmico sys.dm_exec_query_plan para recuperar o plano de execução de uma consulta ou lote Transact-SQL específico.

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 plano de execução XML 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, que você pode especificar como o argumento plan_handle para sys.dm_exec_query_plan recuperar o plano de execução no formato XML da seguinte maneira. O plano de execução no formato XML para a consulta ou lote de execução lenta está contido na coluna query_plan da tabela retornada por sys.dm_exec_query_plan.

USE master;  
GO  
SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO

B. Recuperar 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_query_plan, como se segue. A saída de plano de execução XML de cada plano atualmente no cache de plano está na coluna query_plan da tabela retornada.

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

C. Recuperar todo plano de consulta para o qual o servidor reuniu 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_query_plan, como se segue. O plano de execução XML produzido para cada plano para o qual o servidor reuniu estatísticas atualmente no cache de 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_query_plan(qs.plan_handle);  
GO

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

O exemplo a seguir retorna os planos e o tempo médio de CPU das cinco principais consultas.

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_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO

Confira também

Exibições e funções de gerenciamento dinâmico (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
Referência de operadores físicos e lógicos de plano de execução
sys.dm_exec_text_query_plan (Transact-SQL)