選擇性參數計劃優化 （OPPO）

適用於：適用於： SQL Server 2025 （17.x） Azure SQL 資料庫Microsoft Fabric 中的 SQL 資料庫

選擇性參數一詞是指參數敏感計劃 （PSP） 問題的特定變化，其中在查詢執行期間存在的敏感性參數值會控制是否需要執行搜尋或掃描數據表。 簡單的範例如下：

SELECT column1,
       column2
FROM Table1
WHERE (column1 = @p
      OR @p IS NULL);

在此範例中，SQL Server 一律會選擇掃描數據表 Table1的計劃，即使上有 Table1(col1)索引也一樣。 在有 NULL 的情況下，可能無法使用索引搜尋計畫。 這類< c0 />查詢提示技術可能不適用，因為目前沒有運算元可以在執行期間將索引搜尋動態變更為掃描。 在執行期間，此類搜尋與掃描的組合可能無效，因為該運算符上方的基數估算可能不準確。 結果是對於具有類似查詢模式的較複雜查詢，產生效率低下的計劃選擇和過多的記憶體分配。

選擇性參數計劃優化 （OPPO） 功能會使用自適性計劃優化 （Multiplan） 基礎結構，該基礎結構是透過參數敏感性計劃優化改進所引進，從單一語句產生多個計劃。 這可讓功能根據查詢中使用的參數值做出不同的假設。 在查詢運行時間期間，OPPO 會選取適當的計劃：

• 其中參數值為IS NOT NULL時，系統會使用搜尋計劃或比完整掃描計劃更理想的方案。
• 其中 參數值為 NULL，它會使用掃描計劃。

OPPO 是包含 參數敏感性計劃優化之調適型計劃優化功能系列的一部分，可為 Multiplan 功能集的第二個元件提供解決方案，其中涵蓋動態搜尋功能。

• 等號謂詞

  WHERE column1 = @p
  

• 動態搜尋

  WHERE (column1 = @p1 OR @p1 IS NULL)
    AND (column2 = @p2 OR @p2 IS NOT NULL)
  

術語及其運作方式

Term	Description
調度員表達式	此表達式會根據運行時間參數值評估述詞的基數，並將執行路由傳送至不同的查詢變體。
調度員計畫	包含調度器表達式的計劃會被快取以用於原始查詢。 調度計劃基本上是功能所選取述詞的集合，包含一些額外的細節。 針對每個選定的述詞，發送器計劃中包含的某些詳細資訊為高和低界限值。 這些值可用來將參數值分割成不同的貯體或範圍。 發送器計劃也包含用來計算界限值的統計數據。
查詢變體	當發送器計劃根據運行時間參數值評估述詞的基數時，它會將其 貯體化 ，併產生個別的子查詢來執行。 這些子查詢稱為查詢變體。 查詢變體在計劃快取和查詢存放區中有自己的計劃。 換句話說，藉由使用不同的查詢變體，我們達成單一查詢多個計劃的目標。

例如，請考慮房地產公司的應用程式 Web 窗體，允許選擇性篩選特定清單的臥室數目。 常見的反模式可能是將選擇性篩選表示為：

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

即使參數@bedrooms = 10被透過使用參數標記探查，而且我們知道臥室數目的基數可能非常低，優化器也不會產生利用臥室欄上的索引進行搜尋的計劃，因為這對於@bedroomsNULL的情況來說不屬於有效的計劃。 生成的計畫不包含索引的掃描。

想像一下，如果這可以重寫為兩個不同的語句。 視參數的運行時間值而定，我們可以評估類似如下的內容：

IF @bedrooms IS NULL
    SELECT * FROM Properties;
ELSE
    SELECT * FROM Properties
    WHERE bedrooms = @bedrooms;

我們可以使用調適型計劃優化基礎結構來達成此目的，這可讓您建立分派兩個查詢變體的發送器計劃。

與 PSP 優化所使用的謂詞基數範圍類似，OPPO 將一個系統可用的查詢提示嵌入到計劃的查詢文字中。 此提示不適用於應用程式使用，或您嘗試自行使用它。

繼續上述範例，

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

OPPO 可以產生兩個查詢變體，這些變數可能會在 Showplan XML 中新增下列屬性：

• @bedrooms 為 NULL。 查詢變體 折疊 原始查詢以生成掃描方案。

  SELECT * FROM Properties PLAN PER VALUE（ObjectID = 1234， QueryVariantID = 1， optional_predicate（ @bedrooms 為 NULL））

• @bedrooms IS NOT NULL

  SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE（ObjectID = 1234， QueryVariantID = 2， optional_predicate（@bedrooms 為 NULL）)

使用選擇性參數計劃優化

若要為資料庫啟用 OPPO，需要下列必要條件：

• 資料庫必須使用相容性層級 170。
• OPTIONAL_PARAMETER_OPTIMIZATION必須啟用資料庫範圍的組態。

預設情況下，OPTIONAL_PARAMETER_OPTIMIZATION 資料庫範圍的設定是啟用的。 這表示使用相容性層級 170 的資料庫（SQL Server 2025 中的預設值）預設會使用 OPPO。

您可以執行下列語句，確保資料庫在 SQL Server 2025 中使用 OPPO：

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

若要停用資料庫的選擇性參數計劃優化，請停用 OPTIONAL_PARAMETER_OPTIMIZATION 資料庫範圍設定：

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

透過查詢提示使用選擇性參數計劃優化

您可以使用 DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION 查詢提示來停用指定查詢的選擇性參數計劃優化。 提示必須透過 USE HINT 子句指定。 如需詳細資訊，請參閱 查詢提示。

提示能在任何相容性層級下運作，並覆寫資料庫範圍的OPTIONAL_PARAMETER_OPTIMIZATION 組態。

您可以在查詢中直接指定 DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION 查詢提示，或透過 查詢存放區提示 進行指定。

延伸事件

• optional_parameter_optimization_skipped_reason：當 OPPO 決定查詢不符合優化資格時發生。 此擴充事件遵循與 PSP 優化所使用的parameter_sensitive_plan_optimization_skipped_reason事件相同的模式。 由於查詢可以同時產生 PSP 優化和 OPPO 查詢變體，因此您應該檢查這兩個事件，以了解為什麼有一個或兩個功能都參與。

  下列查詢會顯示略過 PSP 的所有可能原因：

  SELECT map_value
  FROM sys.dm_xe_map_values
  WHERE [name] = 'opo_skipped_reason_enum'
  ORDER BY map_key;
  

• query_with_optional_parameter_predicate：擴充事件遵循與 PSP 優化所使用的query_with_parameter_sensitivity事件相同的模式。 它包含 PSP 優化改善中可用的附加欄位，包括顯示功能所找到的有趣述詞數量、以 JSON 格式提供的有關有趣述詞的詳細數據，以及是否支援 OPPO 的述詞或述詞群。

Remarks

• 查詢變體的 ShowPlan XML 會類似於下列範例，其中，選取的述詞會將其相應的資訊新增至 PLAN PER VALUE 的 optional_predicate 提示中。

<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

• 擴充 query_with_optional_parameter_predicate 事件的範例輸出

Field	Value
optional_parameter_optimization_supported	True
optional_parameter_predicate_count	3
predicate_details	{"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}
query_type	193

相關內容

• 查詢處理架構指南
• 重新編譯執行計劃
• 參數和執行計劃重複使用
• 簡單參數化
• 強制參數化
• 查詢提示 (Transact-SQL)
• SQL 資料庫中的智慧查詢處理
• 參數靈敏度
• ALTER DATABASE SCOPED CONFIGURATION （Transact-SQL）