Microsoft® SQL Server® 쿼리 스토어(Query Store)는 이름 그대로 데이터베이스에서 실행된 쿼리 이력, 쿼리 런타임 실행 통계, 실행 계획을 저장하는 '창고'와 같은 기능입니다. 데이터가 디스크에 저장되기 때문에 언제든지 쿼리 스토어 데이터를 조회하여 문제를 진단할 수 있으며, SQL Server가 재시작되어도 데이터는 영향을 받지 않습니다. SQL Server 2016에서 처음 도입되어 이후 모든 버전에서 사용할 수 있는 쿼리 스토어를 활용하면, 쿼리 계획 변경으로 인한 성능 문제를 효과적으로 해결할 수 있습니다.
쿼리 스토어 소개
성능 문제를 진단할 때 기준선(baseline) 데이터 분석은 매우 유용하지만, 쿼리 스토어가 도입되기 전까지는 SQL Server에서 이러한 정보를 기본적으로 제공하지 않았습니다. 특정 데이터베이스에 대해 쿼리 스토어를 활성화하면, 실행된 쿼리 정보와 함께 실행 계획 및 런타임 통계가 지속적으로 수집됩니다.
쿼리 스토어는 데이터베이스 수준에서 활성화되며, 모든 사용자 데이터베이스와 MSDB 시스템 데이터베이스에 대해 설정할 수 있습니다. 쿼리 스토어 관련 정보와 메타데이터는 별도의 외부 저장소가 아닌 해당 데이터베이스 내부의 내부 테이블에 저장됩니다. 따라서 일반적인 데이터베이스 백업에 필요한 모든 정보가 포함되므로, 쿼리 스토어를 위한 별도의 백업 관리가 필요 없습니다.
쿼리 스토어 데이터를 조회하려면 View Database State 권한이 필요하고, 실행 계획을 강제 적용하거나 강제 적용을 해제하려면 DB_Owner 권한이 있어야 합니다. 이 데이터는 SSMS(SQL Server Management Studio) 또는 T-SQL을 통해 확인할 수 있습니다.
쿼리 스토어 설정 방법
데이터베이스에 대해 쿼리 스토어를 활성화하려면 다음 단계를 따르세요:
- 데이터베이스를 마우스 오른쪽 버튼으로 클릭하고 속성(Properties)으로 이동합니다.
- 페이지 선택(Select a Page)에서 쿼리 스토어(Query Store)를 선택합니다.
- 일반(General) 섹션에서 작업 모드(요청됨) / Operation Mode (Requested)를
Off에서ReadWrite로 변경합니다. - 나머지 필드는 미리 채워진 기본값 그대로 두어도 됩니다.
- 데이터베이스 속성 창에서 확인(OK)을 클릭하면 선택한 데이터베이스에 쿼리 스토어가 활성화됩니다.
T-SQL을 사용해서도 다음 코드로 쿼리 스토어를 활성화할 수 있습니다:
ALTER DATABASE [DB_Name] SET QUERY_STORE = ON;
쿼리 스토어의 주요 구성 옵션
쿼리 스토어의 주요 옵션은 다음과 같습니다:
작업 모드(Operation Mode):
Off,ReadOnly,ReadWrite세 가지 값을 가집니다.ReadOnly모드에서는 새로운 실행 계획이나 쿼리 런타임 통계가 수집되지 않으며, 쿼리 스토어 관련 읽기 전용 작업만 수행됩니다.ReadWrite로 변경하면 선택된 데이터베이스에서 실행되는 쿼리, 해당 쿼리에 사용된 실행 계획, 런타임 통계를 수집하기 시작합니다.데이터 플러시 간격(Data Flush Interval, 분): 수집된 실행 계획과 쿼리 런타임 통계를 메모리에서 디스크로 플러시하는 주기를 설정합니다. 기본값은 15분입니다.
통계 수집 간격(Statistics Collection Interval): 쿼리 스토어 내부에서 쿼리 런타임 통계를 집계하는 간격을 정의합니다. 기본값은 60분입니다.
최대 크기(Max Size, MB): 쿼리 스토어의 최대 크기를 구성합니다. 기본값은 100MB입니다. 쿼리 스토어 데이터는 쿼리 스토어가 활성화된 데이터베이스 자체에 저장되며, 여기서 설정한 크기에 도달하면 작업 모드가 자동으로
ReadOnly로 전환됩니다.캡처 모드(Capture Mode): 어떤 유형의 쿼리를 캡처할지 선택합니다. 기본 옵션인
All은 실행되는 모든 쿼리를 저장합니다.Auto로 설정하면 쿼리 스토어가 우선순위에 따라 캡처 대상을 선별하고, 드물게 실행되는 쿼리나 임시(ad hoc) 쿼리는 무시하려고 시도합니다.오래된 쿼리 임계값(Stale Query Threshold, 일): 데이터가 쿼리 스토어에 얼마나 오래 유지될지 정의합니다. 기본값은 30일입니다.
쿼리 스토어 보고서
쿼리 스토어에는 다음과 같은 기본 제공 보고서가 포함되어 있습니다:
- 성능 저하 쿼리(Regressed Queries): 최근 실행 지표가 악화되었거나 나빠진 쿼리를 신속하게 파악합니다.
- 전체 리소스 소비량(Overall Resource Consumptions): 데이터베이스의 전체 리소스 소비량을 다양한 실행 지표 기준으로 분석합니다.
- 리소스 소비 상위 쿼리(Top Resource Consuming Queries): 데이터베이스 리소스 소비에 가장 큰 영향을 미치는 쿼리를 보여줍니다.
- 강제 계획이 적용된 쿼리(Queries with Forced Plans): 강제 실행 계획이 적용된 모든 쿼리를 표시하는 기본 제공 보고서입니다.
- 변동성이 높은 쿼리(Queries with High Variation): 매개변수화(parameterization) 문제가 가장 빈번하게 발생하는 쿼리를 보여줍니다.
- 추적 쿼리(Tracked Queries): 가장 중요한 쿼리들의 실행을 실시간으로 추적합니다.
쿼리 스토어로 실행 계획 강제하기
실행 계획 회귀(plan regression) 때문에 어제까지 잘 작동하던 쿼리가 오늘 갑자기 너무 느리게 실행되거나, 과도한 리소스를 소모하거나, 심지어 시간 초과(timeout)가 발생할 수 있습니다. 기본적으로 SQL Server는 각 쿼리에 대해 최신 실행 계획만 유지합니다. 스키마, 통계, 인덱스의 변경 사항은 쿼리 옵티마이저가 사용하는 실행 계획을 바꿀 수 있으며, 계획 캐시 메모리 압박으로 인해 계획이 삭제될 수도 있습니다.
반면 쿼리 스토어는 모니터링 중인 각 데이터베이스 내에 쿼리 계획과 시간에 걸쳐 집계된 통계 정보를 저장합니다. 계획 캐시와 달리 쿼리 스토어는 하나의 쿼리에 대해 여러 개의 계획을 유지할 수 있으며, 계획별 통계와 함께 쿼리 계획 변경 이력을 보관합니다. 여러 실행 계획 중에서 선택하여 원하는 계획을 강제 적용(force)할 수 있으며, 이후 쿼리 옵티마이저는 해당 강제 실행 계획만 사용하여 쿼리를 실행합니다.
쿼리 스토어 → 리소스 소비 상위 쿼리 열기(Top Resource Consuming Queries) 보고서는 리소스를 많이 사용하는 쿼리 목록을 보여줍니다. 검토할 쿼리를 하나 선택해 보겠습니다:
마우스를 계획(Plan) 위에 올리면 관련 통계를 확인할 수 있습니다.
이제 서로 다른 계획들을 비교해 보겠습니다.
계획 216 상세 정보:
계획 195 상세 정보:
계획 216의 평균 실행 시간이 더 짧으므로, 이후 실행에서 이 계획을 강제 적용하는 것이 좋습니다. 계획 강제(Force Plan)를 클릭하면 "쿼리 42에 대해 계획 216을 강제 적용하시겠습니까?"라는 확인 메시지가 표시됩니다.
예(Yes)를 클릭합니다. 계획이 강제 적용되면 아래 스크린샷과 같이 체크 표시로 강조됩니다. 이후부터 쿼리 옵티마이저는 이 계획을 사용하여 쿼리를 실행합니다.
쿼리 스토어 활용 모범 사례
- 최신 기능과 향상된 기능을 활용하려면 최신 버전의 SQL Server Management Studio를 사용하세요.
- 쿼리 스토어의 데이터 수집 상태를 정기적으로 확인하고 모니터링하세요.
- 최적 쿼리(Optimal Query) 캡처 모드를 설정하고, 필요에 따라 쿼리 스토어 구성 옵션을 재검토하여 조정하세요.
- 매개변수화되지 않은(non-parameterized) 쿼리 사용을 피하세요.
- 강제 적용된 계획의 상태를 정기적으로 점검하세요.
결론
쿼리 스토어는 SQL Server 2016에서 도입된 매우 유용한 기능입니다. 성능 튜닝은 모든 데이터베이스 관리자(DBA)에게 필수적인 핵심 역량이므로, 쿼리 스토어를 구성하고 활용하는 방법을 반드시 익혀두어야 합니다. 쿼리 스토어를 사용하면 성능 변화를 추적하고, 실행 계획을 비교하여 쿼리 성능 저하 원인을 진단할 수 있습니다. 또한 특정 쿼리에 대해 실행 계획을 강제 적용하면 계획 캐시에 저장된 계획을 재정의하여 성능상 이점을 얻을 수 있습니다. 쿼리 스토어는 쿼리 실행 통계와 계획을 캡처하여 나중에 조회할 수 있도록 저장하는 용도이므로, SQL Server 성능에 큰 부담을 주지 않습니다.
궁금한 점이나 의견이 있다면 피드백 탭을 이용해 남겨주세요.
전문가의 관리·구성 서비스로 환경 최적화하기
Rackspace의 애플리케이션 서비스 (RAS) 전문가들은 폭넓은 애플리케이션 포트폴리오 전반에서 다음과 같은 전문 및 관리형 서비스를 제공합니다:
- e커머스 및 디지털 경험(DX) 플랫폼
- 엔터프라이즈 리소스 플래닝(ERP)
- 비즈니스 인텔리전스(BI)
- Salesforce 고객 관계 관리(CRM)
- 데이터베이스
- 이메일 호스팅 및 생산성 솔루션
Rackspace가 제공하는 것:
- 편향 없는 전문성: 즉각적인 가치를 창출하는 역량에 집중하여 현대화 여정을 단순화하고 안내합니다.
- Fanatical Experience™: '프로세스 우선, 기술 차후(Process first. Technology second.®)' 접근 방식과 전담 기술 지원을 결합하여 종합적인 솔루션을 제공합니다.
- 독보적인 포트폴리오: 풍부한 클라우드 경험을 바탕으로 적합한 기술을 적합한 클라우드에 선택하고 배포할 수 있도록 돕습니다.
- 애자일한 제공: 고객의 여정 단계에 맞춰 함께하며, 우리의 성공을 고객의 성공과 연결합니다.
지금 바로 채팅으로 시작해 보세요.