쿼리 저장소에 대한 모범 사례Best Practice with the Query Store

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 항목에서는 작업에 쿼리 저장소를 사용하는 모범 사례에 대해 설명합니다.This topic outlines the best practices for using the Query Store with your workload.

최신 SQL Server Management Studio 사용 Use the latest SQL Server Management Studio

SQL Server Management StudioSQL Server Management Studio 에는 작업에 대해 수집된 데이터를 사용할 뿐 아니라 쿼리 저장소를 구성하기 위해 디자인된 사용자 인터페이스 집합이 있습니다. has set of user interfaces designed for configuring Query Store as well as for consuming collected data about your workload.
Management StudioManagement Studio 최신 버전은 여기에서 다운로드하세요.Download the latest version of Management StudioManagement Studio here.

문제 해결 시나리오에서 쿼리 저장소를 사용하는 방법에 대한 빠른 설명은 Query Store @Azure Blogs(Azure 블로그의 쿼리 저장소)를 참조하세요.For a quick description on how to use Query Store in troubleshooting scenarios refer to Query Store @Azure Blogs.

Azure SQL Database에서 Query Performance Insight 사용 Use Query Performance Insight in Azure SQL Database

SQL 데이터베이스SQL Database 에서 쿼리 저장소를 사용하는 경우 Query Performance Insight 를 사용하여 시간의 흐름에 따른 DTU 사용을 분석할 수 있습니다.If you run Query Store in SQL 데이터베이스SQL Database you can use Query Performance Insight to analyze DTU consumption over time.
Management StudioManagement Studio 를 사용하여 모든 쿼리(CPU, 메모리, IO 등)에 대한 자세한 리소스 사용을 가져오는 동안 Query Performance Insight에서는 데이터베이스의 전체 DTU 사용에 대한 영향을 빠르고 효율적으로 확인하는 방법을 제공합니다.While you can use Management StudioManagement Studio to get detailed resource consumption for all your queries (CPU, memory, IO, etc.), Query Performance Insight gives you a quick and efficient way to determine their impact on overall DTU consumption for your database.
자세한 내용은 Azure SQL 데이터베이스 Query Performance Insight를 참조하세요.For more information, see Azure SQL Database Query Performance Insight.

탄력적 풀 데이터베이스에서 쿼리 저장소 사용Using Query Store with Elastic Pool Databases

더 세부적으로 패키지된 풀에서는 모든 데이터베이스에서 쿼리 저장소를 사용할 수 있습니다.You can use Query Store in all databases without concerns, in even densely packed pools. 탄력적 풀에서 많은 데이터베이스에 대해 쿼리 저장소를 사용하도록 설정한 경우 발생할 수 있었던 과도한 리소스 사용과 관련된 모든 문제가 해결되었습니다.All issues related to excessive resource usage, that might have occurred when Query Store was enabled for the large number of databases in the Elastic Pools, have been resolved.

쿼리 저장소를 작업에 맞게 조정된 상태로 유지 Keep Query Store adjusted to your workload

작업 및 성능 문제 해결 요구 사항을 기반으로 쿼리 저장소를 구성합니다.Configure Query Store based on your workload and performance troubleshooting requirements.
빠른 시작을 위해서는 기본 매개 변수를 사용하는 것이 좋지만 시간이 흐름에 따라 쿼리 저장소가 동작하는 방식을 모니터링하여 구성을 그에 맞게 조정해야 합니다.The default parameters are good for a quick start but you should monitor how Query Store behaves over time and adjust its configuration accordingly:

query-store-propertiesquery-store-properties

매개 변수 값 설정을 위해 따라야 할 지침은 아래와 같습니다.Here are guidelines to follow for setting parameter values:

최대 크기(MB): 데이터베이스 내부에서 쿼리 저장소가 사용할 데이터 공간에 대한 한도를 지정합니다.Max Size (MB): Specifies the limit for the data space that Query Store will take inside your database. 이는 쿼리 저장소의 작업 모드에 직접적으로 영향을 주는 가장 중요한 설정입니다.This is the most important setting that directly affects operation mode of the Query Store.

