메모리 부여 피드백

적용 대상: SQL Server 2017(14.x) 이상, Azure SQL Managed Instance, Azure SQL Database

너무 크거나 너무 작은 메모리 부여를 사용하여 쿼리가 실행되는 경우가 있습니다. 메모리 부여가 너무 크면 서버에서 병렬 처리를 금지합니다. 메모리 부여가 너무 작으면 디스크에 스필될 수 있으며, 이는 비용이 많이 드는 작업입니다. 메모리 부여 피드백은 이전 실행의 메모리 필요량을 기억하려고 시도합니다(백분위수 피드백, 여러 이전 실행 포함). 이 과거 쿼리 정보에 따라 메모리 부여 피드백은 후속 실행에서 쿼리에 지정된 권한을 적절하게 조정합니다.

이 기능은 세 가지 웨이브로 릴리스되었습니다. 배치 모드 메모리 부여 피드백에 이어 행 모드 메모리 부여 피드백이 도입되었고, SQL Server 2022(16.x)에서는 쿼리 스토어를 사용한 디스크 내 지속성 메모리 부여 피드백 및 백분위수 부여라는 향상된 알고리즘이 도입되었습니다.

참고 항목

다른 쿼리 피드백 기능은 카디널리티 예측(CE) 피드백 및 병렬 처리 수준(DOP) 피드백을 참조하세요.

일괄 처리 모드 메모리 부여 피드백

적용 대상: SQL Server(SQL Server 2017(14.x)부터), Azure SQL 데이터베이스, Azure SQL Managed Instance(데이터베이스 호환성 수준 140부터)

쿼리 실행 후 계획에는 실행에 필요한 최소 필수 메모리 및 모든 행을 메모리에 포함하기 위한 이상적인 메모리 부여 크기가 포함됩니다. 메모리 부여 크기가 잘못 지정된 경우 성능이 저하됩니다. 과도하게 부여하면 메모리가 낭비되고 동시성이 줄어듭니다. 메모리 부여가 부족하면 디스크로 분산되어 비용이 증가합니다. 반복 워크로드를 처리함으로써 일괄 처리 모드 메모리 부여 피드백은 쿼리에 필요한 실제 메모리를 다시 계산한 후 캐시된 계획에 대한 부여 값을 업데이트합니다. 동일한 쿼리 문을 실행할 경우 쿼리는 수정된 메모리 부여 크기를 사용하여 동시성에 영향을 주는 과도한 메모리 부여를 줄이고 디스크로 스필하여 비용을 늘리는 부족한 메모리 부여를 수정합니다.

다음 그래프에서는 일괄 처리 모드 적응 메모리 부여 피드백을 사용하는 한 가지 예를 보여 줍니다. 쿼리를 처음 실행하는 경우 높은 스필로 인해 기간이 88초였습니다.

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

메모리 부여 피드백을 활성화하면 두 번째 실행의 경우 지속 시간이 1초(88초에서 감소)로 감소하고 스필이 완전히 제거되며 부여량이 더 증가합니다.

메모리 부여 피드백 크기 조정

과도한 메모리 부여 조건의 경우 부여된 메모리가 실제로 사용된 메모리 크기의 두 배를 초과하면 메모리 부여 피드백이 메모리 부여를 다시 계산하고 캐시된 계획을 업데이트합니다. 메모리 부여가 1MB 미만인 계획은 초과분에 대해 다시 계산되지 않습니다.

크기가 부족한 메모리 부여 조건의 경우 일괄 처리 모드 연산자에 대해 디스크로 스필이 발생하며 메모리 부여 피드백이 메모리 부여 다시 계산을 트리거합니다. 분산 이벤트는 메모리 부여 피드백에 보고되며, spilling_report_to_memory_grant_feedback 확장 이벤트를 통해 표시될 수 있습니다. 이 이벤트는 계획의 노드 ID와 해당 노드의 분산 데이터 크기를 반환합니다.

조정된 메모리 부여는 GrantedMemory 속성을 통해 실제(실행 후) 계획에 표시됩니다.

그래픽 showplan의 루트 연산자 또는 showplan XML 출력에서 이 속성을 볼 수 있습니다.

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

워크로드가 이 개선에 자동으로 적합하도록 하려면 데이터베이스에 대해 호환성 수준 140을 사용하도록 설정합니다.

