SQL Server on Linux 성능 향상

이번 포스팅에서는 지난 포스팅에서 SQL Server on Linux에서 지원하는 보안 관련 기능을 살펴보았다면 이번에는 성능향상과 관련된 내용을 다뤄보도록 하겠습니다. 

 

ColumnStore Index

 

컬럼스토어 인덱스 기능은 이름에서 알 수 있듯이, 기존 방식과 달리 데이터를 열(Column) 기반으로 저장하는 혁신적인 기술입니다. 일반적으로 데이터베이스는 데이터를 행(Row) 단위로 저장하고 관리합니다. 이는 빠른 데이터 입력/수정/삭제 (INSERT, UPDATE, DELETE)같은 트랜잭션 처리(OLTP)에는 매우 효율적입니다. 하지만 수백만, 수천만 건에 달하는 대규모 데이터를 분석하고 통계내는 작업(OLAP)에는 비효율적입니다. 분석 쿼리는 보통 테이블의 모든 열이 아니라 특정 몇 개의 열만 필요로 하기 때문입니다. 컬럼스토어 인덱스는 이 문제를 해결합니다.

 

 

컬럼스토어 인덱스의 특징은 아래와 같습니다.

• 열 기반 저장 : 데이터를 열 단위로 묶어 저장합니다.
• 높은 압축률 : 같은 열에 있는 데이터는 유사한 경우가 많아 압축률이 매우 높습니다.
• 빠른 분석 : 쿼리가 특정 열만 필요로 할 경우, 필요한 열만 읽어 들이므로 디스크 I/O를 획기적으로 줄여 분석 속도를 비약적으로 향상시킵니다. 

결과적으로, 컬럼스토어 인덱스는 방대한 양의 데이터를 빠르게 스캔하고 집계하는 데 최적화된 기술입니다.

 

지난 포스팅에서 다룬 AdventureWorks DB를 사용하여 T- SQL 구문으로 ColumnStore 인덱스를 추가해 보도록 하겠습니다. 먼저 이를 수행하기 위해 SSMS 상에서 특정 테이블에 비클러스터형 컬럼스토어 인덱스(Nonclustered Columnstore Index, NCCI)를 생성하는 명령 쿼리를 수행해 줍니다.

 

IX_SalesOrderDetail_ColumnStore라는 이름의 비클러스터형 컬럼스토어 인덱스를 생성합니다. 여기서 비클러스터형(Non-clustered)은 데이터는 무작위로 있고, 인덱스 페이지만 정렬되어 실제 데이터가 위치한 페이지 번호(주소)를 가리키는 방식입니다.

USE AdventureWorks
GO
 
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
	ON Sales.SalesOrderDetail
	(UnitPrice, OrderQty, ProductID)
GO

 

이제 객체탐색기의 AdventureWorks 데이터베이스 > Sales.SalesOrderDetail 테이블에 들어가면 인덱스를 펼쳐 아래의 IX_SalesOrderDetail_ColumnStore 인덱스가 추가된 것을 확인할 수 있습니다.

 

또는 T-SQL 구문으로 만들어진 인덱스를 확인할 수도 있습니다. 아래 구문으로 인덱스를 확인해 봅니다.

SELECT * FROM sys.indexes WHERE name = 'IX_SalesOrderDetail_ColumnStore'
GO

 

이제 조회를 할 때 방금 만든 ColumnStoreIndex를 타고 조회가 되는지 확인하기 위해  실제 실행 계획을 결과와 함께 보여주는 버튼을 SSMS에서 켜줍니다. 

 

그리고 아래와 같이 조회 쿼리를 수행해 봅니다.

SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
	SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM Sales.SalesOrderDetail
	GROUP BY ProductID
	ORDER BY ProductID

 

 

위와 같은 결과창이 보여지면서 동시에 실행 계획 탭을 눌러보면 아래와 같이 실제 ColumnStore Index 스캔을 타고 데이터가 조회된 것을 확인 할 수 있습니다.

 

