QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance 로그쓰기 사용량 확인

 

·       Version : Azure SQL

 

Azure SQL Managed Instance Azure 클라우드에서 호스팅되는 완전히 관리되는 SQL Server 인스턴스이다. 관리형 인스턴스에는 최대 로그쓰기 처리량 성능에 일부 제한이 있어 워크로드 속도가 느려질 있다. 이번 포스트에서는 관리형 인스턴스에서 쓰기 로그 처리량 문제를 식별하는 방법에 대해서 살펴본다.

 

Azure SQL Managed Instance에는 최대 로그 쓰기 속도와 같은 가지 기본 제공 리소스 제한이 있다. 로그 쓰기 제한을 도입한 이유는 로그 백업이 들어오는 데이터를 포착할 있어야 하기 때문이다.

·       Overview Azure SQL Database managed instance resource limits : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-resource-limits#service-tier-characteristics

 

이번 포스트에서는 QPI 라이브러리를 사용하여 관리형 인스턴의 대기 통계를 쉽게 분석하고 있다.  QPI 라이브러리를 설치하려면 아래 링크로 이동하여 SQL Server 버전에 대한 SQL 스크립트를 다운로드 한다. QPI 라이브러리는 Query Store 보기에 의존하기 때문에 SQL Server 2016이상, Azure SQL 에서 사용이 가능하다.

·       Download QPI(Query Performance Insights) : https://github.com/JocaPC/qpi#installation

Disclaimer: QPI library is open source library provided as-is and not maintained by Microsoft. There are not guarantees that the results are correct and that there are not bugs in calculations. This is a helper library that can help you to more easily analyze performance of your Managed Instance, but you can do the same job by looking directly at DMVs.

 

라이브러리를 사용하면 대기 통계의 스냅샷을 쉽게 작성하고 잠시 기다렸다가 대기 통계 값을 읽을 있다.

exec qpi.snapshot_wait_stats;

 

waitfor delay '00:00:03';

 

select *

from qpi.wait_stats

order by wait_time_s desc;

 

 

결과를 살펴보면, 관리 인스턴스의 태스크가 INSTANCE_LOG_RATE_GOVERNOR 대기 유형을 기다리고 있음을 있다. 스크립트를 주기적으로 실행하여 이러한 문제쿼리를 식별할 있다.  또한 qpi에서 제공하는 다양한 명령어를 사용하여 관리형 인스턴스의 IO 성능을 분석하여 병목 현상을 식별할 있다.

·       qpi.snapshot_file_stats 프로시저는 sys.dm_io_virtual_file_stats DMV 함수에서 I/O 통계 스냅샷을 작성한다. sys.dm_io_virtual_stats에는 누적 정보가 포함되어 있으며 최근 시간 간격으로 샘플을 계산해야 하므로 스냅샷을 작성해야 한다.

·       qpi.file_stats보기는 마지막 스냅샷 이후 파일 통계를 가져온다. qpi sys.dm_io_virtual_file_stats 데이터를 기반으로 하는 IOPS 처리량등 여러 계산이 포함된다.

 

아래 스크립트는 파일 유형(LOG/DATA)별로 분류된 인스턴스의 쓰기 처리량(MB/s) IOPS 요약한다.

exec qpi.snapshot_file_stats;

 

waitfor delay '00:00:01';

 

select

       type,

       write_mbps = sum(write_mbps),

       iops = sum(iops)

from qpi.file_stats

group by rollup (type);

 

 

결과를 살펴보면 현재 인스턴스에서는 47.5MB/s 로그파일 쓰기와 1226 IOPS 사용하고 있음을 있다. Azure 설명서에서 리소스 제한에 대한 설명을 보면  ~48MB/s 현재 사용되는 인스턴스의 리밋이며, 이러한 리밋으로 인해서 INSTNACE_LOG_RATE_GOVERNOR 대기 통계가 발생하는 것이다. 인스턴스의 최대 사용량 리밋으로 인해서 성능 저하가 발생하지 않도록QPI 스크립트를 주기적으로 실행하여, 인스턴스의 리밋과 QPI결과값을 비교하여,모니터링 있도록 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/identify-log-write-limits-on-azure-sql-managed-instance-using-qpi-library/

 

