쿼리 프로파일링 인프라

아티클
07/12/2024

적용 대상: SQL Server Azure SQL Database

SQL Server 데이터베이스 엔진은 쿼리 실행 계획에 대한 런타임 정보에 액세스하는 기능을 제공합니다. 성능 문제가 발생할 경우, 가장 중요한 작업 중 하나는 실행 중인 워크로드와 리소스 사용량을 정확하게 파악하는 것입니다. 이를 위해 실제 실행 계획에 액세스하는 것이 중요합니다.

쿼리 완성은 실제 쿼리 계획의 가용성을 위한 필수 구성 요소이지만, 활성 쿼리 통계는 한 쿼리 계획 연산자에서 다른 쿼리 계획 연산자로 데이터가 흐르기 때문에 쿼리 실행 프로세스에 대한 실시간 인사이트를 제공할 수 있습니다. 활성 쿼리 계획은 전체 쿼리 진행률 및 생성된 행 수, 경과 시간, 연산자 진행률 등과 같은 연산자 수준의 런타임 실행 통계를 표시합니다. 이 데이터는 쿼리가 완료될 때까지 기다릴 필요 없이 실시간으로 사용할 수 있으므로 이 실행 통계는 오래 실행되는 쿼리, 무한하게 실행되는 쿼리, 끝나지 않는 쿼리 등의 쿼리 성능 문제를 디버깅할 때 매우 유용합니다.

표준 쿼리 실행 통계 프로파일링 인프라

실행 계획, 즉 행 수, CPU 및 I/O 사용량에 대한 정보를 수집하려면 쿼리 실행 통계 프로필 인프라 또는 표준 프로파일링을 사용하도록 설정해야 합니다. 대상 세션에 대한 실행 계획 정보를 수집하는 다음 메서드는 표준 프로파일링 인프라를 활용합니다.

참고 항목

SQL Server Management Studio에서 활성 쿼리 통계 포함 버튼을 클릭하면 표준 프로파일링 인프라가 활용됩니다.
더 높은 버전의 SQL Server에서 경량 프로파일링 인프라를 사용하는 경우 활동 모니터를 통해 보거나 sys.dm_exec_query_profiles DMV를 직접 쿼리할 때 표준 프로파일링 대신 라이브 쿼리 통계를 통해 활용됩니다.

모든 세션에 대한 실행 계획 정보를 전역적으로 수집하는 다음 메서드는 표준 프로파일링 인프라를 활용합니다.

query_post_execution_showplan 확장 이벤트. 확장 이벤트를 사용하려면 확장 이벤트를 사용하여 시스템 작업 모니터링을 참조하세요.
SQL 추적 및 SQL Server Profiler의 Showplan XML 추적 이벤트. 이 추적 이벤트에 대한 자세한 내용은 Showplan XML 이벤트 클래스를 참조하세요.

query_post_execution_showplan 이벤트를 사용하는 확장 이벤트 세션을 실행하는 경우 sys.dm_exec_query_profiles DMV도 채워져 활동 모니터를 사용하거나 DMV를 직접 쿼리하여 모든 세션에 대한 라이브 쿼리 통계를 사용할 수 있습니다. 자세한 내용은 Live Query Statistics를 참조하세요.

간단한 쿼리 실행 통계 프로파일링 인프라

SQL Server 2014(12.x) SP2 및 SQL Server 2016(13.x)부터 새로운 경량 쿼리 실행 통계 프로파일링 인프라 또는 경량 프로파일링이 도입되었습니다.

참고 항목

고유하게 컴파일된 저장 프로시저는 간단한 프로파일링에서 지원되지 않습니다.

간단한 쿼리 실행 통계 프로파일링 인프라 v1

적용 대상: SQL Server(SQL Server 2014(12.x) SP2~SQL Server 2016(13.x)).

SQL Server 2014(12.x) SP2 및 SQL Server 2016(13.x)부터 경량 프로파일링이 도입되어 실행 계획에 대한 정보를 수집하는 성능 오버헤드가 감소했습니다. 표준 프로파일링과 달리 경량 프로파일링은 CPU 런타임 정보를 수집하지 않습니다. 하지만 경량 프로파일링은 행 수 및 I/O 사용 정보는 수집합니다.

경량 프로파일링을 활용하는 새로운 query_thread_profile 확장 이벤트도 도입되었습니다. 이 확장 이벤트는 각 노드 및 스레드의 성능에 대한 더 많은 인사이트를 허용하는 연산자별 실행 통계를 노출합니다. 이 확장 이벤트를 사용하는 샘플 세션은 아래 예제와 같이 구성할 수 있습니다.

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

