SQL Server/SQL Server Tip

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

SungWookKang 2019. 3. 25. 05:24
반응형

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

반응형