일반적인 인덱스(Rowstore)는 한 행의 모든 데이터를 다 읽어야 하지만, 컬럼스토어는 쿼리에 사용된 ProductID, UnitPrice, OrderQty 열만 선택적으로 읽습니다.

 

이러한 컬럼스토어 기술은 데이터 분석이 중요해지면서 대부분의 주요 데이터베이스가 이 기능을 탑재하고 있습니다. Oracle의 In-Memory Column Store, PostgreSQL의 citus, vops 그리고 MariaDB의 ColumnStore 등이 있습니다.

 

SQL Server처럼 기존의 행 기반(Rowstore) 데이터베이스에 기능을 '추가'한 것이 아니라, 처음부터 컬럼 방식으로 설계된 데이터베이스들도 있습니다. Google BigQuery가 클라우드 기반의 대표적인 컬럼 지향 데이터베이스입니다. 대규모 데이터 분석(빅데이터) 분야에서는 이들이 더 많이 쓰이기도 합니다.

---

In-memory OLTP

 

In-memory OLTP는 디스크 기반 테이블과는 다르게 메모리 최적화된 테이블은 메인 메모리에 저장 됩니다. 따라서 데이터를 읽을 때 디스크에서 메모리 버퍼 영역으로 데이터를 로드할 필요가 없게 됩니다.

 

일반적인 디스크 기반 테이블은 데이터를 읽거나 쓸 때, 하드 드라이브에서 메모리 버퍼 영역으로 데이터를 로드하는 복잡하고 느린 과정을 거쳐야 합니다. 하지만 메모리 최적화 테이블은 이 과정을 완전히 생략합니다.

 

• 메인 메모리 저장: 데이터와 인덱스가 서버의 메인 메모리(RAM)에 상주합니다.

• 디스크 I/O 최소화: 데이터를 읽을 때 디스크에 접근할 필요가 없으므로, 디스크 I/O로 인한 병목 현상이 사라집니다.

• 성능 극대화: 디스크 기반 테이블보다 훨씬 빠르게 데이터를 읽고 쓸 수 있어, 트랜잭션 처리(OLTP) 성능을 수십 배까지 향상시킬 수 있습니다.

 

이러한 특성 덕분에 In-memory OLTP는 높은 트랜잭션 처리량이 요구되는 금융 시스템, 게임, 웹 서비스 등에서 큰 효과를 발휘합니다.

 

In-memory OLTP를 사용하기 위해 메모리 최적화 테이블을 우선 만들어보겠습니다. 

 

가장 먼저 DB의 호환성 수준을 확인해 줍니다. 여기서 말하는 호환성 수준(Compatibility Level)이란 쉽게 말해 "이 데이터베이스가 SQL Server의 어느 버전처럼 동작할지 결정하는 설정"입니다. 인메모리 테이블을 만들려면 DB 호환성 수준이 130이어야 합니다. 

USE AdventureWorks
GO
 
SELECT d.compatibility_level
FROM sys.databases as d
WHERE d.name = Db_Name();

 

결과에 현재 호환성 수준이 120인 것을 확인할 수 있으며 인메모리 테이블을 만들려면 이를 130으로 바꿔줘야 합니다.

ALTER DATABASE CURRENT 
SET COMPATIBILITY_LEVEL = 130;

 

트랜잭션이 디스크 기반 테이블과 메모리 기반 테이블에 둘 다 상관이 있다면, 트랜잭션 중 메모리 최적화된 부분은 트랜잭션 격리 수준을 SNAPSHOT 레벨로 설정하여 동작해 줘야 합니다.

 

일반적인 디스크 기반 테이블은 데이터를 안전하게 지키기 위해 '자물쇠(Lock)' 전략을 씁니다. 누군가 데이터를 수정하고 있다면, 다른 사람이 아예 접근하지 못하도록 문을 잠그고 줄을 세우는 방식이죠. 하지만 사용자가 많아지면 줄이 너무 길어져 시스템이 느려진다는 단점이 있습니다.

 

