目錄

• 一、Windows的虛擬內存管理器
• 二、SQL Server 內存體系結構
• 2.1、傳統（虛擬）內存
• 2.2、地址窗口擴展 （AWE） 內存
• 三、從 SQL Server 2012 （11.x） 開始發生的改變
• 3.1、對內存管理的更改
• 3.2、對memory_to_reserve所做的更改
• 四、動態內存管理
• 4.1、堆棧大小
• 五、緩沖區管理
• 5.1、緩沖區管理的工作原理
• 5.2、支持的功能
• 5.3、磁盤 I/O
• 5.4、長 I/O 請求
• 5.5、長時間 I/O 請求的原因
• 六、了解非一致性內存訪問

一、Windows的虛擬內存管理器

地址空間的已提交區域由 Windows 虛擬內存管理器 （VMM） 映射到可用的物理內存。

虛擬內存系統允許物理內存的過度使用，因此虛擬內存與物理內存的比率可以超過 1：1。因此，較大的程序可以在具有各種物理內存配置的計算機上運行。但是，使用比所有進程的總平均工作集多得多的虛擬內存可能會導致性能不佳。

二、SQL Server 內存體系結構

SQL Server 根據需要動態獲取和釋放內存。通常，管理員不必指定應分配給 SQL Server 的內存量，盡管該選項仍然存在并且在某些環境中是必需的。

所有數據庫軟件的主要設計目標之一是最小化磁盤 I/O，因為磁盤讀取和寫入是最耗費資源的操作之一。SQL Server 在內存中構建一個緩沖池，以保存從數據庫中讀取的頁。SQL Server 中的許多代碼專用于最大程度地減少磁盤和緩沖池之間的物理讀取和寫入次數。SQL Server 試圖在兩個目標之間取得平衡：

• 防止緩沖池變得太大，以致整個系統內存不足。
• 通過最大化緩沖池的大小，最大限度地減少數據庫文件的物理 I/O。

在負載較重的系統中，某些需要大量內存才能運行的大型查詢無法獲取請求的最小內存量，并在等待內存資源時收到超時錯誤。若要解決此問題，請增加查詢等待選項。對于并行查詢，請考慮降低最大并行度選項。

在內存壓力很大的系統中，當查詢未獲得位圖所需的最小內存時，查詢計劃中具有合并聯接、排序和位圖的查詢可能會刪除位圖。這可能會影響查詢性能，如果排序過程無法容納在內存中，則可能會增加數據庫中工作表的使用量，從而導致增長。若要解決此問題，請添加物理內存，或調整查詢以使用不同的更快查詢計劃。

2.1、傳統（虛擬）內存

所有 SQL Server 版本都支持 64 位平臺上的傳統內存。SQL Server 進程可以訪問虛擬地址空間，最高可達 x64 體系結構上的操作系統最大值（SQL Server 標準版最多支持 128 GB）。對于 IA64 體系結構，限制為 7 TB（SQL Server 64 （2012.x） 及更高版本不支持 IA11）。

2.2、地址窗口擴展 （AWE） 內存

通過使用地址窗口化擴展插件 （AWE） 和 AWE 所需的內存中鎖定頁 （LPIM） 特權，可以在低虛擬內存條件下將大部分 SQL Server 進程內存鎖定在物理 RAM 中。這在 32 位和 64 位 AWE 分配中都會發生。發生內存鎖定的原因是 AWE 內存不通過 Windows 中的虛擬內存管理器，該管理器控制內存分頁。AWE 內存分配 API 需要“鎖定內存中的頁”（SeLockMemoryPrivilege） 權限。因此，使用 AWE API 的主要好處是在系統上存在內存壓力時將大部分內存駐留在 RAM 中。

如果授予 LPIM，我們強烈建議您將最大服務器內存 （MB） 設置為特定值，而不是保留默認值 2，147，483，647 兆字節 （MB）。

如果未啟用 LPIM，SQL Server 將切換到使用常規內存，并且在操作系統內存耗盡的情況下，錯誤日志中可能會報告錯誤 17890。

