sys.dm_exec_query_plan (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 dos seguintes objetos de gerenciamento dinâmico:

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

Tabela retornada

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 ad hoc consulta 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 com parâmetros 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. Para obter mais informações sobre a parametrização, consulte Parametrização simples e Parametrização forçada.

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

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

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

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

Referência

Conceitos

Outros recursos