쿼리 저장소를 사용하여 성능 모니터링Monitoring performance by using 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

SQL ServerSQL Server 쿼리 저장소 기능을 통해 사용자는 쿼리 계획 선택 및 성능에 대한 정보를 얻을 수 있습니다.The SQL ServerSQL Server Query Store feature provides you with insight on query plan choice and performance. 쿼리 계획 변경으로 인해 발생하는 성능 차이를 신속하게 찾을 수 있도록 하여 성능 문제 해결을 간소화합니다.It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. 쿼리 저장소는 쿼리, 계획 및 런타임 통계의 기록을 자동으로 캡처하고 사용자 검토를 위해 보관합니다.Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. 데이터를 기간별로 구분하여 데이터베이스 사용 패턴을 파악하고 서버에서 쿼리 계획 변경이 발생한 시기를 이해할 수 있게 해줍니다.It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. 쿼리 저장소는 ALTER DATABASE SET 옵션을 사용하여 구성할 수 있습니다.You can configure query store using the ALTER DATABASE SET option.

Azure SQL 데이터베이스의 쿼리 저장소 작업에 대한 자세한 내용은 Azure SQL 데이터베이스에서 쿼리 저장소 작업을 참조하세요.For information about operating the Query Store in Azure SQL Database, see Operating the Query Store in Azure SQL Database.

쿼리 저장소 사용 Enabling the Query Store

새 데이터베이스에서는 기본적으로 쿼리 저장소가 활성 상태가 아닙니다.Query Store is not active for new databases by default.

Management Studio에서 쿼리 저장소 페이지 사용Use the Query Store Page in Management Studio

  1. 개체 탐색기에서 데이터베이스를 마우스 오른쪽 단추로 클릭한 다음 속성을 클릭합니다.In Object Explorer, right-click a database, and then click Properties.

    참고

    최소한 SQL Server 2016SQL Server 2016Management StudioManagement Studio버전이 필요합니다.Requires at least SQL Server 2016SQL Server 2016 version of Management StudioManagement Studio.

  2. 데이터베이스 속성 대화 상자에서 쿼리 저장소 페이지를 선택합니다.In the Database Properties dialog box, select the Query Store page.

  3. 작업 모드(요청) 상자에서 On을 선택합니다.In the Operation Mode (Requested) box, select On.

Transact-SQL 문 사용Use Transact-SQL Statements

  1. ALTER DATABASE 문을 사용하여 쿼리 저장소를 사용하도록 설정합니다.Use the ALTER DATABASE statement to enable the query store. 예를 들어For example:

    ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;  
    

    쿼리 저장소와 관련된 구문 옵션에 대한 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.For more syntax options related to the query store, see ALTER DATABASE SET Options (Transact-SQL).

참고

마스터 또는 tempdb 데이터베이스에 대해서는 쿼리 저장소를 사용하도록 설정할 수 없습니다.You cannot enable the query store for the master or tempdb database.

쿼리 저장소에 있는 정보 Information in the Query Store

SQL ServerSQL Server에서 특정 쿼리에 대한 실행 계획은 일반적으로 통계 변경, 스키마 변경, 인덱스 생성/삭제 등과 같은 여러 이유로 인해 시간에 따라 변경됩니다. 프로시저 캐시(캐시된 쿼리 계획이 저장되는 위치)에는 최신 실행 계획만 저장됩니다.Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. 또한 메모리 부족으로 인해 계획 캐시에서 계획이 제거됩니다.Plans also get evicted from the plan cache due to memory pressure. 따라서 실행 계획 변경으로 인한 쿼리 성능 저하는 간단한 문제가 아니며 해결하는 데 시간이 걸릴 수 있습니다.As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

쿼리 저장소에서는 쿼리당 여러 실행 계획을 유지하므로 쿼리 프로세서가 쿼리에 대해 특정 실행 계획을 사용하도록 하는 정책을 적용할 수 있습니다.Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query. 이를 계획 강제 적용이라고 합니다.This is referred to as plan forcing. 쿼리 저장소의 계획 강제 적용은 USE PLAN 쿼리 힌트와 유사한 메커니즘을 사용하여 제공되지만 사용자 응용 프로그램을 변경할 필요는 없습니다.Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. 계획 강제 적용은 계획 변경으로 인한 쿼리 성능 저하를 짧은 시간 내에 해결할 수 있습니다.Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

