고유하게 컴파일된 저장 프로시저의 성능 모니터링Monitoring Performance of Natively Compiled Stored Procedures

이 항목은 다음에 적용됩니다. 예SQL Server 예Azure SQL Database아니요Azure SQL Data Warehouse 아니요병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 문서에서는 고유하게 컴파일된 저장 프로시저 및 고유하게 컴파일된 다른 T-SQL 모듈의 성능을 모니터링하는 방법에 대해 설명합니다.This article discusses how you can monitor the performance of natively compiled stored procedures and other natively compiled T-SQL modules.

확장 이벤트 사용Using Extended Events

sp_statement_completed 확장 이벤트를 사용하여 쿼리 실행을 추적할 수 있습니다.Use the sp_statement_completed extended event to trace execution of a query. 이 이벤트로 확장 이벤트 세션을 만들고, 필요에 따라 고유하게 컴파일된 특정 저장 프로시저의 object_id에 대해 필터를 사용하면Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure. 각 쿼리 실행 후에 확장 이벤트가 발생합니다.The extended event is raised after the execution of each query. 확장 이벤트에 의해 보고되는 CPU 시간 및 기간은 쿼리에 사용된 CPU 양과 실행 시간을 나타냅니다.The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. 많은 CPU 시간을 사용하는 고유하게 컴파일된 저장 프로시저에는 성능 문제가 있을 수 있습니다.A natively compiled stored procedure that uses a lot of CPU time may have performance problems.

확장 이벤트의line_number와 함께 line_number 를 사용하여 쿼리를 조사할 수 있습니다.line_number, along with the object_id in the extended event can be used to investigate the query. 다음 쿼리를 사용하여 프로시저 정의를 검색할 수 있습니다.The following query can be used to retrieve the procedure definition. 줄 번호를 사용하여 정의 내에서 쿼리를 식별할 수 있습니다.The line number can be used to identify the query within the definition:

select [definition] from sys.sql_modules where object_id=object_id  

sp_statement_completed 확장 이벤트에 대한 자세한 내용은 이벤트를 발생시킨 문을 검색하는 방법을 참조하세요.For more information about the sp_statement_completed extended event, see How to retrieve the statement that caused an event.

데이터 관리 뷰 및 쿼리 저장소 사용Using Data Management Views and Query Store

SQL ServerSQL ServerSQL DatabaseSQL Database에서는 프로시저 수준 및 쿼리 수준 모두에서 고유하게 컴파일된 저장 프로시저에 대한 실행 통계 수집을 지원합니다. and SQL DatabaseSQL Database support collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. 실행 통계 수집은 성능에 미치는 영향 때문에 기본적으로 설정되지 않습니다.Collecting execution statistics is not enabled by default due to performance impact.

실행 통계는 쿼리 저장소에서와 마찬가지로 시스템 뷰 sys.dm_exec_procedure_statssys.dm_exec_query_stats에 반영됩니다.Execution statistics are reflected in the system views sys.dm_exec_procedure_stats and sys.dm_exec_query_stats, as well as in Query Store.

프로시저 수준 실행 통계 수집 활성화Enabling Procedure-Level Execution Statistics Collection

SQL ServerSQL Server: sys.sp_xtp_control_proc_exec_stats(Transact-SQL)를 사용하여 프로시저 수준의 고유하게 컴파일된 저장 프로시저에 대한 통계 수집을 활성화하거나 비활성화할 수 있습니다. SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the procedure-level using sys.sp_xtp_control_proc_exec_stats (Transact-SQL). 다음 명령문을 사용하면 현재 인스턴스에 있는 고유하게 컴파일된 모든 T-SQL 모듈에 대한 프로시저 수준 실행 통계 수집을 활성화할 수 있습니다.The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL 데이터베이스Azure SQL Database: 데이터베이스 범위 구성 옵션 XTP_PROCEDURE_EXECUTION_STATISTICS를 사용하여 프로시저 수준의 고유하게 컴파일된 저장 프로시저에 대한 통계 수집을 활성화하거나 비활성화할 수 있습니다. Azure SQL 데이터베이스Azure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the database-scoped configuration option XTP_PROCEDURE_EXECUTION_STATISTICS. 다음 명령문을 사용하면 현재 데이터베이스에 있는 고유하게 컴파일된 모든 T-SQL 모듈에 대한 프로시저 수준 실행 통계 수집을 활성화할 수 있습니다.The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON

쿼리 수준 실행 통계 수집 활성화Enabling Query-Level Execution Statistics Collection

