Daily Study

16.  가상로그파일 VLF

SQL Server에서 로그파일(LDF)은 데이터 파일처럼 익스텐트로 구성되지 않고

가상 로그 파일(Virtual Log File 이하 VLF)로 구성되어 있습니다.  

VLF는 로그 파일의 크기에 따라 가변적으로 변하며 SQL Server에 의해 정해 집니다.

VLF의 최소 단위는 256K이며 트랜잭션 로그가 가장 작은 512K일 때 두 개의 VLF가 생성 됩니다.  

If the log files grow to a large size in many small increments, they will have many virtual log files. This can slow down database startup and also log backup and restore operations.

로그파일이 큰사이즈로 많이 작은 증가? 이면 가상 로그 파일이 많이 생성됩니다. 이로 인해 데이터베이스 시작뿐 아니라 로그 백업 및 복원 작업이 느려질 수 있습니다. 반대로 로그 파일이 적거나 하나의 증분인 큰 크기로 설정된 경우 적은 수의 매우 큰 가상 로그 파일이 포함됩니다.  

출처 : 

docs.microsoft.com/ko-kr/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-2017#physical_arch

아래 이미지 같이 가상로그파일이 4개인 경우 , 뒤에 2개를 쓰고 있었다고 가정.

그 다음 로그파일 시작은 Virtual Log1이 된다. 

순환식 으로 쓰여진다라는 말.

(위 이미지와 같은 VLF 설명)

출처 : http://www.sqler.com/bSQLQA/597442
 

LDF는 순환구조 형태로 되어있습니다.

아래 나오는 네모를 VLF라고 생각하시면 됩니다.

□: 트랜잭션 로그 백업이 되었거나 혹은 아직 사용되지 않은 것(DBCC LOGINFO 에서 Status 가 0인 값)

■: 현재 사용중이거 혹은 이미 사용했으나 트랜잭션 로그 백업이 되지 않은 것(DBCC LOGINFO 에서 Status 가 2인 값)

기본

■□□□□□□□□□□□□

DML이 많이 발생하면... 이렇게 로그가 많이 사용이 되겠지요~!

■■■■■■■□□□□□□

여기서 트랜잭션 로그 백업을 수행하면.. 아래처럼 현재 사용중인 VLF는 제외하고.. 백업이 됩니다.

□□□□□□■□□□□□□

하지만... 대량의 IO가 유발되고... 트랜잭션 로그 백업 시점에도 트랜잭션이 진행중이라면.. 아래 처럼 될 수도 있습니다 ^^

이 내용은 하단에 링크 참고하세요~

□□■■■■■□□□□□□

다시 원래대로 돌아와서..

DML이 발생하면서..  끝까지 다 쓰게 되면.. 순환해서 앞에서 부터 다시 이용하게 됩니다.

□□□□□□■■■■■■■

이런 식입니다.

■■■□□□■■■■■■■

이러다... 만약 전부 다 쓰게 되면.. 아래 처럼되겠죠~!

■■■■■■■■■■■■■

이 상태에선..더이상 공간이 없기 때문에.. 추가 사이즈를 할당하게 됩니다.

이런 식이 되겠죠. 추가되는 공간은.. 비율 혹은 MB로 설정하도록 되어있습니다.

■■■■■■■■■■■■■□□□

이러다보면... LDF 사이즈가 계속 증가가 되고... VLF 갯수역시 늘어나게 됩니다.

LDF 사이즈를 늘어나지 않게 하려면..반드시 트랜잭션 로그 백업을 수행해야 합니다.

이렇게 증가할때마다 VLF 갯수가 늘어나게 되고... 몇천개씩 되는 문제가 발생하게 됩니다.

- BULK INSERT ?

BULK INSERT는 CSV 파일을 이용해서 대용량 데이터를 테이블에 INSERT 할 때 편리하다.

보통 Bulk INSERT용 테이블을 한개 만들고 Bulk INSERT로 해당 테이블에 INSERT 후 실제 테이블에 INSERT 하는 방식으로 진행한다.

(1) BULK INSERT 예제 : CSV파일  eforexcel.com/wp/downloads-20-sample-csv-files-data-sets-for-testing-till-2-million-records-bank-transactions/

 - FIRSTROW: 시작할 행 , FIELDTERMINATOR : 구분자 , ROWTERMINATOR : 행 구분자 , BATCHSIZE :  일괄 처리 행 수

BULK INSERT A테이블 FROM '파일경로' WITH ( FIRSTROW  = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', BATCHSIZE = 100) 

 * Csv 파일을 메모장으로 열면 컬럼은 ","로 행은 \n로 구분되기 때문에 

- 테이블 생성

CREATE TABLE [dbo]. [Sales] ( 
	[Region] [varchar] (50), 
	[Country] [varchar] (50), 
	[ItemType] [varchar] (50) NULL, 
	[SalesChannel] [ varchar] (50) NULL, 
	[OrderPriority] [varchar] (50) NULL, 
	[OrderDate] datetime, 
	[OrderID] bigint NULL, 
	[ShipDate] datetime, 
	[UnitsSold] float, 
	[UnitPrice] float, 
	[UnitCost] float, 
	[ TotalRevenue] float, 
	[TotalCost] float, 
	[TotalProfit] float 
)

- BLUK INSERT 진행

BULK INSERT Sales
FROM 'C:\Users\khs\Downloads\1500000 Sales Records'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
	ROWTERMINATOR='\n',
	BATCHSIZE = 300000 ) 

- 완료

1. ColumnStore 인덱스란?

메모리 내 columnstore 인덱스는 열 기반 데이터 스토리지 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리합니다.

columnstore 인덱스는 주로 대량 로드 및 읽기 전용 쿼리를 수행하는 데이터 웨어하우징 작업에 효과적입니다. columnstore 인덱스를 사용하면 기존의 행 기반 스토리지보다 최대 10배의 쿼리 성능 이익과 압축되지 않은 데이터 크기보다 최대 7배의 데이터 압축 을 얻을 수 있습니다.

(1) 생성 방법

클러스터 인덱스 기준 

CREATE CLUSTERED COLUMNSTORE INDEX "idx명"  ON "테이블명"

CREATE CLUSTERED COLUMNSTORE INDEX idxCS_SalesOrderDetail ON CS_SalesOrderDetail 

(2) 성능 테스트 : 약 10배차이?!

-  Full Scan  

-  Index Scan