SQL Server and in Guest Best Practices

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

SQL Server는 워크로드에 따라 메모리 사용량을 동적으로 조정할 수 있습니다. SQL Server 최대 서버 메모리 및 최소 서버 메모리 구성 설정을 사용하면 사용 중인 SQL Server 프로세스에 대한 메모리 범위를 정의할 수 있습니다. 최소 서버 메모리의 기본 설정은 0이고 최대 서버 메모리의 기본 설정은 2,147,483,647MB입니다. 최소 서버 메모리는 시작 시 즉시 할당되지 않습니다. 그러나 클라이언트 부하로 인해 메모리 사용량이 이 값에 도달한 후에는 최소 서버 메모리 값을 줄이지 않는 한 SQL Server에서 메모리를 해제하지 않습니다.

SQL Server는 VM의 모든 메모리를 사용할 수 있습니다. 최대 서버 메모리를 설정하면 VM에서 실행 중인 운영 체제 및 기타 애플리케이션을 위해 충분한 메모리를 예약할 수 있습니다. 동일한 VM에서 여러 SQL Server 인스턴스를 실행하는 기존 SQL Server 통합 시나리오에서 최대 서버 메모리를 설정하면 인스턴스 간에 메모리를 효과적으로 공유할 수 있습니다.

호스트 메모리가 부족할 경우 최소 서버 메모리를 설정하는 것이 SQL Server 성능을 유지하는 좋은 방법입니다. vSphere에서 SQL Server를 실행할 때 vSphere 호스트가 메모리 압박을 받는 경우 풍선 드라이버가 SQL Server VM에서 메모리를 부풀려 회수할 수 있습니다. 최소 서버 메모리를 설정하면 SQL Server에 최소한 합리적인 양의 메모리가 제공됩니다.

티어 1 미션 크리티컬 SQL Server 배포의 경우 최대 및 최소 서버 메모리를 모두 동일한 값으로 설정하여 SQL Server 메모리를 고정된 양으로 설정하는 것을 고려하십시오. 최대 및 최소 서버 메모리를 설정하기 전에 운영 체제 및 VM 오버헤드에 충분한 메모리가 남아 있는지 확인합니다. vSphere에 대해 SQL Server 최대 서버 메모리 크기 조정을 수행하려면 다음 공식을 기준으로 삼으십시오:

SQL Max Server Memory = VM Memory - ThreadStack - OS Mem - VM Overhead
ThreadStack = SQL Max Worker Threads * ThreadStackSize
ThreadStackSize = 1MB on x86
                = 2MB on x64   
OS Mem: 1GB for every 4 CPU Cores

메모리의 페이지 잠금

SQL Server 서비스 계정에 메모리 내 페이지 잠금 사용자 권한을 부여하면 Windows Server에서 SQL Server 버퍼 풀 페이지를 페이징하지 못하도록 방지할 수 있습니다. 이 설정은 Windows Server가 상당한 양의 버퍼 풀 메모리를 페이징하는 것을 방지하여 SQL Server가 자체 작업 세트의 감소를 관리할 수 있도록 하므로 유용하고 성능에 긍정적인 영향을 미칩니다.

메모리 내 페이지 잠금을 사용할 때마다 SQL Server 메모리가 잠겨 Windows Server에서 페이지 아웃할 수 없으므로 vSphere 풍선 드라이버가 VM에서 메모리를 회수하려고 할 때 부정적인 영향이 발생할 수 있습니다. SQL Server 메모리 내 페이지 잠금 사용자 권한을 설정한 경우 VM 구성에서 설정한 메모리 양과 일치하도록 VM의 예약도 설정하십시오.

계층 1 미션 크리티컬 SQL Server 설치를 배포하는 경우 메모리 내 페이지 잠금 사용자 권한[92]을 설정하고 VM 메모리 예약을 설정하여 vSphere를 실행하는 SQL Server의 성능과 안정성을 개선하는 것이 좋습니다.

또한 메모리 내 페이지 잠금을 최대 서버 메모리 설정과 함께 사용하여 SQL Server가 VM의 모든 메모리를 점유하지 않도록 해야 합니다.

성능이 덜 중요한 하위 계층의 SQL Server 워크로드의 경우 사용 가능한 호스트 메모리 사용량을 최대화하기 위해 오버 커밋하는 기능이 더 중요할 수 있습니다. 하위 계층의 SQL Server 워크로드를 배포할 때는 메모리 내 페이지 잠금 사용자 권한을 사용하지 않는 것이 좋습니다. 메모리 내 페이지 잠금은 vSphere 풍선 드라이버와 충돌을 일으킵니다. 낮은 계층의 SQL Server 워크로드의 경우 해당 인스턴스가 포함된 VM에 대해 풍선 드라이버가 메모리를 동적으로 관리하도록 하는 것이 좋습니다. 풍선 드라이버가 vSphere 메모리를 동적으로 관리하도록 하면 메모리 사용량을 최대화하고 통합 비율을 높이는 데 도움이 될 수 있습니다.

Large Pages[93]

MMU 가상화를 위한 하드웨어 지원은 일반적으로 많은 워크로드의 성능을 향상시킵니다. 그러나 TLB 미스 처리 시 지연 시간 증가로 인해 오버헤드가 발생할 수 있습니다. 이 비용은 Large Page를 사용하여 제거하거나 완화할 수 있습니다[94]…

SQL Server는 다음 조건이 충족되는 경우 일부 내부 구조 및 버퍼 풀에 메모리를 할당할 때 Large Page 개념을 지원합니다:

  • SQL Server 엔터프라이즈 에디션을 사용하고 있습니다.
  • 컴퓨터에 8GB 이상의 물리적 RAM이 있습니다.
  • 서비스 계정에 대해 메모리 페이지 잠금 권한이 설정되어 있습니다.

