SQL Server 2016 Query Store -실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자

SQL Server 2016 Query Store

• 실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자
• Version : SQL Server 2016

 

SQL Server 2016에 도입된 Query Store는 요청된 쿼리가 실행 될 때 실행 된 쿼리를 저장 및 실행 계획 등을 함께 저장하여 통계 및 계획을 추적하는데 도움이 된다.

 

쿼리를 저장하기 위해서는 Query Store를 활성화 해야 한다. SSMS 및 T-SQL을 사용하여 쿼리 스토어 기능을 활성화할 수 있다.

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON GO

 

 

쿼리 스토어를 활성화하면 쿼리 저장과 관련된 다른 옵션을 설정할 수 있다. SSMS 및 T-SQL을 사용하여 설정을 변경할 수 있다.

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE (OPERATION_MODE = READ_ONLY,                 CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),                 DATA_FLUSH_INTERVAL_SECONDS = 900,                 INTERVAL_LENGTH_MINUTES = 60,                 MAX_STORAGE_SIZE_MB = 100,                 QUERY_CAPTURE_MODE = AUTO,                 SIZE_BASED_CLEANUP_MODE = AUTO) GO

 

설정 변경 옵션에 대한 자세한 내용은 MSDN을 참고 한다.

• Monitoring Performance By Using the Query Store

  https://msdn.microsoft.com/en-US/library/dn817826.aspx#Options

 

 

쿼리 스토어 속성 정보에서 현재 쿼리 스토어가 사용하고 있는 디스크 사용량을 확인할 수 있다. [Purge Query Data] 버튼을 사용하여 쿼리 스토어의 내용을 제거하거나 스크립트를 사용하여 제거할 수 있다. (참고로 CTP 버전에서는 sp_query_store_flush_db에 몇 가지 버그가 있다고 한다.)

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL GO

 

EXEC sys.sp_query_store_flush_db

 

 

아래 스크립트는 쿼리 저장 시스템 개체 및 새로운 확장 이벤트를 확인할 수 있다.

SELECT name, type_desc FROM sys.all_objects WHERE name LIKE '%query_store%' or name= 'query_context_settings'

 

 

확장이벤트	설명
query_store_background_task_persist_started	Fired if the background task for Query Store data persistence started execution
query_store_background_task_persist_finished	Fired if the background task for Query Store data persistence is completed successfully
query_store_load_started	Fired when query store load is started
query_store_db_data_structs_not_released	Fired if Query Store data structures are not released when feature is turned OFF.
query_store_db_diagnostics	Periodically fired with Query Store diagnostics on database level.
query_store_db_settings_changed	Fired when Query Store settings are changed.
query_store_db_whitelisting_changed	Fired when Query Store database whitelisting state is changed.
query_store_global_mem_obj_size_kb	Periodically fired with Query Store global memory object size.
query_store_size_retention_cleanup_started	Fired when size retention policy clean-up task is started.
query_store_size_retention_cleanup_finished	Fired when size retention policy clean-up task is finished.
query_store_size_retention_cleanup_skipped	Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
query_store_size_retention_query_deleted	Fired when size based retention policy deletes a query from Query Store.
query_store_size_retention_plan_cost	Fired when eviction cost is calculated for the plan.
query_store_size_retention_query_cost	Fired when query eviction cost is calculated for the query.
query_store_generate_showplan_failure	Fired when Query Store failed to store a query plan because the showplan generation failed.
query_store_capture_policy_evaluate	Fired when the capture policy is evaluated for a query.
query_store_capture_policy_start_capture	Fired when an UNDECIDED query is transitioning to CAPTURED.
query_store_capture_policy_abort_capture	Fired when an UNDECIDED query failed to transition to CAPTURED.
query_store_schema_consistency_check_failure	Fired when the Query Store schema consistency check failed.

 

자세한 내용은 MSDN을 참고 한다.

• Query Store Stored Procedures : https://msdn.microsoft.com/en-us/library/dn818153.aspx
• Query Store Catalog Views : https://msdn.microsoft.com/en-us/library/dn818149.aspx

 

데이터베이스 속성에서 쿼리 스토어를 활성화 하면 SSMS에서 Query Store 컨테이너를 확인할 수 있다.

 

쿼리 저장 컨테이너에서 마우스 오른쪽을 클릭하면 기본으로 제공되는 리포트를 확인할 수 있다.

 

컨테이너를 확장하여 수집된 쿼리 스토어에 대한 정보를 확인할 수 있다.

 

수집된 데이터 중에 Top Resource Consuming Queries 정보를 살펴보자. (대부분 구성은 비슷하다.)

 

왼쪽의 차트는 수직 및 수평축에 대해 선택된 통계를 기반으로 전체 통계를 표시한다. 메트릭(1)과 통계(2)의 드롭다운 버튼으로 통계를 선택할 수 있다.

 

오른쪽 차트는 다른 색상으로 각 계획을 표시한다. 마우스 포인터를 올려놓으면 (3) 개체에 대한 query id 또는 Plan id에 대한 자세한 정보를 참조할 수 있다. 선택된 메트릭에 따라 상세 정보는 다르다.

 

 

오른쪽 차트의 버블 크기(4)는 실행의 총 수에 따라 달라진다. 다른 계획을 클릭하면(3, 4) 창 하단의 부분(5)이 특정 plan id에 대한 실행 계획을 표시한다. (6)은 창 이름을 나타내며 (7), (8), (9)는 현재 리포트 정보를 나타낸다.

 

 

아래 그림은 드롭 다운 메뉴를 살펴 본다. 모니터 해상도가 작을 경우 오른쪽의 일부 메뉴(6)가 숨겨질 수 있다. 숨겨진 메뉴는 확장해서 볼 수 있다.

 

Track Query(1) 버튼은 추적 쿼리 창을 보여준다.

 

View Query(2)는 T-SQL 스크립트로 새로운 SSMS창에 보여준다.

 

 

Detail Grid(3), Grid(4) 버튼은 통계 정보를 제공한다.

 

구성(5) 버튼은 한 곳에서 모든 창을 구성할 수 있다.

 

 

여러 계획 쿼리가 있는 경우 Compare Plans 버튼을 사용하여 왼쪽 차트와 실행계획을 비교해볼 수 있다.

 

강제 계획 버튼을 눌러 해당 계획을 사용할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4009/sql-server-2016-query-store-introduction/

 

 

2015-09-01 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL 2016, SQL Query Store, 쿼리 튜닝, DB튜닝, 쿼리 스토어, 실행계획 비교, 쿼리 모니터링