쿼리 저장소에서 쿼리, 실행 계획 및 통계를 수집하는 동안 이 한도에 도달할 때까지 데이터베이스에서 크기가 증가합니다.While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. 한도에 도달하면 쿼리 저장소는 작업 모드를 자동으로 읽기 전용으로 변경하고 새 데이터 수집을 중지합니다. 즉 성능 분석은 더 이상 정확하지 않게 됩니다.When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.

서로 다른 쿼리와 계획을 많이 생성하거나 쿼리 기록을 장기간 유지하려고 할 경우에는 기본값(100MB)이 충분하지 않을 수도 있습니다.The default value (100 MB) may not be sufficient if your workload generates large number of different queries and plans or if you want to keep query history for a longer period of time. 현재 공간 사용을 계속 추적하고 최대 크기(MB)를 늘려 쿼리 저장소가 읽기 전용 모드로 전환되지 않도록 합니다.Keep track of current space usage and increase the Max Size (MB) to prevent Query Store from transitioning to read-only mode. Management StudioManagement Studio 를 사용하거나 다음 스크립트를 실행하여 쿼리 저장소 크기에 대한 최신 정보를 확인합니다.Use Management StudioManagement Studio or execute the following script to get the latest information about Query Store size:

USE [QueryStoreDB];  
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason  
FROM sys.database_query_store_options;  

다음 스크립트에서는 새로운 최대 크기 (MB)를 설정합니다.The following script sets a new Max Size (MB):

ALTER DATABASE [QueryStoreDB]  
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);  

통계 수집 간격: 수집된 런타임 통계에 대한 세분성의 수준을 정의합니다(기본값 - 1시간).Statistics Collection Interval: Defines level of granularity for the collected runtime statistic (the default is 1 hour). 문제를 완화하기 위해 세분성이 더 상세하고 시간을 줄여야 할 경우 값을 낮추는 것을 고려해 볼 수 있지만 쿼리 저장소 데이터 크기에 직접 영향을 미칠 수 있음을 염두에 둬야 합니다.Consider using lower value if you require finer granularity or less time to detect and mitigate issues but keep in mind that it will directly affect the size of Query Store data. 통계 수집 간격에 대해 다른 값을 설정하려면 SSMS 또는 Transact-SQL을 사용합니다.Use SSMS or Transact-SQL to set different value for Statistics Collection Interval:

ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);  

오래된 쿼리 임계값(일): 지속형 런타임 통계와 비활성 쿼리의 보존 기간을 제어하는 시간 기반 정리 정책입니다.Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
기본적으로 쿼리 저장소는 30일 동안 데이터를 보관하도록 구성되어 있어 시나리오에서 불필요하게 길 수도 있습니다.By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

사용하지 않을 기록 데이터는 보관하지 않는 것이 좋습니다.Avoid keeping historical data that you do not plan to use. 이렇게 하면 읽기 전용 상태로 변경되는 횟수가 줄어듭니다.This will reduce changes to read-only status. 쿼리 저장소의 데이터 크기와 문제를 검색하여 완화하는 시간도 더 예측 가능해집니다.The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Management StudioManagement Studio 또는 다음 스크립트를 사용하여 시간 기반 정리 정책을 구성합니다.Use Management StudioManagement Studio or the following script to configure time-based cleanup policy:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));  

크기 기반 정리 모드: 쿼리 저장소 데이터 크기가 한도에 도달할 때 자동 데이터 정리의 발생 여부를 지정합니다.Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.

크기 기반 정리를 활성화하여 쿼리 저장소가 항상 읽기-쓰기 모드 상태로 최신 데이터를 수집하는 것이 좋습니다.It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);  

쿼리 저장소 캡처 모드: 쿼리 저장소에 대한 쿼리 캡처 정책을 지정합니다.Query Store Capture Mode: Specifies the query capture policy for the Query Store.

  • All - 쿼리를 모두 캡처합니다.All – Captures all queries. 이 옵션이 기본 옵션입니다.This is the default option.

  • Auto – 빈번하지 않은 쿼리와 중요하지 않은 쿼리에 대한 컴파일 및 실행 기간이 무시됩니다.Auto – Infrequent queries and queries with insignificant compile and execution duration are ignored. 실행 횟수, 컴파일 및 런타임 기간에 대한 임계값이 내부적으로 결정됩니다.Thresholds for execution count, compile and runtime duration are internally determined.

  • None - 쿼리 저장소에서 새 쿼리 캡처가 중지됩니다.None – Query Store stops capturing new queries.

    다음 스크립트는 쿼리 캡처 모드를 자동으로 설정합니다.The following script sets the Query Capture mode to Auto:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);  

