SQL Server 如何在記憶體中記憶體數據

October 30, 2019

我有幾個關於數據記憶體如何工作的問題
假設我們有以下情況：
伺服器重新啟動或我們剛剛執行DBCC DROPCLEANBUFFERS
我們有一個Table150 GB 的列A，有 , B, C, D,列E。
列A是聚集索引鍵，列B和C它們上有非聚集索引。
當我們這樣做
select top 100 * from Table1
是否將整個聚集索引（表）從磁碟讀取到記憶體，即使我們只需要 100 行？還是只有 100 行（它們的數據頁）從磁碟讀取到記憶體？ 2. 與非聚集索引相同，當我們這樣做時
select top 100 * from Table1 where column B = 'some value'
整個非聚集索引 + 聚集索引是否被載入到記憶體中？或者只有 100 行來自非聚集索引和 100 行來自聚集索引？

測試數據
CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL
                    ,B varchar(255),C int,D int,E  int);

INSERT  INTO dbo.Table1 WITH(TABLOCK)
(B,C,D,E)

SELECT TOP(1000000) 'Some Value ' + CAST((ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 400) as varchar(255))-- 400 different values
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

CREATE INDEX IX_B
On dbo.Table1(B);
CREATE INDEX IX_C
On dbo.Table1(C);
第一季度
當我們執行“從 Table1 中選擇前 100 個 *”時 - 整個聚集索引（表）正在從磁碟讀取到記憶體，即使我們只需要 100 行？還是僅將 100 行（它們的數據頁）從磁碟讀取到記憶體？
僅讀取聚集索引中的 100 行。結果，只有它們的數據頁被記憶體到記憶體中。
例子
在我的例子中，它從聚集索引向下讀取，列的 1 - 100 個值A。
SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1
內容如下：
Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
4 次邏輯讀取 = 4 頁
為什麼我們仍然看到你可能會問的scan count = 1邏輯4讀取？
因為它正在執行範圍掃描：
以滿足頂級運營商。
第二季度
與非聚集索引相同，當我們執行“從表 1 中選擇前 100 * 列 B = ‘某個值’”時，整個非聚集索引 + 聚集索引被載入到記憶體中？還是只有 100 行來自非聚集索引 + 100 行來自聚集索引？
範例 1
SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1
WHERE B='Some Value 200'
您可能希望在這裡使用非聚集索引，但實際上仍然使用聚集索引：
清除記憶體287邏輯讀取和2528預讀後讀取。
Table 'Table1'. Scan count 1, logical reads 287, physical reads 1, read-ahead reads 2528, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
我們在從磁碟讀取它們之後記憶體這些預讀，然後287從記憶體中讀取頁面。
預讀機制是 SQL Server 的功能，它甚至在查詢請求數據之前就將數據頁帶入緩衝區記憶體。來源
如果我們檢查記憶體頁面：
cached_pages_count  objectname  indexname   indexid
2536    Table1  PK__Table1__3BD019AE039DA497    1
我們也看到了這一點。
因此，在這種情況下，我們將記憶體更多頁面以更快地滿足查詢，因為我們正在讀取更多行以對其應用殘差謂詞：
但我們只是從聚集索引中讀取這些頁面。
範例 2
您可以更改查詢以利用非聚集索引：
select top 100 B from dbo.Table1
WHERE B='Some Value 200';
它的作用：
這再次給出了小範圍掃描：
Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
此處僅將非聚集索引頁載入到記憶體中。
範例 3
我們可能會通過使用索引提示來強制執行鍵查找以滿足範例 1 中的查詢：
SET STATISTICS IO, TIME ON;
select top 100 * 
from dbo.Table1
WITH(INDEX(IX_B))
WHERE B='Some Value 200';
這再次導致一些預讀讀取和比example1更多的邏輯讀取：
Table 'Table1'. Scan count 1, logical reads 684, physical reads 3, read-ahead reads 465, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
由於我們正在從非聚集索引到聚集索引進行鍵查找，我們將從兩個索引記憶體頁面：