sys.dm_exec_cached_plans (Transact-SQL)

Статья
06/18/2013

Возвращает строку для каждого плана запроса, кэшируемого SQL Server для более быстрого выполнения запросов. Можно использовать динамическое административное представление для поиска кэшированных планов запросов, кэшированного текста запросов, объема памяти, занимаемого кэшированными планами и счетчика повторного использования кэшированных планов.

bucketid

int

Идентификатор сегмента хэша, в который кэшируется запись. Значение указывает диапазон от 0 до значения размера хэш-таблицы для типа кэша.

Для кэшей SQL Plans и Object Plans размер хэш-таблицы может достигать 10007 на 32-разрядных версиях систем и 40009 — на 64-разрядных. Для кэша Bound Trees размер хэш-таблицы может достигать 1009 на 32-разрядных версиях систем и 4001 на 64-разрядных. Для кэша расширенных хранимых процедур размер хэш-таблицы может достигать 127 на 32-разрядных и 64-разрядных версиях систем.

refcounts

int

Число объектов кэша, ссылающихся на данный объект кэша. Значение refcounts для записи должно быть не меньше 1, чтобы размещаться в кэше.

usecounts

int

Количество повторений поиска объекта кэша. Остается без увеличения, если параметризованные запросы обнаруживают план в кэше. Может быть увеличен несколько раз при использовании инструкции showplan.

size_in_bytes

int

Число байтов, занимаемых объектом кэша.

memory_object_address

varbinary(8)

Адрес памяти кэшированной записи. Это значение можно использовать с представлением sys.dm_os_memory_objects, чтобы проанализировать распределение памяти кэшированного плана, и с представлением sys.dm_os_memory_cache_entries для определения затрат на кэширование записи.

cacheobjtype

nvarchar(34)

Тип объекта в кэше. Значение может быть одним из следующих:

Compiled Plan (скомпилированный план)
Compiled Plan Stub (заглушка скомпилированного плана)
Parse Tree (дерево синтаксического анализа)
Extended Proc (расширенные процедуры)
CLR Compiled Func (скомпилированная функция CLR)
CLR Compiled Proc (скомпилированная процедура CLR)

objtype

nvarchar(16)

Тип объекта. Значение может быть одним из следующих:

Значение	Описание
Proc	Хранимая процедура
Prepared	Подготовленная инструкция
Adhoc	Нерегламентированный запрос1
ReplProc	Процедура фильтра репликации
Триггер	Триггер
Представление	Представление
По умолчанию	По умолчанию
UsrTab	Пользовательская таблица
SysTab	Системная таблица
Проверка	Ограничение CHECK
Правило	Правило

plan_handle

varbinary(64)

Идентификатор плана в оперативной памяти. Этот идентификатор является временным и не меняется, только пока план сохраняется в кэше. Это значение можно использовать со следующими функциями динамического управления:

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes

pool_id

int

Идентификатор пула ресурсов, для которого подсчитывается использование памяти для плана.

1 Обозначает код Transact-SQL, отправленный с помощью программ osql или sqlcmd в виде событий языка, а не в виде удаленных вызовов процедур.

Разрешения

Требует разрешения VIEW SERVER STATE на сервере.

Примеры

А.Возвращение текста пакета повторно используемых кэшированных записей

Следующий пример возвращает SQL-текст всех кэшированных записей, использованных более одного раза.

SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 1 
ORDER BY usecounts DESC;
GO

Б.Возвращение планов запросов для всех кэшированных триггеров

Следующий пример возвращает планы запросов для кэшированных триггеров.

SELECT plan_handle, query_plan, objtype 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE objtype ='Trigger';
GO

В.Возвращение параметров SET, с которыми был скомпилирован план

Следующий пример возвращает параметры SET, с использованием которых был скомпилирован план. Для плана также возвращается атрибут sql_handle. Оператор PIVOT используется для вывода атрибутов set_options и sql_handle в виде столбцов, а не строк. Дополнительные сведения о значении, возвращаемом в атрибуте set_options, см. в разделе sys.dm_exec_plan_attributes (Transact-SQL).

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

Г.Возвращение распределения памяти всех кэшированных скомпилированных планов

Следующий пример возвращает распределение памяти, используемой всеми скомпилированными планами в кэше.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, 
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes 
FROM sys.dm_exec_cached_plans AS ecp 
JOIN sys.dm_os_memory_objects AS omo 
    ON ecp.memory_object_address = omo.memory_object_address 
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO