sys.dm_exec_plan_attributes (Transact-SQL)

Aplica-se a: SQL Server

Retorna uma linha por atributo de plano para o plano especificado pelo identificador de plano. Você pode usar esta função com valor de tabela para obter detalhes sobre um plano específico, como os valores chave de cache ou o número atual de execuções simultâneas do plano.

Observação

Algumas das informações retornadas por meio dessa função são mapeadas para a exibição de compatibilidade com versões anteriores sys.syscacheobjects.

Sintaxe

sys.dm_exec_plan_attributes ( plan_handle )  

Argumentos

plan_handle
Identifica exclusivamente um plano de consulta de um lote que foi executado e cujo plano reside no cache de plano. plan_handle é varbinary(64). O identificador de plano pode ser obtido na sys.dm_exec_cached_plans exibição de gerenciamento dinâmico.

Tabela retornada

Nome da coluna	Tipo de dados	Descrição
atributo	varchar(128)	O nome do atributo associado com este plano. A tabela imediatamente abaixo desta lista os atributos possíveis, seus tipos de dados e suas descrições.
value	sql_variant	Valor do atributo que é associado ao plano.
is_cache_key	bit	Indica se o atributo é usado como parte da chave de consulta de cache para o plano.

Na tabela acima, o atributo pode ter os seguintes valores:

