sys.dm_exec_plan_attributes （Transact-SQL）

  • 發行項

適用於：SQL Server

針對計畫控制碼所指定的計畫，針對每個計畫屬性傳回一個資料列。 您可以使用這個資料表值函式來取得特定計劃的詳細資料，例如快取索引鍵值或計畫目前同時執行的次數。

注意

透過此函式傳回的一些資訊會對應至 sys.syscacheobjects 回溯相容性檢視。

語法

sys.dm_exec_plan_attributes ( plan_handle )

引數

plan_handle
唯一識別已執行且其計畫位於計畫快取中的批次查詢計劃。 plan_handle 為 varbinary(64)。 您可以從動態管理檢視sys.dm_exec_cached_plans 取得 計畫控制碼。

傳回的資料表

資料行名稱 資料類型 描述
屬性 varchar(128) 與此計畫相關聯的屬性名稱。 下表緊接在此資料表下方列出可能的屬性、其資料類型及其描述。
value sql_variant 與這個計畫相關聯的屬性值。
is_cache_key bit 指出屬性是否用來作為計畫的快取查閱索引鍵的一部分。

在上述資料表中， 屬性 可以具有下列值：

屬性 資料類型 描述
set_options int 指出計畫編譯的選項值。
objectid int 其中一個主要索引鍵，用於在快取中查閱物件。 這是儲存在資料庫物件的 sys.objects 中 的物件識別碼（程式、檢視、觸發程式等等）。 對於類型為 「Adhoc」 或 「Prepared」 的計畫，它是批次文字的內部雜湊。
dbid int 這是包含計畫所參考實體的資料庫識別碼。

針對臨機操作或備妥的計畫，它是執行批次的資料庫識別碼。
dbid_execute int 對於儲存在 Resource 資料庫中的系統物件，執行快取計畫的資料庫識別碼。 若為所有其他情況，則為 0。
user_id int 值 -2 表示提交的批次不相依于隱含名稱解析，而且可以在不同的使用者之間共用。 這是慣用的方法。 任何其他值代表在資料庫中提交查詢之使用者的使用者識別碼。
language_id smallint 建立快取物件之連接語言的識別碼。 如需詳細資訊，請參閱 sys.syslanguages （Transact-SQL） 。
date_format smallint 建立快取物件的連接日期格式。 如需詳細資訊，請參閱 SET DATEFORMAT (Transact-SQL)。
date_first tinyint 日期第一個值。 如需詳細資訊，請參閱 SET DATEFIRST (Transact-SQL).
compat_level tinyint 表示編譯查詢計劃內容之資料庫中的相容性層級。 傳回的相容性層級是 adhoc 語句目前資料庫內容的相容性層級，而且不受查詢提示 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 影響。 對於預存程式或函式中包含的語句，它會對應至建立預存程式或函式之資料庫的相容性層級。
status int 屬於快取查閱索引鍵一部分的內部狀態位。
required_cursor_options int 使用者指定的資料指標選項，例如資料指標類型。
acceptable_cursor_options int SQL Server 可能會隱含轉換成 的資料指標選項，以支援語句的執行。 例如，使用者可以指定動態資料指標，但允許查詢最佳化工具將此資料指標類型轉換成靜態資料指標。
merge_action_type smallint 做為 MERGE 語句結果的觸發程式執行計畫類型。

0 表示非觸發程式計畫、未以 MERGE 語句結果執行的觸發計畫，或只指定 DELETE 動作之 MERGE 語句結果執行的觸發程式計畫。

1 表示以 MERGE 語句結果執行的 INSERT 觸發程式計畫。

2 表示以 MERGE 語句結果執行的 UPDATE 觸發程式計畫。

3 表示 DELETE 觸發程式計畫，其會以包含對應 INSERT 或 UPDATE 動作的 MERGE 語句結果執行。

對於由串聯動作執行的巢狀觸發程式，這個值是導致串聯的 MERGE 語句動作。
is_replication_specific int 表示編譯此計畫的會話是使用未記載的連接屬性連接到 SQL Server 實例的會話，可讓伺服器將會話識別為複寫元件所建立的會話，以便根據這類複寫元件預期變更伺服器特定功能層面的行為。
optional_spid smallint 連線session_id （spid） 會成為快取索引鍵的一部分，以減少重新編譯的數目。 這可防止單一會話重新編譯涉及非動態系結臨時表的計畫。
optional_clr_trigger_dbid int 只有在 CLR DML 觸發程式的情況下才會填入 。 包含實體的資料庫識別碼。

針對任何其他物件類型，傳回零。
optional_clr_trigger_objid int 只有在 CLR DML 觸發程式的情況下才會填入 。 儲存在 sys.objects 中的 物件識別碼。

針對任何其他物件類型，傳回零。
parent_plan_handle varbinary(64) 一律為 Null。
is_azure_user_plan tinyint 1 適用于從使用者起始之會話的 Azure SQL 資料庫中執行的查詢。

0 表示已從使用者未起始之會話執行的查詢，而是透過從 Azure 基礎結構內執行的應用程式，針對收集遙測或執行系統管理工作的其他用途發出查詢。 客戶不會針對查詢所耗用的資源收取費用，其中is_azure_user_plan = 0。

