Поделиться через

sys.dm_exec_cached_plans (Transact-SQL)

Относится к:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsСистема аналитической платформы (PDW)SQL база данных в Microsoft Fabric

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

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

Примечание.

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

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

Для кэшей планов SQL и планов объектов хэш-таблица может составлять до 10 007 в 32-разрядных системах и до 40 009 в 64-разрядных системах. Для кэша привязанных деревьев размер хэш-таблицы может составлять до 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_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: хранимая процедура
Подготовка: Подготовленная инструкция
Нерегламентированный: нерегламентированный запрос. Ссылается на Transact-SQL, отправленные как языковые события с помощью osql или sqlcmd вместо удаленных вызовов процедур.
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 Идентификатор пула ресурсов, для которого подсчитывается использование памяти для плана.
pdw_node_id int Идентификатор узла, на который находится данное распределение.

Область применения: Azure Synapse Analytics, Analytics Platform System (PDW)

Разрешения

ДЛЯ SQL Server 2019 (15.x) и более ранних версий требуется VIEW SERVER STATE разрешение.

ДЛЯ SQL Server 2022 (16.x) и более поздних версий и Управляемого экземпляра SQL Azure требуются VIEW SERVER PERFORMANCE STATE разрешения.

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

Примеры

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

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

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

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

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

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

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

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;

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

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

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
     INNER 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';

Связанный контент

  • Last updated on