參數敏感性計劃最佳化

  • 發行項

適用於： SQL Server 2022 (16.x) 和更新版本

參數敏感度計畫 (PSP) 最佳化是智慧型查詢處理系列功能的一部分。 在參數化查詢採用單一快取計畫，但對於所有可能的傳入參數值而言並非最佳選項，則上述功能可處理此種案例。 這是非統一資料分佈的情況。 如需詳細資訊，請參閱參數敏感性和參數和執行計劃的重複使用。

如需此問題案例現有因應措施的詳細資訊，請參閱：

PSP 最佳化會自動為單一參數化陳述式啟用多個作用中快取計畫。 快取的執行計畫會根據客戶所提供執行階段參數值來因應不同資料大小。

瞭解參數化

在 SQL Server 資料庫引擎中使用 Transact-SQL (T-SQL) 陳述式內的參數或參數標記時，可以提升關聯式引擎將新的 T-SQL 陳述式與先前編譯之現有執行計畫與升級計畫重新使用力。 如需詳細資訊，請參閱簡單參數化。

您可以藉由指定將資料庫中所有的 SELECT、INSERT、UPDATE 及 DELETE 陳述式依據特定限制進行參數化，以覆寫 SQL Server 預設的簡單參數化行為。 如需詳細資訊，請參閱強制參數化。

PSP 最佳化實作

在初始編譯期間，資料行統計資料長條圖會識別非統一分佈，並評估最有風險的參數化述詞 (最多三個可用的述詞)。 換句話說，如果相同查詢內的多個述詞符合準則，PSP 最佳化會選擇前三個。 PSP 功能會限制評估的述詞數目，以避免使用太多計畫增加計畫快取和查詢存放區 (如果已啟用查詢存放區)。

針對合格的計畫，初始編譯會產生發送器計畫，其中包含稱為發送器運算式的 PSP 最佳化邏輯。 發送器計畫會根據基數範圍界限值述詞對應至查詢變體。

詞彙

發送器運算式

根據執行階段參數值評估述詞的基數，並將執行路由傳送至不同的查詢變體。

發送器計畫

系統會針對原始查詢，快取包含發送器運算式的計畫。 發送器計畫基本上是由功能所選取的述詞集合，其中包含一些額外的詳細資料。 針對選取的每個述詞，發送器計畫中包含的一些詳細資料為高和低界限值。 這些值可用來將參數值分割成不同的貯體或範圍。 發送器計畫也包含用來計算界限值的統計資料。

查詢變體

當發送器計畫根據執行階段參數值評估述詞的基數時，它會將這些值貯體化，並產生個別的子查詢來編譯和執行。 這些子查詢稱為查詢變體。 查詢變體在計畫快取與查詢存放區中有自己的計畫。

述詞基數範圍

在執行階段，會根據執行階段參數值來評估每個述詞的基數。 發送器會在編譯時間將基數值貯體化成三個述詞基數範圍。 例如，PSP 最佳化功能可以建立三個範圍，分別代表低、中和高基數範圍，如下圖所示。

Diagram showing the Parameter Sensitive Plan boundaries.

換句話說，初始編譯參數化查詢時，PSP 最佳化功能會產生稱為發送器方案的殼層計畫。 發送器運算式的邏輯會根據參數的執行階段值，將查詢貯體化成查詢變體。 當實際執行開始時，發送器會執行兩個步驟：

  • 發送器會針對指定的參數集評估其發送器運算式，以計算基數範圍。

  • 發送器會將這些範圍對應至特定的查詢變體，並編譯和執行變體。 藉由有多個查詢變體，PSP 最佳化功能可達成單一查詢擁有多個計畫。

在分派計畫的 ShowPlan XML 中可以看到基數範圍界限：

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