쿼리 성능 문제 해결을 시작 하는 방법How to start with query performance troubleshooting

다음 다이어그램에 표시된 대로 쿼리 저장소의 워크플로 문제는 간단히 해결됩니다.Troubleshooting workflow with Query Store is simple, as shown on the following diagram:

query-store-troubleshootingquery-store-troubleshooting

이전 섹션에서 설명한 대로 Management StudioManagement Studio 를 사용하여 쿼리 저장소를 사용하도록 설정하거나 다음 Transact-SQLTransact-SQL 문을 실행합니다.Enable Query Store by using Management StudioManagement Studio as described in the previous section, or execute the following Transact-SQLTransact-SQL statement:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;  

작업을 정확하게 나타내는 데이터 집합을 쿼리 저장소에서 수집할 때까지 약간의 시간이 걸립니다.It will take some time until Query Store collects the data set that accurately represents your workload. 매우 복잡한 작업의 경우라도 일반적으로 하루면 충분합니다.Usually, one day is enough even for very complex workloads. 그러나 기능을 사용하도록 설정한 후에는 데이터 탐색을 시작하고 즉시 주의가 필요한 쿼리를 식별할 수 있습니다.However, you can start exploring the data and identify queries that need your attention immediately after you enabled the feature.
Management StudioManagement Studio 개체 탐색기의 데이터베이스 노드 아래에서 쿼리 저장소 하위 폴더로 이동하여 특정 시나리오에 대한 문제 해결 보기를 엽니다.Navigate to the Query Store sub-folder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.
Management StudioManagement Studio 쿼리 저장소 보기는 실행 메트릭 집합을 사용하여 동작하며 각 메트릭은 다음 통계 함수 중 하나로 표현됩니다. Query Store views operate with the set of execution metrics, each expressed as any of the following statistic functions:

실행 메트릭Execution metric 통계 함수Statistic function
CPU 시간, 기간, 실행 수, 논리적 읽기 수, 논리적 쓰기, 메모리 사용 및 물리적 읽기CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, and Physical Reads 평균, 최대값, 최소값, 표준 편차, 합계Average, Maximum, Minimum, Standard Deviation, Total

다음 그림은 쿼리 저장소 보기를 찾는 방법을 보여 줍니다.The following graphic shows how to locate Query Store views:

query-store-viewsquery-store-views

다음 표에서는 각 쿼리 저장소 보기를 사용하는 시기를 설명합니다.The following table explains when to use each of the Query Store views:

SSMS 보기SSMS view 시나리오Scenario
재발된 쿼리Regressed Queries 최근에 실행 메트릭이 재발된 쿼리를 정확히 파악합니다.Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse).
응용 프로그램에서 관찰된 성능 문제와 수정하거나 개선해야 할 실제 쿼리의 상관 관계를 지정하려면 이 보기를 사용합니다.Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved.
전체 리소스 사용Overall Resource Consumption 실행 메트릭 중 하나에 대한 데이터베이스의 전체 리소스 사용을 분석합니다.Analyze the total resource consumption for the database for any of the execution metrics.
리소스 패턴(낮 작업 vs. 밤 작업)을 식별하고 데이터베이스에 대한 전체 사용을 최적화하려면 이 보기를 사용합니다.Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.
리소스를 최고로 사용 중인 쿼리Top Resource Consuming Queries 관심 있는 메트릭 실행을 선택하고 제공된 시간 간격 동안 가장 값이 높은 쿼리를 식별합니다.Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval.
데이터베이스 리소스 사용에 가장 큰 영향을 미치는 가장 관련성이 높은 쿼리에 주목하려면 이 보기를 사용합니다.Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption.
강제 계획이 포함된 쿼리Queries With Forced Plans 쿼리 저장소를 사용하여 이전 강제 계획을 나열합니다.Lists previously forced plans using Query Store.
모든 현재 강제 계획에 빠르게 액세스하려면 이 보기를 사용합니다.Use this view to quickly access all currently forced plans.
고변형 쿼리Queries With High Variation 기간, CPU 시간, IO 및 원하는 시간 간격의 메모리 사용량과 같은 사용 가능한 차원과 관련하여 실행 변형이 높은 쿼리를 분석합니다.Analyze queries with high execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage in the desired time interval.
이 뷰를 사용하여 응용 프로그램 전체에서 사용자 경험에 영향을 줄 수 있는, 성능 변동이 큰 쿼리를 식별합니다.Use this view to identify queries with widely variant performance that can be impacting user experience across your applications.
추적된 쿼리Tracked Queries 가장 중요한 쿼리 실행을 실시간으로 추적합니다.Track the execution of the most important queries in real-time. 일반적으로 강제 계획을 사용하는 쿼리가 있고 해당 쿼리 성능이 안정적인지 확인하려고 할 경우 이 보기를 사용합니다.Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.

