針對 SQL Server 低記憶體或記憶體不足的問題進行疑難排解

發行項
12/23/2024

徵兆

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

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

701：無法配置足夠的記憶體來執行查詢。
802：無法取得內存在緩衝池中配置頁面（數據或索引頁）。
1204：無法配置鎖定的記憶體。
6322：無法配置 XML 剖析器記憶體。
6513：由於記憶體壓力而無法初始化 CLR。
6533：AppDomain 因記憶體不足而卸除。
8318：由於記憶體不足而無法載入 SQL 性能計數器。
8356 或 8359：ETW 或 SQL 追蹤因記憶體不足而無法執行。
8556：由於記憶體不足而無法載入 MSDTC。
8645：由於記憶體授與的記憶體不足而無法執行查詢（排序和哈希），如需詳細資訊，請參閱如何針對 SQL Server 錯誤 8645 進行疑難解答。
8902：在 DBCC 執行期間無法配置記憶體。
9695 或 9696：無法配置 Service Broker 作業的記憶體。
17131 或 17132：伺服器啟動失敗，因為記憶體不足。
17890：由於OS正在分頁的SQL記憶體而無法配置記憶體。
18053：錯誤會以 terse 模式列印，因為在格式化期間發生錯誤。已略過追蹤、ETW、通知等等。
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記憶體壓力：

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

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

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

內部記憶體壓力是指 SQL Server 進程內因素所造成的記憶體可用性不足。 SQL Server 進程內可能執行的一些元件是 SQL Server 引擎的「外部」。範例包括 OLE DB 提供者（DLL），例如鏈接的伺服器、SQLCLR 程式或函式、擴充程式（XPs）和 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（s）負責最大的記憶體配置，以解決此問題。例如，如果您發現 OBJECTSTORE_LOCK_MANAGER 記憶體 Clerk 顯示大型記憶體配置，您需要瞭解鎖定管理員為何耗用這麼多記憶體。您可能會發現有查詢可取得許多鎖定。您可以使用索引來優化這些查詢、縮短長時間保留鎖定的任何交易，或檢查鎖定擴大是否停用。每個記憶體 Clerk 或元件都有唯一的存取和使用記憶體方式。如需詳細資訊，請參閱記憶體 Clerk 類型及其描述。如需問題的詳細診斷和解決方案，請參閱 SQL Server 引擎的內部記憶體使用量。

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

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

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

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

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

效能監視器

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

Memory：Available MBytes
Process：Working Set
Process：Private Bytes
SQL Server：Memory Manager：（所有計數器）
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 以外的應用程式或服務是否耗用此伺服器上的記憶體：

Memory：Available 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」的 System 和 Application 事件記錄檔。您可以隨意使用其他字串，例如搜尋的「資源」：
```
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 計數器（SQLServrinstance）中。 SQL Server 引擎內的整體記憶體使用量會 反映在 SQL Server：Memory Manager：Total Server Memory （KB） 計數器中。如果您在 Process：Private Bytes 和 SQL Server：Memory Manager 值之間發現顯著差異：總伺服器記憶體（KB），該差異可能來自 DLL（鏈接的伺服器、XP、SQLCLR 等等）。例如，如果 Private 位元組 為 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 記憶體 Clerks 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 （），您可以使用追蹤旗標 4618 來限制select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore'快取的大小。
- 如果您觀察到記憶體內部 OLTP 來自MEMORYCLERK_XTP記憶體 Clerk 的記憶體問題，您可以參閱監視記憶體內部 OLTP 和記憶體優化 tempdb 元數據（HkTempDB）記憶體不足的錯誤，並針對記憶體內部 OLTP 的記憶體使用量進行疑難解答。

快速緩解可能會讓記憶體可用

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

變更記憶體組態設定

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

最大伺服器記憶體
最小伺服器記憶體

注意

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

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

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

調查查詢工作負載：並行會話數目、目前正在執行的查詢，並查看是否有較不重要的應用程式暫時停止或移至另一個 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 無法處理查詢，您可以考慮重新啟動服務。

請考慮針對特定案例使用資源管理員

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

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

如果問題持續發生，您需要進一步調查，並可能增加伺服器資源（RAM）。

共用方式為