SQL ServerSQL Server: sys.sp_xtp_control_query_exec_stats(Transact-SQL)를 사용하여 쿼리 수준의 고유하게 컴파일된 저장 프로시저에 대한 통계 수집을 활성화하거나 비활성화할 수 있습니다. SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the query-level using sys.sp_xtp_control_query_exec_stats (Transact-SQL). 다음 명령문을 사용하면 현재 인스턴스에 있는 고유하게 컴파일된 모든 T-SQL 모듈에 대한 쿼리 수준 실행 통계 수집을 활성화할 수 있습니다.The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL 데이터베이스Azure SQL Database: 데이터베이스 범위 구성 옵션 XTP_QUERY_EXECUTION_STATISTICS를 사용하여 명령문 수준의 고유하게 컴파일된 저장 프로시저에 대한 통계 수집을 활성화하거나 비활성화할 수 있습니다. Azure SQL 데이터베이스Azure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the database-scoped configuration option XTP_QUERY_EXECUTION_STATISTICS. 다음 명령문을 사용하면 현재 데이터베이스에 있는 고유하게 컴파일된 모든 T-SQL 모듈에 대한 쿼리 수준 실행 통계 수집을 활성화할 수 있습니다.The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON

샘플 쿼리Sample Queries

통계를 수집한 후 고유하게 컴파일된 저장 프로시저에 대한 실행 통계에서 sys.dm_exec_procedure_stats(Transact-SQL)를 사용하여 프로시저를 쿼리하고 sys.dm_exec_query_stats(Transact-SQL)를 사용하여 쿼리를 쿼리할 수 있습니다.After you collect statistics, the execution statistics for natively compiled stored procedures can be queried for a procedure with sys.dm_exec_procedure_stats (Transact-SQL), and for queries with sys.dm_exec_query_stats (Transact-SQL).

다음 쿼리에서는 통계 컬렉션 후 현재 데이터베이스에서 고유하게 컴파일된 저장 프로시저에 대한 실행 통계 및 프로시저 이름을 반환합니다.The following query returns the procedure names and execution statistics for natively compiled stored procedures in the current database, after statistics collection:

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  

다음 쿼리에서는 쿼리 텍스트와 함께 현재 데이터베이스에서 통계가 수집된 고유하게 컴파일된 저장 프로시저의 코든 쿼리에 대한 실행 통계(전체 작업자 시간을 기준으로 정렬됨)도 내림차순으로 반환합니다.The following query returns the query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected, ordered by total worker time, in descending order:

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  st.dbid=db_id() and st.objectid in (select object_id   
from sys.sql_modules where uses_native_compilation=1)  
order by qs.total_worker_time desc  

쿼리 실행 계획Query Execution Plans

고유하게 컴파일된 저장 프로시저는 SHOWPLAN_XML(예상 실행 계획)을 지원합니다.Natively compiled stored procedures support SHOWPLAN_XML (estimated execution plan). 예상 실행 계획을 사용하여 쿼리 계획을 검사하면 잘못된 계획 문제를 모두 찾을 수 있습니다.The estimated execution plan can be used to inspect the query plan, to find any bad plan issues. 잘못된 계획의 일반적인 이유는 다음과 같습니다.Common reasons for bad plans are:

  • 프로시저를 만들기 전에 통계가 업데이트되지 않았습니다.Stats were not updated before the procedure was created.

  • 누락된 인덱스Missing indexes

    다음 Transact-SQLTransact-SQL을 실행하여 Showplan XML을 가져옵니다.Showplan XML is obtained by executing the following Transact-SQLTransact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO  

또는 SQL Server Management StudioSQL Server Management Studio에서 프로시저 이름을 선택하고 예상 실행 계획 표시를 클릭합니다.Alternatively, in SQL Server Management StudioSQL Server Management Studio, select the procedure name and click Display Estimated Execution Plan.

고유하게 컴파일된 저장 프로시저의 예상 실행 계획은 프로시저의 쿼리에 대한 쿼리 연산자 및 식을 보여 줍니다.The estimated execution plan for natively compiled stored procedures shows the query operators and expressions for the queries in the procedure. SQL Server 2014(12.x)SQL Server 2014 (12.x) 에서는 고유하게 컴파일된 저장 프로시저에 대한 일부 SHOWPLAN_XML 특성을 지원하지 않습니다. does not support all SHOWPLAN_XML attributes for natively compiled stored procedures. 예를 들어 쿼리 최적화 프로그램 비용 계산과 관련된 특성은 프로시저에 대한 SHOWPLAN_XML의 일부가 아닙니다.For example, attributes related to query optimizer costing are not part of the SHOWPLAN_XML for the procedure.

참고 항목See Also

고유하게 컴파일된 저장 프로시저Natively Compiled Stored Procedures