쿼리 저장소 힌트

아티클
01/21/2024

적용 대상: SQL Server 2022(16.x) Azure SQL 데이터베이스 Azure SQL Managed Instance

이 문서에서는 쿼리 저장소를 사용하여 쿼리 힌트를 적용하는 방법을 간략하게 설명합니다. 쿼리 저장소 힌트를 사용하면 애플리케이션 코드를 변경하지 않고도 쿼리 계획을 간편하게 셰이핑할 수 있습니다.

쿼리 저장소 힌트는 Azure SQL Database 및 Azure SQL Managed Instance에서 사용할 수 있습니다. 쿼리 저장소 힌트는 SQL Server 2022(16.x)의 SQL Server에 도입된 기능이기도 합니다.

쿼리 저장소를 구성하고 관리하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.
실행 가능한 정보를 검색하고 쿼리 저장소 사용하여 성능을 조정하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 조정을 참조하세요.
Azure SQL Database에서 쿼리 저장소 운영하는 방법에 대한 자세한 내용은 Azure SQL 데이터베이스에서 쿼리 저장소 운영을 참조하세요.

주의

SQL Server 쿼리 최적화 프로그램은 일반적으로 쿼리에 대해 최적의 실행 계획을 선택하므로 힌트는 숙련된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다. 자세한 내용은 쿼리 힌트를 참조하세요.

이 동영상을 보고 쿼리 저장소 힌트에 대한 개요를 확인하세요.

개요

쿼리 최적화 프로그램은 쿼리에 대한 최적의 실행 계획을 선택하는 것이 좋습니다.

최적의 계획을 선택하지 않으면 개발자 또는 DBA가 특정 조건에 맞게 수동으로 최적화하려고 할 수 있습니다. 쿼리 힌트는 OPTION 절을 통해 지정되며 쿼리 실행 동작에 영향을 미치는 데 사용할 수 있습니다. 쿼리 힌트는 다양한 성능 관련 문제에 지역화된 솔루션을 제공하는 데 도움이 되지만 원래 쿼리 텍스트를 다시 작성해야 합니다. 데이터베이스 관리자와 개발자가 쿼리 힌트를 삽입하기 위해 항상 Transact-SQL 코드를 직접 변경할 수 있는 것은 아닙니다. Transact-SQL은 애플리케이션에 하드 코드되거나 애플리케이션에 의해 자동으로 생성될 수 있습니다. 이전에는 개발자가 사용하기 복잡한 계획 지침을 사용해야 할 수 있습니다.

어떤 쿼리 힌트를 적용할 수 있는지에 대한 자세한 내용은 지원되는 쿼리 힌트를 참조하세요.

쿼리 저장소 힌트를 사용하는 경우

이름에서 알 수 있듯이 이 기능은 쿼리 저장소를 확장하고 사용합니다. 쿼리 저장소는 쿼리, 실행 계획 및 관련 런타임 통계를 캡처할 수 있습니다. 쿼리 저장소 전반적인 성능 튜닝 고객 환경을 크게 간소화합니다. SQL Server 2016(13.x)에서 쿼리 저장소가 처음 도입되었으며, 이제 SQL Server 2022(16.x), Azure SQL Managed Instance 및 Azure SQL 데이터베이스에서 기본적으로 사용하도록 설정됩니다.

쿼리 저장소 힌트의 워크플로입니다.

다음은 쿼리 저장소 힌트가 쿼리 수준 성능 문제에 도움이 될 수 있는 예입니다.

실행할 때마다 쿼리를 다시 컴파일합니다.
대량 삽입 작업의 메모리 부여 크기를 제한합니다.
통계를 업데이트할 때 최대 병렬 처리 수준을 제한합니다.
중첩 루프 조인 대신 해시 조인을 사용합니다.
데이터베이스의 다른 항목은 호환성 수준 150으로 유지하면서 특정 쿼리에 대해 호환성 수준 110을 사용합니다.
SELECT TOP 쿼리의 행 목표 최적화를 사용하지 않도록 설정합니다.

쿼리 저장소 힌트를 사용하는 경우:

수정하려는 쿼리 문의 쿼리 저장소 query_id를 식별합니다. 다양한 방법으로 이 작업을 수행할 수 있습니다.
- 쿼리 저장소 카탈로그 뷰 쿼리.
- SQL Server Management Studio를 사용하여 기본 제공 쿼리 저장소 보고서를 사용합니다.
- Azure SQL Database에 Azure Portal Query Performance Insight를 사용합니다.
쿼리에 적용하려는 query_id 및 쿼리 힌트 문자열을 사용하여 sys.sp_query_store_set_hints를 실행합니다. 이 문자열은 하나 이상의 쿼리 힌트를 포함할 수 있습니다. 자세한 내용은 sys.sp_query_store_set_hints를 참조하세요.

만든 후에는 쿼리 저장소 힌트가 유지되고 다시 시작 및 장애 조치(failover) 시에도 존속됩니다. 쿼리 저장소 힌트는 하드 코드된 문 수준의 힌트와 기존 계획 지침의 힌트를 재정의합니다.