반면, 인메모리 테이블은 성능을 극대화하기 위해 줄 세우기를 하지 않는 '낙관적 동시성 제어' 방식을 사용합니다. "서로 충돌할 일이 거의 없을 거야"라고 믿고, 자물쇠를 채우는 대신 각자 작업을 시작하는 순간의 데이터 상태를 사진(스냅샷)으로 찍어 가져가게 합니다.

 

이렇게 하면 서로 방해하지 않고 동시에 수많은 작업을 처리할 수 있습니다. 다만, 자물쇠가 없는 이 독특한 방식 때문에 기존의 일반적인 규칙(Read Committed 등)을 적용하면 시스템이 혼란을 느껴 오류가 발생할 수 있습니다.

 

아래와 같은 구문을 수행하여 메모리 최적화 테이블의 수준을 확실히 강제화 시켜줍니다.

ALTER DATABASE CURRENT 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOP=ON;

 

메모리 최적화 테이블을 만들기 위해서는 사전에 메모리 최적화 파일그룹을 먼저 만들어 줘야 합니다.

 

RAM(메모리)은 전원이 꺼지면 데이터가 모두 사라집니다. 만약 파일 그룹(디스크 저장소)이 없다면, 서버를 재시작할 때마다 데이터가 다 날아갑니다. 인메모리 파일 그룹은 일반적인 데이터 파일(.mdf)과 저장 방식이 완전히 다릅니다. 데이터 파일(Data files)과 델타 파일(Delta files)이라는 쌍으로 데이터를 저장하여, 서버가 켜질 때 이 파일들을 읽어 순식간에 메모리로 데이터를 복구합니다.

 

이를 위해서 아래 구문을 수행해 줍니다.

ALTER DATABASE AdventureWorks
ADD FILEGROUP AdventureWorks_mod
CONTAINS memory_optimized_data

 

이제 만들어진 파일 그룹에 메모리 최적화 테이블 전용 파일을 만듭니다.

ALTER DATABASE AdventureWorks
ADD FILE (NAME='AdventureWorks_mod', FILENAME='/var/opt/mssql/data/AdcentureWorks_mod')
TO FILEGROUP AdventureWorks_mod

 

Putty로 Linux 가상 머신에 접속하여 /var/opt/mssql/data 경로로 들어가면 아래와 같이 AdventureWorks_mod라고 하는 메모리 최적화 전용 파일이 생성된 것을 확인할 수 있습니다.

 

파일 그룹은 DB 별로 오직 하나의 파일 그룹만 생성할 수 있지만 File은 여러 파일을 추가해 줄 수 있습니다. 아래와 같이 파일명 뒤에 2를 붙여 실행합니다. 

ALTER DATABASE AdventureWorks
ADD FILE (NAME='AdventureWorks_mod2', FILENAME='/var/opt/mssql/data/AdcentureWorks_mod2')
TO FILEGROUP AdventureWorks_mod

 

이제 인메모리 테이블을 만들 준비를 마쳤으며, WITH (MEMORY_OPTIMIZED=ON) 구문을 사용하여 인메모리 테이블을 만드는 작업을 진행할 예정입니다.

 

인메모리 테이블은 데이터를 물리적으로 정렬해서 저장하는 '클러스터형 인덱스' 개념이 없습니다. 대신 메모리 주소값을 활용하는 특수한 인덱스를 사용합니다. 따라서 인메모리 테이블은 반드시 비클러스터형(Nonclustered)으로 만들어야 합니다.

 

아래와 같이 메모리 최적화 테이블을 생성하는 구문을 수행해 줍니다.

CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON)
GO

 

이제 만들어진 테이블에 데이터 값들을 INSERT 해줄 것입니다. 아래 구문을 활용하여 값을 INSERT 해줍니다.

