sys.dm_exec_query_plan (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstâ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 disponível neste site da Microsoft. Ele também está disponível no diretório em que 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 ad hoc e preparados, essa coluna é null. A coluna é anulável. |
| number | smallint | Inteiro de procedimento armazenado numerado. Por exemplo, um grupo de procedimentos para o aplicativo de pedidos pode ser nomeado orderproc;1, orderproc;2 e assim por diante. Para lotes ad hoc e preparados, essa coluna é null. 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
Sob as seguintes condições, nenhuma saída Showplan é 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 de captura e de utilização do identificador de plano 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. Showplans XML para tais instruções não podem ser recuperados usando sys.dm_exec_text_query_plan a menos que o lote esteja em execução, atualmente, porque eles não existem no cache.
Se um lote ou procedimento armazenado transact-SQL 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 por 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á somente o texto de instrução e não o plano de consulta real. Para retornar ao plano de consulta, chame sys.dm_exec_query_plan para o identificador do 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 impediu que o plano de consulta retornasse e gerasse o erro 6335. Em 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 no 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 para SQL Server 2022 e posteriores
Requer 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 seguintes consultas no Editor de Consultas de SQL Server Management Studio e clique em ShowPlanXML na coluna query_plan da tabela retornada por sys.dm_exec_query_plan. O Plano de Exibiçã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.
a. 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 plano. 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 de execução lenta ou lote é 0x06000100A27E7C1FA821B10600, que você pode especificar como o argumento plan_handle com sys.dm_exec_query_plan para recuperar o plano de execução no formato XML da seguinte maneira. O plano de execução em formato XML para a consulta ou lote em execução lenta é 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
Consulte 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)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de