sys.dm_exec_query_plan_stats (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2019 (15.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Retorna o equivalente ao último plano de execução real conhecido para um plano de consulta previamente armazenado em cache.

Sintaxe

sys.dm_exec_query_plan_stats ( 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 é varbinary(64).

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

Tabela retornada

Nome da coluna Data type Descrição
dbid smallint A ID do banco de dados de contexto em vigor quando a instrução Transact-SQL correspondente a esse plano foi compilada. Para instruções SQL preparadas e ad hoc, a ID do banco de dados no qual 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 assistemáticos e preparados, essa coluna é nula.

A coluna é anulável.
number smallint Inteiro de procedimento armazenado numerado. Por exemplo, um grupo de procedimentos para o aplicativo orders pode ser denominado orderproc;1, orderproc;2 e assim por diante. Para lotes assistemáticos e preparados, essa coluna é nula.

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 xml Contém a última representação Showplan conhecida em tempo de execução do plano de execução da consulta real especificado com plan_handle. O Showplan está em formato XML. Um plano é gerado para cada lote que contém. Por exemplo, instruções ad hoc Transact-SQL, chamadas de procedimentos armazenados e chamadas de função definidas pelo usuário.

A coluna é anulável.

Comentários

Esse é um recurso de opção de aceitação. Para habilitar no nível do servidor, use o Sinalizador de Rastreamento 2451. Para habilitar no nível do banco de dados, use a opção LAST_QUERY_PLAN_STATS em ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Essa função do sistema funciona sob a infraestrutura de perfil de estatísticas de execução de consulta leve. Para obter mais informações, confira Infraestrutura de Criação de Perfil de Consulta.

A saída de Showplan por sys.dm_exec_query_plan_stats contém as seguintes informações:

  • Todas as informações de tempo de compilação encontradas no plano armazenado em cache
  • Informações de tempo de execução, como o número real de linhas por operador, o tempo total de CPU da consulta e o tempo de execução, avisos de despejo, DOP real, memória máxima usada e memória concedida

Sob as seguintes condições, uma saída de Showplan equivalente a um plano de execução real é retornada na coluna query_plan da tabela retornada para sys.dm_exec_query_plan_stats:

  • O plano pode ser encontrado em sys.dm_exec_cached_plans.

    AND

  • A consulta que está sendo executada é complexa ou consome muitos recursos.

Sob as seguintes condições, uma saída de Showplansimplificada1 é retornada na coluna query_plan da tabela retornada para sys.dm_exec_query_plan_stats:

  • O plano pode ser encontrado em sys.dm_exec_cached_plans.

    AND

  • A consulta é bastante simples, geralmente classificada como parte de uma carga de trabalho OLTP.

1 Refere-se a um Showplan que contém apenas o operador de nó raiz (SELECT).

Nas condições a seguir, nenhuma saída é retornada de sys.dm_exec_query_plan_stats:

  • O plano de consulta especificado pelo uso do endereço plan_handle foi removido do cache de planos.

    OR

  • Em primeiro lugar, o plano de consulta não era armazenável em cache. Para obter mais informações, consulte Cache e reutilização do plano de execução.

Observação

Uma limitação no número de níveis aninhados permitidos no tipo de dados xml significa que sys.dm_exec_query_plan não pode retornar planos de consulta que atendam ou excedam 128 níveis de elementos aninhados. Em versões anteriores do SQL Server, essa condição impedia o retorno do plano de consulta e gerava o erro 6335. No SQL Server 2005 (9.x) Service Pack 2 e versões posteriores, a coluna query_plan retorna NULL.

Permissões

Requer a permissão VIEW SERVER STATE no servidor.

Permissões do SQL Server 2022 e posteriores

É necessária a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Exemplos

A. Observar o último plano de execução de consulta real conhecido para um plano específico armazenado em cache

O exemplo a seguir consulta sys.dm_exec_cached_plans para encontrar o plano interessante e copiar seu plan_handle da saída.

SELECT * FROM sys.dm_exec_cached_plans;
GO

Em seguida, para obter o último plano de execução de consulta real conhecido, use o plan_handle copiado com a função do sistema sys.dm_exec_query_plan_stats.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);
GO

B. Observar o último plano de execução de consulta real conhecido para todos os planos em cache

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO

C. Observar o último plano de execução de consulta real conhecido para um plano específico armazenado em cache e um texto de consulta

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';
GO

D. Examinar eventos em cache para o gatilho

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

Confira também