sys.dm_exec_query_plan (Transact-SQL)sys.dm_exec_query_plan (Transact-SQL)

ESTE TÓPICO APLICA-SE A:simSQL Server (a partir de 2008)nãoBanco de Dados SQL do Microsoft AzurenãoAzure SQL Data Warehouse nãoParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Retorna o plano de execução em formato XML para o lote especificado pelo identificador de plano.Returns the Showplan in XML format for the batch specified by the plan handle. O plano especificado pelo identificador do plano pode estar em cache ou estar sendo executado.The plan specified by the plan handle can either be cached or currently executing.

O esquema XML do plano de execução está publicado e disponível em neste site da Microsoft.The XML schema for the Showplan is published and available at this Microsoft Web site. Ele fica disponível também no diretório em que o SQL ServerSQL Server é instalado.It is also available in the directory where SQL ServerSQL Server is installed.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax


sys.dm_exec_query_plan ( plan_handle )  

ArgumentosArguments

plan_handleplan_handle
Identifica exclusivamente um plano de consulta para um lote em cache ou sendo executado atualmente.Uniquely identifies a query plan for a batch that is cached or is currently executing.

plan_handle é varbinary(64).plan_handle is varbinary(64). plan_handle pode ser obtido dos seguintes objetos de gerenciamento dinâmico:plan_handle can be obtained from the following dynamic management objects:

sys.dm_exec_cached_planssys.dm_exec_cached_plans

sys.dm_exec_query_statssys.dm_exec_query_stats

sys.dm_exec_requestssys.dm_exec_requests

Tabela retornadaTable Returned

Nome da colunaColumn name Tipo de dadosData type DescriptionDescription
dbiddbid smallintsmallint A ID do banco de dados de contexto em vigor quando a instrução Transact-SQLTransact-SQL correspondente a esse plano foi compilada.ID of the context database that was in effect when the Transact-SQLTransact-SQL statement corresponding to this plan was compiled. Para instruções SQL preparadas e ad hoc, a ID do banco de dados no qual as instruções foram compiladas.For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

A coluna é anulável.Column is nullable.
objectidobjectid Intint A identificação do objeto (por exemplo, procedimento armazenado ou função definida pelo usuário) para este plano de consulta.ID of the object (for example, stored procedure or user-defined function) for this query plan. Para lotes ad hoc e preparados, essa coluna é nulo.For ad hoc and prepared batches, this column is null.

A coluna é anulável.Column is nullable.
numbernumber smallintsmallint Inteiro de procedimento armazenado numerado.Numbered stored procedure integer. Por exemplo, um grupo de procedimentos para o pedidos aplicativo pode ser nomeado orderproc; 1, orderproc; 2, e assim por diante.For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. Para lotes ad hoc e preparados, essa coluna é nulo.For ad hoc and prepared batches, this column is null.

A coluna é anulável.Column is nullable.
Criptografadoencrypted bitbit Indica se o procedimento armazenado correspondente está criptografado.Indicates whether the corresponding stored procedure is encrypted.

0 = não criptografado0 = not encrypted

1 = criptografado1 = encrypted

A coluna não é anulável.Column is not nullable.
query_planquery_plan xmlxml Contém a representação de Showplan em tempo de compilação do plano de execução de consulta que é especificado com plan_handle.Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. O Showplan está em formato XML.The Showplan is in XML format. Um plano é gerado para cada lote que contém, por exemplo, instruções ad hoc Transact-SQLTransact-SQL, chamadas de procedimento armazenado e chamadas de função definidas pelo usuário.One plan is generated for each batch that contains, for example ad hoc Transact-SQLTransact-SQL statements, stored procedure calls, and user-defined function calls.

A coluna é anulável.Column is nullable.

RemarksRemarks