Management StudioManagement Studio를 사용하여 리소스를 가장 많이 사용하는 쿼리를 식별하고, 선택한 계획을 변경하여 재발된 쿼리를 수정하는 방법은 @Azure 블로그의 쿼리 저장소를 참조하세요.For a detailed description how to use Management StudioManagement Studio to identify the top resource consuming queries and fix those that regressed due to the change of a plan choice, see Query Store @Azure Blogs.

최적 상태가 아닌 성능의 쿼리를 식별한 경우 수행할 작업은 문제의 성격에 따라 다릅니다.When you identify a query with sub-optimal performance, your action depends on the nature of the problem.

  • 쿼리가 여러 계획으로 실행되고 마지막 계획이 이전 계획보다 훨씬 나쁜 경우 계획 적용 메커니즘을 사용하여 SQL ServerSQL Server 에서 향후 실행에 최적 계획을 사용하도록 할 수 있습니다.If the query was executed with multiple plans and the last plan is significantly worse than previous plan, you can use the plan forcing mechanism to force SQL ServerSQL Server to always use the optimal plan for future executions.

    query-store-force-planquery-store-force-plan

참고

위의 그림에서는 쿼리 계획에 따라 셰이프가 다를 수 있으며, 셰이프는 다음과 같이 가능한 각 상태를 의미합니다.The above graphic may feature different shapes for specific query plans, with the following meanings for each possible status:

셰이프Shape 의미Meaning
CircleCircle 쿼리 완료됨(정상 실행이 완료됨)Query Completed (Regular Execution successfully finished)
SquareSquare 취소됨(클라이언트 시작으로 실행이 중단됨)Cancelled (Client initiated aborted execution)
TriangleTriangle 실패(예외로 실행이 중단됨)Failed (Exception aborted execution)

또한 셰이프 크기는 지정된 시간 간격 내의 쿼리 실행 수를 나타내며, 크기가 크면 실행 수가 많은 것입니다.Also, the size of the shape reflects query execution count within the specified time interval, increasing in size with a higher number of executions.

  • 쿼리에 최적의 실행을 위한 인덱스가 없다는 결론을 내릴 수도 있습니다.You may conclude that your query is missing an index for optimal execution. 이 정보는 쿼리 실행 계획 내에 표시됩니다.This information is surfaced within the query execution plan. 누락된 인덱스를 만들고 쿼리 저장소를 사용하여 쿼리 성능을 확인합니다.Create the missing index and check the query performance using the Query Store.

    query-store-show-planquery-store-show-plan

    작업을 SQL 데이터베이스SQL Database에서 실행하는 경우 인덱스 권장 사항을 자동으로 받을 수 있도록 SQL 데이터베이스SQL Database 인덱스 관리자에 등록합니다.If you run your workload on SQL 데이터베이스SQL Database, sign up for SQL 데이터베이스SQL Database Index Advisor to automatically receive index recommendations.

  • 경우에 따라 실행 계획에서 예상 및 실제 행 수 간의 차이가 심하게 나타나면 통계 재컴파일을 적용할 수 있습니다.In some cases you may enforce statistic recompilation if you see that the difference between the estimated and the actual number of rows in the execution plan is significant.

  • 문제가 있는 쿼리를 다시 작성합니다.Rewrite problematic queries. 예를 들면 쿼리 매개 변수화를 활용하거나 좀 더 최적의 논리를 구현합니다.For example to take advantages of query parameterization or to implement more optimal logic.

