SQL Server 메모리 부족 또는 메모리 부족 문제 해결

아티클
01/30/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 메모리로 인해 메모리를 할당하지 못했습니다.
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 엔진에 "외부"입니다. 예를 들어 연결된 서버, SQLCLR 프로시저 또는 함수, XP(확장 프로시저) 및 OLE 자동화()와 같은 OLE DB 공급자(sp_OA*DLL)가 있습니다. 그 외는 모니터링 목적을 위한 프로세스 안에 DLL을 주입하는 바이러스 백신 또는 그밖 보안 프로그램을 포함합니다. 이러한 구성 요소의 문제 또는 잘못된 디자인으로 인해 메모리 사용량이 커질 수 있습니다. 예를 들어 외부 원본에서 SQL Server 메모리로 2천만 행의 데이터를 캐싱하는 연결된 서버를 고려해 보세요. SQL Server 관련된 한 메모리 클럭은 높은 메모리 사용량을 보고하지 않지만 SQL Server 프로세스 내에서 사용되는 메모리는 높아집니다. 예를 들어 연결된 서버 DLL의 메모리 증가로 인해 SQL Server 메모리 사용량(위 참조)을 잘라내기 시작하고 SQL Server 내의 구성 요소에 대한 메모리 조건이 낮아져 메모리 부족 오류가 발생합니다. 이 문제에 대한 자세한 진단 및 해결 방법은 SQL Server 들어오는 것이 아니라 내부 메모리 압력을 참조하세요.

참고

SQL Server 프로세스 공간(예: MSOLEDBSQL, SQL Native Client)에 사용되는 몇 가지 Microsoft DLL은 보고 및 할당을 위해 SQL Server 메모리 인프라와 인터페이스할 수 있습니다. 를 실행 select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' 하여 목록을 가져와 일부 할당에 대한 메모리 사용량을 추적할 수 있습니다.

원인 3: SQL Server 구성 요소에서 발생하는 내부 메모리 압력

SQL Server 엔진 내의 구성 요소에서 발생하는 내부 메모리 압력으로 인해 메모리 부족 오류가 발생할 수도 있습니다. SQL Server 메모리를 할당하는 메모리 클럭을 통해 추적되는 수백 개의 구성 요소가 있습니다. 이 문제를 resolve 가장 큰 메모리 할당을 담당하는 메모리 클럭을 식별해야 합니다. 예를 들어 메모리 클럭에 OBJECTSTORE_LOCK_MANAGER 큰 메모리 할당이 표시되는 경우 Lock Manager에서 너무 많은 메모리를 사용하는 이유를 이해해야 합니다. 많은 잠금을 획득하는 쿼리가 있을 수 있습니다. 인덱스를 사용하거나, 오랫동안 잠금을 유지하는 트랜잭션을 줄이거나, 잠금 에스컬레이션을 사용하지 않도록 설정했는지 확인하여 이러한 쿼리를 최적화할 수 있습니다. 각 메모리 클럭 또는 구성 요소에는 메모리에 액세스하고 사용하는 고유한 방법이 있습니다. 자세한 내용은 메모리 클럭 유형 및 설명을 참조하세요. 이 문제에 대한 자세한 진단 및 해결 방법은 SQL Server 엔진별 내부 메모리 사용을 참조하세요.

메모리 압력 형식의 시각적 표현

다음 그래프는 SQL Server 메모리 부족 상태로 이어질 수 있는 압력 유형을 보여 줍니다.

메모리 압력 유형의 스크린샷.

문제 해결 데이터를 수집하는 진단 도구

다음 진단 도구를 사용하여 문제 해결 데이터를 수집할 수 있습니다.

성능 모니터

성능 모니터 사용하여 다음 카운터를 구성하고 수집합니다.

메모리:사용 가능한 MBytes
프로세스:작업 집합
프로세스:프라이빗 바이트
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 instance 이름을 마우스 오른쪽 단추로 클릭합니다.
상황에 맞는 메뉴에서 보고서>표준 보고서>메모리 사용량을 선택합니다.

PSSDiag 또는 SQL LogScout

이러한 데이터 요소를 캡처하는 자동화된 다른 방법은 PSSDiag 또는 SQL LogScout과 같은 도구를 사용하는 것입니다.

PSSDiag를 사용하는 경우 Perfmon 수집기 및 사용자 지정 진단\SQL 메모리 오류 수집기를 캡처하도록 구성합니다.
SQL LogScout을 사용하는 경우 메모리 시나리오를 캡처하도록 구성합니다.

다음 섹션에서는 각 시나리오(외부 또는 내부 메모리 압력)에 대한 자세한 단계를 설명합니다.

문제 해결 방법론

메모리 부족 오류가 가끔 나타나거나 짧은 기간 동안 발생하는 경우 자체적으로 해결되는 수명이 짧은 메모리 문제가 있을 수 있습니다. 이러한 경우에는 조치를 취할 필요가 없습니다. 그러나 여러 연결에서 오류가 여러 번 발생하고 몇 초 이상 지속되는 경우 다음 섹션의 진단 및 해결 방법을 따라 메모리 오류를 추가로 해결합니다.

외부 메모리 압력

SQL Server 프로세스 외부의 시스템에서 메모리 부족 상태를 진단하려면 다음 방법을 사용합니다.

성능 모니터 카운터를 수집합니다. SQL Server 이외의 애플리케이션 또는 서비스가 다음 카운터를 보고 이 서버에서 메모리를 사용하고 있는지 조사합니다.

메모리:사용 가능한 MBytes
프로세스:작업 집합
프로세스:프라이빗 바이트

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)) }
}
}

시스템 이벤트 로그를 검토하고 메모리 관련 오류(예: 낮은 가상 메모리)를 찾습니다.
애플리케이션 관련 메모리 문제에 대한 애플리케이션 이벤트 로그를 검토합니다.

다음은 시스템 및 애플리케이션 이벤트 로그에서 키워드(keyword) "메모리"를 쿼리하는 PowerShell 스크립트의 예입니다. 검색에 "리소스"와 같은 다른 문자열을 자유롭게 사용할 수 있습니다.
```
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: 총 서버 메모리(KB) 카운터에 반영됩니다. Process:Private Bytes와 SQL Server:Memory Manager: 총 서버 메모리(KB) 값 간에 상당한 차이가 있는 경우 해당 차이는 DLL(연결된 서버, XP, SQLCLR 등)에서 발생할 수 있습니다. 예를 들어 프라이빗 바이트가 300GB이고 총 서버 메모리가 250GB인 경우 프로세스의 전체 메모리 중 약 50GB가 SQL Server 엔진 외부에서 제공됩니다.
SQL Server AWE API(Locked Pages in Memory)를 사용하는 경우 성능 모니터 개별 프로세스의 메모리 사용량을 추적하는 AWE 카운터를 제공하지 않으므로 문제를 식별하는 것이 더 어렵습니다. SQL Server 엔진 내의 전체 메모리 사용량은 SQL Server:Memory Manager: 총 서버 메모리(KB) 카운터에 반영됩니다. 일반적인 프로세스:프라이빗 바이트 값은 전체 300MB에서 1-2GB 사이일 수 있습니다. 이 일반적인 사용 이외의 Process:Private Bytes 를 많이 사용하는 경우 DLL(연결된 서버, XP, SQLCLR 등)에서 차이가 발생할 수 있습니다. 예를 들어 프라이빗 바이트 카운터가 4-5GB이고 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 자동화 개체()가 사용되는 경우 컨텍스트 값 4(로컬(sp_OA*.exe) OLE 서버만 지정하여 SQL Server 외부 프로세스에서 실행되도록 개체를 구성할 수 있습니다. 자세한 내용은 sp_OACreate 참조하세요.

SQL Server 엔진별 내부 메모리 사용량

SQL Server 엔진 내의 구성 요소에서 발생하는 내부 메모리 압력을 진단하려면 다음 방법을 사용합니다.

SQL Server 대한 성능 모니터 카운터 수집을 시작합니다. SQL Server:Buffer Manager 및 SQL Server: 메모리 관리자.
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
```
메모리 클럭 중 명확한 위반자를 식별하는 경우 해당 구성 요소에 대한 메모리 사용량의 세부 사항을 해결하는 데 집중합니다. 다음은 몇 가지 예입니다.
- 메모리 클럭 MEMORYCLERK_SQLQERESERVATIONS 이 메모리를 사용하는 경우 거대한 메모리 부여를 사용하는 쿼리를 식별하고 인덱스를 통해 최적화하거나, 다시 작성하거나(예: 제거 ORDER by), 메모리 부여 쿼리 힌트를 적용합니다( min_grant_percent 및 max_grant_percent 힌트 참조). 메모리 부여 메모리의 사용을 제어하는 리소스 관리자 풀을 만들 수도 있습니다. 메모리 부여에 대한 자세한 내용은 SQL Server 메모리 부여로 인한 성능 저하 또는 메모리 부족 문제 해결을 참조하세요.
- 많은 수의 임시 쿼리 계획이 캐시되면 메모리 클럭은 CACHESTORE_SQLCP 많은 양의 메모리를 사용합니다. 쿼리 계획을 재사용할 수 없는 매개 변수가 없는 쿼리를 식별하고 저장 프로시저로 변환하거나, 를 사용 sp_executesql하거나, 매개 변수화를 사용하여 FORCED 매개 변수화합니다. 추적 플래그 174를 사용하도록 설정한 경우 이 플래그를 사용하지 않도록 설정하여 문제가 해결되었는지 확인할 수 있습니다.
- 개체 계획 캐시 저장소 CACHESTORE_OBJCP 가 너무 많은 메모리를 사용하는 경우 많은 양의 메모리를 사용하는 저장 프로시저, 함수 또는 트리거를 식별하고 애플리케이션을 다시 디자인할 수 있습니다. 일반적으로 이 오류는 각각에 수백 개의 프로시저가 있는 많은 양의 데이터베이스 또는 스키마로 인해 발생할 수 있습니다.
- 메모리 클럭에 OBJECTSTORE_LOCK_MANAGER 큰 메모리 할당이 표시되면 많은 잠금을 적용하는 쿼리를 식별하고 인덱스를 사용하여 최적화합니다. 잠금이 특정 격리 수준에서 오랫동안 해제되지 않거나 잠금 에스컬레이션이 사용하지 않도록 설정된 경우 검사 발생하는 트랜잭션을 줄입니다.
- 매우 큰 TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore')가 관찰되는 경우 추적 플래그 4618 을 사용하여 캐시 크기를 제한할 수 있습니다.
- 메모리 클럭에서 MEMORYCLERK_XTP 나오는 In-Memory OLTP와 관련된 메모리 문제를 관찰하는 경우 In-Memory OLTP 및 메모리 최적화 tempdb 메타데이터(HkTempDB)의 메모리 사용량 모니터링 및 문제 해결을 참조할 수 있습니다.

메모리를 사용할 수 있는 빠른 릴리프

다음 작업은 일부 메모리를 해제하고 SQL Server 사용할 수 있도록 할 수 있습니다.

메모리 구성 설정 변경

다음 SQL Server 메모리 구성 매개 변수를 확인하고 가능한 경우 최대 서버 메모리를 늘리는 것이 좋습니다.

최대 서버 메모리
최소 서버 메모리

참고

비정상적인 설정이 발견되면 필요에 따라 수정하고 증가된 메모리 요구 사항을 고려합니다. 기본 설정은 서버 메모리 구성 옵션에 나열됩니다.

최대 서버 메모리, 특히 메모리의 잠긴 페이지를 구성하지 않은 경우 OS에 대한 일부 메모리를 허용하도록 특정 값으로 설정하는 것이 좋습니다. 메모리 서버의 잠긴 페이지 구성 옵션을 참조하세요.

시스템에서 워크로드 변경 또는 이동

쿼리 워크로드를 조사합니다. 동시 세션 수, 현재 쿼리 실행 중, 일시적으로 중지되거나 다른 SQL Server 이동할 수 있는 덜 중요한 애플리케이션이 있는지 확인합니다.

읽기 전용 워크로드의 경우 Always On 환경에서 읽기 전용 보조 복제본(replica) 이동하는 것이 좋습니다. 자세한 내용은 읽기 전용 워크로드를 Always On 가용성 그룹의 보조 복제본(replica) 오프로드 및 Always On 가용성 그룹의 보조 복제본(replica) 대한 읽기 전용 액세스 구성을 참조하세요.

가상 머신에 대한 적절한 메모리 구성 확인

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(서버 리소스)을 늘려야 합니다.