sys.dm_exec_query_plan (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

A sys.dm_exec_query_plan função de gerenciamento dinâmico retorna o Showplan em formato XML para o lote especificado pelo handle do 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 está disponível neste site da Microsoft. Ele também está disponível no diretório em que o 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 para um lote que foi executado e seu plano reside no cache de planos ou que está sendo executado no momento. plan_handle é varbinário(64).

O plan_handle pode ser obtido a partir dos seguintes objetos de gerenciamento dinâmico:

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

Tabela retornada

Observações

Nas seguintes condições, nenhuma saída de plano de execução é retornada na query_plan coluna da tabela retornada para sys.dm_exec_query_plan:

• Se o plano de consulta especificado por usar plan_handle foi expulso do cache do plano, a query_plan coluna da tabela retornada é NULL. Por exemplo, essa condição pode ocorrer se houver um atraso de tempo entre o momento em que a alça do plano foi capturada e quando foi usada 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. Os Showplans XML para tais instruções não podem ser recuperados usando sys.dm_exec_query_plan a menos que o lote esteja atualmente em execução, pois elas não existem no cache.

• Se um batch Transact-SQL ou procedimento armazenado contiver uma chamada para uma função definida pelo usuário ou para SQL dinâmico, por exemplo usando EXEC (string), o Showplan XML compilado para a função definida pelo usuário não é incluído na tabela retornada por sys.dm_exec_query_plan para o batch ou procedimento armazenado. Em vez disso, você deve fazer uma chamada separada para sys.dm_exec_query_plan para o handle do plano que corresponde à função definida pelo usuário.

Quando uma consulta ad hoc usa parametrização simples ou forçada, a query_plan coluna contém apenas o texto da instrução e não o plano real da consulta. Para retornar o plano de consulta, chame sys.dm_exec_query_plan o handle de plano da consulta parametrizada preparada. Você pode determinar se a consulta foi parametrizada referenciando a sql coluna da visualização sys.syscacheobjects ou a coluna de texto da visão dinâmica de gerenciamento sys.dm_exec_sql_text .

Permissions

Para executar sys.dm_exec_query_plan, um usuário deve ser membro do papel fixo de servidor administrador do sistema ou ter a VIEW SERVER STATE permissão no servidor.

Permissões para SQL Server 2022 e versões posteriores

Requer a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Examples

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

Para visualizar os Planos de Apresentação XML, execute as seguintes consultas no Editor de Consultas do SQL Server Management Studio, depois selecione ShowPlanXML na query_plan coluna da tabela retornada pelo sys.dm_exec_query_plan Plano de Apresentação XML exibido no painel de resumo do Management Studio. Para salvar o XML Showplan em um arquivo, clique com o botão direito em ShowPlanXML na query_plan coluna, selecione 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 planos. Cada plano de consulta em cache é identificado por um identificador exclusivo chamado de identificador de plano. Você pode especificar esse handle de plano com a sys.dm_exec_query_plan visão de gerenciamento dinâmico para recuperar o plano de execução para 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.

Primeiro, recupere o ID da sessão (SPID) do processo que está executando a consulta ou lote usando o sp_who procedimento armazenado:

exec sp_who;  
GO  

O conjunto de resultados retornado por sp_who indica que o ID da sessão é 54. Você pode usar o ID da sessão com a sys.dm_exec_requests visualização de gerenciamento dinâmico para recuperar o handle do plano usando a seguinte consulta:

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

A tabela retornada por sys.dm_exec_requests indica que o handle de plano para a consulta ou lote de execução lenta é 0x06000100A27E7C1FA821B10600, que você pode especificar como plan_handle argumento para sys.dm_exec_query_plan recuperar o plano de execução em formato XML da seguinte forma. O plano de execução em formato XML para a consulta ou lote de execução lenta está contido na query_plan coluna da tabela retornada por sys.dm_exec_query_plan.

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.

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.

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  

Conteúdo relacionado

• Visões e funções de gerenciamento dinâmico do sistema
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.sp_who (Transact-SQL)
• Referência de operadores lógicos e físicos do Showplan
• sys.dm_exec_text_query_plan (Transact-SQL)