읽을 수 있는 보조 데이터베이스에 대한 쿼리 저장소

적용 대상: SQL Server 2025(17.x) Azure SQL Database 미리 보기

읽기 가능한 보조 복제본에 대한 쿼리 저장소를 사용하면 보조 복제본에서 실행되는 워크로드에 대한 쿼리 저장소 인사이트를 사용할 수 있습니다. 사용하도록 설정하면 보조 복제본은 쿼리 실행 정보(예: 런타임 및 대기 통계)를 주 복제본으로 스트리밍합니다. 여기서 데이터는 쿼리 저장소에 유지되고 모든 복제본에 표시됩니다.

이 기능은 원래 SQL Server 2022(16.x)에서 도입되었지만 기본적으로 꺼져 있으며 사용하도록 설정하려면 추적 플래그가 필요했습니다. 이 기능은 SQL Server 2022(16.x)의 미리 보기 상태로 유지되었기 때문에 부분적으로 발생했습니다.

SQL Server 2025(17.x) 미리 보기 및 Azure SQL Database부터 읽기 가능한 보조 데이터베이스에 대한 쿼리 저장소는 기본적으로 사용하도록 설정됩니다.

읽을 수 있는 보조 복제본에 대해 쿼리 저장소 사용

SQL Server 2025(17.x) 미리 보기 인스턴스에서 읽을 수 있는 보조 데이터베이스에 대해 쿼리 저장소를 사용하려면 먼저 Always On 가용성 그룹을 구성해야 합니다.

Azure SQL Database의 경우 읽을 수 있는 보조 데이터베이스에 대한 쿼리 저장소는 다음 서비스 계층을 지원합니다.

• 활성 지역 복제를 사용하는 범용(기본 제공 고가용성 복제본 없음, 보조 지원을 위한 지역 복제 구성 필요)
• 프리미엄(기본 제공 고가용성 복제본 포함, 활성 지리적 복제도 지원됨)
• 비즈니스에 중요한 기능(내장된 고가용성 복제본 포함; 활성 지역 복제도 지원)

적용 대상: SQL Server 2022(16.x) 이상 버전.

주 복제본에서 쿼리 저장소가 아직 활성화되지 않았거나 READ_WRITE 모드에 있지 않은 경우, 계속하기 전에 활성화해야 합니다. 주 복제본에서 원하는 각 데이터베이스에 대해 다음 스크립트를 실행합니다.

ALTER DATABASE [Database_Name]
    SET QUERY_STORE = ON(OPERATION_MODE = READ_WRITE);

읽을 수 있는 모든 보조 데이터베이스에서 쿼리 저장소를 사용하도록 설정하려면 주 복제본에 연결하고 기능을 사용하도록 등록할 각 데이터베이스에 대해 다음 스크립트를 실행합니다.

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE);

보조 복제본에 대해 자동 계획 수정 사용

적용 대상: SQL Server 2022(16.x) 이상 버전인 Azure SQL Database.

보조 복제본에 대해 쿼리 저장소를 사용하도록 설정한 후 필요에 따라 자동 튜닝을 사용하도록 설정하여 자동 계획 수정 기능이 보조 복제본에 대한 계획을 강제로 적용하도록 할 수 있습니다. 이렇게 하면 쿼리 최적화 프로그램에서 보조 복제본의 실행 계획 회귀로 인한 쿼리 성능 문제를 자동으로 식별하고 해결할 수 있습니다.

보조 복제본에 대해 자동 계획 수정을 사용하도록 설정하려면 주 복제본에 연결하고 원하는 각 데이터베이스에 대해 다음 스크립트를 실행합니다.

ALTER DATABASE [Database_Name]
FOR SECONDARY
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

보조 복제본에 대해 쿼리 저장소 사용 안 함

모든 보조 복제본에서 보조 복제본에 대한 쿼리 저장소 기능을 사용하지 않도록 설정하려면 복제본의 master 데이터베이스에 연결하고 원하는 각 데이터베이스에 primary 대해 다음 스크립트를 실행합니다.

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_ONLY);

보조 복제본에서 쿼리 저장소가 사용하도록 설정되어 있는지 확인