대기 통계는 SQL Server의 성능 문제 해결에 도움이 되는 다른 정보 소스입니다.Wait stats are another source of information that helps to troubleshoot performance in SQL Server. 오랫동안 인스턴스 수준에서만 대기 통계를 사용할 수 있었기 때문에 실제 쿼리로 역추적하기 어려웠습니다.For a long time, wait statistics were available only on instance level, which made it hard to backtrack it to the actual query. SQL Server 2017 및 Azure SQL Database에서는 쿼리 저장소에 대기 통계를 추적하는 다른 차원이 추가되었습니다.In SQL Server 2017 and Azure SQL Database we added another dimension in Query Store that tracks wait stats.

쿼리 저장소 기능을 사용하는 일반적인 시나리오는 다음과 같습니다.Common scenarios for using the Query Store feature are:

  • 이전 쿼리 계획을 적용하여 계획 성능 저하를 빠르게 찾고 해결합니다.Quickly find and fix a plan performance regression by forcing the previous query plan. 실행 계획 변경으로 최근에 성능이 저하된 쿼리를 수정합니다.Fix queries that have recently regressed in performance due to execution plan changes.

  • 지정된 기간 내에 쿼리가 실행된 횟수를 확인하여 DBA의 성능 리소스 문제 해결을 지원합니다.Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.

  • 실행 시간, 메모리 사용 등을 기준으로 이전 n 시간 동안의 상위 n 개 쿼리를 식별합니다.Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.

  • 지정된 쿼리에 대한 쿼리 계획의 기록을 감사합니다.Audit the history of query plans for a given query.

  • 특정 데이터베이스에 대한 리소스(CPU, I/O 및 메모리) 사용 패턴을 분석합니다.Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.

  • 리소스에서 대기 중인 상위 n개 쿼리를 식별합니다.Identify top n queries that are waiting on resources.
  • 특정 쿼리 또는 계획에 대한 대기 특성을 이해합니다.Understand wait nature for a particular query or plan.

쿼리 저장소에는 다음 3개의 저장소가 있습니다.The query store contains three stores:

  • 계획 저장소 - 실행 계획 정보 유지a plan store for persisting the execution plan information
  • 런타임 통계 저장소 - 실행 통계 정보 유지a runtime stats store for persisting the execution statistics information.
  • 대기 통계 저장소 - 대기 통계 정보 유지a wait stats store for persisting wait statistics information.

    쿼리 저장소에서 쿼리에 대해 저장할 수 있는 고유한 계획의 수는 max_plans_per_query 구성 옵션으로 제한됩니다.The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. 성능 향상을 위해 두 저장소에 비동기적으로 정보가 기록됩니다.To enhance performance, the information is written to the two stores asynchronously. 공간 사용을 최소화하기 위해 런타임 통계 저장소의 런타임 실행 통계는 고정된 기간 동안 집계됩니다.To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. 이러한 저장소의 정보는 쿼리 저장소 카탈로그 뷰를 쿼리하여 볼 수 있습니다.The information in these stores is visible by querying the query store catalog views.

    다음 쿼리는 쿼리 저장소의 쿼리 및 계획에 대한 정보를 반환합니다.The following query returns information about queries and plans in the query store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*  
FROM sys.query_store_plan AS Pl  
JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id ;  

재발된 쿼리 기능 사용 Use the Regressed Queries Feature

쿼리 저장소를 사용하도록 설정한 후 개체 탐색기 창의 데이터베이스 부분을 새로 고쳐 쿼리 저장소 섹션을 추가합니다.After enabling the query store, refresh the database portion of the Object Explorer pane to add the Query Store section.

개체 탐색기의 쿼리 저장소 트리Query store tree in Object Explorer

재발된 쿼리 를 선택하여 에서 재발된 쿼리 Management StudioManagement Studio창을 엽니다.Select Regressed Queries to open the Regressed Queries pane in Management StudioManagement Studio. 재발된 쿼리 창에는 쿼리 저장소의 쿼리 및 계획이 표시됩니다.The Regressed Queries pane shows you the queries and plans in the query store. 위쪽의 드롭다운 상자를 사용하여 다양한 기준에 따라 쿼리를 선택할 수 있습니다.Use the drop down boxes at the top to select queries based on various criteria. 계획을 선택하면 그래픽 쿼리 계획이 표시됩니다.Select a plan to see the graphical query plan. 단추를 사용하여 원본 쿼리를 보고, 쿼리 계획을 강제 적용 및 적용 취소하고 표시를 새로 고칠 수 있습니다.Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