三、從 SQL Server 2012 （11.x） 開始發生的改變

3.1、對內存管理的更改

從 SQL Server 2012 （11.x） 開始，單頁分配、多頁分配和 CLR 分配都合并到“任何大小”頁面分配器中，并包含在由最大服務器內存 （MB） 和最小服務器內存 （MB） 配置選項控制的內存限制中。此更改為通過 SQL Server 內存管理器的所有內存要求提供了更準確的大小調整功能。

從 SQL Server 2012 （11.x） 開始，SQL Server 分配的內存可能會超過最大服務器內存 （MB） 設置中指定的值。當總服務器內存 （KB） 值已達到目標服務器內存 （KB） 設置時，可能會發生此行為，如最大服務器內存 （MB） 指定。如果由于內存碎片而沒有足夠的連續可用內存來滿足多頁內存請求（超過 8 KB）的需求，則 SQL Server 可以執行過度使用，而不是拒絕內存請求。

執行此分配后，資源監視器后臺任務將開始向所有內存使用者發出釋放分配的內存的信號，并嘗試使總服務器內存 （KB） 值低于目標服務器內存 （KB） 規范。因此，SQL Server 內存使用情況可能會短暫超過最大服務器內存 （MB） 設置。在此情況下，總服務器內存 （KB） 性能計數器讀數將超過最大服務器內存 （MB） 和目標服務器內存 （KB） 設置。

3.2、對memory_to_reserve所做的更改

在較舊版本的 SQL Server 中，SQL Server 內存管理器留出一部分進程虛擬地址空間 （VAS） 供多頁分配器 （MPA）、CLR 分配器、SQL Server 進程中線程堆棧的內存分配和直接窗口分配 （DWA） 使用。虛擬地址空間的這一部分也稱為“內存要離開”或“非緩沖池”區域。

為這些分配保留的虛擬地址空間由memory_to_reserve配置選項確定。SQL Server 使用的默認值為 256 MB。

由于“任何大小”頁面分配器還處理大于 8 KB 的分配，因此memory_to_reserve值不包括多頁分配。除此更改外，此配置選項的其他所有內容都保持不變。

四、動態內存管理

SQL Server 數據庫引擎的默認內存管理行為是根據需要獲取盡可能多的內存，而不會在系統上造成內存不足。SQL Server 數據庫引擎通過使用 Microsoft Windows 中的內存通知 API 來執行此操作。

當 SQL Server 動態使用內存時，它會定期查詢系統以確定可用內存量。維護此可用內存可防止操作系統 （OS） 分頁。如果可用內存較少，SQL Server 將向操作系統釋放內存。如果有更多內存可用，SQL Server 可能會分配更多內存。SQL Server 僅在其工作負荷需要更多內存時才添加內存;靜態服務器不會增加其虛擬地址空間的大小。如果您注意到任務管理器和性能監視器在使用動態內存管理時顯示可用內存穩定減少，這是默認行為，不應被視為內存泄漏。

最大服務器內存控制 SQL Server 內存分配、編譯內存、所有緩存（包括緩沖池）、查詢執行內存授予、鎖管理器內存和 CLR1記憶（基本上是在sys.dm_os_memory_clerks中找到的任何記憶職員）。

CLR 內存在從 SQL Server 2012 （11.x） 開始的max_server_memory分配下進行管理。

示例：查詢返回有關當前分配的內存的信息。

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage,
    process_physical_memory_low AS sql_process_physical_memory_low,
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

4.1、堆棧大小

線程堆棧的內存、CLR、文件.dll擴展過程、分布式查詢引用的 OLE DB 提供程序、Transact-SQL 語句中引用的自動化對象以及非 SQL Server DLL 分配的任何內存不受最大服務器內存 （MB） 的控制。

當 SQL Server 啟動時，它會根據多個參數（如系統上的物理內存量、服務器線程數和各種啟動參數）計算緩沖池的虛擬地址空間的大小。SQL Server 為緩沖池保留其進程虛擬地址空間的計算量，但它僅獲取（提交）當前加載所需的物理內存量。