예시:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

메모리 부여 피드백 및 매개 변수가 중요한 시나리오

최적 상태를 유지하려면 매개 변수 값마다 다른 쿼리 계획이 필요할 수 있습니다. 이러한 유형의 쿼리를 "매개 변수가 중요한" 쿼리로 정의합니다.

매개 변수가 중요한 계획의 경우, 메모리 부여 피드백은 불안정한 메모리 요구 사항이 있는 경우 쿼리에서 자동으로 비활성화됩니다. 쿼리가 여러 번 반복 실행된 후 메모리 부여 피드백 기능이 비활성화되며, 이는 memory_grant_feedback_loop_disabled 확장 이벤트를 모니터링하여 확인할 수 있습니다. 이 조건은 SQL Server 2022(16.x)에 도입된 메모리 부여 피드백의 지속성 및 백분위수 모드를 통해 완화됩니다. 메모리 부여 피드백의 지속성 기능을 사용하려면 데이터베이스에서 쿼리 저장소 사용하도록 설정하고 "쓰기 읽기" 모드로 설정해야 합니다.

매개 변수 스니핑 및 매개 변수 민감도에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

메모리 부여 피드백 캐싱

피드백은 단일 실행을 위해 캐시된 계획에 저장할 수 있습니다. 그러나 해당 문을 연속적으로 실행하는 경우에 메모리 부여 피드백 조정의 이점을 누릴 수 있습니다. 이 기능은 문의 반복 실행에 적용됩니다. 메모리 부여 피드백은 캐시된 계획만 변경합니다. SQL Server 2022(16.x) 이전에는 변경 내용이 쿼리 저장소 캡처되지 않았습니다.

계획이 캐시에서 제거되면 피드백이 유지되지 않습니다. 장애 조치(failover)가 수행되는 경우에도 피드백이 손실됩니다. OPTION (RECOMPILE)을 사용하는 문은 새 계획을 만들지만 캐시하지 않습니다. 캐시되지 않으므로 메모리 부여 피드백이 생성되지 않으며 해당 컴파일 및 실행에 대해 저장되지 않습니다. 그러나 OPTION (RECOMPILE)을 사용하지 않은 동등한 문(즉, 동일한 쿼리 해시를 사용하는)이 캐시된 후 다시 실행되면 두 번째 이후 연속 실행은 메모리 부여 피드백의 이점을 누릴 수 있습니다.

메모리 부여 피드백 활동 추적

memory_grant_updated_by_feedback 확장 이벤트를 사용하여 메모리 부여 피드백 이벤트를 추적할 수 있습니다. 이 이벤트는 현재 실행 횟수 기록, 메모리 부여 피드백에 의해 계획이 업데이트된 횟수, 수정 전 이상적인 추가 메모리 부여, 메모리 부여 피드백이 캐시된 계획을 수정한 후 이상적인 추가 메모리 부여를 추적합니다.

메모리 부여 피드백, Resource Governor 및 쿼리 힌트

부여되는 실제 메모리는 리소스 관리자 또는 쿼리 힌트에 따른 쿼리 메모리 제한을 준수합니다.

호환성 수준을 변경하지 않고 배치 모드 메모리 부여 피드백 사용 안 함

데이터베이스 호환성 수준 140 이상을 유지하면서 데이터베이스 또는 문 범위에서 메모리 부여 피드백을 사용하지 않도록 설정할 수 있습니다. 데이터베이스에서 발생하는 모든 쿼리 실행에 대해 배치 모드 메모리 부여 피드백을 사용하지 않도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 아래 SQL 문을 실행합니다.

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

사용으로 설정하면 sys.database_scoped_configurations에서 이 설정이 enabled로 표시됩니다.

데이터베이스에서 발생하는 모든 쿼리 실행에 대해 배치 모드 메모리 부여 피드백을 다시 사용하도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 다음 SQL 문을 실행합니다.

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

또한 DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK을 USE HINT 쿼리 힌트로 지정하여 특정 쿼리에 대한 배치 모드 메모리 부여 피드백을 사용하지 않도록 설정할 수도 있습니다. 예:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

USE HINT 쿼리 힌트는 데이터베이스 범위 구성 또는 추적 플래그 설정보다 우선합니다.

