共用方式為


低查詢效能的疑難排解:常數摺疊和基數估計期間的運算式評估

SQL Server 會在早期評估某些常數運算式,以改進查詢效能。這個作業稱為常數摺疊 (Constant Folding)。所謂的常數是指 Transact-SQL 常值,例如 3、'ABC'、'2005-12-31'、1.0e3 或 0x12345678。

可摺疊運算式

SQL Server 會在下列運算式類型中使用常數摺疊:

  • 只包含常數的算術運算式,例如 1+1, 5/3*2。

  • 只包含常數的邏輯運算式,例如 1=1 和 1>2 AND 3>4。

  • SQL Server 視為可摺疊的內建函數,包括 CAST 和 CONVERT。如果內建函數只包含其輸入,並且不含其他內容資訊 (例如 SET 選項、語言設定、資料庫選項和加密金鑰) 時,此內建函數通常是可摺疊。非決定性函數不可摺疊。決定性內建函數可摺疊,但有一些例外。

例外之一是大型物件類型。如果摺疊程序的輸出類型是大型物件類型 (text、image、nvarchar(max)、varchar(max) 或 varbinary(max)),則 SQL Server 不會摺疊此運算式。

不可摺疊運算式

所有其他運算式類型都不可摺疊,尤其是下列運算式類型不可折疊:

  • 非常數運算式,例如結果相依於資料行值的運算式。

  • 結果相依於本機變數或參數 (例如 @x) 的運算式。

  • 非決定性函數。

  • 使用者自訂函數 (Transact-SQL 和 CLR 這兩者)。

  • 結果相依於語言設定的運算式。

  • 結果相依於 SET 選項的運算式。

  • 結果相依於伺服器組態選項的運算式。

可摺疊和不可摺疊常數運算式的範例

請考慮以下查詢:

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

如果這個查詢的 PARAMETERIZATION 資料庫選項不是設為 FORCED (預設為 SIMPLE),則在編譯查詢之前,會評估 117.00 + 1000.00 運算式,並以其結果 1117.00 取代。這項常數摺疊作業的好處包含下列幾點:

  • 執行階段不必重複評估運算式。

  • 查詢最佳化工具可使用評估後的運算式值,來估計 TotalDue > 117.00 + 1000.00 查詢部分的結果集大小。

另一方面,如果 dbo.f 是純量使用者定義函數,則運算式 dbo.f(100) 不可摺疊,因為 SQL Server 不會摺疊含有使用者定義函數的運算式,即使它們是決定性函數也是如此。

基數估計的編譯時間運算式評估

此外,在最佳化期間,結果集大小 (基數) 估計工具 (此為最佳化工具的一部份) 會評估部份運算式,這些運算式不是常數摺疊,但在編譯時間其引數為已知 (不論引數是參數或常數)。

特別是在編譯時間會評估下列這些內建函數和特殊運算子 (如果其所有輸入皆為已知):UPPER、LOWER、RTRIM、DATEPART( 僅限 YY )、GetDate、CAST 和 CONVERT。

下列運算子的所有輸入若為已知,在編譯時間也會加以評估:

  • 算術運算子:+、-、*、/、一元減號 -

  • 邏輯運算子:AND、OR、NOT

  • 比較運算子:<、>、<=、>=、<>、LIKE、IS NULL、IS NOT NULL

在基數估計期間,最佳化工具不會評估任何其他的函數或運算子。

編譯時間運算式評估的範例

假設有以下 Transact-SQL 預存程序:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

在此程序的 SELECT 陳述式最佳化期間,最佳化工具會嘗試評估條件 OrderDate > @d+1 之結果集的預期基數。運算式 @d+1 不是常數摺疊,因為 @d 是參數。然而,在最佳化時間內,此參數的值為已知。這可讓最佳化工具正確估計結果集的大小,協助它選取良好的查詢計畫。

現在看另一個類似範例,但在查詢中以本機變數 @d2 取代上一個範例中的 @d+1,並改為在 SET 陳述式 (而不是查詢) 中評估運算式。

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

在 SQL Server 最佳化 MyProc2 中的 SELECT 陳述式時,@d2 的值未知。因此,最佳化工具會針對 OrderDate > @d2 的選擇性,使用預設估計值 (此處為 30 %)。

最佳作法:使用常數摺疊和編譯時間運算式評估來產生最佳查詢計畫

若要確定能產生最佳的查詢計畫,最好設計查詢、預存程序和批次,讓查詢最佳化工具可根據資料分佈的統計資料,正確估計查詢中條件的選擇性。否則,最佳化工具在估計選擇性時,必須使用預設估計值 (例如上一個範例中的 30 %)

為了確保最佳化工具的基數估計工具可提供良好的估計值,您應該先確定 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 資料庫 SET 選項為 ON (預設值),否則就應針對查詢條件中會被參考的所有資料行,手動建立其統計資料。接著,當您設計查詢中的條件時,請盡量遵循以下規則:

  • 避免在查詢中使用本機變數。請改在查詢中使用參數、常值或運算式。

  • 在包含參數的查詢中使用內嵌的運算子和函數時,請限制在<Compile-Time Expression Evaluation for Cardinality Estimation>下所列出的那些運算子和函數。

  • 確定查詢條件中只有常數的運算式是可摺疊常數,或可在編譯時間進行評估。

  • 如果在查詢中必須使用本機變數來評估運算式,請考慮在查詢之外的不同範圍中評估它。例如,執行下列其中一項,應該會很有用:

    • 將變數的值傳遞至包含您所要評估之查詢的預存程序,並且讓查詢使用程序參數,來取代本機變數。

    • 將本機變數的值當做部分根據,來建構一個含有查詢的字串,然後使用動態 SQL (EXEC 或 sp_executesql) 執行此字串。

    • 將查詢參數化、使用 sp_executesql 執行此查詢,並將變數的值做為參數傳遞給查詢。