참고 항목

쿼리 프로파일링의 성능 오버헤드에 대한 자세한 내용은 개발자 선택: 쿼리 진행률 블로그 게시물을 참조하세요.

query_thread_profile 이벤트를 사용하는 확장 이벤트 세션을 실행하는 경우, 경량 프로파일링을 사용하여 sys.dm_exec_query_profiles DMV도 채워져 활동 모니터를 사용하거나 DMV를 직접 쿼리하여 모든 세션에 대한 라이브 쿼리 통계를 사용할 수 있습니다.

간단한 쿼리 실행 통계 프로파일링 인프라 v2

적용 대상: SQL Server(SQL Server 2016(13.x) SP1~SQL Server 2017(14.x)).

SQL Server 2016(13.x) SP1에는 오버헤드를 최소화하도록 수정된 버전의 간단한 프로파일링이 포함되어 있습니다. 위의 적용 대상에 명시된 버전에 대해 추적 플래그 7412를 사용하여 경량 프로파일링을 전역적으로 사용하도록 설정할 수도 있습니다. 새 DMF sys.dm_exec_query_statistics_xml은 진행 중인 요청에 대한 쿼리 실행 계획을 반환하기 위해 도입되었습니다.

SQL Server 2016(13.x) SP2 CU3 및 SQL Server 2017(14.x) CU11부터 경량 프로파일링을 전역적으로 사용하도록 설정하지 않은 경우, 어느 세션에서든 쿼리 수준에서 경량 프로파일링을 사용하도록 설정하는 데 새로운 USE HINT query hint 인수 QUERY_PLAN_PROFILE을 사용할 수 있습니다. 이 새 힌트가 포함된 쿼리가 완료되면 query_post_execution_showplan 확장 이벤트와 유사한 실제 실행 계획 XML을 제공하는 새 query_plan_profile 확장 이벤트도 출력됩니다.

참고 항목

query_plan_profile 확장 이벤트는 쿼리 힌트가 사용되지 않더라도 경량 프로파일링을 활용합니다.

query_plan_profile 확장 이벤트를 사용하는 샘플 세션은 아래 예제와 같이 구성할 수 있습니다.

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

간단한 쿼리 실행 통계 프로파일링 인프라 v3

적용 대상: SQL Server(SQL Server 2019 (15.x)부터) 및 Azure SQL Database

SQL Server 2019(15.x) 및 Azure SQL 데이터베이스에는 모든 실행에 대한 행 개수 정보를 수집하는 경량 프로파일링의 새로 수정된 버전이 포함되어 있습니다. SQL Server 2019(15.x) 및 Azure SQL 데이터베이스에는 경량 프로파일링이 기본적으로 사용하도록 설정되어 있습니다. SQL Server 2019(15.x)부터 이 추적 플래그 7412는 영향을 주지 않습니다. LIGHTWEIGHT_QUERY_PROFILING 데이터베이스 범위 구성, ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;를 사용하여 데이터베이스 수준에서 경량 프로파일링을 사용하지 않도록 설정할 수 있습니다.

대부분의 쿼리에 대해 마지막으로 알려진 실제 실행 계획, 즉 마지막 쿼리 계획 통계와 동일한 계획을 반환하는 새 DMF sys.dm_exec_query_plan_stats가 도입되었습니다. 마지막 쿼리 계획 통계는 LAST_QUERY_PLAN_STATS 데이터베이스 범위 구성(ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;)을 사용하여 데이터베이스 수준에서 사용하도록 설정할 수 있습니다.

새 query_post_execution_plan_profile 확장 이벤트는 표준 프로파일링을 사용하는 query_post_execution_showplan과 달리 경량 프로파일링을 기반으로 실제 실행 계획과 동등한 값을 수집합니다. SQL Server 2017(14.x)도 CU14부터 이 이벤트를 제공합니다. query_post_execution_plan_profile 확장 이벤트를 사용하는 샘플 세션은 아래 예제와 같이 구성할 수 있습니다.

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

예제 1 - 표준 프로파일링을 사용하는 확장 이벤트 세션

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

예제 2 - 경량 프로파일링을 사용하는 확장 이벤트 세션

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

쿼리 프로파일링 인프라 사용량 참고 자료

다음 표에는 전역적으로(서버 수준) 또는 단일 세션에서 표준 프로파일링 또는 경량 프로파일링을 사용하도록 설정하는 작업이 요약되어 있습니다. 해당 작업을 사용할 수 있는 가장 오래된 버전도 나와 있습니다.