僅限 Azure SQL 資料庫 。
inuse_exec_coNtext int 目前執行中的批次數目，這些批次正在使用查詢計劃。
free_exec_coNtext int 目前未使用的查詢計劃快取執行內容數目。
hits_exec_coNtext int 從計畫快取中取得執行內容並重複使用的次數，可節省重新編譯 SQL 語句的額外負荷。 值是到目前為止所有批次執行的匯總。
misses_exec_coNtext int 在計畫快取中找不到執行內容的次數，導致建立批次執行的新執行內容。
removed_exec_coNtext int 因為快取計畫上的記憶體壓力而移除的執行內容數目。
inuse_cursors int 目前執行的批次數目，其中包含一或多個使用快取計畫的資料指標。
free_cursors int 快取計畫的閒置或可用資料指標數目。
hits_cursors int 非使用中資料指標從快取計畫取得並重複使用的次數。 值是到目前為止所有批次執行的匯總。
misses_cursors int 快取中找不到非使用中資料指標的次數。
removed_cursors int 因為快取計畫上的記憶體壓力而移除的資料指標數目。
sql_handle varbinary(64) 批次的 SQL 控制碼。

權限

在 SQL Server 上，需要 VIEW SERVER STATE 許可權。

在 Azure SQL 資料庫基本、S0 和 S1 服務目標上，以及彈性集區中的資料庫， 需要伺服器管理員 帳戶或 Microsoft Entra 系統管理員 帳戶。 在所有其他SQL 資料庫服務目標上， VIEW DATABASE STATE 資料庫需要許可權。

SQL Server 2022 和更新版本的權限

需要伺服器上的 VIEW SERVER PERFORMANCE STATE 權限。

備註

Set 選項

相同已編譯計畫的複本可能只有set_options 資料行中的 值不同。 這表示不同的連線會針對相同的查詢使用不同的 SET 選項組合。 使用不同選項群組通常是不想要的，因為它可能會導致額外的編譯、較少的計畫重複使用，以及因為快取中的多個計畫複本而導致計畫快取膨脹。

評估設定選項

若要將 set_options 中 傳回的值轉譯為編譯計畫的選項，請從 set_options 值減去值，從最大的可能值開始，直到達到 0 為止。 您減去的每個值都會對應至查詢計劃中使用的選項。 例如，如果set_options 中的 值為 251，則計畫編譯的選項為 ANSI_Null_DFLT_ON （128）、QUOTED_IDENTIFIER （64）、ANSI_NullS（32）、ANSI_WARNINGS （16）、CONCAT_Null_YIELDS_Null （8）、平行計畫（2）和 ANSI_PADDING （1）。

選項 值
ANSI_PADDING 1
ParallelPlan

表示計畫平行處理原則選項已變更。		 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

表示計畫不會使用工作資料表來實作 FOR BROWSE 作業。		 512
TriggerOneRow

表示計畫包含 AFTER 觸發程式差異資料表的單一資料列優化。		 1024
ResyncQuery

指出查詢是由內部系統預存程式送出。		 2048
ARITH_ABORT 4096
NUMERIC_ROUNDABORT 8192
DATEFIRST 16384
DATEFORMAT 32768
LanguageID 65536
後

表示編譯計畫時，DATABASE 選項 PARAMETERIZATION 已設定為 FORCED。		 131072
ROWCOUNT 適用于： SQL Server 2012 （11.x） 和更新版本

262144

資料指標

非使用中資料指標會快取在編譯的計畫中，讓用來儲存資料指標的記憶體可供資料指標的並行使用者重複使用。 例如，假設批次宣告並使用資料指標，而不需解除配置。 如果有兩個使用者執行相同的批次，則會有兩個作用中的資料指標。 一旦解除配置資料指標（可能在不同的批次中），用來儲存資料指標的記憶體就會快取且未釋放。 此非使用中資料指標清單會保留在已編譯的計畫中。 下次使用者執行批次時，快取的資料指標記憶體將會重複使用並適當地初始化為使用中資料指標。

評估資料指標選項

若要將required_cursor_options 中 傳回的值轉譯 為 acceptable_cursor_options到編譯計畫的選項，請從資料行值減去值，從最大的可能值開始，直到達到 0 為止。 您減去的每個值都會對應至查詢計劃中使用的資料指標選項。

選項 值
無 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
就地 8192
FOR select_statement 16384

範例

A. 傳回特定計劃的屬性

下列範例會傳回指定計劃的所有計劃屬性。 先 sys.dm_exec_cached_plans 查詢動態管理檢視，以取得指定計劃的計畫控制碼。 在第二個查詢中，將 取代 <plan_handle> 為來自第一個查詢的計畫控制碼值。

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. 傳回已編譯計畫的 SET 選項和快取計畫的 SQL 控制碼

下列範例會傳回值，代表每個計畫編譯的選項。 此外，會傳回所有快取計畫的 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

另請參閱

動態管理檢視和函數 (Transact-SQL)
執行相關的動態管理檢視和函數 (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)