개체 탐색기에서 이전 상태로 되돌려진 쿼리Regressed queries in object explorer

계획을 강제 적용하려면 쿼리 및 계획을 선택한 다음 계획 강제 적용을 클릭합니다.To force a plan, select a query and plan, and then click Force Plan. 쿼리 계획 기능으로 저장하고 쿼리 계획 캐시에 아직 보존되어 있는 계획만 강제 적용할 수 있습니다.You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

대기 쿼리 찾기 Finding wait queries

SQL Server 2017 CTP 2.0부터 및 Azure SQL Database에서 쿼리 저장소 고객에 대한 시간별 쿼리당 대기 통계를 확인할 수 있습니다.Starting from SQL Server 2017 CTP 2.0 and on Azure SQL Database wait statistics per query over time are available for Query Store customers. 쿼리 저장소에서 대기 유형이 대기 범주에 결합됩니다.In Query Store wait types are combined into wait categories. 전체 매핑은 sys.query_store_wait_stats(Transact-SQL)에서 확인할 수 있습니다.Full mapping is available here sys.query_store_wait_stats (Transact-SQL)

대기 범주는 다양한 대기 유형을 본질적으로 유사한 버킷으로 결합합니다.Wait categories are combining different wait types into buckets similar by nature. 대기 범주마다 문제 해결을 위해 다른 후속 분석이 필요하지만 동일한 범주의 대기 유형은 매우 유사한 문제 해결 경험을 생성하므로 대기를 기반으로 해서 영향을 받는 쿼리를 제공하면 대부분의 조사를 성공적으로 완료할 수 있습니다.Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

쿼리 저장소에 대기 범주를 도입하기 전후에 워크로드에 추가 정보를 가져오는 방법의 몇 가지 예는 다음과 같습니다.Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

이전 경험Previous experience 새로운 경험New experience 작업Action
데이터베이스당 높은 RESOURCE_SEMAPHORE 대기High RESOURCE_SEMAPHORE waits per database 특정 쿼리에 대한 쿼리 저장소의 높은 메모리 대기High Memory waits in Query Store for specific queries 쿼리 저장소에서 상위 메모리 소비 쿼리를 찾습니다.Find the top memory consuming queries in Query Store. 이러한 쿼리는 영향을 받는 쿼리의 추가 진행을 지연합니다.These queries are probably delaying further progress of the affected queries. 해당 쿼리 또는 영향을 받는 쿼리에 대해 MAX_GRANT_PERCENT 쿼리 힌트를 사용하는 것이 좋습니다.Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
데이터베이스당 높은 LCK_M_X 대기High LCK_M_X waits per database 특정 쿼리에 대한 쿼리 저장소의 높은 잠금 대기High Lock waits in Query Store for specific queries 영향을 받는 쿼리에 대한 쿼리 텍스트를 확인하고 대상 엔터티를 식별합니다.Check the query texts for the affected queries and identify the target entities. 자주 실행되며 기간이 높은, 동일한 엔터티를 수정하는 다른 쿼리를 쿼리 저장소에서 찾습니다.Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. 이러한 쿼리를 식별한 후 응용 프로그램 논리를 변경하여 동시성을 개선하거나 덜 제한적인 격리 수준을 사용하는 것이 좋습니다.After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
데이터베이스당 높은 PAGEIOLATCH_SH 대기High PAGEIOLATCH_SH waits per database 특정 쿼리에 대한 쿼리 저장소의 높은 버퍼 IO 대기High Buffer IO waits in Query Store for specific queries 쿼리 저장소에서 물리적 읽기 수가 높은 쿼리를 찾습니다.Find the queries with a high number of physical reads in Query Store. IO 대기가 높은 쿼리와 일치하는 경우 검색 대신 찾기를 수행하여 쿼리의 IO 오버헤드를 최소화하기 위해 기본 엔터티에 대한 인덱스를 도입하는 것이 좋습니다.If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
데이터베이스당 높은 SOS_SCHEDULER_YIELD 대기High SOS_SCHEDULER_YIELD waits per database 특정 쿼리에 대한 쿼리 저장소의 높은 CPU 대기High CPU waits in Query Store for specific queries 쿼리 저장소에서 상위 CPU 소비 쿼리를 찾습니다.Find the top CPU consuming queries in Query Store. 그중에서 높은 CPU 추세가 영향을 받는 쿼리에 대한 높은 CPU 대기와 상호 연결하는 쿼리를 식별합니다.Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. 이러한 쿼리 최적화에 집중하세요. 계획 회귀 또는 누락된 인덱스가 있을 수 있습니다.Focus on optimizing those queries – there could be a plan regression, or perhaps a missing index.

