sys.dm_exec_query_plan (Transact-SQL)

Aplica-se a: SQL ServerBase de Dados SQL do AzureAzure SQL Managed Instancebase de dados SQL em Microsoft Fabric

A sys.dm_exec_query_plan função de gestão dinâmica devolve o Showplan em formato XML para o lote especificado pelo handle do plano. O plano especificado pelo handle do plano pode estar em cache ou estar em execução atualmente.

O esquema XML do Showplan é publicado e está disponível neste site da Microsoft. Também está disponível no diretório onde o SQL Server está instalado.

Transact-SQL convenções de sintaxe

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 do plano ou está em execução no momento. plan_handleé varbinary(64).

O plan_handle pode ser obtido a partir dos seguintes objetos de gestão dinâmica:

• 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

Sob as seguintes condições, nenhuma saída do Showplan é retornada na coluna query_plan da tabela resultante para sys.dm_exec_query_plan.

• Se o plano de consulta especificado por usar plan_handle foi expulso da cache do plano, a query_plan coluna da tabela devolvida é NULL. Por exemplo, esta condição pode ocorrer se houver um atraso temporal entre o momento em que a alça do plano foi capturada e a sua utilização 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 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 não existem na cache.

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

Quando uma consulta ad hoc utiliza 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 devolver o plano de consulta, chame sys.dm_exec_query_plan o handle do plano da consulta parametrizada preparada. Pode determinar se a consulta foi parametrizada referenciando a sql coluna da vista sys.syscacheobjects ou a coluna de texto da vista de gestão dinâmica sys.dm_exec_sql_text .

Permissions

Para executar sys.dm_exec_query_plan, um utilizador deve ser membro do papel fixo de servidor de sysadmin ou ter permissão VIEW SERVER STATE sobre o servidor.

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

Requer permissão VIEW SERVER PERFORMANCE STATE no servidor.

Examples

Os exemplos seguintes mostram como usar a vista de sys.dm_exec_query_plan gestão dinâmica.

Para visualizar os Planos de Exposição XML, execute as seguintes consultas no Editor do Power Query do SQL Server Management Studio, depois selecione ShowPlanXML na query_plan coluna da tabela devolvida pelo sys.dm_exec_query_plan Plano de Exposição XML apresentado no painel de resumo do Management Studio. Para guardar o XML Showplan num ficheiro, clique com o botão direito em ShowPlanXML na query_plan coluna, selecione Guardar Resultados Como, nomeie o ficheiro no formato <file name>.sqlplan; por exemplo, MyXMLShowplan.sqlplan.

A. Recuperar o plano de consulta em cache para uma consulta de Transact-SQL ou batch 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 utilizador, são armazenados numa área de memória chamada cache plano. Cada plano de consulta em cache é identificado por um identificador único chamado handle de plano. Pode especificar este handle de plano com a sys.dm_exec_query_plan vista de gestão dinâmica para recuperar o plano de execução para uma determinada consulta Transact-SQL ou lote.

Se uma consulta Transact-SQL ou batch correr durante muito tempo numa ligação específica ao SQL Server, recupere o plano de execução dessa consulta ou lote para descobrir o que está a causar o atraso. O exemplo seguinte mostra como recuperar o XML Showplan para uma consulta ou batch de execução lenta.

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

exec sp_who;  
GO  

O conjunto de resultados que é devolvido por sp_who indica que o ID da sessão é 54. Pode usar o ID da sessão com a sys.dm_exec_requests vista de gestão dinâmica para recuperar o handle do plano usando a seguinte consulta:

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

A tabela que é devolvida por sys.dm_exec_requests indica que o handle do plano para a consulta ou lote de execução lenta é 0x06000100A27E7C1FA821B10600, que 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 devolvida por sys.dm_exec_query_plan.

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. Recuperar todos os planos de consulta do cache do plano

Para obter um instantâneo de todos os planos de consulta que residem na cache de planos, recupere os handles de planos de todos os planos de consulta na cache consultando a sys.dm_exec_cached_plans vista de gestão dinâmica. As maçanetas do plano são armazenadas na plan_handle coluna de sys.dm_exec_cached_plans. Depois, use o operador CROSS APPLY para passar os handles do plano da sys.dm_exec_query_plan seguinte forma. A saída XML Showplan para cada plano atualmente no cache do plano está na coluna query_plan da tabela que é retornada.

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. Recuperar todos os planos de consulta para os quais o servidor recolheu estatísticas de consulta da cache do plano

Para obter um instantâneo de todos os planos de consulta para os quais o servidor recolheu estatísticas que atualmente residem na cache do plano, recupere os handles de planos desses planos na cache consultando a sys.dm_exec_query_stats vista de gestão dinâmica. As maçanetas do plano são armazenadas na plan_handle coluna de sys.dm_exec_query_stats. Depois, use o operador CROSS APPLY para passar os handles do plano da sys.dm_exec_query_plan seguinte forma. A saída XML do Showplan para cada plano para o qual o servidor recolheu estatísticas atualmente na cache do plano está na query_plan coluna da tabela que é devolvida.

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. Obter informações sobre as cinco principais consultas pelo tempo médio de CPU

O exemplo seguinte devolve os planos e o tempo médio de CPU para as 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

• Vistas e funções de gestão dinâmica 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 aos operadores lógicos e físicos do showplan
• sys.dm_exec_text_query_plan (Transact-SQL)