然后，實例會根據需要繼續獲取內存以支持工作負載。隨著越來越多的用戶連接和運行查詢，SQL Server 會按需獲取更多的物理內存。SQL Server 實例繼續獲取物理內存，直到達到其最大服務器內存 （MB） 分配目標或操作系統指示不再有多余的可用內存;當內存超過最小服務器內存設置時，它會釋放內存，并且操作系統指示可用內存不足。

當其他應用程序在運行 SQL Server 實例的計算機上啟動時，它們會消耗內存，并且可用物理內存量將低于 SQL Server 目標。SQL Server 實例調整其內存消耗。如果另一個應用程序停止并且有更多內存可用，則 SQL Server 實例會增加其內存分配的大小。SQL Server 每秒可以釋放和獲取幾兆字節的內存，從而能夠快速適應內存分配更改。

五、緩沖區管理

SQL Server 數據庫的主要用途是存儲和檢索數據，因此密集磁盤 I/O 是數據庫引擎的核心特征。由于磁盤 I/O 操作可能會消耗許多資源并且需要相對較長的時間才能完成，因此 SQL Server 專注于提高 I/O 的效率。緩沖液管理是實現這種效率的關鍵組成部分。緩沖區管理組件由兩種機制組成：用于訪問和更新數據庫頁的緩沖區管理器，以及用于減少數據庫文件 I/O 的緩沖區緩存（也稱為緩沖池）。

5.1、緩沖區管理的工作原理

緩沖區是內存中的 8 KB 頁，大小與數據頁或索引頁相同。因此，緩沖區緩存分為 8 KB 頁。緩沖區管理器管理將數據或索引頁從數據庫磁盤文件讀取到緩沖區緩存以及將修改的頁寫回磁盤的功能。頁將保留在緩沖區緩存中，直到緩沖區管理器需要緩沖區來讀取更多數據。僅當數據被修改時，數據才會寫回磁盤。緩沖區緩存中的數據在寫回磁盤之前可以多次修改。

當 SQL Server 啟動時，它會根據多個參數（如系統上的物理內存量、配置的最大服務器線程數和各種啟動參數）計算緩沖區緩存的虛擬地址空間的大小。SQL Server 為緩沖區緩存保留此計算量的進程虛擬地址空間（稱為內存目標），但它僅獲取（提交）當前加載所需的物理內存量。您可以查詢sys.dm_os_sys_info目錄視圖中的committed_target_kb列和committed_kb列，以分別返回保留為內存目標的頁數和緩沖區緩存中當前提交的頁數。

SQL Server 啟動和緩沖區緩存獲取其內存目標之間的時間間隔稱為上升。在此期間，讀取請求會根據需要填充緩沖區。例如，單個 8 KB 頁讀取請求填充單個緩沖區頁。這意味著上升取決于客戶端請求的數量和類型。通過將單頁讀取請求轉換為對齊的八頁請求（構成一個范圍）來加快提升速度。這使得爬坡可以更快地完成，尤其是在具有大量內存的機器上。

由于緩沖區管理器使用 SQL Server 進程中的大部分內存，因此它與內存管理器配合使用，以允許其他組件使用其緩沖區。緩沖區管理器主要與以下組件交互：

• 資源管理器，用于控制總體內存使用情況，在 32 位平臺中，用于控制地址空間使用情況。
• 用于低級別文件 I/O 操作的數據庫管理器和 SQL Server 操作系統 （SQLOS）。
• 用于預寫日志記錄的日志管理器。

5.2、支持的功能

• 緩沖區管理器可識別非一致性內存訪問 （NUMA）。緩沖區緩存頁分布在硬件 NUMA 節點上，這允許線程訪問在本地 NUMA 節點上分配的緩沖區頁，而不是從外部內存訪問。
• 緩沖區管理器支持熱添加內存，允許用戶在不重新啟動服務器的情況下添加物理內存。
• 緩沖區管理器支持 64 位平臺上的大頁面。頁面大小特定于 Windows 版本。
• 緩沖區管理器提供通過動態管理視圖公開的額外診斷。可以使用這些視圖監視特定于 SQL Server 的各種操作系統資源。