Atributo	Tipo de dados	Descrição
set_options	int	Indica os valores de opção com os quais o plano foi compilado.
objectid	int	Uma das chaves principais usadas para pesquisar um objeto no cache. Essa é a ID de objeto armazenada em sys.objects para objetos de banco de dados (procedimentos, exibições, gatilhos e assim por diante). Para planos do tipo "Adhoc" ou "Preparado", é um hash interno do texto de lote.
dbid	int	É o identificador do banco de dados que contém a entidade à qual o plano se refere. Para planos ad hoc ou preparados, é o identificador do banco de dados da partir do qual o lote é executado.
dbid_execute	int	Para objetos do sistema armazenados no banco de dados Resource , a ID do banco de dados a partir da qual o plano armazenado em cache é executado. 0 para todos os outros casos.
user_id	int	Um valor de -2 indica que o lote enviado não depende da resolução de nome implícita e pode ser compartilhado entre usuários diferentes. Este é o método preferencial. Qualquer outro valor representa a identificação do usuário que submete a consulta no banco de dados.
language_id	smallint	A identificação de idioma da conexão que criou o objeto de cache. Para obter mais informações, consulte sys.syslanguages (Transact-SQL).
date_format	smallint	O formato de data da conexão que criou o objeto de cache. Para obter mais informações, veja SET DATEFORMAT (Transact-SQL).
date_first	tinyint	Primeiro valor de data. Para obter mais informações, veja SET DATEFIRST (Transact-SQL).
compat_level	tinyint	Representa o nível de compatibilidade definido no banco de dados em cujo contexto o plano de consulta foi compilado. O nível de compatibilidade retornado é o nível de compatibilidade do contexto de banco de dados atual para instruções ad hoc e não é afetado pela dica de consulta QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n. Para instruções contidas em um procedimento armazenado ou função, ele corresponde ao nível de compatibilidade do banco de dados no qual o procedimento armazenado ou função é criado.
status	int	Bits de status interno que fazem parte da chave de consulta do cache.
required_cursor_options	int	Opções de cursor especificadas pelo usuário, como o tipo de cursor.
acceptable_cursor_options	int	Opções de cursor para as quais o SQL Server pode converter implicitamente para dar suporte à execução da instrução. Por exemplo, o usuário pode especificar um cursor dinâmico, mas o otimizador de consulta pode converter esse tipo de cursor para um cursor estático.
merge_action_type	smallint	O tipo de plano de execução de gatilho usado como o resultado de uma instrução MERGE. 0 indica plano de não gatilho, um plano de gatilho não executado como o resultado de uma instrução MERGE ou um plano de gatilho executado como o resultado de uma instrução MERGE que só especifica uma ação DELETE. 1 indica um plano de gatilho INSERT que executa como o resultado de uma instrução MERGE. 2 indica um plano de gatilho UPDATE que executa como o resultado de uma instrução MERGE. 3 indica um plano de gatilho DELETE que executa como o resultado de uma instrução MERGE que contém uma ação INSERT ou UPDATE correspondente. Para gatilhos aninhados executados por cascateamento de ações, este valor é a ação da instrução MERGE que causou a cascata.
is_replication_specific	int	Representa que a sessão da qual esse plano foi compilado é aquela que se conectou à instância do SQL Server usando uma propriedade de conexão não documentada que permite que o servidor identifique a sessão como uma criada por componentes de replicação, de modo que o comportamento de determinados aspectos funcionais do servidor seja alterado de acordo com o que esse componente de replicação espera.
optional_spid	smallint	O session_id de conexão (spid) torna-se parte da chave de cache para reduzir o número de recompilações. Isso impede recompilações para a reutilização de um plano de uma única sessão envolvendo tabelas temporárias não vinculadas dinamicamente.
optional_clr_trigger_dbid	int	Preenchido apenas no caso de um gatilho DML do CLR. A ID do banco de dados que contém a entidade. Para qualquer outro tipo de objeto, retorna zero.
optional_clr_trigger_objid	int	Preenchido apenas no caso de um gatilho DML do CLR. A ID do objeto armazenada em sys.objects. Para qualquer outro tipo de objeto, retorna zero.
parent_plan_handle	varbinary(64)	Sempre NULL.
is_azure_user_plan	tinyint	1 para consultas executadas em um Banco de Dados SQL do Azure de uma sessão iniciada por um usuário. 0 para consultas que foram executadas de uma sessão não iniciada por um usuário final, mas por aplicativos em execução na infraestrutura do Azure que emitem consultas para outras finalidades de coleta de telemetria ou execução de tarefas administrativas. Os clientes não são cobrados por recursos consumidos por consultas em que is_azure_user_plan = 0. Somente Banco de Dados SQL do Azure.
inuse_exec_context	int	Número de lotes em execução que estão usando o plano de consulta.
free_exec_context	int	Número de contextos de execução em cache do plano de consulta que não está sendo utilizado atualmente.
hits_exec_context	int	Número de vezes que o contexto de execução foi obtido do cache de plano e reutilizado, economizando a sobrecarga de recompilar a instrução SQL. O valor é uma agregação de todas as execuções de lote até o momento.
misses_exec_context	int	Número de vezes que não foi possível localizar um contexto de execução no cache de plano, resultando na criação de um contexto de execução novo para a execução de lote.
removed_exec_context	int	Número de contextos de execução que foram removidos devido à pressão de memória no plano de cache.
inuse_cursors	int	Número de lotes em execução que contêm um ou mais cursores que estão usando o plano de cache.
free_cursors	int	Número de cursores inativos ou livres no plano de cache.
hits_cursors	int	Número de vezes que um cursor inativo foi obtido do plano de cache e reutilizado. O valor é uma agregação de todas as execuções de lote até o momento.
misses_cursors	int	Número de vezes que não foi possível localizar um cursor inativo no cache.
removed_cursors	int	Número de cursores de que foram removidos devido à pressão de memória no plano de cache.
sql_handle	varbinary(64)	O identificador SQL do lote.

Permissões

No SQL Server, requer a permissão VIEW SERVER STATE.

Nos objetivos de serviço Básico, S0 e S1 do Banco de Dados SQL do Azure e para bancos de dados em pools elásticos, a conta de administrador do servidor ou a conta de administrador do Microsoft Entra é necessária. Em todos os outros objetivos de serviço do Banco de Dados SQL, a VIEW DATABASE STATE permissão é necessária no banco de dados.

Permissões do SQL Server 2022 e posteriores

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

Comentários

Opções de configuração

As cópias do mesmo plano compilado podem diferir apenas pelo valor na coluna set_options . Indica que conexões diferentes estão usando conjuntos diferentes de opções SET para a mesma consulta. Usar conjuntos diferentes de opções não é desejável, porque podem causar compilações extras, baixa reutilização de plano e inflação de cache do plano, devido a várias cópias de planos no cache.

Avaliando opções de configuração