구성 옵션 Configuration Options

다음 옵션을 사용하여 쿼리 저장소 매개 변수를 구성할 수 있습니다.The following options are available to configure query store parameters.

OPERATION_MODE
READ_WRITE(기본값) 또는 READ_ONLY일 수 있습니다.Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
STALE_QUERY_THRESHOLD_DAYS 인수를 구성하여 쿼리 저장소에 데이터를 보존할 일수를 지정합니다.Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store. 기본값은 30입니다.The default value is 30. SQL 데이터베이스SQL Database Basic 버전의 경우 기본값은 7일입니다.For SQL 데이터베이스SQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDS
쿼리 저장소에 기록된 데이터가 디스크에 유지되는 빈도를 결정합니다.Determines the frequency at which data written to the query store is persisted to disk. 성능 최적화를 위해 쿼리 저장소에서 수집한 데이터는 디스크에 비동기적으로 기록됩니다.To optimize for performance, data collected by the query store is asynchronously written to the disk. 이 비동기 전송이 발생하는 빈도는 DATA_FLUSH_INTERVAL_SECONDS를 통해 구성됩니다.The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. 기본값은 900(15분)입니다.The default value is 900 (15 min).

MAX_STORAGE_SIZE_MB
쿼리 저장소의 최대 크기를 구성합니다.Configures the maximum size of the query store. 쿼리 저장소의 데이터가 MAX_STORAGE_SIZE_MB 제한에 도달하는 경우 쿼리 저장소에서는 자동으로 상태를 읽기/쓰기에서 읽기 전용으로 변경하고 새 데이터 수집을 중지합니다.If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data. 기본값은 100Mb입니다.The default value is 100Mb. SQL 데이터베이스SQL Database Premium Edition의 경우 기본값은 1Gb이고, SQL 데이터베이스SQL Database Basic Edition의 경우 기본값은 10Mb입니다.For SQL 데이터베이스SQL Database Premium edition, default is 1Gb and for SQL 데이터베이스SQL Database Basic edition, default is 10Mb.

INTERVAL_LENGTH_MINUTES
런타임 실행 통계 데이터가 쿼리 저장소로 집계되는 간격을 결정합니다.Determines the time interval at which runtime execution statistics data is aggregated into the query store. 공간 사용을 최적화하기 위해 런타임 통계 저장소의 런타임 실행 통계는 고정된 기간 동안 집계됩니다.To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. 이 고정된 기간은 INTERVAL_LENGTH_MINUTES를 통해 구성됩니다.This fixed time window is configured via INTERVAL_LENGTH_MINUTES. 기본값은 60입니다.The default value is 60.

SIZE_BASED_CLEANUP_MODE
총 데이터 양이 최대 크기에 가까워졌을 때 정리 프로세스를 자동으로 활성화할지의 여부를 제어합니다.Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. AUTO(기본값) 또는 OFF일 수 있습니다.Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODE
쿼리 저장소가 모든 쿼리 또는 실행 횟수 및 리소스 소비량에 따른 관련 쿼리를 캡처하거나 새 쿼리 추가 및 현재 쿼리 추적을 중지할지의 여부를 지정합니다.Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. ALL(모든 쿼리 캡처), AUTO(자주 발생하지 않은 쿼리 및 컴파일/실행 기간이 의미 없는 쿼리 무시) 또는 NONE(새 쿼리 캡처 중지)일 수 있습니다.Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). Azure SQL 데이터베이스에서 AUTO인 동안 SQL Server 2016의 기본값은 ALL입니다.The default value on SQL Server 2016 is ALL, while on Azure SQL Database is AUTO.

MAX_PLANS_PER_QUERY
각 쿼리에 대하여 유지되는 계획의 수를 나타내는 정수입니다.An integer representing the maximum number of plans maintained for each query. 기본값은 200입니다.The default value is 200.

