쿼리 저장소 힌트
적용 대상:
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance
이 문서에서는 쿼리 저장소를 사용하여 쿼리 힌트를 적용하는 방법을 간략하게 설명합니다. 쿼리 저장소 힌트를 사용하면 애플리케이션 코드를 변경하지 않고도 쿼리 계획을 간편하게 셰이핑할 수 있습니다.
쿼리 저장소 힌트는 Azure SQL Database 및 Azure SQL Managed Instance에서 사용할 수 있습니다. 쿼리 저장소 힌트는 SQL Server 2022(16.x)의 SQL Server에 도입된 기능이기도 합니다.
- 쿼리 저장소를 구성하고 관리하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.
- 실행 가능한 정보를 검색하고 쿼리 저장소 사용하여 성능을 조정하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 조정을 참조하세요.
- Azure SQL Database에서 쿼리 저장소 운영하는 방법에 대한 자세한 내용은 Azure SQL Database에서 쿼리 저장소 운영을 참조하세요.
주의
SQL Server 쿼리 최적화 프로그램은 일반적으로 쿼리에 대해 최적의 실행 계획을 선택하므로 힌트는 숙련된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다. 자세한 내용은 쿼리 힌트를 참조하세요.
쿼리 저장소 힌트에 대한 개요는 이 비디오를 시청하세요.
개요
이상적으로 쿼리 최적화 프로그램은 쿼리에 대한 최적의 실행 계획을 선택합니다.
최적의 계획을 선택하지 않으면 개발자 또는 DBA가 특정 조건에 맞게 수동으로 최적화하려고 할 수 있습니다. 쿼리 힌트는 절을 통해 OPTION 지정되며 쿼리 실행 동작에 영향을 미치는 데 사용할 수 있습니다. 쿼리 힌트는 다양한 성능 관련 문제에 지역화된 솔루션을 제공하는 데 도움이 되지만 원래 쿼리 텍스트를 다시 작성해야 합니다. 데이터베이스 관리자와 개발자는 항상 Transact-SQL 코드를 직접 변경하여 쿼리 힌트를 삽입할 수 없습니다. Transact-SQL은 애플리케이션에 하드 코딩되거나 애플리케이션에서 자동으로 생성될 수 있습니다. 이전에는 개발자가 사용하기 복잡할 수 있는 계획 지침에 의존해야 할 수도 있습니다.
어떤 쿼리 힌트를 적용할 수 있는지에 대한 자세한 내용은 지원되는 쿼리 힌트를 참조하세요.
쿼리 저장소 힌트를 사용하는 경우
이름에서 알 수 있듯이 이 기능은 쿼리 저장소를 확장하고 사용합니다. 쿼리 저장소 쿼리, 실행 계획 및 관련 런타임 통계를 캡처할 수 있습니다. 쿼리 저장소 전반적인 성능 조정 고객 환경을 크게 간소화합니다. SQL Server 2016(13.x)은 쿼리 저장소 처음 도입되었으며, 이제 SQL Server 2022(16.x), Azure SQL Managed Instance 및 Azure SQL Database에서 기본적으로 사용하도록 설정됩니다.
먼저 쿼리가 실행된 다음, 쿼리 저장소 의해 캡처됩니다. 그런 다음 DBA는 쿼리에 대한 쿼리 저장소 힌트를 만듭니다. 그 후 쿼리는 쿼리 저장소 힌트를 사용하여 실행됩니다.
쿼리 저장소 힌트가 쿼리 수준 성능 문제에 도움이 될 수 있는 예:
- 실행할 때마다 쿼리를 다시 컴파일합니다.
- 대량 삽입 작업의 메모리 부여 크기를 제한합니다.
- 통계를 업데이트할 때 최대 병렬 처리 수준을 제한합니다.
- 중첩 루프 조인 대신 해시 조인을 사용합니다.
- 데이터베이스의 다른 모든 항목을 호환성 수준 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 사용합니다. 힌트는 유효한 문자열 형식 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 피드백이 구현된 것과 동일한 쿼리 저장소 힌트를 수동으로 만든 다음, 힌트가 있는 쿼리는 캡처 정책의 자동 보존으로 인해 더 이상 클린 적용되지 않습니다.
쿼리 저장소 힌트 및 가용성 그룹
자세한 내용은 보조 복제본(replica) 대한 쿼리 저장소 참조하세요.
- SQL Server 2022(16.x) 이전에는 가용성 그룹의 기본 복제본(replica) 대해 쿼리 저장소 힌트를 적용할 수 있습니다.
- SQL Server 2022(16.x)부터 보조 복제본(replica) 대한 쿼리 저장소 사용하도록 설정된 경우 쿼리 저장소 힌트는 가용성 그룹의 보조 복제본(replica) 대해 복제본(replica) 인식합니다.
- 보조 복제본(replica) 쿼리 저장소 사용하도록 설정된 경우 특정 복제본(replica) 또는 복제본(replica) 집합에 쿼리 저장소 힌트를 추가할 수 있습니다. sys.sp_query_store_set_query_hints SQL Server 2022(16.x)에서 도입된 매개 변수에 의해
@query_hint_scope설정됩니다. - sys.query_store_복제본(replica) 쿼리하여 사용 가능한 복제본(replica) 집합을 찾습니다.
- sys.query_store_plan_forcing_locations 보조 복제본(replica) 강제 적용된 계획을 찾습니다.
쿼리 저장소 힌트 모범 사례
- 잠재적인 새 쿼리 저장소 힌트에 대한 쿼리를 평가하기 전에 인덱스 및 통계 유지 관리를 완료합니다.
- 쿼리 저장소 힌트를 사용하기 전에 최신 호환성 수준에서 애플리케이션 데이터베이스를 테스트합니다.
- 예를 들어 PSP(매개 변수 중요 계획) 최적화는 SQL Server 2022(16.x)(호환성 수준 160)에서 도입되었으며, 쿼리당 여러 활성 계획을 사용하여 비파일형 데이터 배포를 처리합니다. 환경에서 최신 호환성 수준을 사용할 수 없는 경우 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)';
다음 구문을 사용하여 쿼리 힌트를 적용할 수도 있습니다. 예를 들어 레거시 카드inality 추정기를 강제 적용하는 옵션입니다.
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;
관련 콘텐츠
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기