PSP 最佳化產生的提示會附加至查詢變體的 ShowPlan XML 中的 SQL 陳述式。 提示無法直接使用，且無法在手動新增時進行剖析。 此提示包含下列元素：

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[schemaName].[tableName].[columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

  • ObjectID 來自目前陳述式所屬的模組 (亦即預存程序、函式、觸發程式)；另假設陳述式已從模組產生。 如果陳述式是動態或隨選 SQL (亦即 sp_executesql)，ObjectID 元素會等於 0。
  • QueryVariantID 大致相當於 PSP 最佳化所選取之所有述詞的範圍組合。 例如，如果查詢有兩個符合 PSP 資格的述詞，且每個述詞都有三個範圍，則會有九個編號為 1-9 的查詢變異範圍。
  • 述詞範圍是從發送器運算式產生的述詞基數範圍資訊。

此外，查詢變體 (存在於發送器元素內) 的 ShowPlan XML 內：

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

備註

  • PSP 最佳化功能目前僅適用於相等述詞。

  • 如果發生明顯的資料分佈變化，就會自動重建發送器計劃。 查詢變體計畫會視需要獨立重新編譯，如同任何其他查詢計畫類型一樣，受限於預設的重新編譯事件。 如需重新編譯的詳細資訊，請參閱重新編譯執行計畫。

  • sys.query_store_plan (Transact-SQL) 查詢存放區系統類別目錄檢視已變更，以區分一般編譯計畫、發送器計畫，以及查詢變體計畫。 新的查詢存放區系統目錄檢視 sys.query_store_query_variant (Transact-SQL) 包含原始參數化查詢之間的父系-子系關聯性 (也稱為父代查詢) 相關資訊、發送器計畫及其子查詢變體。

  • 當有多個屬於相同資料表的述詞時，PSP 最佳化會根據基礎統計資料長條圖選取具有最多資料扭曲的述詞。 例如，對於 SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2，由於 column1 = @predicate1 和 column2 = @predicate2 都來自相同的資料表 table1，因此該功能只會評估最扭曲的述詞。 不過，如果範例查詢牽涉到 UNION 之類的運算子，PSP 會評估多個述詞。 例如，如果查詢具有類似 SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate 的特性，PSP 會在此案例中挑選最多兩個述詞，因為系統會將此案例視為兩個不同的資料表。 您可以從透過資料表別名自我聯結的查詢觀察到相同的行為。

  • 查詢變體的 ShowPlan XML 看起來會類似下列範例，其中兩個選取的述詞都會將其各自的資訊新增至 PLAN PER VALUE PSP 相關的提示。

    <Batch>
  <Statements>
    <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>
      <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">

  • 您可以使用下列一或多個方法，來影響 PSP 最佳化功能所使用的目前扭曲閾值：

    • 基數估算器 (CE) 追蹤旗標，例如追蹤旗標 9481 (全域、工作階段或查詢層級)

    • 嘗試降低使用中 CE 模型，或影響 CE 模型對於多個述詞獨立所做之假設的資料庫範圍組態選項。 這在多重資料行統計資料不存在的情況下特別有用，這會影響 PSP 最佳化評估這些述詞候選資格的能力。

    • 如需詳細資訊，請參閱使用 SQL Server 2014 基數估計工具最佳化查詢計劃白皮書的適用於多個述詞提高相互關聯假設一節。 較新的 CE 模型會嘗試為述詞的結合和分離假設某些相關性和較少的獨立性。 使用舊版 CE 模型可能會影響多重資料行聯結案例中述詞的選擇性計算方式。 此動作應該只在特定案例中考量，而且不建議針對大部分工作負載使用舊版 CE 模型。

  • PSP 最佳化目前會編譯並執行每個查詢變體作為全新備妥陳述式，這是如果發送器計畫是以模組 (亦即，預存程序、觸發程式、函式、檢視等) 為基礎，查詢變異會遺失object_id與任何父代模組關聯的原因之一。 作為已備妥的陳述式，object_id 不是任何可以直接對應至 sys.objects 中物件的項目，但基本上是以批次文字內部哈希為基礎的計算值。 如需詳細資訊，請參閱 sys.dm_exec_plan_attributes DMV 文件的傳回的資料表一節。

    查詢變體計畫會放在計畫快取物件存放區中 (CACHESTORE_OBJCP)，而發送器計畫放在 SQL 計畫快取存放區 (CACHESTORE_SQLCP)。 不過，如果父代查詢是模組的一部分，且不是動態或隨選 sql，則 PSP 功能會在屬於 PLAN PER VALUE 提示 (由 PSP 新增至 ShowPlan XML) 的 ObjectID 屬性內，儲存查詢變體父系的 object_id。 快取程序、函式和觸發程式的匯總效能統計資料，可以繼續用於其各自的用途。 更細微的執行相關統計資料 (例如在類似 sys.dm_exec_query_stats DMV 的檢視中找到的統計資料) 仍會包含查詢變體的資料，不過，在沒有額外針對每個需要更細微執行階段統計資料的查詢變體處理 ShowPlan XML 的情況下，sys.objects 資料表內查詢變體 object_id 和物件之間的關聯目前並未對齊。 如果啟用查詢存放區，則可以從查詢存放區取得查詢變體的執行階段和等候統計資料資訊，而不需要額外的 ShowPlan XML 剖析技術。

  • 由於 PSP 查詢變體會以全新備妥陳述式的形式執行，因此不會在各種計畫快取相關 sys.dm_exec_* DMV 中自動公開 object_id，而無須壓縮 ShowPlan XML 並套用文字模式比對技術 (亦即其他 XQuery 處理)。 目前只有 PSP 最佳化發送器計畫會發出適當的父代物件識別碼。 查詢存放區內會公開 object_id，因為查詢存放區允許比計畫快取階層所提供的關聯式模式更多。 如需詳細資訊，請參閱 <查詢存放區系統目錄檢視>sys.query_store_query_variant (Transact-SQL)>。

考量

  • 若要啟用 PSP 最佳化，請在執行查詢時，為所連線的資料庫啟用資料庫相容性層級 160。

  • 如需 PSP 最佳化功能的其他深入解析，建議您開啟查詢存放區來啟用查詢存放區整合。 下列範例會針對名為 MyNewDatabase 的預先存在的資料庫開啟查詢存放區：

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

注意

從 SQL Server 2022 (16.x) 開始，查詢存放區預設會針對所有新建立的資料庫啟用。

  • 若要停用資料庫層級的 PSP 最佳化，請使用 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF 資料庫範圍設定。

  • 若要停用查詢層級的 PSP 最佳化，請使用 DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION 查詢提示。

  • 如果透過追蹤旗標 4136、PARAMETER_SNIFFING 資料庫範圍組態或 USE HINT('DISABLE_PARAMETER_SNIFFING') 查詢提示來停用參數探查，則會停用相關聯的工作負載和執行內容的 PSP 最佳化。 如需詳細資訊，請參閱提示 (Transact-SQL) - 查詢和 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)。

  • 儲存在計劃快取中每個發送器的唯一計劃變體數目會受到限制，以避免快取膨脹。 未記載內部閾值。 由於每個 SQL 批次都有建立多個計畫的可能性，而且每個查詢變體計畫在計畫快取中都有獨立的項目，因此有可能達到允許的計畫項目數目的預設上限。 如果計畫快取收回率明顯偏高，或超過 CACHESTORE_OBJCP 和 CACHESTORE_SQLCP快取存放區的大小，您應該考慮套用追蹤旗標 174。

  • 在查詢存放區中針對查詢儲存的唯一計劃變體數目，受限於 max_plans_per_query 組態選項。 因為查詢變體可以有多個計畫，所以查詢存放區內每個查詢總共可以有 200 個計畫。 此數目包含屬於父代查詢之所有發送器的所有查詢變體計畫。 請考慮增加 max_plans_per_query 查詢存放區組態選項。

    • 例如，在唯一計畫數目可能超過預設查詢存放區 max_plans_per_query 限制的情形下，您具有下列行為。 假設您有一個查詢 ID 為 10 的查詢，其有兩個發送器計畫，而每個發送器計畫則有 20 個查詢變體 (總共 40 個查詢變體)。 查詢 ID 10 的計畫總數是查詢變體和兩個發送器計畫的 40 個計畫。 父代查詢本身 (查詢 ID 10) 也可能有 5 個一般 (非發送器) 計畫。 這會造成有 47 個計畫 (40 個來自查詢變體、2 個發送器，以及 5 個非 PSP 相關計畫)。 此外，如果每個查詢變體也平均有五個計畫，則在此案例中，父代查詢在查詢存放區中也可能有超過 200 個計畫。 這也取決於此範例父代查詢可能會參考之資料集中的大量資料扭曲。

  • 針對每個對應至指定發送器的查詢變體：

    • query_plan_hash 是唯一的。 此資料行可在 sys.dm_exec_query_stats 和其他動態管理檢視與目錄資料表中使用。
    • plan_handle 是唯一的。 此資料行可在 sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_cached_plans 及其他動態管理檢視和函數以及目錄資料表中使用。
    • 對於其他對應至相同發送器的變體來說，query_hash 很常見，因此可以判斷僅輸入參數值不同之查詢的彙總資源使用狀況。 此資料行可在 sys.dm_exec_query_stats、sys.query_store_query 及其他動態管理檢視和目錄資料表中使用。
    • 由於在編譯期間新增至查詢文字的特殊 PSP 最佳化識別碼，因此 sql_handle 是唯一的。 此資料行可在 sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_cached_plans 及其他動態管理檢視和函數以及目錄資料表中使用。 查詢存放區會提供與 sys.query_store_query 目錄資料表中 last_compile_batch_sql_handle 資料行相同的控制代碼資訊。
    • query_id 在查詢存放區中是唯一的。 此資料行可在 sys.query_store_query 和其他查詢存放區目錄資料表中使用。