행 모드 메모리 부여 피드백

적용 대상: SQL Server(SQL Server 2019(15.x)부터), Azure SQL 데이터베이스, Azure SQL Managed Instance(데이터베이스 호환성 수준 150부터)

행 모드 메모리 부여 피드백은 일괄 처리 및 행 모드 연산자의 메모리 부여 크기를 둘 다 조정하여 일괄 처리 모드 메모리 부여 피드백 기능을 확장합니다.

Azure SQL Database에서 행 모드 메모리 부여 피드백을 사용하도록 설정하려면 쿼리를 실행할 때 연결된 데이터베이스의 데이터베이스 호환성 수준 150 이상을 사용하도록 설정합니다.

예시:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

일괄 처리 모드 메모리 부여 피드백과 마찬가지로 memory_grant_updated_by_feedback XEvent를 통해 행 모드 메모리 부여 피드백 작업이 표시됩니다. 또한 행 모드와 배치 모드 모두에서 메모리 부여 피드백 작업의 현재 상태를 더 잘 파악할 수 있도록 두 가지 새로운 쿼리 실행 계획 특성이 도입될 예정입니다.

메모리 부여 피드백에는 쿼리 저장소가 필요하지 않지만, SQL Server 2022(16.x)에 도입된 지속성 개선 사항에서는 데이터베이스에 대해 쿼리 저장소가 활성화되어 있고 “읽기 쓰기” 상태여야 합니다. 지속성에 대한 자세한 내용은 이 문서 뒷부분의 백분위수 및 지속성 모드 메모리 부여 피드백을 참조하세요.

행 모드 메모리 부여 피드백 작업은 memory_grant_updated_by_feedback 확장 이벤트를 통해 표시됩니다.

행 모드 메모리 부여 피드백부터 실제 실행 후 계획의 경우 MemoryGrantInfo 쿼리 계획 XML 요소에 추가되는 두 개의 새 쿼리 계획 특성인 IsMemoryGrantFeedbackAdjusted 및 LastRequestedMemory가 표시됩니다.

• LastRequestedMemory 특성은 이전 쿼리 실행에서 부여된 메모리를 KB(킬로바이트) 단위로 표시합니다.
• IsMemoryGrantFeedbackAdjusted 특성을 사용하면 실제 쿼리 실행 계획 내의 명령문에 대한 메모리 부여 피드백의 상태를 확인할 수 있습니다.

이 특성에 표시되는 값은 다음과 같습니다.

IsMemoryGrantFeedbackAdjusted 값	설명
아니요: 처음 실행	메모리 부여 피드백은 첫 번째 컴파일 및 관련 실행에 대한 메모리를 조정하지 않습니다.
아니요: 정확한 부여	디스크에 분산되지 않고 문이 부여된 메모리의 50% 이상을 사용하는 경우 메모리 부여 피드백이 트리거되지 않습니다.
아니요: 피드백 사용 안 함	메모리 부여 피드백이 지속적으로 트리거되고 메모리 증가 작업과 메모리 감소 작업 간에 변동하는 경우 데이터베이스 엔진은 문에 대한 메모리 부여 피드백을 사용하지 않도록 설정합니다.
예: 조정	메모리 부여 피드백이 적용되었으며 다음 실행에서 추가로 조정될 수 있습니다.
예: 백분위수 조정	메모리 부여 피드백은 가장 최근 실행뿐만 아니라 더 많은 기록을 살펴보는 백분위수 부여 알고리즘을 사용하여 적용 중입니다.
예: 안정적	메모리 부여 피드백이 적용되어 이제 이전 실행에 마지막으로 부여된 메모리가 현재 실행에 부여된 메모리로 안정적으로 유지됩니다.

백분위수 및 지속성 모드 메모리 부여 피드백

적용 대상: SQL Server(SQL Server 2022(16.x)부터), Azure SQL 데이터베이스, Azure SQL Managed Instance(현재, 지속성만 해당)

이 기능은 SQL Server 2022(16.x)에서 도입되지만 이 성능 개선은 데이터베이스 호환성 수준 140(SQL Server 2017에서 도입됨) 이상 또는 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 힌트 140 이상에서 작동하는 쿼리에 대해, 쿼리 저장소가 데이터베이스에서 사용되도록 설정하고 "읽기 쓰기" 상태인 경우에 사용할 수 있습니다.