보조 복제본의 데이터베이스에 연결하여 복제본에서 secondary 쿼리 저장소가 사용하도록 설정되어 있는지 확인하고 다음 t-sql 문을 실행할 수 있습니다.

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

sys.database_query_store_options 카탈로그 뷰를 쿼리한 결과는 쿼리 저장소의 실제 상태가 이며, READ_CAPTURE_SECONDARY이(가) readonly_reason임을 나타내야 할 것입니다.

비고

용어

복제본 집합은 데이터베이스의 읽기/쓰기 복제본(주 복제본) 및 논리 단위로 처리되는 하나 이상의 읽기 전용 복제본(보조)으로 정의됩니다. 이 컨텍스트의 역할은 특정 복제본의 역할을 나타냅니다. 복제본이 주 역할을 수행하는 경우 데이터 수정 및 읽기 작업을 모두 수행할 수 있는 읽기/쓰기 복제본입니다. 복제본이 읽기 전용 작업만 수행하도록 구성되는 경우, 보조 역할(보조, 지역 보조, 지리적 ha 보조)을 맡게 됩니다. 역할은 계획되거나 계획되지 않은 장애 조치 이벤트를 통해 변경될 수 있으며, 이 경우 주 복제본이 보조 복제본으로, 또는 그 반대로 변경될 수 있습니다.

현재 지원되는 역할은 다음과 같습니다.

• 기본
• Secondary
• 지리 보조 시스템
• Geo HA 보조
• 명명된 복제본

작동 방식

쿼리에 대해 저장된 데이터는 역할 기준으로 워크로드로 분석할 수 있습니다. 읽기 가능한 보조 복제본에 대한 쿼리 저장소를 사용하면 보조 복제본에 대해 실행될 수 있는 고유한 읽기 전용 워크로드의 성능을 모니터링할 수 있습니다. 데이터는 역할 수준에서 집계됩니다. 예를 들어 SQL Server 분산 가용성 그룹 구성은 다음으로 구성될 수 있습니다.

• 하나의 주 복제본, AG1(가용성 그룹 1)의 일부

• 두 개의 로컬 보조 복제본은 AG1의 일부입니다.

• 별도의 AG2(가용성 그룹)의 일부인 다른 위치에 있는 하나의 원격 주 복제본입니다. SQL Server 용어에서는 일반적으로 글로벌 포워더라고도 합니다. 그러나 읽기 가능한 보조 복제본에 대한 쿼리 저장소 기능에서는 이를 지리적으로 분산된 보조 복제본으로 가정하고 Geo secondary 복제본으로 인식하고 참조합니다.

AG1의 보조 복제본 중 하나에 대해 읽기 전용 워크로드가 실행될 때 AG1 및 AG2가 읽기 전용 연결을 허용하도록 구성된 경우 쿼리 저장소 실행 통계는 AG1의 주 복제본으로 전송되고 집계되고 해당 데이터가 AG2의 전역 전달자를 포함한 모든 보조 복제본으로 다시 전송되기 전에 역할에서 secondary 생성된 데이터로 유지됩니다. AG2의 주 복제본인 전역 포워더에 별도의 워크로드가 실행되면, 해당 데이터는 AG1의 주 복제본으로 다시 전송되어 Geo secondary 역할에서 생성된 데이터로서 저장됩니다.

관찰 가능성 관점에서 sys.query_store_runtime_stats 시스템 카탈로그 뷰는 실행 통계가 시작된 역할을 식별하는 데 도움이 되도록 확장됩니다. 이 보기와 sys.query_store_replicas 시스템 카탈로그 뷰 간에는 더 친숙한 역할 이름을 제공할 수 있습니다. SQL Server, Azure SQL Database 및 Azure SQL Managed Instance에서 replica_name 열은 다음과 같습니다 NULL. 그러나 명명된 복제본이 있고 읽기 전용 워크로드에 사용되는 경우 하이퍼스케일 서비스 계층에 대해 replica_name 열이 채워집니다.

지난 8시간 동안 상위 50개 쿼리에 대한 전체 분석을 제공하는 데 사용할 수 있는 t-sql 쿼리의 예는 다음과 같습니다. 모든 복제본에서 사용된 CPU 리소스는 다음과 같습니다.

-- Top 50 queries by CPU across all replicas in the last 8 hours
DECLARE @hours AS INT = 8;

