sys.dm_exec_text_query_plan (Transact-SQL)

Sintaxe

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

Argumentos

• plan_handle
  Identifica exclusivamente um plano de consulta para um lote em cache ou sendo executado atualmente. plan_handle é varbinary(64).

  O identificador de plano pode ser obtido dos seguintes objetos de gerenciamento dinâmico:

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

• statement_start_offset | 0 | DEFAULT
  Indica, em bytes, a posição inicial da consulta que a linha descreve dentro do texto de seu lote ou objeto persistente. statement_start_offset é int. Um valor de 0 indica o começo do lote. O valor padrão é 0.

  O deslocamento de início da instrução pode ser obtido dos seguintes objetos de gerenciamento dinâmico:

  sys.dm_exec_query_stats

  sys.dm_exec_requests

• statement_end_offset | -1 | DEFAULT
  Indica, em bytes, a posição final da consulta que a linha descreve dentro do texto de seu lote ou objeto pesistente.

  statement_start_offset is int.

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

Tabela retornada

Comentários

Sob as seguintes condições, nenhuma saída Showplan é retornada na coluna de plano da tabela retornada para sys.dm_exec_text_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 quando o identificador de plano foi captado e quando foi usado com sys.dm_exec_text_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 para tais instruções não podem ser recuperados usando sys.dm_exec_text_query_plan 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_text_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 plan_handle 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_text_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çãosys.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.

Permissões

Para executar sys.dm_exec_text_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

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

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 Showplan 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 é 54. Você pode usar o SPID com a exibição de gerenciamento dinâmico sys.dm_exec_requests para recuperar o identificador do plano usando a 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 é 0x06000100A27E7C1FA821B10600. O exemplo a seguir retorna o plano de consulta para o identificador de plano especificado e usa os valores padrão 0 e -1 para retornar todas as instruções na consulta ou lote.

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

B. Recuperar todo o plano de consulta do cache de plano

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 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 AS qs 
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
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_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO

Consulte também

Referência

Conceitos

Outros recursos