데이터베이스 개발자 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
2150 SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상) jevida(강성욱) 2019.03.26 528
2149 SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인 jevida(강성욱) 2019.03.26 766
2148 SQL Server 2019에서 향상된 Rowstore batch mode jevida(강성욱) 2019.03.26 420
2147 SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화 jevida(강성욱) 2019.03.26 438
2146 In-memory optimized table에 사용되는 Hash Index jevida(강성욱) 2019.03.26 390
2145 VM환경에서 AG를 구성하였을때VSS 백업 동작 변경 jevida(강성욱) 2019.03.25 394
2144 SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치 jevida(강성욱) 2019.03.25 419
2143 SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size jevida(강성욱) 2019.03.25 309
2142 SQL Server 설치시 발생하는 1638 오류 jevida(강성욱) 2019.03.25 410
2141 SQL Server 666코드의 고유 식별자 오류 jevida(강성욱) 2019.03.25 293
2140 SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅 jevida(강성욱) 2019.03.25 349
2139 SQL Server Scheduling and Yielding 트러블슈팅 jevida(강성욱) 2019.03.25 351
2138 SQL Server 2016 Tempdb 경합(contention) 최적화 jevida(강성욱) 2019.03.25 437
2137 XEvent를 사용하여 Auto tuning 작업 모니터링 jevida(강성욱) 2019.03.25 450
2136 SQL Server In-Memory OLTP에 ASP.NET 세션 상태 저장하기 jevida(강성욱) 2019.03.25 351
2135 SQL Server에서 JSON 데이터 저장하기 jevida(강성욱) 2019.03.25 424
2134 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.25 231
» SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 440
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 258
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 397





XE Login