在查詢存放區中強制執行的計劃

使用相同的 sp_query_store_force_plan 和 sp_query_store_unforce_plan 預存程序來操作發送器或變體計畫。

如果強制執行變數，則不會強制執行父代發送器。 如果強制執行發送器，則只會將該發送器的變體視為適合使用：

  • 先前從其他發送器強制執行的變體會變成閒置中，但在其發送器再次強制執行之前，會保留 [強制] 狀態
  • 先前在已變成非作用中之相同發送器中的強制變體，會再次強制執行

查詢存放區查詢提示行為

  • 將查詢存放區提示新增至查詢變體 (子查詢) 時，提示會以與非 PSP 查詢相同的方式套用。 如果提示也已套用至查詢存放區中的父代查詢，則查詢變體提示的優先順序較高。

  • 將查詢存放區提示新增至父代查詢，且子查詢 (查詢變體) 沒有現有的查詢存放區提示時，父代查詢中的提示會由子查詢 (查詢變體) 繼承。

  • 如果從父代查詢中移除查詢存放區查詢提示，子查詢 (查詢變體) 也會移除該提示。

  • 如果將 RECOMPILE 提示新增至父代查詢，系統會在從計畫快取中移除任何現有的查詢變體計畫之後產生非 PSP 計畫，因為 PSP 功能不會對具有 RECOMPILE 提示的查詢運作。

  • 您可以使用擴充事件 query_store_hints_application_success 和 query_store_hints_application_failed 事件，來觀察查詢存放區提示結果。 針對 sys.query_store_query_hints 資料表，其包含已套用之查詢提示的相關資訊。 如果提示只套用在父代查詢上，則系統目錄會包含父代查詢的提示資訊，但不會包含其子查詢的提示資訊，不過子查詢會繼承父代查詢的提示。

