이는 최대 RAM 사용량으로 15GB가 구성된 SQL Server 2016 CU2 인스턴스입니다. MDOP는 4입니다. 이 인스턴스의 데이터베이스는 사용자가 매우 산발적으로 쿼리하며 이는 읽기 전용 보조 복제본입니다.
문제: 이 서버에서 동시에 실행되는 다른 쿼리가 없는 경우에도 메모리 고갈로 인해 매주 모든 쿼리가 RESOURCE_SEMAPHORE 상태가 됩니다. 인스턴스를 다시 시작하면 쿼리가 다시 작동하기 시작합니다. 아래 이미지를 보면 최대 작업 공간 메모리가 부족하다는 것을 알 수 있으며 이는 정말 잘못된 쿼리입니다(천문학적 쿼리 비용).
다시 시작 전: 사용 가능한 최대 작업 공간 메모리가 1,8GB이고 모든 잘못된 쿼리가 RESOURCE_SEMAPHORE 상태로 전환됩니다.
인스턴스를 다시 시작한 후: 충분한 작업공간 메모리(~11GB)가 있고 끔찍한 쿼리의 경우에도 더 이상 RESOURCE_SEMAPHORE가 없습니다.
보시다시피 ~2GB의 RAM을 요구하는 혐오스러운 쿼리입니다. 이 이미지에서 SSMS는 인스턴스가 다시 시작되었기 때문에 필요한 메모리가 실제로 부여되었음을 보여줍니다. 다시 시작하기 전에 메모리 부여는 NULL로 유지되고 쿼리는 RESOURCE_SEMAPHORE 대기 상태로 유지됩니다.
이제 우리가 알고 싶은 것은 시간이 지남에 따라 최대 작업 공간 메모리가 감소하고 실행 중인 쿼리가 없을 때 해제되지 않는 이유는 무엇입니까?
답변1
15,728,640,000바이트의 총 할당 메모리 중 Stolen Server Memory는 12,828,864,000바이트를 차지하며 정상적인 작업에 충분하지 않은 것으로 보입니다.
문서에 따르면 SQL Server, 메모리 관리자 개체:
도난당한 서버 메모리(KB)
서버가 데이터베이스 페이지 이외의 목적으로 사용하는 메모리 양을 지정합니다.
안답변더 말한다:
도난당한 메모리는 정렬이나 해싱 작업(쿼리 작업 공간 메모리)에 사용되는 버퍼 또는 잠금, 트랜잭션 컨텍스트 및 연결 정보와 같은 내부 데이터 구조를 저장하기 위해 할당을 위한 일반 메모리 저장소로 사용되는 버퍼를 설명합니다. . 지연 기록기 프로세스는 도난당한 버퍼를 버퍼 풀에서 플러시하는 것이 허용되지 않습니다.
낭비적인 쿼리는 엄청난 정렬 작업을 수행한 것으로 보이며 SQL Server는 할당된 메모리를 해제하지 않습니다. 메모리는 버퍼 풀에서 가져오며 다른 용도로 사용할 수 없습니다.
옵션이 너무 많지 않습니다.
- 엄청난 양의 데이터를 정렬하지 않도록 해당 쿼리를 개선하세요.
- SQL Server에 메모리 추가
- 정기적으로 또는 특히 이 쿼리를 실행한 후에 SQL Server를 다시 시작하십시오.