2019-09-10 / Sungwook Kang / http://sungwookkang.com

 

Azure SQL, QPI, Query Performance Insights, SQL Server 2016, Query Store

SQL Server 2016 JSON 형식을 일반  ROW 형식으로 반환하기

 

·         Version : SQL Server 2016

 

SQL Server 2016 부터 지원되는 JSON 형식은 쿼리 결과를 JSON 형식으로 반환 할 수 있으며 JSON 형식의 데이터를 일반 ROW로 데이터를 반환할 수도 있다. 이번 포스트에서는 JSON 형식의 데이터를 일반 ROW형식으로 반환하는 방법에 대해서 알아본다.

 

JSON 형식의 텍스트를 구문 분석하여 일반 ROW 형식으로 데이터를 반환하는 함수는 OPENJSON 이다. OPENJSON 함수를 사용하여 JSON 컬렉션을 행 집합으로 변환한 후 데이터에 대해서 SQL 쿼리를 실행하거나 SQL Server 테이블에 삽입할 수 있다.

 

OPEN JSON 함수는 단일 JSON 개체 또는 JSON 개체 컬렉션을 받아서 하나 또는 여러 개의 행으로 변환한다. 기본적으로 OPENJSON 함수는 다음 데이터를 반환한다.

·         JSON 개체에서 이 함수는 첫 번쨰 수준에서 발견된 모든 키:값 쌍을 반환한다.

·         JSON 배열에서 이 함수는 모든 요소  및 해당 인덱스를 반환한다.

WITH 옵션을 사용하여 열을 명시적으로 지정할 수 있다.

 

[기본 구조를 사용하는 OPENJSON]

결과에 대해 명시적으로 스키마를 제공하지 않는다.

DECLARE @json NVARCHAR(MAX)

 

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

 

SELECT *

FROM OPENJSON(@json);

 

 

[명시적 구조를 사용하는 OPENJSON]

OPENJSON 함수의 WITH절을 함께 사용하여 결과의 스키마를 지정하여 WITH에 포함된 열만 반환한다.

DECLARE @json NVARCHAR(MAX)

SET @json =  

  N'[ 

       { 

         "Order": { 

           "Number":"SO43659", 

           "Date":"2011-05-31T00:00:00" 

         }, 

         "AccountNumber":"AW29825", 

         "Item": { 

           "Price":2024.9940, 

           "Quantity":1 

         } 

       }, 

       { 

         "Order": { 

           "Number":"SO43661", 

           "Date":"2011-06-01T00:00:00" 

         }, 

         "AccountNumber":"AW73565", 

         "Item": { 

           "Price":2024.9940, 

           "Quantity":3 

         } 

      } 

 ]' 

 

SELECT * FROM 

 OPENJSON ( @json ) 

WITH (  

              Number   varchar(200) '$.Order.Number' , 

              Date     datetime     '$.Order.Date', 

              Customer varchar(200) '$.AccountNumber', 

              Quantity int          '$.Item.Quantity' 

 )

 

 

[참고자료]

https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

 

2017-10-17 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, JSON format, OPENJSON

In-Memory OLTP 사용시 메모리 할당량 초과 오류

 

·         Version : SQL Server 2014, 2016

 

SQL Server 에서 In-Memory OLTP 사용하는 환경에서 아래와 같은 오류가 에러로그에 기록되어 있다면 현재 적용되어 있는 In-Memory 용량을 초과한 것이다.

[INFO] HkDatabaseTryAcquireUserMemory(): Database ID: [7]. Out of user memory quota: requested = 131200; available = 74641; quota = 34359738368; operation = 1.

 

오류 로그에 기록된 내용은 아래와 같다.

·         requested = 131200 : 요청된 바이트

·         available = 74641 : 사용할 있는 바이트

·         quota = 34359738368 : 현재 할당된 바이트 (Standard Edition : 32G)

·         operation = 1 : 메모리 최적화 테이블 변수를 생성할 있도록 메모리가 요청

Ø  operation 0 : create memory optimized table

Ø  operation  1 : create memory optimized table variable

Ø  operation  2 : insert

Ø  operation  3 : update

 