SELECT TOP 50 qsq.query_id,
              qsp.plan_id,
              CASE qrs.replica_group_id WHEN 1 THEN 'PRIMARY' WHEN 2 THEN 'SECONDARY' WHEN 3 THEN 'GEO SECONDARY' WHEN 4 THEN 'GEO HA SECONDARY' ELSE CONCAT('NAMED REPLICA_', qrs.replica_group_id) END AS replica_type,
              qsq.query_hash,
              qsp.query_plan_hash,
              SUM(qrs.count_executions) AS sum_executions,
              SUM(qrs.count_executions * qrs.avg_logical_io_reads) AS total_logical_reads,
              SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) AS total_cpu_ms,
              AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
              AVG(qrs.avg_cpu_time / 1000.0) AS avg_cpu_ms,
              ROUND(TRY_CAST (SUM(qrs.avg_duration * qrs.count_executions) AS FLOAT) / NULLIF (SUM(qrs.count_executions), 0) * 0.001, 2) AS avg_duration_ms,
              COUNT(DISTINCT qsp.plan_id) AS number_of_distinct_plans,
              qsqt.query_sql_text
FROM sys.query_store_runtime_stats_interval AS qsrsi
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
     INNER JOIN sys.query_store_plan AS qsp
         ON qsp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_query AS qsq
         ON qsq.query_id = qsp.query_id
     INNER JOIN sys.query_store_query_text AS qsqt
         ON qsq.query_text_id = qsqt.query_text_id
WHERE qsrsi.start_time >= DATEADD(HOUR, -@hours, GETUTCDATE())
GROUP BY qsq.query_id, qsq.query_hash, qsp.query_plan_hash, qsp.plan_id, qrs.replica_group_id, qsqt.query_sql_text
ORDER BY SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) DESC, AVG(qrs.avg_cpu_time / 1000.0) DESC;

SSMS(SQL Server Management Studio) 21 이상 버전의 쿼리 저장소 보고서는 다양한 복제본 집합/역할에서 쿼리 저장소 데이터를 볼 수 있는 방법을 제공하는 복제본 드롭다운 목록을 제공합니다. 또한 개체 탐색기 보기 내에서 쿼리 저장소 노드는 읽을 수 있는 보조 복제본에 연결된 경우 쿼리 저장소의 현재 상태(즉, READ_CAPTURE)를 반영합니다.

Azure 진단 설정에서 읽을 수 있는 보조 원격 분석에 대한 쿼리 저장소

적용: Azure SQL Database

Azure 진단 설정을 통해 쿼리 저장소 런타임 통계 를 스트리밍하는 경우 원격 분석 데이터의 복제본 원본을 식별하는 데 도움이 되는 두 개의 열이 포함됩니다.

• is_primary_b: 데이터가 주 복제본(true) 또는 보조 복제본(false)에서 발생했는지 여부를 나타내는 부울 값입니다.
• replica_group_id: 복제본 역할에 해당하는 정수입니다.

이러한 열은 복제본 집합에서 워크로드를 분석할 때 메트릭 및 성능 데이터를 명확히 하는 데 필수적입니다. 쿼리 저장소 런타임 통계를 Log Analytics, Event Hubs 또는 Azure Storage로 스트리밍하도록 진단 설정을 구성하는 경우 쿼리 및 대시보드가 이러한 열에 대한 계정을 사용하여 복제본 역할별로 데이터를 적절하게 분할하도록 합니다. 진단 설정 및 사용 가능한 메트릭을 구성하는 방법에 대한 자세한 내용은 Azure Monitor의 진단 설정을 참조하세요.

읽을 수 있는 보조 데이터베이스에 대한 쿼리 저장소의 성능 고려 사항

보조 복제본에서 쿼리 정보를 주 복제본으로 다시 보내는 데 사용하는 채널은 보조 복제본을 최신 상태로 유지하는 데 사용되는 채널과 동일합니다. 여기서 channel는 무엇을 의미하나요?

HADR(가용성 그룹) 구성에서 복제본은 주 복제본과 보조 복제본 간에 로그 블록, 승인 및 상태 메시지를 전달하는 전용 전송 계층을 사용하여 서로 동기화됩니다. 이렇게 하면 데이터 일관성 및 장애 조치(failover) 준비 상태가 보장됩니다.

