sys.dm_exec_query_plan (Transact-SQL)

Artigo
06/18/2013

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 do Plano de Execução está publicado e disponível neste site da Microsoft. Ele fica disponível também no diretório em que o SQL Server é instalado.

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

sys.dm_exec_query_plan ( plan_handle )

Argumentos

plan_handle
Identifica exclusivamente um plano de consulta para um lote em cache ou sendo executado atualmente.

plan_handle é varbinary(64). plan_handle pode ser obtido nos seguintes objetos de gerenciamento dinâmico:

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

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 de Showplan 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 procedimento armazenado 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 foi retirado do cache do plano, 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 colocadas em cache, como instruções de operação em massa ou instruções que contêm literais de cadeia de caracteres maiores que 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 Transact-SQL ou procedimento armazenado contém uma chamada a uma função definida pelo usuário ou uma chamada para SQL dinâmico, usando EXEC (string), por exemplo, o XML Showplan 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 ligação separada para sys.dm_exec_text_query_plan para o identificador de plano que corresponde à função definida pelo usuário.

Quando uma consulta ad hoc usa a parametrização simples ou forçada, a coluna query_plan conterá somente o texto da 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.

Devido a uma limitação no número de níveis aninhados permitida no tipo de dados xml, sys.dm_exec_query_plan não pode retornar planos de consulta que se encontram ou excedem 128 níveis de elementos aninhados. Em versões anteriores do SQL Server, esta condição evitava que o plano de consulta retornasse e gerasse um erro 6335. No SQL Server 2005 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 permissão VIEW SERVER STATE no servidor.

Exemplos

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

Para visualizar os Showplans 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 por 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 e 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

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 colocados em cache em uma área da memória denominada 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 para uma consulta ou lote Transact-SQL em particular.

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

Observação
Executar este exemplo, substitua os valores para session_id e plan_handle por valores específicos de seu 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 que é retornada por sys.dm_exec_requests indica que o identificador de plano para a consulta ou lote em execução lenta é 0x06000100A27E7C1FA821B10600, que você pode especificar como o argumento plan_handle com sys.dm_exec_query_plan para recuperar o plano de execução em formato XML, como se segue. 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 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 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