쿼리 저장소에서 쿼리 데이터가 계속 수집되는지 확인 Verify Query Store is Collecting Query Data Continuously

쿼리 저장소에서 작업 모드가 자동으로 변경될 수 있습니다.Query Store can silently change operations mode. 쿼리 저장소의 상태를 정기적으로 모니터링하여 쿼리 저장소가 작동 중인지 확인하고, 예방 가능한 원인으로 인해 오류가 발생하지 않도록 조치를 취해야 합니다.You should regularly monitor the state of the Query Store to ensure that the Query Store is operating, and to take action to avoid failures due to preventable causes. 다음 쿼리를 실행하여 작업 모드를 결정하고 가장 관련성이 높은 매개 변수를 확인합니다.Execute the following query to determine the operation mode and view the most relevant parameters:

USE [QueryStoreDB];  
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

actual_state_descdesired_state_desc 간 차이는 작업 모드 변경이 자동으로 발생했음을 나타냅니다.The difference between the actual_state_desc and desired_state_desc indicates that a change of operations mode occurred automatically. 가장 일반적인 변경은 쿼리 저장소가 읽기 전용 모드로 자동 전환되는 경우입니다.The most common change is for the Query Store to silently switch to read-only mode. 내부 오류로 인해 쿼리 저장소가 오류 상태로 종료될 수도 있지만 아주 드문 경우입니다.In extremely rarely circumstances, Query Store can end up in the ERROR state because of internal errors.

실제 상태가 읽기 전용인 경우 readonly_reason 열을 사용하여 원인을 파악합니다.When the actual state is read-only, use the readonly_reason column to determine the root cause. 일반적으로 크기 할당량 초과로 인해 쿼리 저장소가 읽기 전용 모드로 전환되었음을 알 수 있습니다.Typically you will find that Query Store transitioned to read-only mode because the size quota was exceeded. 이 경우 readonly_reason은 65536으로 설정됩니다.In that case the readonly_reason is set to 65536. 다른 이유를 보려면 sys.database_query_store_options(Transact-SQL)를 참조하세요.For other reasons, see sys.database_query_store_options (Transact-SQL).

다음 단계를 수행하여 쿼리 저장소를 읽기-쓰기 모드로 전환하고 데이터 수집을 활성화해 보세요.Consider the following steps to switch Query Store to read-write mode and activate data collection:

  • ALTER DATABASEMAX_STORAGE_SIZE_MB옵션을 사용하여 최대 저장소 크기를 늘립니다.Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.

  • 다음 문을 사용하여 쿼리 저장소 데이터를 정리합니다.Clean up Query Store data by using the following statement:

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;  
    

    명시적으로 작업 모드를 읽기-쓰기로 변경하는 다음 문을 실행하여 위 단계 중 하나 또는 모두를 적용할 수 있습니다.You can apply one or both of these steps by the executing the following statement that explicitly changes operation mode back to read-write:

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

사전 조치를 위해 다음 단계를 수행합니다.Take the following steps to be proactive:

  • 모범 사례를 적용하여 작동 모드의 자동 변동을 방지할 수 있습니다.You can prevent silent changes of operation mode by applying best practices. 쿼리 저장소 크기가 항상 최대 허용된 값 이하인지 확인하면 읽기-쓰기 모드로 전환될 기회가 급격히 줄어듭니다.If you ensure that Query Store size is always below the maximally allowed value that will dramatically reduce a chance of transitioning to read-only mode. 크기가 한도에 도달할 때 쿼리 저장소에서 데이터를 자동으로 정리할 수 있도록 쿼리 저장소 구성 섹션에 설명된 대로 크기 기반 정책을 활성화합니다.Activate size based policy as described in the Configure Query Store section, so that the Query Store automatically cleans data when the size approaches the limit.

  • 가장 최근의 데이터가 보존되었는지 확인하려면 오래된 정보를 정기적으로 제거하도록 시간 기반 정책을 구성합니다.In order to make sure that most recent data is retained, configure time-based policy to remove stale information regularly.

  • 마지막으로 쿼리 캡처 모드를 자동으로 설정 옵션을 설정해 보세요. 이렇게 하면 주로 작업과 관련성이 낮은 쿼리가 필터링을 통해 제외됩니다.Finally, you should consider setting Query Capture Mode to Auto as it filters out queries that are usually less relevant for your workload.