범위	표준 프로파일링	경량 프로파일링
전역	XE를 사용한 `query_post_execution_showplan` XEvent 세션; SQL Server 2012(11.x)부터	추적 플래그 7412, SQL Server 2016(13.x) SP1부터
전역	`Showplan XML` 추적 이벤트를 사용한 SQL 추적 및 SQL Server Profiler, SQL Server 2000부터	XE를 사용한 `query_thread_profile` XEvent 세션; SQL Server 2014(12.x) SP2부터
전역	-	XE를 사용한 `query_post_execution_plan_profile` XEvent 세션; SQL Server 2017(14.x) CU14 및 SQL Server 2019(15.x)부터
세션	`SET STATISTICS XML ON` 사용, SQL Server 2000부터	XE와 `QUERY_PLAN_PROFILE` 함께 XEvent 세션과 함께 쿼리 힌트를 `query_plan_profile` 사용합니다. SQL Server 2016(13.x) SP2 CU3 및 SQL Server 2017(14.x) CU11부터
세션	`SET STATISTICS PROFILE ON` 사용, SQL Server 2000부터	-
세션	SSMS에서 활성 쿼리 통계 버튼 클릭, SQL Server 2014(12.x) SP2부터	-

설명

Important

sys.dm_exec_query_statistics_xml 참조하는 모니터링 저장 프로시저를 실행하는 동안 임의 액세스 위반이 발생할 수 있으므로 KB 4078596이 SQL Server 2016(13.x) 및 SQL Server 2017(14.x)에 설치되어 있는지 확인합니다.

경량 프로파일링 v2와 낮은 오버헤드부터, 아직 CPU에 바인딩되지 않은 모든 서버는 경량 프로파일링을 지속적으로 실행할 수 있으며, 데이터베이스 전문가는 언제든지 실행 중인 실행을 활용할 수 있습니다. 예를 들어 활동 모니터를 사용하거나 sys.dm_exec_query_profiles를 직접 쿼리하고 런타임 통계가 있는 쿼리 계획을 얻을 수 있습니다.

쿼리 프로파일링의 성능 오버헤드에 대한 자세한 내용은 개발자 선택: 쿼리 진행률 블로그 게시물을 참조하세요.

참고 항목

경량 프로파일링을 활용하는 확장 이벤트는 표준 프로파일링 인프라가 이미 활성화된 경우 표준 프로파일링의 정보를 사용합니다. 예를 들어 query_post_execution_showplan을 사용 중인 확장 이벤트 세션이 실행 중이고, query_post_execution_plan_profile을 사용 중인 다른 세션이 시작됩니다. 두 번째 세션에서는 표준 프로파일링의 정보를 계속 사용합니다.

참고 항목

SQL Server 2017(14.x)에서 경량 프로파일링은 기본적으로 해제되어 있지만 query_post_execution_plan_profile을 사용하는 XEvent 추적이 시작될 때 활성화되며 추적이 중지되면 다시 비활성화됩니다. 따라서 query_post_execution_plan_profile을 기반으로 하는 Xevent 추적이 SQL Server 2017(14.x) 인스턴스에서 자주 시작되고 중지되는 경우 추적 플래그 7412로 전역 수준에서 경량 프로파일링을 활성화하여 반복되는 활성화/비활성화 오버헤드를 방지하는 것이 좋습니다.

참고 항목

성능 모니터링 및 튜닝
성능 모니터링 및 튜닝 도구
작업 모니터 열기(SQL Server Management Studio)
활동 모니터
쿼리 저장소를 사용한 성능 모니터링
확장 이벤트를 사용하여 시스템 작업 모니터링
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
추적 플래그
실행 계획 논리 및 물리 연산자 참조
실제 실행 계획
활성 쿼리 통계

다음을 통해 공유

쿼리 프로파일링 인프라

표준 쿼리 실행 통계 프로파일링 인프라

간단한 쿼리 실행 통계 프로파일링 인프라

간단한 쿼리 실행 통계 프로파일링 인프라 v1

간단한 쿼리 실행 통계 프로파일링 인프라 v2

간단한 쿼리 실행 통계 프로파일링 인프라 v3

예제 1 - 표준 프로파일링을 사용하는 확장 이벤트 세션

예제 2 - 경량 프로파일링을 사용하는 확장 이벤트 세션

쿼리 프로파일링 인프라 사용량 참고 자료

설명

참고 항목

피드백

추가 리소스