針對記憶體不足或記憶體不足的 SQL Server

發行項
03/20/2024

徵狀

SQL Server 使用對應至複雜且豐富的功能集的複雜記憶體架構。由於各種記憶體需求，記憶體耗用量和記憶體壓力可能有許多來源，最終導致記憶體不足的情況。

有一些常見的錯誤表示 SQL Server 記憶體不足。錯誤的範例包括：

701：無法配置足夠的記憶體來執行查詢。
802：無法取得記憶體來配置緩衝池中的頁面 (數據或索引頁面) 。
1204：無法配置鎖定的記憶體。
6322：無法配置 XML 剖析器的記憶體。
6513：因記憶體壓力而無法初始化 CLR。
6533：AppDomain 因記憶體不足而卸除。
8318：因為記憶體不足而無法載入 SQL 性能計數器。
8356 或 8359：ETW 或 SQL 追蹤因記憶體不足而無法執行。
8556：因為記憶體不足而無法載入 MSDTC。
8645：無法執行查詢，因為記憶體沒有記憶體授與 (排序和哈希) 如需詳細資訊，請參閱如何針對錯誤 8645 SQL Server 進行疑難解答。
8902：無法在 DBCC 執行期間配置記憶體。
9695 或 9696：無法配置 Service Broker 作業的記憶體。
17131 或 17132：伺服器啟動失敗，因為記憶體不足。
17890：因為操作系統已將 SQL 記憶體分頁，所以無法配置記憶體。
22986 或 22987：因記憶體不足而導致異動數據擷取失敗。
25601：Xevent 引擎記憶體不足。
26053：SQL 網路介面因記憶體不足而無法初始化。
30085、30086、30094：SQL 全文檢索作業因記憶體不足而失敗。

原因

許多因素可能會導致記憶體不足。這些因素包括操作系統設定、物理記憶體可用性、在 SQL Server 內使用記憶體的元件，以及目前工作負載的記憶體限制。在大部分情況下，因記憶體不足錯誤而失敗的查詢並不是造成此錯誤的原因。整體而言，原因可以分成三個類別：

原因 1：外部或 OS 記憶體壓力

外部壓力是指來自進程外部元件的高記憶體使用率，導致記憶體不足而無法 SQL Server。您必須了解系統上的其他應用程式是否正在耗用記憶體，並導致記憶體可用性不足。 SQL Server 是極少數的應用程式之一，其設計目的是藉由縮減其記憶體使用量來回應OS記憶體壓力。這表示，如果應用程式或驅動程式要求記憶體，OS 會傳送訊號給所有應用程式以釋出記憶體，而 SQL Server 會藉由減少自己的記憶體使用量來回應。少數其他應用程式會回應，因為它們不是設計來接聽該通知。因此，如果 SQL Server 開始縮減其記憶體使用量，則其記憶體集區會減少，而且任何需要記憶體的元件都可能無法取得。因此，您會開始收到 701 或其他記憶體相關錯誤。如需 SQL 如何動態配置和釋放記憶體的詳細資訊，請參閱 SQL Server 記憶體架構。如需問題的詳細診斷和解決方案，請參閱本文中的外部記憶體壓力。

有三種廣泛的問題類別可能會造成OS記憶體壓力：

應用程式相關問題：一或多個應用程式一起耗盡可用的物理記憶體。 OS 會嘗試釋放一些記憶體，以響應資源的新應用程式要求。常見的方法是找出哪些應用程式耗盡記憶體，並採取必要步驟來平衡它們之間的記憶體，而不會導致 RAM 耗盡。
設備驅動器問題：如果驅動程式不正確地呼叫記憶體配置函式，設備驅動器可能會造成所有進程的工作集分頁。
操作系統產品問題。

如需這些步驟和疑難解答步驟的詳細說明，請參閱 MSSQLSERVER_17890。

原因 2：內部記憶體壓力，不是來自 SQL Server