오류 상태Error State

쿼리 저장소를 복구하려면 명시적으로 읽기-쓰기 모드를 설정해 보고 실제 상태를 다시 확인합니다.To recover Query Store try explicitly setting the read-write mode and check actual state again.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

문제가 지속되면 쿼리 저장소 데이터의 손상이 디스크에서 지속되고 있음을 나타냅니다.If the problem persists, it indicates corruption of the Query Store data is persisted on the disk.

영향을 받는 데이터베이스 내에서 sp_query_store_consistency_check 저장 프로시저를 사용하여 쿼리 저장소를 복구할 수 있습니다.Query Store could be recovered by executing sp_query_store_consistency_check stored procedure within the affected database.

그래도 해결되지 않으면 읽기-쓰기 모드를 요청하기 전에 쿼리 저장소를 정리할 수 있습니다.If that didn't help, you can try to clear Query Store before requesting read-write mode.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE CLEAR;  
GO  

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

최적의 쿼리 캡처 모드 설정Set the Optimal Query Capture Mode

쿼리 저장소에 가장 관련성이 높은 데이터를 보관합니다.Keep the most relevant data in Query Store. 다음 표에서는 각 쿼리 캡처 모드에 대한 일반적인 시나리오를 설명합니다.The following table describes typical scenarios for each Query Capture Mode:

쿼리 캡처 모드Query Capture Mode 시나리오Scenario
AllAll 모든 쿼리 셰이프 및 해당 실행 빈도 관점에서 작업과 기타 통계를 철저하게 분석합니다.Analyze your workload thoroughly in terms of all queries shapes and their execution frequencies and other statistics.

작업에서 새 쿼리를 식별합니다.Identify new queries in your workload.

사용자나 자동 매개 변수화에 대한 기회를 식별하는 데 임시 쿼리를 사용하는지 검색합니다.Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.
AutoAuto 관련성이 높고 조치가 가능한 쿼리에 주목합니다. 정기적으로 실행되거나 리소스 사용이 상당히 많은 쿼리가 여기 해당합니다.Focus your attention on relevant and actionable queries; those queries that execute regularly or that have significant resource consumption.
NoneNone 런타임 시 모니터링하고 다른 쿼리에서 발생할 수 있는 방해 요소를 제거할 쿼리 집합을 이미 캡처했습니다.You have already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries may introduce.

이 모드는 환경 테스트 및 벤치마킹에 적합합니다.None is suitable for testing and bench-marking environments.

또한 응용 프로그램 작업을 모니터링하도록 구성된 쿼리 저장소 구성을 제공하는 소프트웨어 공급업체에게도 적절합니다.None is also appropriate for software vendors who ship Query Store configuration configured to monitor their application workload.

이 모드는 주의해서 사용해야 합니다. 중요한 새 쿼리를 추적하고 최적화할 기회를 놓칠 수 있기 때문입니다.None should be used with caution as you might miss the opportunity to track and optimize important new queries. 필요한 특정 시나리오가 없으면 사용하지 마세요.Avoid using None unless you have a specific scenario that requires it.

쿼리 저장소에 가장 관련성이 높은 데이터 보관Keep the Most Relevant Data in Query Store

관련된 데이터만 포함하도록 쿼리 저장소를 구성하면 정기 작업에 미치는 영향을 최소화하면서 유용한 문제 해결 경험을 제공하면서 계속 실행됩니다.Configure the Query Store to contain only the relevant data and it will run continuously providing great troubleshooting experience with a minimal impact on your regular workload.
다음 표에서는 모범 사례를 제공합니다.The following table provides best practices:

