적용 대상:
Microsoft Fabric Preview의 SQL Server 2022(16.x) 이상 버전 Azure SQL DatabaseAzure SQL Managed Instance SQL 데이터베이스
이 문서에서는 쿼리 저장소를 사용하여 쿼리 힌트를 적용하는 방법을 간략하게 설명합니다. 쿼리 저장소 힌트를 사용하면 애플리케이션 코드를 변경하지 않고도 쿼리 계획을 간편하게 셰이핑할 수 있습니다.
- 쿼리 저장소를 구성하고 관리하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.
- 실행 가능한 정보를 검색하고 쿼리 저장소 사용하여 성능을 조정하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 조정을 참조하세요.
- Azure SQL Database에서 쿼리 저장소 운영하는 방법에 대한 자세한 내용은 Azure SQL 데이터베이스에서 쿼리 저장소 운영을 참조하세요.
Caution
SQL Server 쿼리 최적화 프로그램은 일반적으로 쿼리에 대해 최적의 실행 계획을 선택하므로 힌트는 숙련된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다. 자세한 내용은 쿼리 힌트를 참조하세요.
이 동영상을 보고 쿼리 저장소 힌트에 대한 개요를 확인하세요.
Overview
쿼리 최적화 프로그램은 쿼리에 대한 최적의 실행 계획을 선택하는 것이 좋습니다.
최적의 계획을 선택하지 않으면 개발자 또는 DBA(데이터베이스 관리자)가 특정 조건에 맞게 수동으로 최적화하려고 할 수 있습니다. 쿼리 힌트는 OPTION 절을 통해 지정되며 쿼리 실행 동작에 영향을 미치는 데 사용할 수 있습니다. 쿼리 힌트는 다양한 성능 관련 문제에 지역화된 솔루션을 제공하는 데 도움이 되지만 원래 쿼리 텍스트를 다시 작성해야 합니다. 데이터베이스 관리자와 개발자는 항상 Transact-SQL 코드를 직접 변경하여 쿼리 힌트를 추가할 수 없습니다. Transact-SQL은 애플리케이션에 하드 코드되거나 애플리케이션에 의해 자동으로 생성될 수 있습니다. 이전에는 개발자가 사용하기 복잡한 계획 지침을 사용해야 할 수 있습니다.
쿼리 저장소 힌트는 쿼리 Transact-SQL 텍스트를 직접 수정하지 않고 쿼리 힌트를 쿼리에 삽입하도록 하여 이 문제를 해결합니다. 어떤 쿼리 힌트를 적용할 수 있는지에 대한 자세한 내용은 지원되는 쿼리 힌트를 참조하세요.
쿼리 저장소 힌트를 사용하는 경우
이름에서 알 수 있듯이 이 기능은 쿼리 저장소를 확장하고 사용합니다. 쿼리 저장소는 쿼리, 실행 계획 및 관련 런타임 통계를 캡처할 수 있습니다. 쿼리 저장소 전반적인 성능 튜닝 고객 환경을 크게 간소화합니다. SQL Server 2016(13.x)은 쿼리 저장소를 처음 도입했으며, 이제 Microsoft Fabric Preview의 SQL Server 2022(16.x), Azure SQL Managed Instance, Azure SQL Database 및 SQL Database에서 기본적으로 사용하도록 설정됩니다.
먼저 쿼리가 실행된 다음, 쿼리 저장소에 의해 캡처됩니다. 그런 다음 DBA는 쿼리에 대한 쿼리 저장소 힌트를 만듭니다. 그 후 쿼리는 쿼리 저장소 힌트를 사용하여 실행됩니다.
다음은 쿼리 저장소 힌트가 쿼리 수준 성능 문제에 도움이 될 수 있는 예입니다.
- 실행할 때마다 쿼리를 다시 컴파일합니다.
- 대량 삽입 작업의 메모리 부여 크기를 제한합니다.
- 통계를 업데이트할 때 최대 병렬 처리 수준을 제한합니다.
- 중첩 루프 조인 대신 해시 조인을 사용합니다.
- 데이터베이스의 다른 항목은 호환성 수준 150으로 유지하면서 특정 쿼리에 대해 호환성 수준 110을 사용합니다.
- 쿼리에 대한 행 목표 최적화를
SELECT TOP사용하지 않도록 설정합니다.
쿼리 저장소 힌트를 사용하는 경우:
수정하려는 쿼리 문의 쿼리 저장소
query_id를 식별합니다. 다양한 방법으로 이 작업을 수행할 수 있습니다.- 쿼리 저장소 카탈로그 뷰 쿼리(Transact-SQL).
- SQL Server Management Studio를 사용하여 기본 제공 쿼리 저장소 보고서를 사용합니다.
- Azure SQL Database에 Azure Portal Query Performance Insight를 사용합니다.
쿼리에 적용하려는
sys.sp_query_store_set_hints및 쿼리 힌트 문자열을 사용하여query_id를 실행합니다. 이 문자열은 하나 이상의 쿼리 힌트를 포함할 수 있습니다. 자세한 내용은 sys.sp_query_store_set_hints를 참조하세요.
만든 후에는 쿼리 저장소 힌트가 유지되고 다시 시작 및 장애 조치(failover) 시에도 존속됩니다. 쿼리 저장소의 힌트는 하드 코드된 문 수준의 힌트와 기존 계획 지침의 힌트보다 우선합니다.
쿼리 힌트가 쿼리 최적화에 가능한 것과 모순되는 경우 쿼리 실행이 차단되지 않고 힌트가 적용되지 않습니다. 힌트로 인해 쿼리가 실패하는 경우 힌트는 무시되며 sys.query_store_query_hints 최신 오류 세부 정보를 볼 수 있습니다.
쿼리 저장소 힌트를 사용하기 전에
쿼리 저장소 힌트 사용을 시작하기 전에 다음을 고려합니다.
- 잠재적인 새 쿼리 저장소 힌트에 대한 쿼리를 평가하기 전에 통계 유지 관리 및 인덱스 유지 관리 (필요한 경우)를 완료합니다. 통계 유지 관리 및 낮은 수준의 인덱스 유지 관리로 쿼리 힌트가 필요한 문제를 해결할 수 있습니다.
- 쿼리 저장소 힌트를 사용하기 전에 최신 호환성 수준에서 애플리케이션 데이터베이스를 테스트하여 쿼리 힌트가 필요한 문제를 해결하는지 확인합니다.
- 예를 들어 PSP(매개 변수 중요 계획) 최적화는 호환성 수준 160에서 SQL Server 2022(16.x)에 도입되었습니다. 쿼리당 여러 활성 계획을 사용하여 비유형 데이터 배포를 처리합니다. 환경에서 최신 호환성 수준을 사용할 수 없는 경우에도
RECOMPILE힌트를 사용하는 쿼리 저장소 힌트를 지원되는 호환성 수준에서 사용할 수 있습니다.
- 예를 들어 PSP(매개 변수 중요 계획) 최적화는 호환성 수준 160에서 SQL Server 2022(16.x)에 도입되었습니다. 쿼리당 여러 활성 계획을 사용하여 비유형 데이터 배포를 처리합니다. 환경에서 최신 호환성 수준을 사용할 수 없는 경우에도
- 쿼리 저장소 힌트는 데이터베이스 엔진 기본 쿼리 계획 동작을 재정의합니다. 성능 관련 문제를 해결해야 하는 경우에만 쿼리 저장소 힌트를 사용해야 합니다.
- 쿼리 저장소 힌트, 문 수준 힌트, 계획 지침 및 쿼리 저장소 강제 계획을 언제든 데이터 볼륨 및 배포가 변경되고 데이터베이스 마이그레이션 프로젝트 중에 다시 평가해야 합니다. 데이터 볼륨 및 배포가 변경되면 쿼리 저장소 힌트가 최적이 아닐 실행 계획을 생성할 수 있습니다.
쿼리 저장소의 힌트 시스템 저장 프로시저
힌트를 만들거나 업데이트하려면 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를 사용합니다.
Tip
쿼리 해시와 일치하는 모든 query_id 값에 대한 힌트를 설정하거나 지워야 할 수 있습니다.
dbo.sp_query_store_modify_hints_by_query_hash 이 작업을 수행하기 위해 루프에서 sys.sp_query_store_set_hints 또는 sys.sp_query_store_clear_hints 시스템 저장 프로시저를 호출하는 샘플 저장 프로시저입니다.
실행 계획 XML 특성
힌트가 적용되면 다음 결과 집합이 StmtSimpleXML 형식으로 실행 계획의 요소에 나타납니다.
| Attribute | Description |
|---|---|
QueryStoreStatementHintText |
쿼리에 적용된 실제 쿼리 저장소 힌트 |
QueryStoreStatementHintId |
쿼리 힌트의 고유 식별자 |
QueryStoreStatementHintSource |
쿼리 저장소 힌트의 원본(예: User) |
Note
이러한 XML 요소는 SET STATISTICS XML 및 SET SHOWPLAN_XML Transact-SQL 명령의 출력을 통해 사용할 수 있습니다.
쿼리 저장소 힌트 및 기능의 상호 운용성
- 쿼리 저장소 힌트는 다른 하드코딩된 문 수준 힌트와 계획 지침을 재정의합니다.
- 힌트를
ABORT_QUERY_EXECUTION제외하고 쿼리 저장소 힌트가 있는 쿼리는 항상 실행됩니다. 반대되는 쿼리 저장소 힌트는 무시되며, 그렇지 않으면 오류가 발생합니다. - 쿼리 저장소 힌트가 모순되면 데이터베이스 엔진이 쿼리 실행을 차단하지 않으며 쿼리 저장소 힌트가 적용되지 않습니다.
- 단순 매개 변수화에 적합한 문에는 쿼리 저장소 힌트가 지원되지 않습니다.
- 힌트는
RECOMPILE데이터베이스 수준에서 강제 매개 변수화 집합과 호환되지 않습니다. 데이터베이스에 강제 매개 변수화 집합이 있고RECOMPILE힌트가 쿼리에 대한 쿼리 저장소 힌트의 일부인 경우 데이터베이스 엔진은 힌트를 무시하고RECOMPILE지정된 경우 다른 힌트를 적용합니다.- 데이터베이스 엔진은
RECOMPILE힌트가 무시되었다는 경고(오류 코드 12461)를 발생합니다. - 강제 매개 변수화 사용 사례 고려 사항에 대한 자세한 내용은 강제 매개 변수화 사용에 대한 지침을 참조하세요.
- 데이터베이스 엔진은
- 수동으로 만든 쿼리 저장소 힌트는 쿼리 저장소 정리에서 제외됩니다. 힌트와 쿼리는 자동 보존 캡처 정책에 의해 정리되지 않습니다.
- 쿼리는 사용자가 수동으로 제거할 수 있습니다. 그러면 연결된 쿼리 저장소 힌트도 제거됩니다.
- CE 피드백을 통해 자동으로 생성된 쿼리 저장소 힌트는 캡처 정책의 자동 보존에 의해 정리될 수 있습니다.
- DOP 피드백 및 메모리 부여 피드백은 쿼리 저장소 힌트를 사용하지 않고 쿼리 동작을 결정합니다. 자동 보존 캡처 정책에 의해 쿼리가 정리되면 DOP 피드백 및 메모리 부여 피드백 데이터도 정리됩니다.
- CE 피드백이 수동으로 구현한 것과 동일한 쿼리 저장소 힌트를 만드는 경우 힌트가 있는 쿼리는 더 이상 자동 보존 캡처 정책에 의해 정리되지 않습니다.
쿼리 저장소 힌트 및 보조 복제본
보조 복제본에 대한 쿼리 저장소가 활성화되지 않는 한, 쿼리 저장소 힌트는 보조 복제본에 영향을 주지 않습니다. 자세한 내용은 읽을 수 있는 보조 복제본에 대한 쿼리 저장소를 참조하세요.
- SQL Server 2022(16.x) 및 이전 버전에서는 쿼리 저장소 힌트를 주 복제본에만 적용할 수 있습니다.
- SQL Server 2022(16.x) 이상 버전에서는 보조 복제본에 대한 쿼리 저장소를 사용하는 경우 가용성 그룹의 보조 복제본에 쿼리 저장소 힌트를 적용할 수 있습니다.
- 보조 복제본에 대한 쿼리 저장소를 사용하도록 설정한 경우 특정 복제본 그룹에만 적용되도록 쿼리 저장소 힌트를 추가할 수 있습니다. 이렇게 하려면
@replica_group_id매개 변수를 사용하여 sys.sp_query_store_set_query_hints를 호출합니다. 반대로 sys.sp_query_store_clear_query_hints 사용하여 특정 복제본 그룹에서 쿼리 저장소 힌트를 제거할 수 있습니다. - sys.query_store_replicas 쿼리하여 사용 가능한 복제본 그룹을 찾습니다.
- sys.query_store_plan_forcing_locations를 사용하여 보조 복제본에 강제 적용된 계획을 찾습니다.
Examples
A. 쿼리 저장소 힌트 데모
Azure SQL Database의 다음 쿼리 저장소 힌트 연습에서는 BACPAC 파일(.bacpac)을 통해 가져온 데이터베이스를 사용합니다. 새 데이터베이스를 Azure SQL Database 서버로 가져오는 방법을 알아봅니다. 빠른 시작: Azure SQL Database 또는 Azure SQL Managed Instance의 데이터베이스로 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;
마지막으로 query_id를 사용하여 39에서 힌트를 제거합니다.
EXEC sys.sp_query_store_clear_hints @query_id = 39;