• 백분위수 메모리 부여 피드백은 SQL Server 2022(16.x)에서 기본적으로 사용하도록 설정되지만, 쿼리 저장소 사용하도록 설정되지 않았거나 쿼리 저장소 "읽기 쓰기" 상태가 아닌 경우에는 적용되지 않습니다.
• 메모리 부여, CE 및 DOP 피드백에 대한 지속성은 SQL Server 2022(16.x)에서 기본적으로 사용하도록 설정되지만, 쿼리 저장소 사용하도록 설정되지 않았거나 쿼리 저장소 "읽기 쓰기" 상태가 아닌 경우에는 적용되지 않습니다.
• 메모리 부여 피드백에 대한 백분위수 및 지속성은 Azure SQL Database에서 사용할 수 있으며, 기존 데이터베이스와 새 데이터베이스 모두에서 기본적으로 사용하도록 설정됩니다.
• 메모리 부여 피드백에 대한 백분위수 및 지속성은 현재 Azure SQL Managed Instance에서 사용할 수 없습니다.

데이터베이스에 이 기능을 사용하도록 설정하기 전에 워크로드에 대한 성능 기준을 설정하는 것이 좋습니다. 기준 번호를 사용하면 기능에서 의도한 이점을 얻을 수 있는지 확인하는 데 도움이 됩니다.

메모리 부여 피드백(MGF)은 과거 성능에 따라 쿼리에 할당된 메모리 크기를 조정하는 기존 기능입니다. 그러나 이 프로젝트의 초기 단계에서는 계획과 함께 메모리 부여 조정만 캐시에 저장했습니다. 계획이 캐시에서 제거되면 피드백 프로세스가 다시 시작되어야 하므로 제거 후 쿼리가 처음 몇 번 실행될 때 성능이 저하됩니다. 새로운 해결책은 캐시 제거 시에도 이점이 지속되도록 쿼리 저장소에 있는 다른 쿼리 정보와 함께 부여 정보를 유지하는 것입니다. 메모리 부여 피드백 지속성과 백분위수는 방해가 없는 방식으로 메모리 부여 피드백의 기존 제한 사항을 해결합니다.

또한 권한 부여 크기 조정은 가장 최근에 사용한 권한 부여만을 고려했습니다. 따라서 매개 변수가 있는 쿼리 또는 워크로드가 실행될 때마다 메모리 부여 크기가 크게 달라지는 경우 가장 최근의 부여 정보가 부정확할 수 있습니다. 실행 중인 쿼리의 실제 요구 사항과 일치하지 않을 수 있습니다. 이러한 상황에서 메모리 부여 피드백은 항상 마지막으로 사용한 부여 값을 기준으로 메모리를 조정하기 때문에 성능에 도움이 되지 않습니다. 다음 이미지는 백분위수 및 지속성 모드가 적용되지 않은 메모리 부여 피드백으로 가능한 동작을 보여줍니다.

보시다시피, 이 비정상적이지만 가능한 쿼리 동작에서 실제 필요한 메모리 양과 부여된 메모리 양 사이의 변동으로 인해 쿼리 실행 자체가 메모리 양을 번갈아 가며 수행되는 경우 메모리 낭비와 부족이 발생하게 됩니다. 이러한 경우 메모리 부여 피드백은 득보다 실이 많다는 것을 인식하여 스스로 사용 안 함으로 설정합니다.

단순히 마지막 실행이 아닌 최근 쿼리 기록에 대한 백분위수 기반 계산을 사용하면 과거 실행 사용 기록을 기반으로 부여 크기 값을 부드럽게 조정하고 스필을 최소화하도록 최적화할 수 있습니다. 예를 들어, 동일한 교대 워크로드에서 다음과 같은 메모리 부여 동작을 볼 수 있습니다.

쿼리 최적화 도구는 캐시된 계획 실행에 대한 과거 메모리 부여 크기 요구 사항의 높은 백분위수를 사용하여 쿼리 저장소에 유지된 데이터를 사용하여 메모리 부여 크기를 계산합니다. 메모리 부여 조정을 수행하는 백분위수 조정은 최근 실행 기록을 기반으로 합니다. 시간이 지남에 따라 부여되는 메모리 부여는 스필과 낭비되는 메모리를 줄여줍니다.