下表可摘要說明具有查詢提示和計畫強制行為的 PSP：

查詢變體提示或計畫 父代具有使用者套用的提示 父代具有已套用意見反應的提示 父代具有手動強制計畫 父代具有 APRC 1 強制計畫
透過使用者提示 查詢變體提示 查詢變體提示 查詢變體提示 N/A
透過意見反應提示 查詢變體提示 查詢變體提示 查詢變體提示 N/A
由使用者強制執行的計畫 查詢變體
強制計畫		 查詢變體
強制計畫		 查詢變體
強制計畫		 查詢變體
強制計畫
由 APC 強制執行的計畫 查詢變體
強制計畫		 查詢變體
強制計畫		 查詢變體
強制計畫		 查詢變體
強制計畫
沒有提示或強制計畫 父代使用者的提示 沒有提示 沒有動作 沒有動作

1 自動微調功能的自動計畫修正元件

擴充的事件

  • parameter_sensitive_plan_optimization_skipped_reason：於略過參數敏感性計畫功能時發生。 使用此事件來監視略過 PSP 最佳化的原因。

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

    SELECT name, map_value FROM sys.dm_xe_map_values WHERE name ='psp_skipped_reason_enum' ORDER BY map_key;

  • parameter_sensitive_plan_optimization：於查詢使用 PSP 最佳化功能時發生。 僅限偵錯通道。 某些相關欄位可能是：

    • is_query_variant：描述是否為發送器計畫 (父代) 或查詢變體計畫 (子系)
    • predicate_count：PSP 所選取的述詞數目
    • query_variant_id：顯示查詢變體識別碼。 值為 0 表示物件是發送器計畫 (父代)。

SQL Server 稽核行為

