데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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



출처: https://sqlmvp.tistory.com/1253?category=618825 [Database Lab]
No. Subject Author Date Views
» SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 458
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 265
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 413
2130 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 399
2129 SQL Server 2016 향상된 복제 기능 – 배포 데이터베이스 클린업 향상 jevida(강성욱) 2019.03.25 299
2128 SQL Server 2017향상된 복제 기능 – 배포 데이터베이스의 AG 지원 jevida(강성욱) 2019.03.25 453
2127 SQL Server 2017 향상된 복제 기능 - 복제에이전트 프로필 매개변수의 동적 새로 고침 jevida(강성욱) 2019.03.25 498
2126 SQL Server update on Docker jevida(강성욱) 2019.03.25 391
2125 SQL Server 2017에서 향상된 UDF 실행 계획 jevida(강성욱) 2019.03.25 355
2124 ETL data error with MariaDB ODBC 3.0 (from Aurora to MS SQL) jevida(강성욱) 2019.03.25 511
2123 Run the SQL Server 2017 with Docker jevida(강성욱) 2019.03.25 435
2122 SQL Server Configuration Manager 실행 오류 (WMI 공급자 연결 오류) jevida(강성욱) 2019.03.25 478
2121 BULK INSERT 동시에 여러개 실행 향지 2019.01.30 870
2120 대용량 데이터 조인에 대한 고민 ( Hash Join VS Nested Loop Join ) 향지 2018.04.04 4312
2119 How to check if Azure SQL is using In-Memory jevida(강성욱) 2018.03.31 3238
2118 MSSQL-CLI를 활용한 크로스플랫폼에서 SQL Server 관리하기 jevida(강성욱) 2018.03.31 3555
2117 SSMS – Search for execution plan jevida(강성욱) 2018.03.31 3734
2116 SQL Server Parallelism and Wait change (CXAPCKET, CXCONSUMER) jevida(강성욱) 2018.03.31 3956
2115 SQL Server Statistics Update row sampling rate and histogram step jevida(강성욱) 2018.03.31 3648
2114 SSMS – AlwaysOn Group Latency report jevida(강성욱) 2018.03.31 3388





XE Login