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 |
[참고자료]
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
출처: https://sqlmvp.tistory.com/1253?category=618825 [Database Lab]