WAIT_STATS_CAPTURE_MODE
쿼리 저장소에서 대기 통계 정보를 캡처할지 여부를 제어합니다.Controls if Query Store captures wait statistics information. OFF = 0 또는 ON = 1(기본값)일 수 있습니다.Can be OFF = 0 or ON = 1 (default)

sys.database_query_store_options 뷰를 쿼리하여 쿼리 저장소의 현재 옵션을 확인할 수 있습니다.Query the sys.database_query_store_options view to determine the current options of the query store. 값에 대한 자세한 내용은 sys.database_query_store_options를 참조하세요.For more information about the values, see sys.database_query_store_options.

Transact-SQLTransact-SQL 문을 사용하여 옵션을 설정하는 방법에 대한 자세한 내용은 옵션 관리를 참조하세요.For more information about setting options by using Transact-SQLTransact-SQL statements, see Option Management.

Management StudioManagement Studio 을(를) 통해 또는 다음 뷰 및 프로시저를 사용하여 쿼리 저장소를 보고 관리할 수 있습니다.View and manage Query Storethrough Management StudioManagement Studio or by using the following views and procedures.

sys.fn_stmt_sql_handle_from_sql_stmt(Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

쿼리 저장소 카탈로그 뷰Query Store Catalog Views

카탈로그 뷰에 쿼리 저장소에 대한 정보가 표시됩니다.Catalog views present information about the Query Store.

sys.database_query_store_options(Transact-SQL)sys.database_query_store_options (Transact-SQL) sys.query_context_settings(Transact-SQL)sys.query_context_settings (Transact-SQL)
sys.query_store_plan(Transact-SQL)sys.query_store_plan (Transact-SQL) sys.query_store_query(Transact-SQL)sys.query_store_query (Transact-SQL)
sys.query_store_query_text(Transact-SQL)sys.query_store_query_text (Transact-SQL) sys.query_store_runtime_stats(Transact-SQL)sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats(Transact-SQL)sys.query_store_wait_stats (Transact-SQL) sys.query_store_runtime_stats_interval(Transact-SQL)sys.query_store_runtime_stats_interval (Transact-SQL)

쿼리 저장소 저장 프로시저Query Store Stored Procedures

저장 프로시저로 쿼리 저장소를 구성합니다.Stored procedures configure the Query Store.

sp_query_store_flush_db(Transact-SQL)sp_query_store_flush_db (Transact-SQL) sp_query_store_reset_exec_stats(Transact-SQL)sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan(Transact-SQL)sp_query_store_force_plan (Transact-SQL) sp_query_store_unforce_plan(Transact-SQL)sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan(Transct-SQL)sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_query(Transact-SQL)sp_query_store_remove_query (Transact-SQL)

주요 사용 시나리오 Key Usage Scenarios

옵션 관리 Option Management

이 섹션에서는 쿼리 저장소 기능 자체를 관리하는 데 대한 지침을 제공합니다.This section provides some guidelines on managing Query Store feature itself.

쿼리 저장소가 현재 활성 상태인가요?Is Query Store currently active?

쿼리 저장소는 데이터를 사용자 데이터베이스 내에 저장하며 이 때문에 쿼리 저장소에는 크기 제한(MAX_STORAGE_SIZE_MB로 구성)이 있습니다.Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). 쿼리 저장소의 데이터가 이 제한에 도달하면 쿼리 저장소는 자동으로 상태를 읽기/쓰기에서 읽기 전용으로 변경하고 새 데이터 수집을 중지합니다.If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

쿼리 저장소가 현재 활성 상태인지, 및 현재 런타임 통계를 수집하는지의 여부를 정하려면 sys.database_query_store_options를 쿼리합니다.Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

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

쿼리 저장소 상태는 actual_state 열에 의해 결정됩니다.Query Store status is determined by actual_state column. 원하는 상태와 다른 경우 readonly_reason 열에서 자세한 정보를 확인할 수 있습니다.If it’s different than the desired status, the readonly_reason column can give you more information.
쿼리 저장소의 크기가 할당량을 초과하면 기능 readon_only 모드로 전환됩니다.When Query Store size exceeds the quota, the feature will switch to readon_only mode.

쿼리 저장소 옵션 가져오기Get Query Store options

쿼리 저장소 상태에 대한 자세한 정보를 찾으려면 사용자 데이터베이스에서 다음을 실행합니다.To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;  

쿼리 저장소 간격 설정Setting Query Store interval

