VENDAS: 1-800-867-1389
Expandir Minimizar

sys.dm_exec_text_query_plan

Atualizado: abril de 2011

Este tópico está DESATUALIZADO. Você pode encontrar a versão mais atual em Referência de Transact-SQL do SQL 14.

ImportantImportante
Este tópico não está atualizado. Para obter a versão atual, consulte sys.dm_exec_text_query_plan.

Retorna o plano de execução em formato de texto para um lote Transact-SQL ou para uma instrução específica dentro do lote. O plano de consulta especificado pelo identificador do plano pode estar em cache ou estar sendo executado. Esta função com valor de tabela é semelhante a sys.dm_exec_query_plan, mas com as seguintes diferenças:

  • A saída do plano de consulta é retornada em formato de texto.

  • A saída do plano de consulta não é limitada em tamanho.

  • Instruções individuais podem ser especificadas dentro do lote.

Convenções de sintaxe Transact-SQL


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

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.

 

Nome da coluna Tipo de dados Descrição

dbid

smallint

O ID do banco de dados de contexto em vigor quando a instrução Transact-SQL correspondente a este plano foi compilada. Para instruções SQL preparadas e ad hoc, o ID do banco de dados onde 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

nvarchar(max)

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 de texto. 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.

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

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.

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.

noteObservaçã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

Para recuperar um instantâneo de todos os planos de consulta que residem no cache de plano, recupere os identificadores de todos os planos de consulta no cache 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. Então, use o operador CROSS APPLY para passar o identificador de plano para sys.dm_exec_text_query_plan como segue. A saída de plano de execução 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_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO

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. Então, use o operador CROSS APPLY para passar o identificador de plano para sys.dm_exec_text_query_plan como segue. A saída de plano de execução de cada 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_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
GO

O exemplo a seguir retorna os planos de consulta e o tempo médio de CPU das cinco principais consultas. A função sys.dm_exec_text_query_plan especifica os valores padrão 0 e -1 para retornar todas as instruções no lote no plano de consulta.

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

Isso foi útil para você?
(1500 caracteres restantes)
Agradecemos os seus comentários
A Microsoft está realizando uma pesquisa online para saber sua opinião sobre o site do MSDN. Se você optar por participar, a pesquisa online lhe será apresentada quando você sair do site do MSDN.

Deseja participar?
Mostrar:
© 2014 Microsoft