쿼리 힌트가 쿼리 최적화에 사용할 수 있는 쿼리 힌트와 일치하지 않는 경우, 쿼리 실행이 차단되지 않고 힌트가 적용되지 않습니다. 힌트로 인해 쿼리가 실패하는 경우 힌트가 무시되고 sys.query_store_query_hints에서 최신 오류 세부 정보를 볼 수 있습니다.

쿼리 저장소 힌트 시스템 저장 프로시저

힌트를 만들거나 업데이트하려면 sys.sp_query_store_set_hints를 사용합니다. 힌트는 유효한 T-SQL 문자열 형식 N'OPTION (...)'으로 지정됩니다.

쿼리 저장소 힌트를 만들 때 특정 query_id에 대한 쿼리 저장소 힌트가 없으면 새 쿼리 저장소 힌트가 만들어집니다.
쿼리 저장소 힌트를 만들거나 업데이트할 때 특정 query_id에 대한 쿼리 저장소 힌트가 이미 있는 경우 제공된 마지막 값은 연결된 쿼리의 이전에 지정된 값을 재정의합니다.
query_id가 없다면 오류가 발생합니다.

참고 항목

지원되는 힌트의 전체 목록은 sys.sp_query_store_set_hints를 참조하세요.

query_id와 연결된 힌트를 제거하려면 sys.sp_query_store_clear_hints를 사용합니다.

실행 계획 XML 특성

힌트가 적용되면 실행 계획의 StmtSimple 요소에 XML 형식으로 다음 결과 집합이 표시됩니다.

Attribute	설명
`QueryStoreStatementHintText`	쿼리에 적용된 실제 쿼리 저장소 힌트
`QueryStoreStatementHintId`	쿼리 힌트의 고유 식별자
`QueryStoreStatementHintSource`	쿼리 저장소 힌트 원본(예: “사용자”)

참고 항목

이 XML 요소는 Transact-SQL 명령 SET STATISTICS XML 및 SET SHOWPLAN XML의 출력을 통해서만 사용할 수 있습니다.

쿼리 저장소 힌트 및 기능 상호 운용성

쿼리 저장소 힌트는 다른 하드 코드된 문 수준의 힌트와 계획 지침을 재정의합니다.
쿼리는 항상 실행됩니다. 반대되는 쿼리 저장소 힌트는 무시되며, 그렇지 않으면 오류가 발생합니다.
쿼리 저장소 힌트가 모순되는 경우 SQL Server 쿼리 실행을 차단하지 않으며 쿼리 저장소 힌트가 적용되지 않습니다.
단순 매개 변수화 - 간단한 매개 변수화에 적합한 문에는 쿼리 저장소 힌트가 지원되지 않습니다.
강제 매개 변수화 - RECOMPILE 힌트는 데이터베이스 수준에서 설정된 강제 매개 변수화와 호환되지 않습니다. 데이터베이스에 강제 매개 변수화 집합이 있고 RECOMPILE 힌트가 쿼리에 대해 쿼리 저장소에서 설정된 힌트 문자열의 일부인 경우 SQL Server는 RECOMPILE 힌트를 무시하고 다른 힌트를 적용합니다(적용되는 경우).
- 또한 SQL Server는 RECOMPILE 힌트가 무시되었다는 경고(오류 코드 12461)를 실행합니다.
- 강제 매개 변수화 사용 사례 고려 사항에 대한 자세한 내용은 강제 매개 변수화 사용에 대한 지침을 참조하세요.
수동으로 만든 쿼리 저장소 힌트는 정리 시에 제외됩니다. 힌트와 쿼리는 캡처 정책의 자동 보존에 의해 쿼리 저장소에서 정리되지 않습니다.
- 사용자가 쿼리를 수동으로 제거할 수 있으므로 연결된 쿼리 저장소 힌트도 제거됩니다.
- CE 피드백을 통해 자동으로 생성된 쿼리 저장소 힌트는 캡처 정책의 자동 보존에 의해 정리될 수 있습니다.
- DOP 피드백 및 메모리 부여 피드백은 쿼리 저장소 힌트를 사용하지 않고 쿼리 동작을 결정합니다. 캡처 정책의 자동 보존으로 쿼리를 정리하면 DOP 피드백과 메모리 부여 피드백 데이터도 정리됩니다.
- CE 피드백이 구현된 것과 동일한 쿼리 저장소 힌트를 수동으로 만들 수 있으며, 그러면 힌트가 있는 쿼리가 캡처 정책의 자동 보존에 의해 더 이상 정리되지 않습니다.

쿼리 저장소 힌트 및 가용성 그룹

자세한 내용은 보조 복제본을 위한 쿼리 저장소를 참조하세요.

