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

아티클
08/21/2023

적용 대상:SQL Server Azure SQL DatabaseAzure 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 Database는 프로시저 수준과 쿼리 수준에서 고유하게 컴파일된 저장 프로시저에 대한 실행 통계 수집을 지원합니다. 성능 영향으로 인해 실행 통계 수집은 기본적으로 사용하도록 설정되지 않습니다.

실행 통계는 쿼리 저장소뿐만 아니라 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 Database 및 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 Database 및 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의 일부가 아닙니다.

참고 항목

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