5.3、磁盤 I/O

緩沖區管理器僅執行對數據庫的讀取和寫入。其他文件和數據庫操作（如打開、關閉、擴展和收縮）由數據庫管理器和文件管理器組件執行。

緩沖區管理器的磁盤 I/O 操作具有以下特征：

• 所有 I/O 都是異步執行的，這允許調用線程繼續處理，而 I/O 操作在后臺進行。
• 所有 I/O 都在調用線程中發出，除非正在使用關聯 I/O 選項。關聯性 I/O 掩碼選項將 SQL Server 磁盤 I/O 綁定到指定的 CPU 子集。在高端 SQL Server 聯機事務處理 （OLTP） 環境中，此擴展可以增強發出 I/O 的 SQL Server 線程的性能。
• 多頁 I/O 通過分散-收集 I/O 完成，這允許將數據傳入或傳出不連續的內存區域。這意味著 SQL Server 可以快速填充或刷新緩沖區緩存，同時避免多個物理 I/O 請求。

5.4、長 I/O 請求

緩沖區管理器報告任何未完成至少 15 秒的 I/O 請求。這有助于系統管理員區分 SQL Server 問題和 I/O 子系統問題。報告錯誤消息 833，并顯示在 SQL Server 錯誤日志中。長 I/O 可以是讀取或寫入;消息中當前未指示。長 I/O 消息是警告，而不是錯誤。它們并不表示 SQL Server 的問題，而是表示底層 I/O 系統的問題。報告這些消息是為了幫助系統管理員更快地找到 SQL Server 響應時間不佳的原因，并區分 SQL Server 無法控制的問題。因此，它們不需要任何操作，但系統管理員應調查 I/O 請求花費這么長時間的原因，以及時間是否合理。

5.5、長時間 I/O 請求的原因

較長的 I/O 消息可能表示 I/O 被永久阻止且永遠不會完成（稱為丟失 I/O），或者只是表示它尚未完成。無法從消息中判斷出哪種情況，盡管丟失的 I/O 通常會導致閂鎖超時。
長 I/O 通常表示 SQL Server 工作負荷對于磁盤子系統來說過于密集。在以下情況下，可能指示磁盤子系統不足：

• 在繁重的 SQL Server 工作負荷期間，錯誤日志中會出現多個長 I/O 消息。
• 性能監視器計數器顯示較長的磁盤延遲、較長的磁盤隊列或無磁盤空閑時間。

長 I/O 也可能是由 I/O 路徑中的組件（例如，驅動程序、控制器或固件）不斷推遲處理舊的 I/O 請求，轉而處理更接近磁盤磁頭當前位置的新請求引起的。根據哪些請求最接近讀/寫頭的當前位置來優先處理請求的常用技術稱為“電梯搜索”。這可能很難用性能監視器工具證實，因為大多數 I/O 都會及時得到維護。執行大量順序 I/O 的工作負載（如備份和還原、表掃描、排序、創建索引、批量加載和清零文件）可能會加劇長 I/O 請求。

六、了解非一致性內存訪問

SQL Server 可識別非一致性內存訪問 （NUMA），并且在沒有特殊配置的 NUMA 硬件上表現良好。隨著時鐘速度和處理器數量的增加，減少使用這種額外處理能力所需的內存延遲變得越來越困難。為了規避這種情況，硬件供應商提供了大型 L3 緩存，但這只是一個有限的解決方案。NUMA 體系結構為此問題提供了可擴展的解決方案。

SQL Server 旨在利用基于 NUMA 的計算機，而無需進行任何應用程序更改。

到此這篇關于SQLServer的內存管理架構詳解的文章就介紹到這了,更多相關SQLServer內存管理架構內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！