SQL Server 2022(16.x) 이전에는 가용성 그룹의 주 복제본에 대해 쿼리 저장소 힌트를 적용할 수 있습니다.
SQL Server 2022(16.x)부터 보조 복제본의 쿼리 저장소를 사용하도록 설정할 경우 쿼리 저장소 힌트에서 가용성 그룹의 보조 복제본에 대한 복제본도 인식합니다.
보조 복제본에 대한 쿼리 저장소를 사용하도록 설정된 경우 특정 복제본 또는 복제본 집합에 쿼리 저장소 힌트를 추가할 수 있습니다. sys.sp_query_store_set_query_hints에서 이는 SQL Server 2022(16.x)에서 도입된 @query_hint_scope 매개 변수에 의해 설정됩니다.
sys.query_store_replicas를 쿼리하여 사용 가능한 복제본 집합을 찾습니다.
sys.query_store_plan_forcing_locations를 사용하여 보조 복제본에 강제 적용된 계획을 찾습니다.

쿼리 저장소 힌트 모범 사례

잠재적인 새 쿼리 저장소 힌트에 대한 쿼리를 평가하기 전에 인덱스 및 통계 유지 관리를 완료합니다.
쿼리 저장소 힌트를 사용하기 전에 최신 호환성 수준에서 애플리케이션 데이터베이스를 테스트합니다.
- 예를 들어 SQL Server 2022(16.x)(호환성 수준 160)에서 PSP(매개 변수 중요 계획) 최적화가 도입되었으며, 이 최적화는 쿼리당 여러 활성 계획을 사용하여 균일하지 않은 데이터 배포를 처리합니다. 환경에서 최신 호환성 수준을 사용할 수 없는 경우 RECOMPILE 힌트를 사용하는 쿼리 저장소 힌트를 지원하는 호환성 수준에서 사용할 수 있습니다.
쿼리 저장소 힌트는 SQL Server 쿼리 계획 동작을 재정의합니다. 성능 관련 문제를 해결해야 하는 경우에만 쿼리 저장소 힌트를 사용하는 것이 좋습니다.
데이터 배포가 변경될 때마다 그리고 데이터베이스 마이그레이션 프로젝트 중에 쿼리 저장소 힌트, 문 수준 힌트, 계획 지침 및 쿼리 저장소 강제 계획을 다시 평가하는 것이 좋습니다. 데이터 배포가 변경되면 쿼리 저장소 힌트가 최적이 아닌 실행 계획을 생성할 수 있습니다.

예제

A. 쿼리 저장소 힌트 데모

Azure SQL Database 내 쿼리 저장소 힌트의 다음 연습에서는 BACPAC 파일(.bacpac)을 통해 가져온 데이터베이스를 사용합니다. 새 데이터베이스를 Azure SQL Database 서버로 가져오는 방법을 알아보려면 빠른 시작: 데이터베이스로 BACPAC 파일 가져오기를 참조하세요.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. 쿼리 저장소에서 쿼리 식별

다음 예제에서는 sys.query_store_query_text 및 sys.query_store_query를 쿼리하여 실행된 쿼리 텍스트 조각의 query_id를 반환합니다.

이 데모에서 튜닝하려는 쿼리는 SalesLT 샘플 데이터베이스에 있습니다.

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

쿼리 저장소는 쿼리 데이터를 시스템 뷰에 즉시 반영하지는 않습니다.

쿼리 저장소 시스템 카탈로그 뷰에서 쿼리를 식별합니다.

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

다음 샘플에서 SalesLT 데이터베이스의 이전 쿼리 예제는 query_id 39로 식별되었습니다.

식별되면 구성된 메모리 제한 비율의 최대 메모리 부여 크기를 적용하는 힌트를 query_id에 적용합니다.

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

다음 구문을 사용하여 쿼리 힌트를 적용할 수도 있습니다. 예를 들어 레거시 카디널리티 예측 도구를 강제로 적용하는 옵션입니다.

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

쉼표로 구분된 목록을 사용하여 여러 쿼리 힌트를 적용할 수 있습니다.

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

query_id 39에 대한 쿼리 저장소 힌트를 검토합니다.

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

마지막으로 sp_query_store_clear_hints를 사용하여 query_id 39에서 힌트를 제거합니다.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

다음을 통해 공유

쿼리 저장소 힌트

개요

쿼리 저장소 힌트를 사용하는 경우

쿼리 저장소 힌트 시스템 저장 프로시저

실행 계획 XML 특성

쿼리 저장소 힌트 및 기능 상호 운용성

쿼리 저장소 힌트 및 가용성 그룹

쿼리 저장소 힌트 모범 사례

예제

A. 쿼리 저장소 힌트 데모

B. 쿼리 저장소에서 쿼리 식별

피드백

추가 리소스

다음을 통해 공유

쿼리 저장소 힌트

개요

쿼리 저장소 힌트를 사용하는 경우

쿼리 저장소 힌트 시스템 저장 프로시저

실행 계획 XML 특성

쿼리 저장소 힌트 및 기능 상호 운용성

쿼리 저장소 힌트 및 가용성 그룹

쿼리 저장소 힌트 모범 사례

예제

A. 쿼리 저장소 힌트 데모

B. 쿼리 저장소에서 쿼리 식별

관련 콘텐츠

피드백

추가 리소스