Sob as seguintes condições, nenhuma saída Showplan é retornada no query_plan coluna da tabela retornada para sys.DM exec_query_plan:Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

  • Se o plano de consulta é especificado usando plan_handle foi removido do cache do plano, o query_plan coluna da tabela retornada é nula.If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. Por exemplo, essa condição pode ocorrer se houver um atraso entre quando o identificador de plano foi captado e quando foi usado com sys.DM exec_query_plan.For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.

  • Algumas instruções Transact-SQLTransact-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.Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. Planos de execução XML para tais instruções não podem ser recuperados usando sys.DM exec_query_plan , a menos que o lote está em execução porque eles não existem no cache.XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

  • Se um Transact-SQLTransact-SQL lote ou procedimento armazenado contém uma chamada para uma função definida pelo usuário ou uma chamada para SQL dinâmico, por exemplo, usando EXEC (cadeia de caracteres), o XML Showplan compilado para a função definida pelo usuário não está incluída na tabela retornado por sys.DM exec_query_plan para o lote ou procedimento armazenado.If a Transact-SQLTransact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Em vez disso, você deve fazer uma chamada separada para sys.DM exec_query_plan para o identificador de plano que corresponde à função definida pelo usuário.Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

    Quando uma consulta ad hoc usa parametrização simple ou forçada, o query_plan coluna conterá apenas o texto da instrução e não o plano de consulta real.When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. Para retornar o plano de consulta, chame sys.DM exec_query_plan do identificador do plano de consulta parametrizada preparada.To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. Você pode determinar se a consulta foi parametrizada referenciando a sql coluna o syscacheobjects exibição ou a coluna de texto a dm_exec_sql_textexibição de gerenciamento dinâmico.You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

    Devido a uma limitação no número de níveis aninhados permitida no xml tipo de dados, sys.DM exec_query_plan não pode retornar planos de consulta que atendem ou excedem 128 níveis de elementos aninhados.Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. Em versões anteriores do SQL ServerSQL Server, esta condição evitava que o plano de consulta retornasse e gerasse um erro 6335.In earlier versions of SQL ServerSQL Server, this condition prevented the query plan from returning and generates error 6335. Em SQL Server 2005SQL Server 2005 Service Pack 2 e versões posteriores, o query_plan coluna retorna NULL.In SQL Server 2005SQL Server 2005 Service Pack 2 and later versions, the query_plan column returns NULL. Você pode usar o sys.DM exec_text_query_plan (Transact-SQL) a função de gerenciamento dinâmico para retornar a saída do plano de consulta em formato de texto.You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format.

PermissõesPermissions

Para executar sys.DM exec_query_plan, um usuário deve ser um membro do sysadmin função de servidor fixa ou ter a permissão VIEW SERVER STATE no servidor.To execute sys.dm_exec_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

ExemplosExamples

Os exemplos a seguir mostram como usar o sys.DM exec_query_plan exibição de gerenciamento dinâmico.The following examples show how to use the sys.dm_exec_query_plan dynamic management view.

Para exibir os planos de execução XML, execute as seguintes consultas no Editor de consultas de SQL Server Management StudioSQL Server Management Studio, em seguida, clique em ShowPlanXML no query_plan coluna da tabela retornada por sys.dm_ exec_query_plan.To view the XML Showplans, execute the following queries in the Query Editor of SQL Server Management StudioSQL Server Management Studio, then click ShowPlanXML in the query_plan column of the table returned by sys.dm_exec_query_plan. O plano de execução XML é exibido no painel de resumo do Management StudioManagement Studio.The XML Showplan displays in the Management StudioManagement Studio summary pane. Para salvar o plano de execução XML em um arquivo, clique com botão direito ShowPlanXML no query_plan coluna, clique em salvar resultados como, nomeie o arquivo no formato < file_name>. sqlplan; por exemplo, Myxmlshowplan.To save the XML Showplan to a file, right-click ShowPlanXML in the query_plan column, click Save Results As, name the file in the format <file_name>.sqlplan; for example, MyXMLShowplan.sqlplan.

A.A. Recuperar o plano de consulta em cache para uma consulta ou lote Transact-SQL de execução lentaRetrieve the cached query plan for a slow-running Transact-SQL query or batch