쿼리 런타임 통계를 집계하는 간격(기본값은 60분)을 재정의할 수 있습니다.You can override interval for aggregating query runtime statistics (default is 60 minutes).

ALTER DATABASE <database_name>   
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);  

참고

정수 값은 INTERVAL_LENGTH_MINUTES에 허용되지 않습니다.Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. 1, 5, 10, 15, 30, 60, 1440분 중 하나를 사용할 수 있습니다.Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

간격에 대한 새 값은 sys.database_query_store_options 뷰를 통해 노출됩니다.New value for interval is exposed through sys.database_query_store_options view.

쿼리 저장소 공간 사용량Query Store space usage

현재 쿼리 저장소 크기를 확인하려면 데이터베이스에서 다음 문의 실행을 제한합니다.To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb   
FROM sys.database_query_store_options;  

쿼리 저장소 저장 공간이 꽉 차는 경우 다음 문을 사용하여 저장소를 확장합니다.If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>   
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);  

모든 쿼리 저장소 옵션 설정Set all Query Store options

단일 ALTER DATABASE 문 사용하여 여러 쿼리 저장소 옵션을 한 번에 설정할 수 있습니다.You can set multiple Query Store options at once with a single ALTER DATABASE statement.

ALTER DATABASE <database name>   
SET QUERY_STORE (  
    OPERATION_MODE = READ_WRITE,  
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),  
    DATA_FLUSH_INTERVAL_SECONDS = 3000,  
    MAX_STORAGE_SIZE_MB = 500,  
    INTERVAL_LENGTH_MINUTES = 15,  
    SIZE_BASED_CLEANUP_MODE = AUTO,  
    QUERY_CAPTURE_MODE = AUTO,  
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON 
);  

공간 정리Cleaning up the space

데이터베이스 생성 중 쿼리 저장소 내부 테이블이 PRIMARY 파일 그룹에 만들어지며 해당 구성은 나중에 변경할 수 없습니다.Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. 공간이 부족한 경우 다음 문을 사용하여 이전 쿼리 저장소 데이터를 지울 수 있습니다.If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;  

또는 임시 쿼리 데이터는 쿼리 최적화 및 계획 분석과 관련성이 적고 공간만 많이 차지하므로 임시 쿼리 데이터만 지울 수도 있습니다.Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

임시 쿼리 삭제 한번만 실행되고 24시간 이상 지난 쿼리를 삭제합니다.Delete ad-hoc queries This deletes the queries that were only executed only once and that are more than 24 hours old.

DECLARE @id int  
DECLARE adhoc_queries_cursor CURSOR   
FOR   
SELECT q.query_id  
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q   
    ON q.query_text_id = qt.query_text_id  
JOIN sys.query_store_plan AS p   
    ON p.query_id = q.query_id  
JOIN sys.query_store_runtime_stats AS rs   
    ON rs.plan_id = p.plan_id  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  
ORDER BY q.query_id ;  

OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @id  
        EXEC sp_query_store_remove_query @id  
        FETCH NEXT FROM adhoc_queries_cursor INTO @id  
    END   
CLOSE adhoc_queries_cursor ;  
DEALLOCATE adhoc_queries_cursor;  

더 이상 중요하지 않은 데이터를 지우는 다른 논리를 사용하여 프로시저를 직접 정의할 수 있습니다.You can define your own procedure with different logic for clearing up data you no longer want.

위의 예제에서는 필요 없는 데이터를 제거하는 sp_query_store_remove_query 확장 저장 프로시저를 사용합니다.The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. 또한, 다음을 사용 가능:You can also use:

  • sp_query_store_reset_exec_stats – 지정된 계획에 대한 런타임 통계를 지웁니다.sp_query_store_reset_exec_stats – to clear runtime statistics for a given plan.

  • sp_query_store_remove_plan – 단일 계획을 제거합니다.sp_query_store_remove_plan – to remove a single plan.

성능 감사 및 문제해결 Performance Auditing and Troubleshooting

쿼리 저장소는 쿼리 실행 전반에서 컴파일 및 런타임 메트릭 기록을 유지하여 사용자가 작업에 대한 질문을 할 수 있습니다.Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

마지막 n개 쿼리가 데이터베이스에서 실행되었나요?Last n queries executed on the database?

SELECT TOP 10 qt.query_sql_text, q.query_id,   
    qt.query_text_id, p.plan_id, rs.last_execution_time  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
ORDER BY rs.last_execution_time DESC;  

