緩衝池到底有多重要？

我一直在對用於替換現有生產伺服器的新（虛擬）伺服器進行一些測試。我們懷疑目前的生產伺服器規格過高，因此正在從低級別調整新伺服器的虛擬組件（RAM、CPU 等），直到性能適合處理目前工作負載。

雖然認識到新伺服器處理現有伺服器工作負載能力的真正測試是針對新伺服器測試該工作負載，但出於興趣，我對現有伺服器和新伺服器進行了一些簡單、任意的測試：

• 恢復數據庫 - 讀取速度
• 備份數據庫 - 寫入速度
• 大型數據庫上的 DBCC CHECKDB - 耗時

（為了使上述所有測試保持一致，兩台伺服器上都使用了相同的數據庫備份）

以上所有都支持新伺服器（更快的讀寫，更快的 CHECKDB）

我做的最後一個基本測試是測試在SELECT *冷記憶體和熱記憶體上的最大表之一上執行簡單操作所需的時間，以進一步了解讀取速度。

下面是我在兩台伺服器上執行的測試程式碼

USE StackOverflow

SET STATISTICS IO, TIME ON

CHECKPOINT

DBCC DROPCLEANBUFFERS

SELECT * FROM Users  /* cold cache run */
SELECT * FROM Users  /* warm cache run */

統計IO如下：

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 12 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
  CPU time = 62 ms,  elapsed time = 266 ms.

(14080580 rows affected)
Table 'Users'. Scan count 1, logical reads 250190, physical reads 1, page server reads 0, read-ahead reads 250200, page server read-ahead reads 0, lob logical reads 1387, lob physical reads 58, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 24047 ms,  elapsed time = 508456 ms.

(14080580 rows affected)
Table 'Users'. Scan count 1, logical reads 250190, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 1385, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 23297 ms,  elapsed time = 478627 ms.

我的兩台伺服器上的結果相似，但令我印象深刻的是，從磁碟而不是 RAM 讀取時，經過的時間僅慢了 30 秒。

我對 SQL Server 為何將所有內容讀入緩衝池的理解是 RAM 比磁碟快——在這個測試中，RAM 只快 6%。

基於上述，如果我的“現實生活”查詢是 2000 毫秒從記憶體中讀取，那麼從 RAM 讀取數據需要 3200 毫秒。可以說，這不是一個明顯的區別。

那麼，為什麼我們要竭盡全力確保數據和伺服器有盡可能多的 RAM 來記憶體數據，而它似乎只快 6% 呢？

我的問題是故意的，因為我覺得我在這裡忽略了一些東西，我很欣賞我的測試本質上非常簡單。

對我忽略的事情的想法：

• 如果數據在 RAM 中，則可以減少繁忙伺服器上的磁碟爭用
• 我很欣賞 RAM 用於 SQL Server 中的其他東西，而不僅僅是緩衝池
• SQL Server 大概在早期磁碟比現代 SSD 慢得多的時候就有這種架構

您為測量冷記憶體和熱記憶體的差異而執行的測試查詢將花費大部分執行時間等待ASYNC_NETWORK_IO。在您的情況下，將 1400 萬行發送到 SSMS 可能需要大約 450 秒。如果減去等待時間，您可能會發現冷記憶體查詢所需的時間大約是暖記憶體查詢的兩倍。

SQL Server 也有各種減少 I/O 等待時間的技巧。其中一個技巧是預讀讀取。您執行的查詢類型可能會從預讀中受益不少。幾乎所有的 I/O 都是通過預讀機製完成的：

預讀讀數 250200

並非所有查詢都能從預讀中獲得如此大的優勢。我碰巧寫了一篇最近的部落格文章，其中有一個由於 I/O 等待而導致查詢性能非常差的範例。複製相關部分：

執行儲存過程

$$ with the FAST_FORWARD option $$在我的機器上大約需要 2 毫秒。 …

遺憾的是，如果我刪除 FAST_FORWARD 選項，程式碼需要 50 秒才能在我的機器上執行。是什麼導致了執行時的巨大差異？

…

對於游標執行，查詢所需的 95% 的 I/O 都沒有預讀。當您必須使用 DOP 1 查詢執行 240000 次 I/O 時，即使是亞毫秒 I/O 延遲也會很痛苦。總之，FAST_FORWARD 游標能夠使用索引來有效地查找 20 個匹配的行。帶有預設選項的游標會執行大約 15 GB 的 I/O，這些 I/O 不符合預讀條件。

回到你的系統，你可以考慮COUNT_BIG(*)對桌子發出一個。這會將客戶端等待時間減少到幾乎為零，並將更直接地向您展示冷記憶體和熱記憶體之間的區別。如果你想在新伺服器上測試 I/O 而沒有預讀的好處，你可以嘗試執行這篇博文中提到的展示程式碼：

use tempdb;

CREATE TABLE #outer (
   ID BIGINT IDENTITY(1, 1) NOT NULL,
   DUMMY BIGINT NOT NULL,
   PRIMARY KEY (ID)
);
INSERT INTO #outer (DUMMY)
SELECT TOP (130000) 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
CREATE TABLE #inner (
   ID BIGINT IDENTITY(1, 1) NOT NULL,
   BIG_DUMMY CHAR(5000) NOT NULL,
   PRIMARY KEY (ID)
);
INSERT INTO #inner (BIG_DUMMY)
SELECT TOP (130000) '0'
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

SELECT COUNT_BIG(*)
FROM #outer o
WHERE o.DUMMY = 1
OR EXISTS (
   SELECT 1
   FROM #inner i
   WHERE i.ID = o.ID
)
OPTION (MAXDOP 1, QueryRuleOff BuildSpool);

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