최선의 구현 방법Best Practice 설정Setting
보관된 기록 데이터를 제한합니다.Limit retained historical data. 자동 정리를 활성화하도록 시간 기준 정책을 구성합니다.Configure time-based policy to activate auto-cleanup.
관련 없는 쿼리를 필터링하여 제외합니다.Filter out non-relevant queries. 쿼리 캡처 모드를 자동으로 구성합니다.Configure Query Capture Mode to Auto.
최대 크기에 도달하면 관련성이 적은 쿼리를 삭제합니다.Delete less relevant queries when maximum size is reached. 크기 기반 정리 정책을 활성화합니다.Activate size-based cleanup policy.

매개 변수화되지 않은 쿼리 사용 방지 Avoid using non-parameterized queries

반드시 필요한 경우가 아니면 매개 변수화되지 않은 쿼리를 사용하는 것은 좋은 방법이 아닙니다(예: 임시 분석).Using non-parameterized queries when that is not absolutely necessary (for example in case of ad-hoc analysis) is not a best practice. 쿼리 최적화 프로그램에서 고유한 쿼리 텍스트 모두에 대해 쿼리를 컴파일하도록 강제로 캐시된 계획은 다시 사용할 수 없습니다.Cached plans cannot be reused which forces Query Optimizer to compile queries for every unique query text. 이 항목에 대한 자세한 내용은 강제 매개 변수화 사용 지침을 참조하세요.For more information on this topic, see Guidelines for Using Forced Parameterization.
또한 잠재적으로 서로 다른 쿼리 텍스트가 많고 결과적으로 셰이프가 비슷한 서로 다른 실행 계획이 많아져 쿼리 저장소가 갑자기 크기 할당량을 초과할 수도 있습니다.Also, Query Store can rapidly exceed the size quota because of potentially a large number of different query texts and consequently a large number of different execution plans with similar shape.
결과적으로 작업 성능이 최적이 아닌 상태가 되고 쿼리 저장소가 읽기 전용 모드로 전환되거나 쿼리 저장소에서 들어오는 쿼리 추적을 위해 지속적으로 데이터를 삭제할 수도 있습니다.As a result, performance of your workload will be sub-optimal and Query Store might switch to read-only mode or might be constantly deleting the data trying to keep up with the incoming queries.

다음 옵션을 고려해야 합니다.Consider following options:

  • 해당되는 경우 쿼리를 매개 변수화합니다. 예를 들면 저장 프로시저 또는 sp_executesql 내부로 쿼리를 래핑합니다.Parameterize queries where applicable, for example wrap queries inside a stored procedure or sp_executesql. 이 항목에 대한 자세한 내용은 매개 변수 및 실행 계획 재사용을 참조하세요.For more information on this topic, see Parameters and Execution Plan Reuse.
  • 작업에 여러 쿼리 계획이 있는 일회용 임시 배치가 많이 포함된 경우 임시 작업을 위해 최적화 옵션을 사용합니다.Use the Optimize for Ad Hoc Workloads option if your workload contains many single use ad-hoc batches with different query plans.

    • 고유한 query_hash 값의 수를 sys.query_store_query에 있는 총 항목 수와 비교합니다.Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. 비율이 1에 가까우면 임시 작업에서는 다른 쿼리를 생성합니다.If the ratio is close to 1 your ad-hoc workload generates different queries.
  • 다른 쿼리 계획 수가 크지 않으면 데이터베이스 또는 쿼리의 하위 집합에 대해 강제 매개 변수화를 적용합니다.Apply forced parameterization, for the database or for a subset of queries if the number of different query plans is not large.

    • 선택한 쿼리에 대해서만 매개 변수화를 강제로 실행하려면 계획 지침을 사용합니다.Use plan guide to force parameterization only for the selected query.

    • 작업에 서로 다른 쿼리 계획의 수가 적은 경우(예: 고유한 query_hash의 수와 sys.query_store_query에 있는 총 항목 수 사이의 비율이 1 미만일 때) PARAMETERIZATION 데이터베이스 옵션 명령을 사용하도록 강제 매개 변수화를 구성합니다.Configure forced parameterization as using the Parameterization database option command, if there are a small number of different query plans in your workload: when the ratio between the count of distinct query_hash and the total number of entries in sys.query_store_query is much less than 1.

  • 쿼리 캡처 모드 를 AUTO로 설정하여 리소스 사용이 작은 임시 쿼리를 자동으로 필터링하여 제외합니다.Set the Query Capture Mode to AUTO to automatically filter out ad-hoc queries with small resource consumption.