-- 메모리 최적화 테이블에 값 INSERT
INSERT dbo.ShoppingCart VALUES (8798, SYSDATETIME(), NULL)
INSERT dbo.ShoppingCart VALUES (23, SYSDATETIME(), 45.4)
INSERT dbo.ShoppingCart VALUES (80, SYSDATETIME(), NULL)
INSERT dbo.ShoppingCart VALUES (342, SYSDATETIME(), 65.4)

---

 

Query Store

쿼리 스토어는 쿼리, 실행계획, 런타임 통계에 대해 자세한 정보를 수집할 수 있게 해주는 기능입니다. 만약 쿼리의 성능 저하 문제가 발생했을 때, 관리자들은 보통 '어제 잘되던 쿼리가 오늘 갑자기 느려졌다'는 상황에 직면합니다. 이런 경우, 쿼리 스토어가 없으면 이전 상태로 되돌아가는 원인을 파악하기가 매우 어렵습니다. 쿼리 스토어는 이러한 문제를 해결하기 위해, 데이터베이스의 '블랙박스' 역할을 합니다.

• 실행 게획 변화 추척 : 쿼리의 성능이 변했을 때, 쿼리 스토어에 기록된 과거 실행 계획과 현재 실행 계획을 비교하여 원인을 파악할 수 있습니다.
• 성능 문제 식별 : 실행 시간이 오래 걸리거나 CPU를 많이 사용하는 쿼리를 쉽게 식별하여 튜닝 대상을 빠르게 찾을 수 있습니다.
• 성능 회귀 분석 : 특정 패치나 인덱스 변경 후 성능이 저하되었을 경우, 변경 전후의 성능 데이터를 비교하여 문제를 진단하고 이전의 좋은 실행 계획을 강제로 적용할 수 있습니다.

쿼리 스토어는 성능 분석에 막대한 양의 정보를 제공하지만, 시스템 성능에 영향을 줄 수도 있기 때문에 기본적으로는 비활성화되어 있습니다. 따라서 이 기능을 사용하려면 데이터베이스 단위로 직접 활성화해주어야 합니다.

 

활성화 하기 위해서는 ALTER DATABASE 구문으로 해당 DB 단위로 활성화를 시켜 줘야 합니다. 쿼리스토어 부분 쿼리를 아래와 같이 수행해 줍니다.

ALTER DATABASE AdvnetureWorks
SET QUERY_STORE = ON
	(OPERATION_MODE = READ_WRITE,
	QUERY_CAPTURE_MODE = ALL,
	MAX_STORAGE_SIZE_MB = 100,
	CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

 

이제 3번 이상 쿼리를 수행합니다.

SELECT TOP 10 * FROM Person.Person;
SELECT COUNT(*) FROM Production.Product;
SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > 1000;

 

1분 정도 후에 아래 구문으로 쿼리스토어에 저장된 값들을 호출해 봅니다.

-- 쿼리 스토어에 저장된 값 조회
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

 

아래와 같이 쿼리 별로 다양한 정보가 나타납니다.

 

목적	주요 확인 컬럼 / 방법	상세 설명
최근 실행된 쿼리 확인	last_execution_time	해당 컬럼을 기준으로 내림차순(DESC) 정렬하여 최신 실행 기록을 파악합니다.
실행 계획 변경 감지	query_id, plan_id	동일한 query_id에 대해 여러 개의 plan_id가 존재하는지 확인하여 성능 변화를 추적합니다.
쿼리 사용 빈도 파악	execution_count	sys.query_store_runtime_stats 테이블과 JOIN하여 얼마나 자주 호출되는지 통계를 확인합니다.
특정 SQL 문제 조사	query_sql_text, plan_id	SQL 본문과 실행 계획 ID를 조합하여 성능 병목이 발생하는 구체적인 지점을 분석합니다.

 

 

동사: 끼워 넣다, 지르다, 끼우다 명사: 끼워 넣는 것