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

發行項
12/15/2008

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 查詢部份的結果集大小。

另一方面，if 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 AdventureWorks
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 AdventureWorks
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 2005 最佳化 MyProc2 中的 SELECT 陳述式時，@d2 的值未知。因此，最佳化工具會針對 OrderDate > @d2 的選擇性，使用預設估計值 (此處為 30 %)。

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

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

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

避免在查詢中使用本機變數。請改在查詢中使用參數、常值或運算式。
在包含參數的查詢中使用內嵌的運算子和函數時，請限制在＜基數估計的編譯時間運算式評估＞下所列出的那些運算子和函數。
確定查詢條件中只有常數的運算式是可摺疊常數，或可在編譯時間進行評估。
如果在查詢中必須使用本機變數來評估運算式，請考慮在查詢之外的不同範圍中評估它。例如，執行下列其中一項，應該會很有用：
- 將變數的值傳遞至包含您所要評估之查詢的預存程序，並且讓查詢使用程序參數，來取代本機變數。
- 將本機變數的值當作部份根據，來建構一個含有查詢的字串，然後使用動態 SQL (EXEC 或 sp_executesql) 執行此字串。
- 將查詢參數化、使用 sp_executesql 執行此查詢，並將變數的值作為參數傳遞給查詢。

請參閱

共用方式為