고유하게 컴파일된 저장 프로시저의 성능 모니터링

아티클
07/12/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

이 문서에서는 네이티브 컴파일된 저장 프로시저 및 기타 네이티브 컴파일된 T-SQL 모듈의 성능을 모니터링하는 방법을 설명합니다.

확장 이벤트 사용

sp_statement_completed 확장 이벤트를 사용하여 쿼리 실행을 추적합니다. 필요에 따라 특정 네이티브 컴파일된 저장 프로시저에 object_id 필터를 사용하여 이 이벤트로 확장 이벤트 세션을 만듭니다. 확장 이벤트는 각 쿼리를 실행한 후에 발생합니다. 확장 이벤트에서 보고한 CPU 시간 및 기간은 쿼리가 사용한 CPU 양과 실행 시간을 나타냅니다. CPU 시간을 많이 사용하는 네이티브 컴파일된 저장 프로시저에는 성능 문제가 있을 수 있습니다.

line_number는 확장 이벤트의 object_id와 함께 쿼리를 조사하는 데 사용될 수 있습니다. 다음 쿼리를 사용하여 프로시저 정의를 검색할 수 있습니다. 줄 번호를 사용하여 정의 내에서 쿼리를 식별할 수 있습니다.

SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;

데이터 관리 보기 및 쿼리 저장소 사용

SQL Server 및 Azure SQL 데이터베이스는 프로시저 수준과 쿼리 수준에서 네이티브 컴파일된 저장 프로시저에 대한 실행 통계 수집을 지원합니다. 성능 영향으로 인해 실행 통계 수집은 기본적으로 사용하도록 설정되지 않습니다.

실행 통계는 시스템 뷰 sys.dm_exec_procedure_stats, sys.dm_exec_query_stats 및 쿼리 저장소에 반영됩니다.

프로시저 수준 실행 통계

SQL Server: sys.sp_xtp_control_proc_exec_stats(Transact-SQL)를 사용하여 프로시저 수준의 네이티브 컴파일된 저장 프로시저에서 통계 수집을 활성화 또는 비활성화합니다. 다음 명령문을 사용하면 현재 인스턴스에 있는 고유하게 컴파일된 모든 T-SQL 모듈에 대한 프로시저 수준 실행 통계 수집을 활성화할 수 있습니다.

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL 데이터베이스 및 SQL Server: 데이터베이스 범위 구성 옵션 XTP_PROCEDURE_EXECUTION_STATISTICS를 사용하여 프로시저 수준의 네이티브 컴파일된 저장 프로시저에 대한 통계 수집을 활성화 또는 비활성화합니다. 다음 문을 사용하면 현재 데이터베이스에 있는 네이티브 컴파일된 모든 T-SQL 모듈에 대한 프로시저 수준 실행 통계 수집을 활성화할 수 있습니다.

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

쿼리 수준 실행 통계

SQL Server: sys.sp_xtp_control_query_exec_stats(Transact-SQL)를 사용하여 쿼리 수준의 네이티브 컴파일된 저장 프로시저에서 통계 수집을 활성화 또는 비활성화합니다. 다음 문을 사용하면 현재 인스턴스에 있는 네이티브 컴파일된 모든 T-SQL 모듈에 대한 쿼리 수준 실행 통계 수집을 활성화할 수 있습니다.

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL 데이터베이스 및 SQL Server: 데이터베이스 범위 구성 옵션 XTP_QUERY_EXECUTION_STATISTICS를 사용하여 문 수준의 네이티브 컴파일된 저장 프로시저에 대한 통계 수집을 활성화 또는 비활성화합니다. 다음 문을 사용하면 현재 데이터베이스에 있는 네이티브 컴파일된 모든 T-SQL 모듈에 대한 쿼리 수준 실행 통계 수집을 활성화할 수 있습니다.

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

샘플 쿼리

통계를 수집한 후에는 네이티브 컴파일된 저장 프로시저에 대한 실행 통계를 쿼리하여 sys.dm_exec_procedure_stats(Transact-SQL)를 사용하는 프로시저와 sys.dm_exec_query_stats(Transact-SQL)를 사용하는 쿼리에 대해 쿼리할 수 있습니다.

다음 쿼리에서는 통계 컬렉션 후 현재 데이터베이스에서 고유하게 컴파일된 저장 프로시저에 대한 실행 통계 및 프로시저 이름을 반환합니다.

SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

다음 쿼리는 통계가 수집된 현재 데이터베이스의 네이티브 컴파일된 저장 프로시저의 모든 쿼리에 대한 실행 통계뿐만 아니라 총 작업자 시간 순의 내림차순으로 쿼리 텍스트를 반환합니다.

SELECT st.objectid,
        OBJECT_NAME(st.objectid) AS 'object name',
        SUBSTRING(
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) AS 'query text',
        qs.creation_time, qs.last_execution_time, qs.execution_count,
        qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, 
        qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
        qs.min_elapsed_time, qs.max_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

쿼리 실행 계획

고유하게 컴파일된 저장 프로시저는 SHOWPLAN_XML(예상 실행 계획)을 지원합니다. 예상 실행 계획을 사용하여 쿼리 계획을 검사하면 잘못된 계획 문제를 모두 찾을 수 있습니다. 잘못된 계획의 일반적인 이유는 다음과 같습니다.

프로시저를 만들기 전에 통계가 업데이트되지 않았습니다.
누락된 인덱스

실행 계획 XML은 다음 Transact-SQL을 실행하여 가져옵니다.

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

또는 SQL Server Management Studio에서 프로시저 이름을 선택하고 예상 실행 계획 표시를 클릭합니다.

네이티브 컴파일된 저장 프로시저의 예상 실행 계획은 프로시저의 쿼리 연산자 및 식을 보여줍니다. SQL Server 2014(12.x)는 네이티브 컴파일된 저장 프로시저에 대한 모든 SHOWPLAN_XML 특성을 지원하지 않습니다. 예를 들어 쿼리 최적화 프로그램 비용 계산과 관련된 특성은 프로시저에 대한 SHOWPLAN_XML의 일부가 아닙니다.

참고 항목

고유하게 컴파일된 저장 프로시저

다음을 통해 공유