위의 메시지 외에도 사용자 어플리케이션에서는 아래와 같은 메시지를 수신하는 경우도 있다.

Msg 41823, Level 16, State 171, Line 6 Could not perform the operation because the database has reached its quota for in-memory tables. See 'http://go.microsoft.com/fwlink/?LinkID=623028' for more information.

 

메모리 최적화 테이블 변수 사용시 루프를 사용할때 위와 같은 메모리 부족 오류가 자주 발생 할 수 있으니 주의 할 수 있도록 한다.

·         메모리 최적화 테이블변수와 701 오류 (loop 사용으로 인한 메모리 부족 오류) : http://sqlmvp.kr/220996905075

 

SQL Server 2016 In-Memory OLTP quotas.

Edition

In-Memory OLTP quota(per DB)

Express

352MB

Web

16GB

Standard

32GB

Developer

Unlimited

Enterprise

Unlimited

 

 

[참고자료]

·         https://blogs.msdn.microsoft.com/psssql/2017/06/07/you-may-see-out-of-user-memory-quota-message-in-errorlog-when-you-use-in-memory-oltp-feature/

·         https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/in-memory-oltp-in-standard-and-express-editions-with-sql-server-2016-sp1/

 

 

2017-08-23 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, In memory OLTP, SQL Server, DBA, Memory optimize table, In Memory quota


Multisubnet환경의 AG 그룹에서 링크드 서버 사용시 주의점

 

·         Version : SQL Server 2012, 2014, 2016

 

SQL Server 2012부터 도입된 가용성 그룹(AG) SQL Server에서 제공할 있는 최고의 가용성을 보장하지만 구성이 까다로울 있다. 특히 다중 서브넷 환경에서 장애 조치를 구성하는 경우 예상치 못한 문제를 많이 겪을 있다. 필자 또한 단일 서브넷에서의 AG 경험은 있었지만 다중 서브넷 환경에서 구성 운영은 처음이어서 (Cloud  VM으로  다중 서브넷(Multi Region구성)  다양한 이슈를 겪고 있다.(기술적 이슈는 아님)

 

이번 이슈는 Multisubnet  AG구성을  구성한 환경에서 링크드 서버를 구성할때, Native Client 사용하였을 경우 Node 1에서 Node2 Failover되었을때 Node2번으로 접속하지 못하는 경우가 발생하였다.   문제를 해결하기 위해서는 ODBC 사용해서 링크드 서버를 구성해야 한다. 아래 구성받법을 참고해서 링크드 서버를 구성 있도록 한다.

 

ODBC 드라이버를 다운로드 받아 설치 한다. 이때 11버전 이상을 선택 한다. (SQL 2016 경우ODBC 드라이버 버전이 13이다.)

·         Microsoft® ODBC Driver 13 for SQL Server® - Windows + Linux

https://www.microsoft.com/en-us/download/details.aspx?id=50420

 

AG에서 사용할 ODBC 연결을 생성한다. System DSN 탭에서 연결을 생성한다.

 

 

Datasource ODBC Dirver 13 for SQL Server 선택한다.

 

ODBC에서 사용할 이름을 지정하고 Server 경우 AG 리스너 이름을 입력한다.

 

 

 

기술적으로는 로그인 비밀번호를 넣지 않아도 되지만 링크 서버에 사용할 로그인과 비밀번호를 사용하여 테스트 있다. 물론 계정 비밀번호는 저장되지 않는다.

 

 

마지막 단계에서Multisubnet failver 옵션을  체크한다. 기능이 Failover 되었을때 다른 노드로 연결을 시도한다.

 

ODBC 생성이 완료되었으면 SSMS에서 링크드를 구성한다. 공급자는 ODBC 드라이버를 선택한다. 그리고 Datasource부분에 ODBC에서 생성했던 ODBC 이름을 사용하여 링크드 서버를 구성한다.

 



[참고자료]

https://royalsql.com/2016/09/29/mayday-this-is-an-emergency-my-linked-server-is-using-odbc-to-connect-to-an-ag/

 

 

2017-08-15 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, MS SQL , SQL Server Always On, AG 구성, 멀티서브넷 이중화, Linked Server, 링크드 서버, ODBC 링크드 구성


+ Recent posts