內部記憶體壓力是指由 SQL Server 進程內的因素所造成的記憶體不足可用性。某些可能在 SQL Server 進程內執行的元件是 SQL Server 引擎的「外部」元件。範例包括 OLE DB 提供者 (DLL) ，例如鏈接的伺服器、SQLCLR 程式或函式、擴充程式 (XP) ，以及 OLE Automation (sp_OA*) 。其他包括防病毒軟體或其他安全性程式，這些安全性程式會將 DLL 插入進程以供監視之用。這些元件中任何一個元件中的問題或設計不佳，都可能導致大量記憶體耗用量。例如，假設鏈接的伺服器會將 2000 萬個數據列的數據從外部來源快取到 SQL Server 記憶體。就 SQL Server 而言，沒有任何記憶體 Clerk 會報告高記憶體使用量，但 SQL Server 進程內耗用的記憶體會很高。例如，鏈接伺服器 DLL 的記憶體成長會導致 SQL Server 開始減少其記憶體使用量， (如上) 所示，而且會為 SQL Server 內元件建立低記憶體條件，因而導致記憶體不足錯誤。如需問題的詳細診斷和解決方案，請參閱內部記憶體壓力，而不是來自 SQL Server。

注意事項

在 SQL Server 進程空間中使用的一些 Microsoft DLL (例如 MSOLEDBSQL、SQL Native Client) 能夠與 SQL Server 記憶體基礎結構進行介面，以進行報告和配置。您可以執行 select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' 來取得它們的清單，並追蹤其部分配置的記憶體耗用量。

原因 3：來自 SQL Server 元件 () 的內部記憶體壓力

來自 SQL Server 引擎內元件的內部記憶體壓力也可能導致記憶體不足錯誤。有數百個元件會透過記憶體 Clerk 追蹤，以在 SQL Server 中配置記憶體。您必須識別哪些記憶體 clerk () 負責最大的記憶體配置來解決此問題。例如，如果您發現 OBJECTSTORE_LOCK_MANAGER 記憶體 Clerk 顯示大量的記憶體配置，您必須瞭解為什麼鎖定管理員會耗用太多記憶體。您可能會發現有一些查詢會取得許多鎖定。您可以使用索引來優化這些查詢、縮短長時間保留鎖定的任何交易，或檢查鎖定擴大是否已停用。每個記憶體 Clerk 或元件都有存取和使用記憶體的唯一方式。如需詳細資訊，請參閱記憶體 Clerk 類型及其描述。如需問題的詳細診斷和解決方案，請參閱 SQL Server 引擎的內部記憶體使用量。

記憶體壓力類型的視覺表示法

下圖說明可能導致 SQL Server 記憶體不足狀況的壓力類型：

記憶體壓力類型的螢幕快照。

收集疑難解答數據的診斷工具

您可以使用下列診斷工具來收集疑難解答資料：

效能監視器

使用效能監視器設定並收集下列計數器：

記憶體：可用的 MBytes
Process：Working Set
Process：Private Bytes
SQL Server：記憶體管理員： (所有計數器)
SQL Server：Buffer Manager： (所有計數器)

DMV 或 DBCC MEMORYSTATUS

您可以使用 sys.dm_os_memory_clerks 或 DBCC MEMORYSTATUS 來觀察 SQL Server 內的整體記憶體使用量。

SSMS 中的記憶體耗用量標準報告

在 SQL Server Management Studio 中檢視記憶體使用量：

啟動 SQL Server Management Studio 並連線到伺服器。
在 物件總管 中，以滑鼠右鍵按兩下 SQL Server 實例名稱。
在操作功能表中，選取 [報告>標準報表>記憶體耗用量]。

PSSDiag 或 SQL LogScout

擷取這些數據點的另一個自動化方式是使用 PSSDiag 或 SQL LogScout 之類的工具。

如果您使用 PSSDiag，請將它設定為擷取 Perfmon 收集器和 自定義診斷\SQL 記憶體錯誤 收集器。
如果您使用 SQL LogScout，請將它設定為擷取 記憶體 案例。

下列各節說明每個案例的更詳細步驟， (外部或內部記憶體壓力) 。

疑難解答方法

如果偶爾出現記憶體不足錯誤，或是一小段時間，可能會有短期內存問題自行解決。在這些情況下，您可能不需要採取動作。不過，如果錯誤在多個連接上發生多次，且持續持續數秒或更久，請遵循下列各節中的診斷和解決方案，進一步針對記憶體錯誤進行疑難解答。

外部記憶體壓力

若要在 SQL Server 程式之外診斷系統上的記憶體不足狀況，請使用下列方法：

收集效能監視器計數器。查看下列計數器，調查 SQL Server 以外的應用程式或服務是否正在耗用此伺服器上的記憶體：

記憶體：可用的 MBytes
Process：Working Set
Process：Private Bytes

