How to check if Azure SQL is using In-Memory

-          Azure SQL에서 In-Memory 사용하는지 확인하는 방법

-          Premium Tier에서 Standard Tier  변경  프리미엄 서비스 사용 확인

 

·         Version : AzureSQL

 

Azure SQL 사용시 Tier 마다 지원되는 기능이 조금씩 다르다그중 P Tier Premium 서비스로 In-Memroy 테이블이 지원된다이번 포스트에서는 P Tier에서 하위 S Tier 변경  In-Memory 서비스를 사용하고 있는 테이블이 있는지 확인하는 방법에 대해서 알아본다.

 

·         Service Tier : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers

Basic

Standard

Premium

Target workload

Development and production

Development and production

Development and production

Uptime SLA

99.99%

99.99%

99.99%

Backup retention

7 days

35 days

35 days

CPU

Low

Low, Medium, High

Medium, High

IO throughput (approximate)

2.5 IOPS per DTU

2.5 IOPS per DTU

48 IOPS per DTU

IO latency (approximate)

5 ms (read), 10 ms (write)

5 ms (read), 10 ms (write)

2 ms (read/write)

Columnstore indexing

N/A

S3 and above

Supported

In-memory OLTP

N/A

N/A

Supported

 

아래 스크립트를 실행 하면 현재 Azure SQL에서 In-Memory OLTP 지원하는지 확인할  있다. 1 반환되면 지원함을 의미한다.

SELECT DatabasePropertyEx(DB_NAME(), 'IsXTPSupported');

 


 

아래 스크립트는 Tier 다운그레이드할때 In-Memory OLTP 제거 해야하는 테이블   컴파일된 모듈을 나타낸다.

SELECT * FROM sys.tables WHERE is_memory_optimized=1

SELECT * FROM sys.table_types WHERE is_memory_optimized=1

SELECT * FROM sys.sql_modules WHERE uses_native_compilation=1

 


 

 

[참고자료]

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-in-memory

2018-03-27 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Azure, In-Memory OLTP, Azure SQL Downgrade, How to check Azure SQL down grade





profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.