PSP 最佳化會提供發送器計畫陳述式的稽核資料，以及與發送器相關聯的任何查詢變體。 SQL Server 稽核內的 additional_information 資料行也會提供適當的 T-SQL 堆疊資訊，以供查詢變體使用。 使用 MyNewDatabase 資料庫作為範例，如果此資料庫具有名為 T2 的資料表，以及名稱為 usp_test 的預存程序，在執行 usp_test 預存程序之後，稽核記錄可能包含下列項目：

action_id object_name 陳述式 additional_information
AUSC <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
EX usp_test exec usp_test 300
SL T2 從 dbo.t2 (其中 ID=@id) 中選取 * <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL T2 select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100.0, 100000.0))) tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
EX usp_test exec usp_test 60000
SL T2 從 dbo.t2 (其中 ID=@id) 中選取 * <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL T2 select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[dbo].[T2].[ID] = @id, 100.0, 100000.0))) <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

已知問題

問題 探索日期 狀態 解決日期
在某些情況下，SQL Server 2022 (16.x) 中的查詢存放區會發生存取違規例外狀況。 啟用 PSP 最佳化查詢存放區整合時，您可能會遇到存取違規例外狀況。 如需詳細資訊，請參閱參數敏感度計畫最佳化，原因為何？中的更新。 2023 年 3 月 已解決 2023 年 8 月 (CU 7)

已解決

在某些情況下，SQL Server 2022 中的查詢存放區會發生存取違規例外狀況

注意

從 SQL Server 2022 (16.x) 累積更新 7 開始，已發佈了幾個修正程式，用於解決可能導致存取違規的競爭條件。 如果在套用 SQL Server 2022 (16.x) 累積更新 7 之後，發生與 PSP 最佳化和查詢存放區整合相關的存取違規，請考慮下列因應措施章節。

發生此問題的原因是，當已執行查詢的執行階段統計資料從查詢存放區的記憶體表示法 (在 MEMORYCLERK_QUERYDISKSTORE_HASHMAP 記憶體 Clerk 中找到) 保存至查詢存放區的磁碟版本時，可能會造成此問題。 執行階段統計資料 (顯示為執行階段統計資料) 會保留在記憶體中，時間長短依 SET QUERY_STORE 陳述式 DATA_FLUSH_INTERVAL_SECONDS 選項所定義的期限 (預設值為 15 分鐘)。 您可以使用 [Management Studio 查詢存放區] 對話方塊輸入 [資料排清間隔 (分鐘)] 的值，這會在內部轉換為秒。 如果系統有記憶體壓力，執行階段統計資料就能在使用 DATA_FLUSH_INTERVAL_SECONDS 選項所定義的時間之前排清至磁碟。 當與查詢存放區查詢計畫清除相關的其他查詢存放區背景執行緒 (也就是 STALE_QUERY_THRESHOLD_DAYS 及/或 MAX_STORAGE_SIZE_MB 查詢存放區選項) 從查詢存放區進行查詢時，存在這樣一個案例：查詢變體及/或其相關聯的發送器陳述式可能會過早地取值。 這可能導致在將查詢變體插入查詢存放區或從中刪除的作業期間，會發生存取違規。

如需查詢存放區作業的詳細資訊，請參閱 <查詢存放區如何收集資料> 一文中的備註章節。

因應措施：在套用 SQL Server 2022 (16.x) 累積更新 7 之後，如果在啟用查詢存放區與 PSP 整合時，系統仍然遇到存取違規，則您可以移除查詢存放區中的查詢變體，或在查詢或資料庫層級暫時停用 PSP 功能，直到其他修正可用為止。

  • 若要停用資料庫層級的 PSP 最佳化，請使用 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF 資料庫範圍組態。
  • 若要停用查詢層級的 PSP 最佳化，請使用 DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION 查詢提示。

若要從查詢存放區移除所有查詢變體，而不只是出現在sys.query_store_query_variant (Transact-SQL) 目錄檢視中的查詢變體，則可以使用類似下列查詢的查詢。 將 [<database>] 取代為發生問題的適當資料庫：

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the query store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

如果您的查詢存放區很大，或者您的系統具有大量工作負載及/或大量符合查詢存放區擷取資格的特定非參數化查詢，可能需要一些時間才能關閉查詢存放區。 若要在這些案例中強制關閉查詢存放區，請使用 ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) 命令，而非先前範例 T-SQL 中的命令。 若要尋找非參數化查詢，請參閱在查詢存放區中尋找非參數化查詢。

相關內容