각 쿼리에 대한 실행 수는 몇 개입니까?Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,   
    SUM(rs.count_executions) AS total_execution_count  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text  
ORDER BY total_execution_count DESC;  

지난 시간 내에 평균 실행 시간이 가장 긴 쿼리 수는 몇 개입니까?The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,  
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,   
    rs.last_execution_time   
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())  
ORDER BY rs.avg_duration DESC;  

지난 24시간 동안 평균 물리적 IO 읽기가 가장 큰 쿼리 수 및 해당하는 평균 행 수 및 실행 수는 몇 개입니까?The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,   
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,   
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi   
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id  
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())   
ORDER BY rs.avg_physical_io_reads DESC;  

여러 계획을 사용하는 쿼리는 무엇입니까?Queries with multiple plans? 이러한 쿼리는 계획 선택 변경으로 인한 재발을 일으킬 수 있으므로 특히 흥미롭습니다.These queries are especially interesting because they are candidates for regressions due to plan choice change. 다음 쿼리는 이러한 쿼리와 함께 모든 계획을 식별합니다.The following query identifies these queries along with all plans:

WITH Query_MultPlans  
AS  
(  
SELECT COUNT(*) AS cnt, q.query_id   
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id  
JOIN sys.query_store_plan AS p  
    ON p.query_id = q.query_id  
GROUP BY q.query_id  
HAVING COUNT(distinct plan_id) > 1  
)  

SELECT q.query_id, object_name(object_id) AS ContainingObject,   
    query_sql_text, plan_id, p.query_plan AS plan_xml,  
    p.last_compile_start_time, p.last_execution_time  
FROM Query_MultPlans AS qm  
JOIN sys.query_store_query AS q  
    ON qm.query_id = q.query_id  
JOIN sys.query_store_plan AS p  
    ON q.query_id = p.query_id  
JOIN sys.query_store_query_text qt   
    ON qt.query_text_id = q.query_text_id  
ORDER BY query_id, plan_id;  

최근에 성능이 저하된 쿼리(다른 시점과 비교)는 무엇입니까?Queries that recently regressed in performance (comparing different point in time)? 다음 쿼리 예제는 지난 48 시간에 계획 선택 변경으로 인해 실행 시간이 두 배가 된 모든 쿼리를 반환합니다.The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. 쿼리에서는 모든 런타임 통계 간격을 나란히 비교합니다.Query compares all runtime stat intervals side by side.

SELECT   
    qt.query_sql_text,   
    q.query_id,   
    qt.query_text_id,   
    rs1.runtime_stats_id AS runtime_stats_id_1,  
    rsi1.start_time AS interval_1,   
    p1.plan_id AS plan_1,   
    rs1.avg_duration AS avg_duration_1,   
    rs2.avg_duration AS avg_duration_2,  
    p2.plan_id AS plan_2,   
    rsi2.start_time AS interval_2,   
    rs2.runtime_stats_id AS runtime_stats_id_2  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p1   
    ON q.query_id = p1.query_id   
JOIN sys.query_store_runtime_stats AS rs1   
    ON p1.plan_id = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi1   
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan AS p2   
    ON q.query_id = p2.query_id   
JOIN sys.query_store_runtime_stats AS rs2   
    ON p2.plan_id = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi2   
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id  
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())   
    AND rsi2.start_time > rsi1.start_time   
    AND p1.plan_id <> p2.plan_id  
    AND rs2.avg_duration > 2*rs1.avg_duration  
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;  

계획 선택 변경과 관련된 성능 저하뿐 아니라 성능 저하를 모두 확인하려면 이전 쿼리에서 AND p1.plan_id <> p2.plan_id 조건을 제거하면 됩니다.If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

가장 오래 대기 중인 쿼리는 무엇인가요?Queries that are waiting the most? 이 쿼리는 가장 오래 대기 중인 상위 10개의 쿼리를 반환합니다.This query will return top 10 queries that wait the most.

 SELECT TOP 10
   qt.query_text_id,
   q.query_id,
   p.plan_id,
   sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

