子樹成本與性能時間的 SQL 關係

“SQL 最終子樹成本”和“查詢時間性能”之間的一般關係是什麼？

範例：當調整查詢時，子樹成本從 0.2 變為 0.1，這是否意味著查詢時間會快兩倍？我的查詢中沒有看到這種情況。

我們有一個伺服器，即使使用“設置統計時間”和“DBCC DROPCLEANBUFFERS”也無法真正衡量查詢性能。伺服器、事務、程序、後台項目中正在進行不同的程序。

謝謝，

子樹成本表示計劃的估計成本。在調查查詢優化器為什麼選擇一個計劃而不是另一個計劃時，它可能很有用。例如，您可能會看到一個帶有雜湊連接的計劃，並認為循環連接會是更有效的選擇。添加查詢提示以強制循環連接和比較子樹成本有助於確定 SQL Server 選擇散列連接的原因。

由於許多原因，計劃的估計成本通常與查詢的“性能時間”不匹配，包括硬體差異、其他程序的阻塞、整體伺服器工作負載、模型限制以及基於不完善資訊的假設。此外，0.1 與 0.2 的子樹成本實際上根本沒有有意義的差異。如果您的查詢相對於您的其他工作負載具有較低的相對成本，但該查詢執行了很長時間，這表明查詢優化器正在做出不正確的假設或推論。這些類型問題的根本原因通常歸結為基數估計。另一方面，有時一個相對昂貴的查詢會執行很長時間。查看計劃中估計需要很長時間的部分可以提供有用的線索來解釋為什麼查詢執行了很長時間。但是，一些查詢調諧器會告訴您根本不要查看估計成本。

下面是一些範例查詢，只是為了表明估計成本和執行時間之間可能存在極大差異。我正在 SQL Server 2017 上進行測試，但可以在所有版本中提供類似的展示。首先，我將 100k 個連續整數放入一個堆中：

CREATE TABLE dbo.OptimizerUnits (ID BIGINT NOT NULL);

INSERT INTO dbo.OptimizerUnits WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

考慮以下查詢：

SELECT ID
FROM dbo.OptimizerUnits 
WHERE 
(ID % 10) % 101  = 10

人類可以查看該查詢並推斷它不會返回任何行，但優化器目前沒有內置這種邏輯。相反，它猜測將返回大約 990 行。這使以下查詢的總估計成本為 79590.1 個單位：

WITH OptimizerUnitsCTE (ID) AS 
(
   SELECT ID
   FROM dbo.OptimizerUnits 
   WHERE 
   (ID % 10) % 101  = 10
)
SELECT TOP (100) t1.ID, t2.ID, t3.ID
FROM OptimizerUnitsCTE t1
CROSS JOIN OptimizerUnitsCTE t2
CROSS JOIN OptimizerUnitsCTE t3
ORDER BY t1.ID + t2.ID + t3.ID DESC;

但是，查詢在我的機器上執行時間不到 50 毫秒。

現在讓我們朝另一個方向走。考慮以下查詢：

SELECT ID
FROM dbo.OptimizerUnits 
WHERE 
(ID % 10) % 101  = 1
AND (ID % 10) % 102  = 1
AND (ID % 10) % 103  = 1
AND (ID % 10) % 104  = 1

再一次，人類可以推斷出上述查詢將返回正好 10000 行。查詢優化器不知道這一點，它猜測查詢將只返回 16.7439 行。這導致以下查詢的估計成本為 1.45306 個優化器單元：

WITH OptimizerUnitsCTE (ID) AS 
(
   SELECT ID
   FROM dbo.OptimizerUnits 
   WHERE 
   (ID % 10) % 101  = 1
   AND (ID % 10) % 102  = 1
   AND (ID % 10) % 103  = 1
   AND (ID % 10) % 104  = 1
)
SELECT TOP (100) t1.ID, t2.ID, t3.ID
FROM OptimizerUnitsCTE t1
CROSS JOIN OptimizerUnitsCTE t2
CROSS JOIN OptimizerUnitsCTE t3
ORDER BY t1.ID + t2.ID + t3.ID DESC;

我在我的機器上執行了一段時間的查詢，估計大約需要 4.5 天才能完成。

總之，較差的基數估計使得成本為 79590.1 單位的查詢需要不到一秒的時間，而成本為 1.45306 單位的查詢需要大約 4.5 天。

引用自：https://dba.stackexchange.com/questions/190655