지속성은 DOP 피드백 및 CE 피드백에도 적용됩니다.

메모리 부여 피드백 기능 사용 및 사용 안 함

호환성 수준을 변경하지 않고 행 모드 메모리 부여 피드백 사용 안 함

데이터베이스 호환성 수준 150 이상을 유지하면서 데이터베이스 또는 문 범위에서 행 모드 메모리 부여 피드백을 사용하지 않도록 설정할 수 있습니다 데이터베이스에서 발생하는 모든 쿼리 실행에 대해 행 모드 메모리 부여 피드백을 사용하지 않도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 다음 SQL 문을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

데이터베이스에서 발생하는 모든 쿼리 실행에 대한 행 모드 메모리 부여 피드백을 재활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

또한 DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK을 USE HINT 쿼리 힌트로 지정하여 특정 쿼리에 대한 행 모드 메모리 부여 피드백을 사용하지 않도록 설정할 수도 있습니다. 예:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

USE HINT 쿼리 힌트는 데이터베이스 범위 구성 또는 추적 플래그 설정보다 우선합니다.

메모리 부여 피드백 지속성 및 백분위수 사용

지속성 및 백분위수 피드백은 Azure SQL Database 및 SQL Server 2022(16.x)에서 기본적으로 사용하도록 설정됩니다.

쿼리를 실행할 때 연결된 데이터베이스에 대해 데이터베이스 호환성 수준 140 이상을 사용합니다. ALTER DATABASE를 통해 변경할 수 있습니다.

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

이 기능의 지속성 부분이 사용되는 모든 데이터베이스에 대해 쿼리 저장소를 사용하도록 설정해야 합니다.

백분위수 사용 안 함

데이터베이스에서 발생하는 모든 쿼리 실행에 대해 메모리 부여 피드백 백분위수를 사용하지 않도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT의 기본 설정은 ON입니다.

지속성 사용 안 함

데이터베이스에서 시작된 모든 쿼리 실행에 대한 메모리 부여 피드백 지속성을 사용하지 않도록 설정하려면

해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

메모리 부여 피드백 지속성을 사용하지 않도록 설정하면 수집된 기존 피드백도 제거됩니다.

MEMORY_GRANT_FEEDBACK_PERSISTENCE의 기본 설정은 ON입니다.

메모리 부여 피드백에 대한 고려 사항

sys.database_scoped_configurations를 쿼리하여 현재 설정을 볼 수 있습니다.

참고 항목

BATCH_MODE_MEMORY_GRANT_FEEDBACK 및 ROW_MODE_MEMORY_GRANT_FEEDBACK이 모두 OFF로 설정되어 있으면 이 기능이 작동하지 않습니다.

피드백 데이터가 이제 쿼리 저장소에 유지되면 쿼리 저장소 사용 요구 사항이 몇 가지 증가합니다.

백분위수 기반 메모리는 스필을 줄이는 측면에서 오류 권한을 부여합니다. 더 이상 마지막 실행만 기반으로 하지 않고 여러 과거 실행에 대한 관찰을 기반으로 하기 때문에 실행 간에 메모리 부여 요구 사항이 크게 분산되는 진동하는 워크로드에 대한 메모리 사용량이 증가할 수 있습니다.

SQL Server 2022(16.x)부터 보조 복제본에 대한 쿼리 저장소를 사용하도록 설정하면 가용성 그룹의 보조 복제본에 대해 메모리 부여 피드백이 복제본을 인식합니다. 메모리 부여 피드백은 주 복제본과 보조 복제본에서 다르게 피드백을 적용할 수 있습니다. 그러나 메모리 부여 피드백은 보조 복제본에는 유지되지 않으며, 장애 조치(failover) 시 이전 주 복제본의 메모리 부여 피드백이 새 기본 복제본에 적용됩니다. 보조 복제본이 주 복제본이 될 때 적용된 피드백은 손실됩니다. 자세한 내용은 보조 복제본을 위한 쿼리 저장소를 참조하세요.

관련 콘텐츠

• SQL 데이터베이스의 인텔리전트 쿼리 처리
• 지능형 쿼리 처리 기능 상세 설명
• RECONFIGURE(Transact-SQL)
• 성능 모니터링 및 튜닝
• ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)