고유하게 컴파일된 저장 프로시저의 성능 모니터링
적용 대상: 
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의 일부가 아닙니다.