SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링

SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링

 

·         Version : SQL Server, Azure SQL

 

SQL Server 및 Azure SQL에서 네이티브 컴파일된 저장 프로시저에 대한 성능 모니터링 옵션이 추가되었다. 이 옵션은 Azure SQL 및 SQL Server에서 사용할 수 있다. 새로 추가된 모니터링 옵션은 XTP_PROCEDURE_EXECUTION_STATISTICS 및 XTP_QUERY_EXECUTION_STATISTICS으로 In-Memory OLTP를 사용하는 데이터베이스의 네이티브 컴파일된 프로시저에 대한 모니터링 및 문제 해결에 대한 정보를 제공한다. 이 옵션을 활성화 하여 Query Store와 sys.dm_exec_query_stats및 sys.dm_exec_procedure_stats DMV를 사용하여 컴파일된 저장 프로시저의 성능을 모니터링할 수 있다. 실행 통계 수집은 시스템 오버헤드를 유발하므로 사용하지 않은 경우 통계 수집을 비활성화 하는 것이 좋다.

 

아래 스크립트는 Azure SQL에서 프로시저 수준에서 실행 통계 수집을 활성한다. 현재 인스턴스에 있는 네이티브 컴파일된 모든 T-SQL 모듈에 대한 프로시저 수준 실행 통계 수집을 활성화 한다.

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON

 

아래 스크립트는 Azure SQL에서 쿼리 수준에서 실행 통계 수집을 활성화 한다. 현재 인스턴스에 있는 네이티브 컴파일된 모든 T-SQL 모듈에 대한 쿼리 수준 실행 통계 수집을 활성화 한다.

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON

 

SQL Server에서 쿼리 수준의 실행 통계 수집을 활성화 하려면 아래 스크립트를 실행 한다. 현재 인스턴스에 있는 네이티브 컴파일된 모든 T-SQL 모듈에 대한 쿼리 수준 실행 통계 수집을 활성화 한다.

EXEC sys.sp_xtp_control_query_exec_stats 1

 

 

 

 

통계를 수집한 후 네이티브 컴파일된 저장프로시저에 대한 실행 통계에서 sys.dm_exec_procedue_stats를 사용하여 프로시저 실행 통계를 쿼리하고, sys.dm_exec_querystats를 사용하여 쿼리 실행 통계를 조회할 수 있다. 아래 스크립트는 현재 데이터베이스에서 네이티브 컴파일된 저장프로시저에 대한 프로시저 이름 및 실행 통계 정보를 보여준다.

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 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

 

실습을 위해 In-Memory 데이터베이스 생성 및 Memory Optimized table, Native compile 를 생성하고 SP를 실행 한다.

CREATE DATABASE Demo  ON  PRIMARY(NAME = [Demo_data],  FILENAME = 'D:\SQLDATA\Demo_data.mdf', size=500MB)  , FILEGROUP [Demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA(  NAME = [Demo_dir],  FILENAME = 'D:\SQLDATA\Demo_dir')  LOG ON (name = [Demo_log], Filename='D:\SQLDATA\Demo_log.ldf', size=500MB)  COLLATE Latin1_General_100_BIN2;  go    use Demo go   CREATE TABLE [dbo].[SalesOrders]  (       [order_id] [int] NOT NULL,       [order_date] [datetime] NOT NULL,       [order_status] [tinyint] NOT NULL       CONSTRAINT [PK_SalesOrders] PRIMARY KEY NONCLUSTERED HASH   (       [order_id]  ) WITH ( BUCKET_COUNT = 2097152)  ) WITH ( MEMORY_OPTIMIZED = ON )  go    -- Interpreted.  CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME2, @status TINYINT  AS   BEGIN     INSERT dbo.SalesOrders VALUES (@id, @date, @status);  END  go    -- Natively Compiled.  CREATE PROCEDURE [dbo].[InsertOrderXTP]        @id INT, @date DATETIME2, @status TINYINT    WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  AS   BEGIN ATOMIC WITH        (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'       )    INSERT dbo.SalesOrders VALUES (@id, @date, @status);  END  go    SELECT * from SalesOrders;  go    EXECUTE dbo.InsertOrder @id= 10, @date = '1956-01-01 12:00:00', @status = 1;  EXECUTE dbo.InsertOrderXTP @id= 11, @date = '1956-01-01 12:01:00', @status = 2;    SELECT * from SalesOrders;

 

위 스크립트 실행이 완료 되었으면 아래 스크립트를 실행하여 네이티브 컴파일된 쿼리 실행 통계를 확인한다.

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

 

 

 

[참고자료]

·         https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/04/04/monitoring-performance-of-natively-compiled-stored-procedures-database-scoped-configuration-options/

·         https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/monitoring-performance-of-natively-compiled-stored-procedures?view=sql-server-2017

·         https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/native-compilation-advisor?view=sql-server-2017

 

 

2018-06-13 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, Azure SQL, Native compile procedure, DB Monitoring, DB 모니터링, 저장 프로시저, stored procedure, XTP_PROCEDURE_EXECUTION_STATISTICS, XTP_QUERY_EXECUTION_STATISTICS