쿼리 프로파일링 인프라

  • 아티클

적용 대상:SQL ServerAzure SQL Database

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

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

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

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

참고 항목

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

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

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_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 Database에는 모든 실행에 대한 행 개수 정보를 수집하는 경량 프로파일링의 새로 수정된 버전이 포함되어 있습니다. 경량 프로파일링은 SQL Server 2019(15.x) 및 Azure SQL Database에서 기본적으로 사용하도록 설정됩니다. 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);

쿼리 프로파일링 인프라 사용 지침

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

Scope 표준 프로파일링 경량 프로파일링
전역 XE를 사용한 query_post_execution_showplan xEvent 세션 SQL Server 2012(11.x)부터 추적 플래그 7412; SQL Server 2016(13.x) SP1부터
전역 추적 이벤트가 있는 Showplan XML SQL 추적 및 SQL Server 프로파일러 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)에서는 경량 프로파일링이 기본적으로 꺼져 있지만 XEvent 추적이 query_post_execution_plan_profile 시작될 때 활성화된 다음 추적이 중지되면 다시 비활성화됩니다. 따라서 SQL Server 2017(14.x) 인스턴스에서 query_post_execution_plan_profile Xevent 추적을 자주 시작하고 중지하는 경우 반복되는 활성화/비활성화 오버헤드를 방지하기 위해 traceflag 7412를 사용하여 전역 수준에서 경량 프로파일링을 활성화하는 것이 좋습니다.

참고 항목

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