SQL 서버 메모리 사용량 최적화

Microsoft SQL Server는 메모리를 사용하여 데이터를 캐시하고 서버의 성능을 높입니다. 기본적으로 SQL Server는 디스크에서 메모리로 데이터 페이지를 읽습니다. 저장하라고 지시한 만큼의 페이지를 저장하고 필요한 만큼만 제거합니다. 서버에서 이전에 사용한 페이지를 요구하는 쿼리를 받는 경우 처음부터 페이지를 만드는 대신 메모리에서 빠르게 해당 페이지를 가져올 수 있습니다.

SQL Server가 메모리를 전부 사용하거나 메모리 리소스를 지나치게 소진한다는 것은 널리 퍼진 오해입니다. SQL Server는 성능 최적화를 위해 작동하므로 서버에 할당된 메모리를 최대한 많이 사용합니다. 하지만 SQL Server에 얼마나 많은 양의 메모리를 할당하고 다른 애플리케이션 또는 OS를 위해 얼마나 많은 양의 메모리를 남겨둘지는 전적으로 사용자에게 달렸습니다.

시스템 성능을 최적의 상태로 유지하기 위해 함께 작동하는 세 가지 핵심 구성요소는 CPU, 스토리지, 메모리입니다. 성능 병목 현상을 방지하려면 메모리를 효과적으로 할당해야 합니다.

SQL Server에 할당할 메모리의 양을 결정하는 일은 상당히 까다롭습니다. SQL Server 메모리 사용량을 지속적으로 모니터링해야 하기 때문입니다. SQL Server 메모리를 모니터링 및 극대화하면 SQL Server 성능과 서버의 쿼리 응답 시간을 개선할 수 있습니다.

SQL Server 메모리 사용량 모니터링 작업은 데이터베이스 관리자(DBA)가 SQL Server용 컴퓨터 시스템뿐만이 아니라 전체 컴퓨터 시스템 내의 메모리 사용량 측정치를 관찰하고, 이 데이터를 사용하여 한정된 메모리 리소스를 적절히 할당하는 과정을 수반합니다.

SQL Server 메모리 사용량 모니터링의 주된 목표는 운영 체제 또는 기타 애플리케이션의 성능 저하를 유발하지 않고 SQL Server에 지정해야 하는 메모리의 양을 파악하는 것입니다. 이를 위해서는 시스템의 성능 메트릭을 모니터링하고 이 데이터를 사용하여 SQL Server 메모리 튜닝에 영향을 주어야 합니다.

여러 가지 요인이 컴퓨터 시스템 메모리 사용량 증가에 영향을 줍니다. SQL 메모리 사용량 모니터링 및 최적화에는 이러한 요인에 대한 데이터 위치를 파악하여 주목해야 하는 SQL 쿼리를 파악하는 과정이 수반됩니다. 메모리 사용량에 영향을 주는 요인 몇 가지는 다음과 같습니다.

• 페이지 예상 수명(PLE는 메모리 사용 모니터링에 중요한 메트릭입니다. PLE가 하락하면 페이지가 다른 용도로 메모리에 유지되지 않는다는 것을 나타냅니다. 이러한 현상은 SQL Server로 인해 버퍼 메모리가 고갈될 때 일반적으로 발생합니다.)

• 캐시 크기를 계획
• OS 메모리 사용량
• 버퍼 캐시 크기
• 버퍼 캐시 적중률
• 메모리 페이징 속도
• 초당 로그 바이트
• 초당 로그 플러시
• 프로시저 캐시 적중률
• SQL 컴파일

SQL Server 메모리 사용과 관련한 모니터링 작업에는 SQL Server가 있는 모든 시스템에서 존재하는 SQL Server 메모리 소비 보고서를 살펴보는 작업이 수반됩니다. 이 보고서는 섹션별로 정보를 제공하며 다음과 같은 내용을 포함하고 있습니다.

• 가장 많은 메모리를 소모하는 구성 요소들
• 버퍼 페이지 분산
• 최근 7일간 메모리 변화
• 구성요소별 메모리 사용량