SQL Server 2008부터 잠금 관리 및 버퍼 풀과 같은 일부 내부 구조는 앞의 조건이 충족되는 경우 자동으로 대용량 페이지를 사용할 수 있습니다. 오류 로그에서 다음 메시지를 확인하면 이를 확인할 수 있습니다:

2009-06-04 12:21:08.16 Server Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server Large Page Allocated: 32MB

64비트 시스템에서는 추적 플래그 834로 SQL Server를 시작하여 모든 SQL Server 버퍼 풀 메모리가 Large Page를 사용하도록 추가로 설정할 수 있습니다. 추적 플래그 834를 사용하도록 설정하면 다음과 같은 동작이 변경됩니다:

  • SQL Server 2012 이상에서는 열 저장소 기능을 사용하는 경우 추적 플래그 834를 활성화하지 않는 것이 좋습니다. 참고: SQL Server 2019에서는 컬럼스토어 인덱싱이 사용되며 워크로드가 대용량 메모리 페이지의 이점을 누릴 수 있는 경우 추적 플래그 876을 도입했습니다.
  • 게스트 운영 체제에서 대용량 페이지를 사용하도록 설정하고 VM이 대용량 페이지를 지원하는 호스트에서 실행 중인 경우 vSphere는 VM의 메모리에서 투명 페이지 공유를 수행하지 않습니다.
  • 추적 플래그 834를 사용하도록 설정하면 SQL Server 시작 동작이 변경됩니다. 런타임에 메모리를 동적으로 할당하는 대신 SQL Server는 시작 중에 모든 버퍼 풀 메모리를 할당합니다. 따라서 SQL Server 시작 시간이 상당히 지연될 수 있습니다.
  • 추적 플래그 834를 활성화하면 SQL Server는 4KB 블록 대신 2MB 연속 블록으로 메모리를 할당합니다. 호스트가 오랫동안 실행된 후에는 조각화로 인해 연속적인 메모리를 확보하기 어려울 수 있습니다. SQL Server가 필요한 양의 연속 메모리를 할당할 수 없는 경우 더 적은 양을 할당하려고 시도할 수 있으며, 그러면 SQL Server가 의도한 것보다 적은 메모리로 실행될 수 있습니다.

추적 플래그 834는 SQL Server의 성능을 향상시키지만 모든 배포 시나리오에서 사용하기에 적합하지 않을 수 있습니다. 고도로 통합된 환경에서 SQL Server를 실행하는 경우 메모리 오버 커밋이 일반적이라면 이 설정을 사용하지 않는 것이 좋습니다. 이 설정은 호스트의 과잉 가입 및 메모리 과잉 커밋이 없는 고성능 티어 1 SQL Server 워크로드에 더 적합합니다. 항상 SQL Server 오류 로그에서 메시지를 확인하여 올바른 대용량 페이지 메모리가 부여되었는지 확인하세요. 다음 예제를 참조하십시오:

2009-06-04 14:20:40.03 Server Using large pages for buffer pool.
2009-06-04 14:27:56.98 Server 8192 MB of large page memory allocated.

CXPACKET, MAXDOP, CTFP

병렬 계획을 사용하여 SQL Server에서 쿼리가 실행되면 쿼리 작업이 여러 패킷으로 분할되어 여러 코어에서 처리됩니다. 시스템이 쿼리가 완료될 때까지 대기하는 시간은 CXPACKET으로 계산됩니다.

MAXDOP(maximum degree of parallelism)는 병렬 계획에서 쿼리를 실행하는 데 사용되는 프로세서 수를 제어하는 고급 구성 옵션입니다. 이 값을 1로 설정하면 병렬 계획을 모두 비활성화합니다. 기본값은 일반적으로 너무 낮은 것으로 간주되는 5입니다.

CTFP(cost threshold for parallelism)는 쿼리에 병렬 계획이 사용되는 임계값을 지정하는 옵션입니다. 값은 초 단위로 지정되며 기본값은 5로, SQL Server에서 쿼리를 직렬로 실행할 때 5초 이상 걸릴 것으로 판단되면 쿼리에 대한 병렬 계획이 사용된다는 의미입니다. 5는 일반적으로 오늘날의 CPU 속도에 비해 너무 낮은 것으로 간주됩니다.

가상 환경에서의 이러한 구성 값과 관련하여 인터넷에는 상당한 오해와 잘못된 조언이 있습니다. 데이터베이스에서 낮은 성능이 관찰되고 CXPACKET이 높은 경우, 많은 DBA가 MAXDOP 값을 1로 설정하여 병렬 처리를 완전히 비활성화하기로 결정합니다.

여러 CPU에서 처리하면 이점이 있는 대규모 작업이 있을 수 있으므로 이 방법은 권장되지 않습니다. 대신 CTFP 값을 5초에서 약 50초로 늘려서 큰 쿼리만 병렬로 실행되도록 하는 것이 좋습니다. VM의 NUMA 노드에 있는 코어 수에 대한 Microsoft의 권장 사항(8개 이하)에 따라 MAXDOP를 설정합니다.

MAXDOP를 1로 설정하고 쿼리 코드에서 병렬 처리를 설정하기 위해 MAXDOP = N 쿼리 힌트를 설정할 수도 있습니다. 어떤 경우든 이러한 고급 설정의 구성은 SQL Server를 사용하는 프런트엔드 애플리케이션 워크로드에 따라 달라집니다.

자세한 내용은 SQL Server의 ‘max degree of parallelism’ 구성 옵션에 대한 Microsoft 문서 권장 사항 및 지침(https://support.microsoft.com/en-us/kb/2806535)을 참조하세요.