최적화된 중첩 루프 또는 일괄 처리 정렬을 사용하는 쿼리에서 높은 CPU 또는 메모리 부여가 발생할 수 있습니다.

이 문서는 SQL Server 쿼리를 실행할 때 높은 CPU 사용량이 발생하는 문제를 resolve 데 도움이 됩니다.

적용 대상: SQL Server
원래 KB 번호: 2009160

증상

동시 워크로드가 매우 많은 Microsoft SQL Server 운영하는 경우 쿼리에서 몇 가지 성능 문제가 발생할 수 있습니다. 이 동작은 중간에서 높은 CPU 사용량 또는 극단적인 메모리 부여 요청으로 나타날 수 있습니다.

OOM 조건, 계획 캐시 제거를 위한 메모리 압력 또는 예기치 않은 RESOURCE_SEMAPHORE 대기와 같은 다른 부작용이 발생할 수도 있습니다.

또한 CPU 또는 메모리를 많이 사용하는 쿼리에 대한 쿼리 계획에는 중첩 루프 조인 연산자에 대한 OPTIMIZED 특성이 True로 설정된 것을 알 수 있습니다.

원인

이 문제는 SQL Server 쿼리 프로세서에서 성능을 개선하기 위한 선택적 정렬 작업을 도입하는 경우에 발생할 수 있습니다. 이 작업을 "최적화된 중첩 루프" 또는 "일괄 처리 정렬"으로 알려져 있으며 쿼리 최적화 프로그램은 이러한 연산자를 가장 잘 도입할 시기를 결정합니다. 드문 경우지만 쿼리는 몇 개의 행에만 영향을 주지만 정렬 작업에 대한 설정 비용이 너무 커서 최적화된 중첩 루프의 비용이 그 이점보다 큽니다. 따라서 이러한 경우 예상과 비교하여 성능이 느려질 수 있습니다.

해결 방법

추적 플래그 2340

이 문제를 해결하려면 추적 플래그 2340을 사용하여 최적화를 사용하지 않도록 설정합니다. 추적 플래그 2340은 쿼리 계획을 생성할 때 최적화된 중첩 루프 조인에 정렬 작업(일괄 정렬)을 사용하지 않도록 쿼리 프로세서에 지시합니다. 이는 전체 instance 영향을 줍니다.

이 추적 플래그를 사용하도록 설정하기 전에 애플리케이션을 철저히 테스트하여 이 최적화를 사용하지 않도록 설정할 때 예상되는 성능 이점을 얻을 수 있는지 확인할 수 있습니다. 이는 계획에서 처리되는 행 수가 크게 증가할 때 정렬 최적화가 유용할 수 있기 때문입니다.

자세한 내용은 DBCC TRACEON - 추적 플래그(Transact-SQL)를 참조하세요.

DISABLE_OPTIMIZED_NESTED_LOOP 힌트를 사용하도록 코드 수정

또는 다음 DISABLE_OPTIMIZED_NESTED_LOOP 쿼리 힌트를 적용하여 쿼리 수준에서 최적화를 사용하지 않도록 설정합니다.

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

자세한 내용은 DISABLE_OPTIMIZED_NESTED_LOOP 참조하세요.

추가 정보

데이터베이스 엔진 서비스 시작 옵션

적용 대상

• SQL Server 2005~SQL Server 2019