沒有讀取的索引的緩衝區使用情況
使用 SQL Server 2008 R2，供應商數據庫中的主事務表與其他所有表相比都非常龐大，並且有大約 14 個索引。其中一些索引不會在我們的環境中使用，但我們無法刪除它們。這不是問題，這只是我們必須忍受的事情。
**我的問題是為什麼這些低或無讀取索引中的一些似乎正在使用記憶體 - 比同一個大表上經常使用的其他索引更多。**我原以為大部分緩衝區記憶體都會用於經常讀取的對象。這些索引上發生的唯一事情是寫入成本。
例如，其中一個低讀取索引分配了大約 2 GB 的記憶體（佔索引總大小的 58%），而另一個分配了 1.7 GB 的記憶體（佔其大小的 27%）。同時，怪物大小且使用良好的聚集索引本身只有 4 gigs（其大小的 2%）。具有大量讀取的不同 NC 索引在緩衝區記憶體中只有 100 MB（其大小的 5%）。
查看物理統計數據，我可以看到碎片非常糟糕。從該表上的所有寫入和非順序插入中可以理解這一點。不過，我不確定它是否與記憶體使用有關。
查看這些索引的操作統計數據也很有趣。
- Leaf_ghost_count 提醒我，該表上發生的刪除操作與插入操作的數量差不多（來自正常清理過程）。
- 這些低讀取索引之一具有數據庫中的一些最高 page_lock_wait 值。也許這僅僅是因為寫入？
- 其他 2 個具有一些最高的 page_io_latch_wait 值。我知道閂鎖的使用與記憶體使用有關，所以這是有道理的。
我意識到這是一個抽象的問題，我沒有提供很多實際的統計數據。我只是好奇 SQL Server 是如何做出這些緩衝區記憶體使用決策的，並想知道是否有人理解它。
Sql Server 的 Buffer Pool 是一個很奇妙的東西。它足夠聰明，可以以相當智能的方式處理各種情況。這裡有幾個例子顯示了緩衝池的行為乍看起來很奇怪，但實際上相當聰明。
考慮在具有 64 GB 記憶體可用於緩衝池的伺服器上的 400 GB 聚集索引。如果一個查詢掃描整個 400 GB 的索引，查詢完成后索引應該使用多少緩衝池？
可能不是很多。 如果 Sql Server 主動記憶體它訪問的所有數據，它在掃描期間讀取的最後 50GB 可能在記憶體中。但是，下次執行相同的查詢時，這 50GB 是沒有用的，因為 Sql 必須先從磁碟讀取前 350GB，然後才能到達最後的 50GB。這是一個足夠常見的場景，Sql 可以智能地處理它。相反，所有中間級別的索引頁面更有可能與一些葉子頁面一起被記憶體。
我有一個 512GB 記憶體的伺服器，但它似乎從來沒有使用過所有的記憶體，或者它使用它..奇怪？
Sql Server 支持 NUMA。 在 NUMA（非統一記憶體訪問）系統上，一些記憶體比其他記憶體更接近/更快地接近 CPU。例如，一個 4 插槽系統可能有 4 個記憶體控制器（每個插槽 1 個）。CPU 訪問連接到它的控制器上的記憶體比通過其他 cpu 的控制器訪問記憶體要快。（我已經看到訪問速度慢了 1.4-1.7 倍）。
Sql Server 知道這一點，並且更願意在遠端節點記憶體之前分配本地節點記憶體。由於請求可以在任何 cpu 上執行（它們通常不會在它們之間跳轉）並因此在任何節點上分配記憶體，因此訪問索引的第一個請求會將其頁面記憶體在本地記憶體中。在大多數情況下，您最終會得到相當均勻的記憶體頁面分佈，但是，每個節點都由 sql server 單獨管理，因此有效地，您的 512 GB 實際上是四個 128 GB 緩衝池。您可能會遇到這樣的情況，節點 1 使用全部 128GB，節點 2 使用 80GB，頁面從節點 1 被丟棄以騰出空間，而節點 2 有大量可用空間。
為什麼我從索引中大量讀取的記憶體比只接收寫入的索引要少？
一個可能的原因是檢查點。 根據您的恢復間隔，可能在數據更改發生幾分鐘後，檢查點將頁面寫入數據文件（它已經在磁碟上的日誌文件中）。在此期間，頁面不能被丟棄以在緩衝池中騰出空間。
除了設置大小之外，還有其他方法可以更改緩衝池的行為方式嗎？
**對緩衝池及其做出的決定沒有太多控制。**這很好，因為您通常不需要調整它。觀察“SQLServer:Buffer Manager”和“SQLServer:Buffer Node”的性能計數器可以深入了解緩衝池在做什麼，它的大小是否足夠，你的 IO 子系統是否可以跟上檢查點的速度。
關於你提到的幾個項目：
- 磁碟上的碎片不會直接影響記憶體使用。但是，如果一個索引由於刪除而在頁面上有 50% 的可用空間，它將在磁碟和記憶體中佔用兩倍的空間，但這與可用空間有關，而不是碎片。
- PAGE_IO_LATCH 等待測量 IO 子系統將頁面載入到記憶體中所需的時間。它們是磁碟性能而非記憶體的指標。
- 你是對的，高頁面鎖定等待是由於寫入。寫入使頁面變髒，並且臟頁必須停留在緩衝池中，直到它們被檢查點到磁碟。