이 데이터를 모니터링하면 SQL Server의 최대 메모리로 설정해야 할 메모리 양을 결정하고 요구사항과 사용 방식의 변화에 따라 이 최대 값을 조정할 방법을 확인할 수 있습니다.

SQL Server 메모리 사용량을 수동으로 모니터링할 수 있기는 하지만, 메트릭 위치를 지속적으로 파악하고 비교하는 일은 다양한 화면과 도구에서 진행해야 하므로 쉽지 않습니다. 대신 단일 도구를 사용하여 SQL Server 메모리 모니터링 요구사항을 충족하는 것이 최선입니다. 관련 데이터를 수집하고, 상관 관계를 분석하고, 단일 통합 콘솔에서 정보를 제공하는 SolarWinds^® Database Performance Analyzer는 이러한 도구 중 하나입니다.

SQL Server 사용량의 모니터링은 SQL Server 성능과 직접적인 상관 관계에 있기에 중요합니다. 또한 쿼리 실행 속도에 막대한 영향을 미칩니다.

PLE를 일반 SQL Server 메모리 사용량 모니터링 기능의 일부로 모니터링하면 SQL Server의 버퍼 메모리가 부족한지 여부를 파악할 수 있습니다. 이는 인덱스가 누락되었거나 SQL 문이 제대로 작성되지 않음을 나타낼 수 있습니다.

아쉽게도 SQL Server의 기본 메모리 사용량 설정은 전혀 이상적이지 않습니다. 이러한 설정을 최적화하지 않으면 메모리에 대한 투자 증대만이 성능 향상을 위한 유일한 방법이라고 생각할 수 있습니다. 하지만 이는 많은 비용만 소모할 뿐이며 문제는 해결하지 않습니다. 이는 메모리가 다시 부족해지기 전까지 버티기 위한 임시방편일 뿐이며 계속 메모리를 추가 구매하게 됩니다.

SQL Server 메모리 사용량을 모니터링하고 여기에서 도출하는 인사이트를 적용하면 성능 문제를 유발하는 문제에 정면으로 대응할 수 있습니다. 메모리 사용량을 최적화하면 메모리 추가에 비용을 낭비할 필요 없이 SQL Server 성능을 더욱 개선할 수 있습니다.

SQL Server 메모리 사용량 모니터링 도구를 사용하면 SQL Server 성능을 최적화하고 서버의 메모리 사용량을 개선하는 데 필수적인 인사이트를 확보할 수 있습니다. 이 도구를 사용하면 메모리 사용량에 영향을 주는 다양한 요인에 관한 정보를 확인할 수 있을 뿐만 아니라 열악하거나 많은 비용이 드는 쿼리에 필요한 성능 메트릭을 확인할 수 있는 가시성도 확보할 수 있습니다.

DBA는 SQL Server 메모리 사용량 모니터링 도구를 통해 활성 세션으로부터 매 초마다 데이터를 가져와서 집계함으로써 주목해야 하는 SQL 쿼리를 파악할 수 있습니다. 또한 SQL 문의 순위를 응답 시간 순으로 매길 수 있어 문제가 있는 부분을 더욱 쉽게 식별할 수 있습니다.

모니터링 도구를 활용하면 응답 시간과 CPU, 스토리지, 메모리 등 시스템 리소스 간의 상관 관계를 단일 통합 콘솔에서 직접 확인할 수 있습니다. 평균 실행, 쿼리 기간, CPU 시간, 논리/물리적 읽기 등 다양한 성능 메트릭에 가시성을 제공하면, 모니터링 도구는 효과적인 SQL 쿼리 최적화를 위한 최선의 실행 방식에 대한 정보를 수집하고, 기준선을 확립하고, 내역을 유지할 수 있습니다.

메모리와 관련해서는, SQL Server 모니터링 도구가 페이지 예상 수명에 관한 정보를 지속적으로 수집하고 캐시 크기 계획 등을 수립하도록 설계되었다는 점이 가장 큰 장점 중 하나입니다.