Para converter o valor retornado em set_options para as opções com as quais o plano foi compilado, subtraia os valores do valor set_options , começando com o maior valor possível, até chegar a 0. Cada valor subtraído corresponde a uma opção que foi usada no plano de consulta. Por exemplo, se o valor em set_options for 251, as opções com as quais o plano foi compilado serão ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Plano Paralelo(2) e ANSI_PADDING (1).

Opção	Valor
ANSI_PADDING	1
Plano Paralelo Indica que as opções de paralelismo de plano foram alteradas.	2
FORCEPLAN	4
CONCAT_NULL_YIELDS_NULL	8
ANSI_WARNINGS	16
ANSI_NULLS	32
QUOTED_IDENTIFIER	64
ANSI_NULL_DFLT_ON	128
ANSI_NULL_DFLT_OFF	256
NoBrowseTable Indica que o plano não usa uma tabela de trabalho para implementar uma operação de FOR BROWSE.	512
TriggerOneRow Indica que o plano contém uma única otimização de linha para tabelas delta de gatilho AFTER.	1024
ResyncQuery Indica que a consulta foi submetida através de procedimentos armazenados do sistema interno.	2048
ARITH_ABORT	4096
NUMERIC_ROUNDABORT	8192
DATEFIRST	16384
DATEFORMAT	32768
LanguageID	65536
UPON Indica que a opção de banco de dados PARAMETERIZATION foi definida como FORCED quando o plano foi compilado.	131072
ROWCOUNT	Aplica-se a: SQL Server 2012 (11.x) e posterior 262144

Cursores

Cursores inativos são colocados em cache em um plano compilado de forma que a memória usada para armazenar o cursor pode ser usada de novo por usuários simultâneos de cursores. Por exemplo, suponha que um lote declara e usa um cursor sem desalocá-lo. Se houver dois usuários executando o mesmo lote, haverá dois cursores ativos. Quando os cursores são desalocados (potencialmente em lotes diferentes), a memória usada para armazenar o cursor é gravada em cache e não é liberada. Esta lista de cursores inativos é mantida no plano compilado. Na próxima vez que um usuário executar o lote, a memória de cursor em cache será usada novamente e inicializada adequadamente como um cursor ativo.

Avaliando opções de cursor

Para converter o valor retornado em required_cursor_options e acceptable_cursor_options para as opções com as quais o plano foi compilado, subtraia os valores do valor da coluna, começando com o maior valor possível, até chegar a 0. Cada valor subtraído corresponde a uma opção cursor que foi usada no plano de consulta.

Opção	Valor
Nenhum	0
INSENSITIVE	1
SCROLL	2
READ ONLY	4
FOR UPDATE	8
LOCAL	16
GLOBAL	32
FORWARD_ONLY	64
KEYSET	128
DYNAMIC	256
SCROLL_LOCKS	512
OPTIMISTIC	1024
STATIC	2048
FAST_FORWARD	4096
IN PLACE	8192
FOR select_statement	16384

Exemplos

R. Retornando os atributos de um plano específico

O exemplo a seguir retorna todos os atributos de um plano específico. A exibição de gerenciamento dinâmico sys.dm_exec_cached_plans é consultada primeiro para obter o identificador para o plano especificado. Na segunda consulta, substitua o <plan_handle> por um valor de identificador de plano da primeira consulta.

SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype   
FROM sys.dm_exec_cached_plans;  
GO  
SELECT attribute, [value], is_cache_key  
FROM sys.dm_exec_plan_attributes(<plan_handle>);  
GO  

B. Retornando as opções SET para planos compilados e a o identificador SQL para planos em cache

O exemplo a seguir retorna um valor que representa as opções com as quais cada plano foi compilado. Além disso, o identificador SQL para todos os planos em cache é retornado.

SELECT plan_handle, pvt.set_options, pvt.sql_handle  
FROM (  
    SELECT plan_handle, epa.attribute, epa.value   
    FROM sys.dm_exec_cached_plans   
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa  
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa   
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;  
GO  

Confira também

Exibições e funções de gerenciamento dinâmico (Transact-SQL)
Funções e exibições de gerenciamento dinâmico relacionadas à execução (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)