최근에 성능이 저하된 쿼리(최근 및 기록 실행 비교)는 무엇입니까?Queries that recently regressed in performance (comparing recent vs. history execution)? 다음 쿼리는 실행 기간을 기준으로 쿼리 실행을 비교합니다.The next query compares query execution based periods of execution. 이 특정 예제의 쿼리는 최근 기간(1시간) 및 기록 기간(어제)의 실행을 비교하고 additional_duration_workload를 초래한 쿼리를 식별합니다.In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. 이 메트릭은 최근 평균 실행 기간과 기록 평균 실행 간의 차이에 최근 실행 수를 곱한 값으로 계산됩니다.This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. 이 값은 실제로 기록에 비해 최근 실행으로 초래된 추가 기간을 나타냅니다.It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour  
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = SYSUTCDATETIME();  

--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = SYSUTCDATETIME();  

WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time \<= @history_start_time   
               AND rs.last_execution_time > @history_start_time)  
        OR (rs.first_execution_time \<= @history_end_time   
               AND rs.last_execution_time > @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time \<= @recent_start_time   
               AND rs.last_execution_time > @recent_start_time)  
        OR (rs.first_execution_time \<= @recent_end_time   
               AND rs.last_execution_time > @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE additional_duration_workload > 0  
ORDER BY additional_duration_workload DESC  
OPTION (MERGE JOIN);  

쿼리 성능 안정성 유지 관리 Maintaining Query Performance Stability

여러 번 실행되는 쿼리의 경우 SQL ServerSQL Server 에서 다른 계획을 사용하여 리소스 사용률 및 기간이 달라짐을 알 수 있습니다.For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. 쿼리 저장소를 사용하면 쿼리 성능이 저하되는 시기를 확인하고 관심 있는 기간 내에 최적의 계획을 결정할 수 있습니다.With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. 그런 다음 향후 쿼리 실행에 해당하는 최적의 계획을 적용할 수 있습니다.You can then force that optimal plan for future query execution.

또한 자동으로 매개 변수화되거나 수동으로 매개 변수화되는 매개 변수를 사용하여 일관성이 없는 쿼리 성능도 식별할 수 있습니다.You can also identify inconsistent query performance for a query with parameters (either auto- parameterized or manually parameterized). 여러 계획 중에 대부분의 매개 변수 값에 대해 빠르고 최적화된 계획을 식별하고 해당 계획을 강제 적용할 수 있습니다. 이를 통해 다양한 사용자 시나리오에 대해 예측 가능한 성능을 유지할 수 있습니다.Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

쿼리에 대 한 계획 강제 적용(강제 적용 정책 적용)Force or a plan for a query (apply forcing policy). 특정 쿼리에 계획을 ;강제 적용하는 경우 쿼리를 실행할 때마다 강제 적용된 계획이 실행됩니다.When a plan is forced for a certain query, every time a query comes to execution it will be executed with the plan that is forced.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;  

sp_query_store_force_plan 을 사용할 경우 쿼리 저장소에서 해당 쿼리에 대한 계획으로 기록된 계획만 강제로 적용할 수 있습니다.When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. 즉, 쿼리 저장소가 활성 상태일 때 해당 쿼리를 실행하는 데 이미 사용된 계획만 쿼리에 사용할 수 있습니다.In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

쿼리에 대한 계획 강제 적용 제거Remove plan forcing for a query. 다시 SQL ServerSQL Server 쿼리 최적화 프로그램 사용하여 최적의 쿼리 계획을 계산하려면 sp_query_store_unforce_plan 을 사용하여 쿼리에 대해 선택한 계획의 강제 적용을 취소합니다.To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;  

관련 항목:See Also

쿼리 저장소에 대한 모범 사례 Best Practice with the Query Store
메모리 내 OLTP와 쿼리 저장소 사용 Using the Query Store with In-Memory OLTP
쿼리 저장소 사용 시나리오 Query Store Usage Scenarios
쿼리 저장소에서 데이터를 수집하는 방법 How Query Store Collects Data
쿼리 저장소 저장 프로시저(Transact-SQL) Query Store Stored Procedures (Transact-SQL)
쿼리 저장소 카탈로그 뷰(Transact-SQL) Query Store Catalog Views (Transact-SQL)
성능 모니터링 및 튜닝 Monitor and Tune for Performance
성능 모니터링 및 튜닝 도구 Performance Monitoring and Tuning Tools
작업 모니터 열기(SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
활성 쿼리 통계 Live Query Statistics
작업 모니터 Activity Monitor
sys.database_query_store_options(Transact-SQL)sys.database_query_store_options (Transact-SQL)
Azure SQL 데이터베이스에서 쿼리 저장소 작업Operating the Query Store in Azure SQL Database