PLE, 즉 페이지 예상 수명(Page life expectancy)은 레퍼런스 없이 버퍼 풀에서 페이지가 머무는 시간을 초 단위로 측정한 수치입니다. 페이지가 메모리에 오래 머물수록 페이지가 재사용될 가능성이 높아집니다. PLE가 하락하면 페이지가 다른 용도로 메모리에 유지되지 않는다는 것을 나타냅니다. 이러한 현상은 SQL Server의 버퍼 메모리가 고갈될 때 일반적으로 발생합니다. 일반적으로 제대로 작성되지 않은 SQL 문 또는 인덱스 누락으로 인해 쿼리가 너무 많은 데이터를 읽고 소량의 결과 세트를 반환하기 때문에 SQL Server가 너무 많은 메모리를 사용할 수 있습니다. 적절한 인덱싱과 제대로 작성된 쿼리로 메모리 사용량을 최적화하며 메모리의 부담을 줄일 수 있습니다.

계획 캐시가 버퍼 풀 크기를 보고 구성된 메모리 크기를 표시합니다. SQL 문이 실행될 때마다 SQL 문에 대한 계획이 이미 존재하는지 엔진에서 검색합니다. 존재하는 경우에는 새로운 계획을 컴파일하는 데 시간을 낭비하지 않고 엔진에서 해당 계획을 사용합니다. 이러한 방식으로 캐시가 SQL Server 엔진의 효율성을 높입니다. 한 번만 실행되는 문에 대한 쿼리 계획의 저장을 일회용 계획이라고 합니다. 일회용 계획이 너무 많으면 SQL Server 메모리가 소진되고 결과적으로 SQL Server 성능이 열악해질 수 있습니다.

SQL Server 메모리 사용량 모니터링 도구는 이 두 메트릭을 꾸준히 모니터링하고 상세한 데이터를 제공함으로써 DBA에 필요한 정보를 제공하여 SQL Server 메모리 할당과 전반적인 SQL 쿼리를 효과적으로 조정 및 최적화하는 데 도움이 됩니다.

SolarWinds SQL Sentry는 데이터베이스 관리자가 SQL Server 메모리 사용량을 최적화할 수 있도록 포괄적인 도구와 실시간 모니터링을 제공합니다. 다양한 메모리 관련 요인에 대한 데이터를 수집하고, 문제가 있는 쿼리를 식별하며 사용자가 성능 기준선을 설정하고, 성능 분석을 간소화하도록 해줍니다. 또한 모니터링 기능을 VMware 환경으로 확장하여 사전 예방적 관리가 가능하고 메모리 메트릭을 넘어 데이터베이스 상태에 대한 전체적인 관점을 제공합니다. SQL Sentry의 기능은 SQL Server 환경에서 최고의 성능과 안정성을 유지하는 데 도움이 됩니다.

• SQL Server 메모리 : 버퍼 캐시 크기
• SQL Server 메모리 : 캐시 크기 계획 수립(SQL)
• SQL Server 메모리 : 캐시 크기 계획(Objects)
• SQL Server 메모리 : 기타 캐시 크기
• SQL Server 메모리 : In-Mem OLTP
• SQL Server 메모리: 쿼리 부여
• SQL Server 메모리: 컬럼스토어
• SQL Server 메모리 : 캐시 적중률 계획(SQL)
• SQL Server 메모리 : 캐시 적중률 계획(Objects)
• SQL Server 메모리 : 도난된 버퍼
• SQL Server 메모리 : 페이지 읽기
• SQL Server 메모리 : 페이지 쓰기
• SQL Server 메모리 : 페이지 예상 수명
• SQL Server 메모리 : 메모리 부여 보류 중
• 시스템 메모리 : SQL Server별 메모리 / SSAS 인스턴스
• 시스템 메모리 : 기타 프로세스 메모리
• 시스템 메모리 : 파일 캐시 메모리
• 시스템 메모리 : 증가된 메모리**
• 시스템 메모리 : 사용 가능한 메모리
• 시스템 메모리 : 오류 읽기
• 시스템 메모리 : 오류 쓰기
• 시스템 메모리 : 페이지 파일 사용량

전체 목록은 SolarWinds 문서에서 확인할 수 있습니다.