Planos de Consulta para vários tipos de lotes Transact-SQLTransact-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.Query plans for various types of Transact-SQLTransact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. Cada plano de consulta em cache é identificado por um identificador exclusivo chamado de identificador de plano.Each cached query plan is identified by a unique identifier called a plan handle. Você pode especificar esse identificador de plano com a sys.DM exec_query_plan exibição de gerenciamento dinâmico para recuperar o plano de execução para um determinado Transact-SQLTransact-SQL consulta ou lote.You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQLTransact-SQL query or batch.

Se uma consulta Transact-SQLTransact-SQL ou lote for executado por muito tempo em uma determinada conexão com SQL ServerSQL Server, recupere o plano de execução para essa consulta ou lote para descobrir o que está causando o retardo.If a Transact-SQLTransact-SQL query or batch runs a long time on a particular connection to SQL ServerSQL Server, retrieve the execution plan for that query or batch to discover what is causing the delay. O exemplo a seguir mostra como recuperar o plano de execução XML para uma consulta ou lote de execução lenta.The following example shows how to retrieve the XML Showplan for a slow-running query or batch.

Observação

Para executar esse exemplo, substitua os valores de session_id e plan_handle com valores específicos de seu servidor.To run this example, replace the values for session_id and plan_handle with values specific to your server.

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:First, retrieve the server process ID (SPID) for the process that is executing the query or batch by using the sp_who stored procedure:

USE master;  
GO  
exec sp_who;  
GO  

O conjunto de resultados retornado por sp_who indica que o SPID é 54anos.The result set that is returned by sp_who indicates that the SPID is 54. 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:You can use the SPID with the sys.dm_exec_requests dynamic management view to retrieve the plan handle by using the following query:

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

A tabela que é retornada por exec_requests indica que o identificador de plano para a consulta ou lote em execução lenta é 0x06000100A27E7C1FA821B10600, que você pode especificar como o plan_handle argumento com sys.dm_exec_query_plan para recuperar o plano de execução em formato XML como se segue.The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600, which you can specify as the plan_handle argument with sys.dm_exec_query_plan to retrieve the execution plan in XML format as follows. O plano de execução em formato XML para a consulta ou lote em execução lenta é contido no query_plan coluna da tabela retornada por sys.dm_exec_query_plan.The execution plan in XML format for the slow-running query or batch is contained in the query_plan column of the table returned by sys.dm_exec_query_plan.

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

B.B. Recuperar todo o plano de consulta do cache de planoRetrieve every query plan from the plan cache

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.To retrieve a snapshot of all query plans residing in the plan cache, retrieve the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view. Os identificadores de plano são armazenados na coluna plan_handle de sys.dm_exec_cached_plans.The plan handles are stored in the plan_handle column of 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.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. 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.The XML Showplan output for each plan currently in the plan cache is in the query_plan column of the table that is returned.

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

C.C. Recuperar todo plano de consulta para o qual o servidor reuniu estatísticas de consulta do cache de planoRetrieve every query plan for which the server has gathered query statistics from the plan cache

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.To retrieve a snapshot of all query plans for which the server has gathered statistics that currently reside in the plan cache, retrieve the plan handles of these plans in the cache by querying the sys.dm_exec_query_stats dynamic management view. Os identificadores de plano são armazenados na coluna plan_handle de sys.dm_exec_query_stats.The plan handles are stored in the plan_handle column of 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.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. 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.The XML Showplan output for each plan for which the server has gathered statistics currently in the plan cache is in the query_plan column of the table that is returned.

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

D.D. Recuperar as informações sobre as cinco principais consultas por tempo médio de CPURetrieve information about the top five queries by average CPU time

O exemplo a seguir retorna os planos e o tempo médio de CPU das cinco principais consultas.The following example returns the plans and average CPU time for the top five queries.

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émSee Also

Exibições e funções de gerenciamento dinâmico (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL) sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL) sp_who (Transact-SQL)
Referência de operadores físicos e lógicos de plano de execução Showplan Logical and Physical Operators Reference
sys.dm_exec_text_query_plan (Transact-SQL)sys.dm_exec_text_query_plan (Transact-SQL)