sys.dm_exec_cached_plans (Transact-SQL)

Статья
05/23/2023

Применимо к: SQL Server Azure SQL Database Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

В базе данных Azure SQL динамические административные представления не могут предоставлять сведения, которые могут повлиять на сдерживание базы данных, или сведения о других базах данных, к которым у пользователя есть доступ. Чтобы избежать предоставления этих сведений, каждая строка, содержащая данные, которые не принадлежат подключенного клиента, отфильтровывается. Кроме того, значения в столбцах memory_object_address и pool_id фильтруются; значение столбца равно NULL.

Примечание

Чтобы вызвать этот метод из Azure Synapse Analytics или Analytics Platform System (PDW), используйте имя sys.dm_pdw_nodes_exec_cached_plans. Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Имя столбца	Тип данных	Описание
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)	Тип объекта. Ниже приведены возможные значения и их описания. Процедура: хранимая процедура Подготовлено: подготовленная инструкция Adhoc: нерегламентированный запрос. Ссылается на Transact-SQL, отправленные как события языка с помощью osql или sqlcmd , а не как удаленные вызовы процедур. ReplProc: Replication-filter-procedure Триггер: триггер Представление: представление По умолчанию: по умолчанию 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	Идентификатор пула ресурсов, для которого подсчитывается использование памяти для плана.
pdw_node_id	int	Область применения: Azure Synapse Analytics, Analytics Platform System (PDW) Идентификатор узла, на который находится данное распределение.

Разрешения

На SQL Server и управляемом экземпляре SQL необходимо разрешение VIEW SERVER STATE.

Для целей обслуживания баз данных SQL уровня "Базовый", S0 и S1, а также для баз данных в эластичных пулах необходимо иметь учетную запись администратора сервера, администратора Azure Active Directory или членство ##MS_ServerStateReader##в роли сервера. Для всех остальных целей обслуживания базы данных SQL требуется разрешение VIEW DATABASE STATE в базе данных или членство в роли сервера ##MS_ServerStateReader##.

Разрешения для SQL Server 2022 и более поздних версий

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

Примеры

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

Следующий пример возвращает 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, 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

См. также:

Динамические административные представления и функции (Transact-SQL)
Динамические административные представления и функции, связанные с выполнением (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_plan_attributes (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_os_memory_objects (Transact-SQL)
sys.dm_os_memory_cache_entries (Transact-SQL)
FROM (Transact-SQL)