읽기 가능한 보조 데이터베이스에 대한 쿼리 저장소를 사용하도록 설정하면 별도의 네트워크 엔드포인트가 만들어지지 않습니다. 대신 기존 전송 계층을 통해 새 논리 통신 경로를 설정합니다.

• Azure SQL Database(비 하이퍼스케일), Azure SQL Managed Instance 및 SQL Server의 경우 HADR(고가용성 및 재해 복구) Always On 전송 계층을 사용합니다.

• Azure SQL Database 하이퍼스케일의 경우 계산 노드와 로그 서비스/페이지 서버 간의 통신 채널인 RbIo(원격 Blob I/O) 전송 계층이 사용됩니다. RbIo는 로그 레코드 및 데이터 페이지를 이동하기 위한 신뢰할 수 있는 암호화된 채널을 제공합니다.

이 경로는 동일한 암호화된 세션을 사용하여 일반 로그 레코드 트래픽과 함께 쿼리 저장소 실행 데이터(쿼리 텍스트, 계획, 런타임/대기 통계)를 멀티플렉싱합니다. 이 기능에는 모든 복제본의 관점에서 sys.database_query_store_internal_state을 쿼리하여 볼 수 있는 자체 캡처 및 수신 큐가 있습니다.

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

보조 데이터베이스의 데이터는 주 데이터베이스의 동일한 쿼리 저장소 테이블에 유지되므로 스토리지 요구 사항이 증가할 수 있습니다. 부하가 많은 경우 전송 채널에서 대기 시간 또는 백프레소를 관찰할 수 있습니다. 주 데이터베이스의 쿼리 저장소에 적용되는 동일한 임시 쿼리 캡처 제한 사항도 보조 데이터베이스에도 적용됩니다. 쿼리 저장소 크기 및 캡처 정책 관리에 대한 자세한 내용 및 지침은 쿼리 저장소에서 가장 관련성이 큰 데이터 유지를 참조하세요.

음수 쿼리 ID/계획 ID 표시 여부

음수 ID들은 주 데이터베이스에 영구적으로 저장되기 전에 보조 데이터베이스에 대한 쿼리/계획을 위한 임시 메모리 내 자리 표시자를 나타냅니다.

쿼리 저장소 데이터가 읽기 가능한 보조 복제본에서 주 복제본으로 유지되기 전에 쿼리 및 계획에 쿼리 저장소의 로컬 메모리 내 표현인 MEMORYCLERK_QUERYDISKSTORE_HASHMAP 임시 식별자가 할당될 수 있습니다. 쿼리 및 계획 ID는 음수로 표시될 수 있으며 주 복제본이 신뢰할 수 있는 식별자를 할당할 때까지 자리 표시자이며 쿼리 저장소에서 쿼리가 구성된 캡처 모드 요구 사항을 충족한다고 결정한 후에 발생합니다. 사용자 지정 캡처 정책이 있는 경우 시스템 카탈로그 뷰를 쿼리하여 sys.database_query_store_options 충족해야 하는 요구 사항을 검토할 수 있습니다.

SELECT query_capture_mode_desc,
       capture_policy_execution_count,
       capture_policy_total_compile_cpu_time_ms,
       capture_policy_total_execution_cpu_time_ms
FROM sys.database_query_store_options;

쿼리가 캡처된 것으로 지정되면 런타임/대기 통계 및 계획을 유지할 수 있으며 로컬 임시 ID는 양수 ID로 바뀝니다. 또한 계획 강제 적용 또는 힌트 기능을 사용할 수 있습니다.

관련 콘텐츠

• 데이터베이스 설정 옵션(Transact-SQL) ALTER
• sys.query_store_replicas
• sys.query_store_plan_forcing_locations(Transact-SQL)
• sys.sp_query_store_force_plan(Transact-SQL)
• 쿼리 저장소 힌트
• 쿼리 저장소 사용 시나리오
• sys.database_query_store_options(Transact-SQL)
• 쿼리 저장소 사용한 워크로드 모니터링 모범 사례
• 쿼리 저장소 관리하기 위한 모범 사례
• 쿼리 저장소 사용하여 성능 조정