sys.dm_exec_plan_attributes (Transact-SQL)

Cópias do mesmo plano compilado podem diferir somente 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. Para obter mais informações, consulte Recomendações de ajuste de consulta.

Avaliando opções de configuração

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

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

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