基數估計如何影響 SQL Server 中的 CPU 和讀取？

試圖理解一個概念，不知道在哪裡尋找答案。我一直在研究基數估計及其對記憶的影響。在優化查詢以獲得更好的儲存過程基數估計的過程中，我發現了一些有趣的數據。

我們 DBA 的團隊負責人要求我優化一個儲存過程。這個特殊的過程對我們的伺服器造成了嚴重的問題，DBA 不確定為什麼，他試圖優化它但沒有任何成功。問題在於程序的簡單性。它正在加入 4 個表並返回大約 20 列。

他向我提供了一個電子表格，其中包含某一天超過 13,000 次處決的數據。該數據包括每次執行的參數、持續時間、CPU 和讀取次數。我對其進行了優化，並於 3 天前上線。昨天我詢問了它投入生產後第二天的類似數據，以便我可以查看優化是否有效。真正的問題是原始 proc 在測試中執行得非常好，即使使用電子表格中最差執行的參數，它也只有在生產中並且一天內與所有其他程序一起執行如此多的執行時才會出現問題正在執行。這個過程和奧秘（對我們來說）是我開始研究基數的原因，因為所有 JOIN 都在估計行和實際行上給了我一些巨大的差異。這導致我問這個問題試圖更多地了解統計數據的工作原理，昨天我問了這個關於記憶體授予的問題。

我今天的問題源於優化前後的執行數據。我已經在測試伺服器上測試了舊程序，如果 4 個表的索引中有 3 個具有更大的樣本量，那麼該程序就不需要更多優化。我花了很多時間來處理帶有附加表的 JOIN，並在臨時表上創建了幾個臨時表和索引，以便獲得更好的基數估計。

新的優化程序執行良好。它在當天的 11 小時時間跨度內執行了超過 54k 次。每次跑步的平均持續時間要好得多，但“只”快了 34%。我說“僅”是因為 CPU 的使用量減少了 98%，讀取量減少了 95.5%。這就是為什麼我放上一個故事。就像我提到的，原始過程在一次性執行的測試中執行良好，與新過程相比，我在 CPU 上獲得了 15-20% 的改進，並且讀取了一些更好的執行，幾個執行具有相同的 CPU 和一些有更差的 CPU、讀取或持續時間。

問題是，為什麼在執行繁重的生產環境中，基數估計會影響 CPU 和讀取這麼多？我正在尋找技術原因，如果存在相關性，這將包括在我下週做的一次展示中，以嘗試在我們的指數上推廣更好的樣本量。如果沒有相關性，我可以接受它作為答案，但是當真正改進的只是基數時，仍然知道什麼可能導致這種變化會很好。

首先，如果您可以為正在調整的查詢提供一個實際的執行計劃，那就太好了。否則，您將獲得的所有答案都會有些籠統，可能並不完全適用於您的情況。

問題是，為什麼在執行繁重的生產環境中，基數估計會影響 CPU 和讀取這麼多？

效果是間接的。一般來說，更好的基數估計將允許查詢優化器對執行計劃做出更好的選擇——這可能會導致更少的讀取和 CPU。

考慮這個簡單的查詢：

SELECT * 
FROM dbo.TableA a
ORDER BY a.[high];

結果應該按 排序high，並且列上有一個非聚集索引high。由於 SQL Server 只希望從索引掃描中看到 50 行，因此它決定從預先排序的索引中讀取數據，並進行 50 次鍵查找以獲取其餘列（以避免對數據進行排序）。

如果 SQL Server 期望 10,050 行從索引掃描中出來，我們將得到以下計劃：

它掃描按number列排序的聚集索引。然後它必須對該數據進行high排序以滿足ORDER BY查詢的一部分。此查詢具有以下 CPU 和 IO 特徵：

• CPU time = 0 ms, elapsed time = 326 ms.
• Table 'TableA'. Scan count 1, logical reads 95

如果我在有 10,000 行時強制執行鍵查找計劃（以模擬“壞基數估計”），則查詢具有以下特徵：

• CPU time = 15 ms, elapsed time = 305 ms.
• Table 'TableA'. Scan count 1, logical reads 20768

如您所見，錯誤的基數估計導致 I/O 和 CPU 增加。

這是一個非常簡化的範例，但希望它有助於說明一般觀點。

每次跑步的平均持續時間要好得多，但“只”快了 34%。我說“僅”是因為 CPU 的使用量減少了 98%，讀取量減少了 95.5%

如果讀取和 CPU 都下降了，但持續時間並沒有像您預期的那樣下降，原因可能與實時環境中的並發性有關。

大衛布朗很好地說明了這一點：

這通常表明查詢中使用的並行度有所降低。與單執行緒計劃相比，跨多個核心的並行掃描每秒產生更多的 CPU 和讀取。這是關注查詢調整持續時間的問題之一。

如果這是一個並行查詢，您在實時環境中獲得的並行度 (DOP) 可能比在您的測試中低。檢查DegreeOfParallelism屬性（例如<QueryPlan DegreeOfParallelism="8"）的執行計劃 XML 以查看它在兩個環境之間是否不同。

您調整的儲存過程可能會被阻止等待其他查詢釋放鎖。如果您能夠測量等待統計資訊，則此問題將顯示為LCK_*等待。這是“低 CPU，高持續時間”問題的典型原因。

或者，在某些情況下，儲存過程甚至需要一段時間才能開始執行。如果正在執行需要大量記憶體授予的其他查詢，您可能會遇到RESOURCE_SEMAPHORE等待。如果有很多並行查詢同時執行，尤其是當它們執行很長時間並等待鎖定時，您可能會遇到THREADPOOL等待。

這裡有一些資源可能有助於追踪在這種情況下生產中發生的事情：

• 使用SQL Server First Responder Kit
  • 執行 sp_Blitz 以大致了解伺服器上發生的情況，並查看是否存在任何真正糟糕的等待
  • 在一天中的代表性時間（發生問題時）執行 sp_BlitzFirst 以獲取有關等待、正在執行的查詢等資訊

---

如果您想重現我顯示的查詢結果以便自己解決這個問題，以下是定義：

CREATE TABLE [dbo].[TableA]
(
   [name] [nvarchar](35) NULL,
   [number] [int] NOT NULL,
   [type] [nchar](3) NOT NULL,
   [low] [int] NULL,
   [high] [int] NULL,
   [status] [int] NULL
);
CREATE CLUSTERED INDEX CX_number 
ON dbo.TableA ([number]);

CREATE NONCLUSTERED INDEX IX_high 
ON dbo.TableA ([high]);

INSERT INTO dbo.TableA
SELECT TOP (10000) v1.*
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2;

SET STATISTICS TIME, IO ON;

SELECT * 
FROM dbo.TableA a
ORDER BY a.[high];

SELECT * 
FROM dbo.TableA a WITH (INDEX (IX_high)) -- force the key lookup plan
ORDER BY a.[high];

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