쿼리에 대해 포함하는 개체를 유지 관리할 경우 DROP 또는 CREATE 패턴 방지 Avoid a DROP and CREATE pattern when maintaining containing objects for the queries

쿼리 저장소는 쿼리 항목을 포함하는 개체(저장 프로시저, 함수 및 트리거)에 연결합니다.Query Store associates query entry with a containing object (stored procedure, function, and trigger). 포함하는 개체를 다시 만들면 같은 쿼리 텍스트에 대해 새 쿼리 항목이 생성됩니다.When you recreate a containing object, a new query entry will be generated for the same query text. 시간이 흐름에 따라 해당 쿼리에 대한 성능 통계를 추적하는 작업이 방지되고 강제 적용 메커니즘이 사용됩니다.This will prevent you from tracking performance statistics for that query over time and use plan forcing mechanism. 이 문제를 방지하려면 ALTER <object> 프로세스를 사용하여 가능할 때마다 포함하는 개체 정의를 변경합니다.To avoid this, use the ALTER <object> process to change a containing object definition whenever it is possible.

강제 계획의 상태를 정기적으로 확인 Check the status of Forced Plans regularly

강제 계획은 중요한 쿼리 성능을 수정하고 쿼리를 좀 더 예측 가능하게 하는 편리한 메커니즘입니다.Plan forcing is a convenient mechanism to fix performance for the critical queries and make them more predictable. 그러나 계획 힌트 및 계획 가이드와 마찬가지로 강제 계획은 이후 실행에 사용됨을 보장하지는 않습니다.However, as with plan hints and plan guides, forcing a plan is not a guarantee that it will be used in future executions. 일반적으로 실행 계획에서 참조하는 개체가 변경되거나 삭제되는 방식으로 데이터베이스 스키마가 변경하는 경우 강제 계획이 실패하기 시작합니다.Typically, when database schema changes in a way that objects referenced by the execution plan are altered or dropped, plan forcing will start failing. 이 경우 실제 강제 실패 이유가 sys.query_store_plan에 표시되는 동안 SQL ServerSQL Server에서는 쿼리 재컴파일로 대체합니다.In that case SQL ServerSQL Server falls back to query recompilation while the actual forcing failure reason is surfaced in sys.query_store_plan. 다음 쿼리는 강제 계획에 대한 정보를 반환합니다.The following query returns information about forced plans:

USE [QueryStoreDB];  
GO  

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,  
    force_failure_count, last_force_failure_reason_desc  
FROM sys.query_store_plan AS p  
JOIN sys.query_store_query AS q on p.query_id = q.query_id  
WHERE is_forced_plan = 1;  

전체 이유 목록을 보려면 sys.query_store_plan을 참조하세요.For full list of reasons, refer to sys.query_store_plan. query_store_plan_forcing_failed XEvent를 사용하여 문제 해결 강제 오류를 추적할 수 있습니다.You can also use the query_store_plan_forcing_failed XEvent to track troubleshoot plan forcing failures.

강제 계획이 포함된 쿼리가 있을 경우 데이터베이스 이름 변경 방지 Avoid renaming databases if you have queries with Forced Plans

실행 계획에서는 세 부분으로 된 이름을 참조합니다(database.schema.object).Execution plans reference objects using three-part names database.schema.object.

데이터베이스의 이름을 바꾸면 계획 강제 적용에 실패하여 모든 후속 쿼리 실행 시 다시 컴파일됩니다.If you rename a database, plan forcing will fail which will cause recompilation in all subsequent query executions.

관련 항목:See Also

쿼리 저장소 카탈로그 뷰(Transact-SQL) Query Store Catalog Views (Transact-SQL)
쿼리 저장소 저장 프로시저(Transact-SQL) Query Store Stored Procedures (Transact-SQL)
메모리 내 OLTP와 쿼리 저장소 사용 Using the Query Store with In-Memory OLTP
관련된 뷰, 함수 및 프로시저 Monitoring Performance By Using the Query Store
쿼리 처리 아키텍처 가이드Query Processing Architecture Guide