以下是使用 PowerShell 收集 Perfmon 記錄的範例：

clear
$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\Memory\Available MBytes"),
   ("\\$serverName" +"\Process(*)\Working Set"),
   ("\\$serverName" +"\Process(*)\Private Bytes")
)

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
$_.CounterSamples | ForEach-Object   {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
}
}

檢閱系統事件記錄檔，並尋找記憶體相關錯誤 (例如虛擬記憶體不足) 。
檢閱應用程式事件記錄檔，以瞭解應用程式相關的記憶體問題。

以下是 PowerShell 腳本的範例，可查詢系統和應用程式事件記錄檔中的關鍵詞「memory」。您可以隨意使用其他字串，例如搜尋的「資源」：
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
解決較不重要應用程式或服務的任何程式代碼或設定問題，以減少其記憶體使用量。
如果 SQL Server 以外的應用程式正在取用資源，請嘗試停止或重新排程這些應用程式，或考慮在不同的伺服器上執行它們。這些步驟將會移除外部記憶體壓力。

內部記憶體壓力，不是來自 SQL Server

若要診斷模組在 SQL Server 內 (DLL) 所造成的內部記憶體壓力，請使用下列方法：

如果 SQL Server 未使用記憶體中鎖定的頁面 (AWE API) ，其大部分的記憶體都會反映在效能監視器中的 Process：Private Bytes 計數器 (SQLServr 實例) 。來自 SQL Server 引擎內的整體記憶體使用量會反映在 SQL Server：Memory Manager： Total Server Memory (KB) 計數器中。如果您發現 Process：Private Bytes 和 SQL Server：Memory Manager： Total Server Memory (KB) 之間的顯著差異，則該差異可能來自於 DLL (鏈接伺服器、XP、SQLCLR 等) 。例如，如果私用位元組為300 GB，而伺服器記憶體總計為250 GB，則程式中大約50 GB的整體記憶體來自 SQL Server引擎外部。
如果 SQL Server 使用記憶體中的鎖定頁面 (AWE API) ，則識別問題會更具挑戰性，因為效能監視器不會提供追蹤個別進程記憶體使用量的 AWE 計數器。 SQL Server 引擎內的整體記憶體使用量會反映在 SQL Server：Memory Manager： Total Server Memory (KB) 計數器中。一般 Process：Private Bytes 值整體可能介於 300 MB 和 1-2 GB 之間。如果您發現 Process：Private Bytes 的大量使用方式超過此一般用途，則差異可能來自 DLL (連結的伺服器、XP、SQLCLR 等) 。例如，如果 Private bytes 計數器為 4-5 GB，且 SQL Server 使用記憶體中的鎖定頁面 (AWE) ，則私人位元組的很大一部分可能來自 SQL Server 引擎外部。這是近似技術。
使用 Tasklist 公用程式來識別在 SQL Server 空間內載入的任何 DLL：
```
tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
您也可以使用下列查詢來檢查載入的模組 (DLL) ，並查看是否有任何非預期的情況。
```
SELECT * FROM sys.dm_os_loaded_modules
```
如果您懷疑連結的伺服器模組造成大量的記憶體耗用量，您可以停用 [ 允許記憶體處理 ] 選項，將它設定為無法處理。如需詳細資訊，請參閱建立連結的伺服器。並非所有連結的伺服器 OLE DB 提供者都可能用完進程。如需詳細資訊，請連絡產品製造商。
在使用 OLE 自動化物件 () sp_OA* 的罕見情況下，您可以將內容值指定為 4 (僅限本機 (.exe) OLE 伺服器) ，以將物件設定為在 SQL Server 外部的進程中執行。如需詳細資訊，請參閱 sp_OACreate。

SQL Server 引擎的內部記憶體使用量

若要診斷來自 SQL Server 引擎內元件的內部記憶體壓力，請使用下列方法：

開始收集 SQL Server 的效能監視器計數器：SQL Server：Buffer Manager 和 SQL Server：Memory Manager。

多次查詢記憶體 SQL Server 員 DMV，以查看引擎內發生最高記憶體耗用量的位置：

SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC

或者，您可以觀察更詳細 DBCC MEMORYSTATUS 的輸出，以及當您看到這些錯誤訊息時變更的方式。
```
DBCC MEMORYSTATUS
```
如果您在記憶體 Clerk 之間識別出清楚的語句，請專注於解決該元件的記憶體耗用量細節。以下是數個範例：
- 如果記憶體 Clerk MEMORYCLERK_SQLQERESERVATIONS 正在取用記憶體，請識別使用大量記憶體授與的查詢，並透過索引加以優化、重寫它們 (移除 ORDER by，例如) ，或套用記憶體授與查詢提示 (查看 min_grant_percent和max_grant_percent提示 ) 。您也可以建立資源管理員集區來控制記憶體授與記憶體的使用量。如需記憶體授與的詳細資訊，請參閱針對 SQL Server 中記憶體授與所造成的效能緩慢或記憶體不足問題進行疑難解答。
- 如果快取大量臨機操作查詢計劃， CACHESTORE_SQLCP 記憶體 Clerk 會使用大量的記憶體。識別其查詢計劃無法重複使用的非參數化查詢，並藉由轉換成預存程式、使用 sp_executesql或使用 FORCED 參數化來將其參數化。如果您已啟用追蹤旗標 174，您可以停用它，以查看這是否可解決問題。
- 如果物件計劃快取存放區 CACHESTORE_OBJCP 耗用太多記憶體，請識別哪些預存程式、函式或觸發程式正在使用大量的記憶體，並可能重新設計應用程式。通常，這可能是因為有大量的資料庫或架構，每個都有數百個程式。
- OBJECTSTORE_LOCK_MANAGER如果記憶體 Clerk 顯示大量的記憶體配置，請識別套用許多鎖定的查詢，並使用索引加以優化。縮短導致鎖定不會在特定隔離等級中長時間釋放的交易，或檢查鎖定擴大是否已停用。
- 如果您觀察到非常大 TokenAndPermUserStore 的 select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore' () ，您可以使用追蹤旗標 4618 來限制快取的大小。
- 如果您觀察到來自 MEMORYCLERK_XTP 記憶體 Clerk 的 In-Memory OLTP 的記憶體問題，您可以參閱監視及疑難解答 In-Memory OLTP 的記憶體使用量和記憶體優化的 tempdb 元數據 (HkTempDB) 記憶體不足錯誤。

可讓記憶體可供使用的快速緩解

下列動作可能會釋放一些記憶體，並使其可供 SQL Server 使用：

變更記憶體組態設定

檢查下列 SQL Server 記憶體組態參數，並盡可能考慮增加伺服器記憶體上限：

最大伺服器記憶體
min server memory

注意事項

如果您注意到不尋常的設定，請視需要加以更正，並考慮增加的記憶體需求。預設設定會列在 [伺服器記憶體組態選項] 中。

如果您尚未設定 最大伺服器記憶體，特別是記憶體中鎖定的頁面，請考慮將它設定為特定值，以允許操作系統的一些記憶體。請參閱鎖定記憶體中的頁面伺服器組態選項。

變更或移除系統的工作負載

調查查詢工作負載：並行會話數目、目前正在執行查詢，以及查看是否有較不重要的應用程式可能暫時停止或移至另一個 SQL Server。

針對唯讀工作負載，請考慮將它們移至 Always On 環境中的唯讀次要複本。如需詳細資訊，請參閱將只讀工作負載卸除至 Always On 可用性群組的次要複本和設定 Always On 可用性群組之次要複本的只讀存取權。

確保虛擬機的記憶體設定正確

如果您在虛擬機 (VM) 上執行 SQL Server，請確定不會過度認可 VM 的記憶體。如需如何設定 VM 記憶體的概念，請參閱虛擬化 - 過度認可記憶體，以及如何在 VM 中偵測記憶體和針對記憶體過度認可 (ESX/ESXi 虛擬機效能問題進行疑難解答) 。

釋放記憶體 SQL Server

您可以執行下列一或多個 DBCC 命令，以釋放數個 SQL Server 記憶體快取：

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

重新啟動 SQL Server 服務

在某些情況下，如果您需要處理記憶體嚴重耗盡，SQL Server 無法處理查詢，您可以考慮重新啟動服務。

請考慮針對特定案例使用 Resource Governor

如果您使用 Resource Governor，建議您檢查資源集區和工作負載群組設定，以查看它們是否未大幅限制記憶體。

在實體或虛擬伺服器上新增更多 RAM

如果問題持續發生，您必須進一步調查，並可能增加 RAM) (伺服器資源。

共用方式為