메모리 부여 피드백
적용 대상: 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 Database
쿼리 실행 후 계획에는 실행에 필요한 최소 필수 메모리 및 모든 행을 메모리에 포함하기 위한 이상적인 메모리 부여 크기가 포함됩니다. 메모리 부여 크기가 잘못 지정된 경우 성능이 저하됩니다. 과도하게 부여하면 메모리가 낭비되고 동시성이 줄어듭니다. 메모리 부여가 부족하면 디스크로 분산되어 비용이 증가합니다. 반복 워크로드를 처리함으로써 일괄 처리 모드 메모리 부여 피드백은 쿼리에 필요한 실제 메모리를 다시 계산한 후 캐시된 계획에 대한 부여 값을 업데이트합니다. 동일한 쿼리 문이 실행되면 쿼리는 수정된 메모리 부여 크기를 사용하여 동시성에 영향을 주는 과도한 메모리 부여를 줄이고 디스크에 비용이 많이 드는 메모리 부여를 수정합니다.
다음 그래프에서는 일괄 처리 모드 적응 메모리 부여 피드백을 사용하는 한 가지 예를 보여 줍니다. 쿼리의 첫 번째 실행의 경우 높은 분산으로 인해 기간이 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초에서 감소)이고, 유출이 완전히 제거되고, 권한 부여가 더 높습니다.
메모리 부여 피드백 크기 조정
과도한 메모리 부여 조건의 경우 부여된 메모리가 실제 사용된 메모리 크기의 2배보다 큰 경우 메모리 부여 피드백은 메모리 부여를 다시 계산하고 캐시된 계획을 업데이트합니다. 메모리 부여가 1MB 미만인 계획은 초과분에 대해 다시 계산되지 않습니다.
일괄 처리 모드 연산자를 위해 디스크에 분산되는 크기가 부족한 메모리 부여 조건의 경우 메모리 부여 피드백은 메모리 부여의 다시 계산을 트리거합니다. 분산 이벤트는 메모리 부여 피드백에 보고되며 확장 이벤트를 통해 spilling_report_to_memory_grant_feedback 표시될 수 있습니다. 이 이벤트는 계획의 노드 ID와 해당 노드의 분산 데이터 크기를 반환합니다.
조정된 메모리 부여는 속성을 통해 GrantedMemory 실제(실행 후) 계획에 표시됩니다.
그래픽 실행 계획의 루트 연산자 또는 실행 계획 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 수 있습니다. 이 이벤트는 현재 실행 횟수 기록, 메모리 부여 피드백에 의해 계획이 업데이트된 횟수, 수정 전에 이상적인 추가 메모리 부여 및 메모리 부여 피드백이 캐시된 계획을 수정한 후 이상적인 추가 메모리 부여를 추적합니다.
메모리 부여 피드백, 리소스 관리자 및 쿼리 힌트
부여되는 실제 메모리는 리소스 관리자 또는 쿼리 힌트에 따른 쿼리 메모리 제한을 준수합니다.
호환성 수준을 변경하지 않고 일괄 처리 모드 메모리 부여 피드백 사용 안 함
데이터베이스 호환성 수준 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 사용하도록 설정된 것으로 표시됩니다.
데이터베이스에서 시작된 모든 쿼리 실행에 대한 일괄 처리 모드 메모리 부여 피드백을 다시 사용하도록 설정하려면 해당 데이터베이스의 컨텍스트 내에서 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;
USE HINT 쿼리 힌트로 지정하여 DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK 특정 쿼리에 대한 일괄 처리 모드 메모리 부여 피드백을 사용하지 않도록 설정할 수도 있습니다. 예시:
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 Database
행 모드 메모리 부여 피드백은 일괄 처리 및 행 모드 연산자의 메모리 부여 크기를 둘 다 조정하여 일괄 처리 모드 메모리 부여 피드백 기능을 확장합니다.
Azure SQL Database에서 행 모드 메모리 부여 피드백을 사용하도록 설정하려면 쿼리를 실행할 때 연결된 데이터베이스에 대해 데이터베이스 호환성 수준 150 이상을 사용하도록 설정합니다.
예시:
ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;
일괄 처리 모드 메모리 부여 피드백과 마찬가지로 행 모드 메모리 부여 피드백 작업은 XEvent를 memory_grant_updated_by_feedback 통해 표시됩니다. 또한 행 및 일괄 처리 모드 모두에 대한 메모리 부여 피드백 작업의 현재 상태를 더 잘 파악할 수 있도록 두 가지 새로운 쿼리 실행 계획 특성을 도입했습니다.
메모리 부여 피드백에는 쿼리 저장소 필요하지 않지만 SQL Server 2022(16.x)에서 도입된 지속성 향상을 위해서는 데이터베이스 및 "읽기 쓰기" 상태에서 쿼리 저장소 사용하도록 설정해야 합니다. 지속성에 대한 자세한 내용은 이 문서의 뒷부분에 있는 백분위수 및 지속성 모드 메모리 부여 피드백을 참조하세요.
행 모드 메모리 부여 피드백 작업은 확장 이벤트를 통해 memory_grant_updated_by_feedback 표시됩니다.
행 모드 메모리 부여 피드백부터 실제 실행 후 계획의 경우 MemoryGrantInfo 쿼리 계획 XML 요소에 추가되는 두 개의 새 쿼리 계획 특성인 IsMemoryGrantFeedbackAdjusted 및 LastRequestedMemory가 표시됩니다.
- 이 특성은
LastRequestedMemory이전 쿼리 실행에서 부여된 메모리를 KB(킬로바이트)로 표시합니다. IsMemoryGrantFeedbackAdjusted특성을 사용하면 실제 쿼리 실행 계획 내의 명령문에 대한 메모리 부여 피드백의 상태를 확인할 수 있습니다.
이 특성에 표시되는 값은 다음과 같습니다.
IsMemoryGrantFeedbackAdjusted 값 |
설명 |
|---|---|
| 아니요: 첫 번째 실행 | 메모리 부여 피드백은 첫 번째 컴파일 및 관련 실행에 대한 메모리를 조정하지 않습니다. |
| 아니요: 정확한 부여 | 디스크에 분산되지 않고 문이 부여된 메모리의 50% 이상을 사용하는 경우 메모리 부여 피드백이 트리거되지 않습니다. |
| No: Feedback disabled | 메모리 부여 피드백이 지속적으로 트리거되고 메모리 증가 작업과 메모리 감소 작업 간에 변동하는 경우 데이터베이스 엔진은 문에 대한 메모리 부여 피드백을 사용하지 않도록 설정합니다. |
| 예: 조정 | 메모리 부여 피드백이 적용되었으며 다음 실행을 위해 추가로 조정될 수 있습니다. |
| 예: 백분위수 조정 | 메모리 부여 피드백은 가장 최근의 실행보다 더 많은 기록을 살펴보는 백분위수 부여 알고리즘을 사용하여 적용되고 있습니다. |
| 예: 안정적 | 메모리 부여 피드백이 적용되고 부여된 메모리는 이제 안정적입니다. 즉, 이전 실행에 마지막으로 부여된 것은 현재 실행에 대해 부여된 것입니다. |
백분위수 및 지속성 모드 메모리 부여 피드백
적용 대상: SQL Server(SQL Server 2022(16.x)부터), Azure SQL Database
이 기능은 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;
USE HINT 쿼리 힌트로 지정하여 DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK 특정 쿼리에 대한 행 모드 메모리 부여 피드백을 사용하지 않도록 설정할 수도 있습니다. 예시:
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)부터 보조 복제본(replica) 대한 쿼리 저장소 사용하도록 설정되면 가용성 그룹의 보조 복제본(replica) 대한 메모리 부여 피드백이 복제본(replica) 인식됩니다. 메모리 부여 피드백은 기본 복제본(replica) 보조 복제본(replica) 피드백을 다르게 적용할 수 있습니다. 그러나 메모리 부여 피드백은 보조 복제본(replica) 유지되지 않으며 장애 조치 시 이전 주 복제본(replica) 메모리 부여 피드백이 새 주 복제본(replica) 적용됩니다. 기본 복제본(replica) 될 때 보조 복제본(replica) 적용된 피드백은 손실됩니다. 자세한 내용은 보조 복제본(replica) 대한 쿼리 저장소 참조하세요.
관련 콘텐츠
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기