[R&D]SQL Server

2005 : XML 데이터 관리를 위한 기본 지원이 포함. CLR (COMMON LANGUAGE RUNTIME) 통합이 도입 됨. MVCC(Multi-Version Concurrency Control)를 도입하여 SNAPSHOT의 격리 수준 사용 가능 x64 플랫폼을 기본적으로 지원하는 첫 번째 버전

데이터베이스 수준에서 중복 및 장애 조치 기능을 제공하는 미러링 기능이 도입, SSMS 지원. SSIS 도입.

2008 + 2008R2 : SQL Server Agent 지원, 백업 압축 지원. CDC 지원. 필터링 인덱스 지원.

2012 : BIDS가 SSDT로 대체 됨. 데이터베이스를 지정 시간으로 복원하는 방법에 대한 시각적 지원 향상. 최대 15,000개의 파티션 지원 (이전 버전에서는 1,000개로 제한)

2014 : Azure, 메모리 최적화 테이블 통합이 도입됨. Azure로 마이그레이션 또는 백업 가능, URL에 대한 SQL Server 백업 기능 지원. 백업 중 백업 파일에 암호화 선택 가능.

2016 : PolyBase 지원 도입 (PolyBase : csv 파일과 같은 NoSQL 데이터를 쿼리 가능). JSON 데이터 처리 지원 강화. 보안 강화를 위한 항상 암호화가 도입됨.

2017 : Linux, Docker에 설치 가능, Python 지원, 지능형 쿼리 처리 도입.

2019 : 빅데이터 클러스터 쿠버네티스에서 실행되는 SQL Server, Spark 및 HDFS 컨테이너의 확장성 있는 클러스터 배포. 테이블 변수 컴파일 향상, 지능형 쿼리 성능 향상. JAVA언어 사용 가능.

SQL Server의 전체 구조

 

서버프로세스 (Work Thread)

사용자 프로세스와 통신하면서 사용자의 각종 명령어를 처리.

절차 : 사용자의 요청 → SQL 파싱 → 페이지 READ → 결과 전송

백그라운드 프로세스

SQL Server	설명
Database cleanup / Shrinking Thread	장애가 발생한 시스템을 재기동할 때 인스턴스 복구를 수행하고, 임시 세그먼트와 익스텐트를 모니터링한다
Open Data Services(OPS)	이상이 생긴 프로세스가 사용하던 리소스를 복구한다
Lazy writer Thread	버퍼 캐시에 있는 더티 버퍼를 데이터 파일에 기록
Log writer Thread	로그 버퍼 엔트리를 redo 로그 파일에 기록한다
Database Checkpoint Thread	checkpoint 프로세스는 이전의 checkpoint 가 일어났던 마지막 시점 이후의 데이터베이스 변경 사항을 데이터파일에 기록하도록 트리거링하고, 기록이 완료되면 현재 어디까지 기록했는지를 컨트롤 파일과 데이터 파일 헤더에 기록한다. 장애가 발생하면 마지막 체크포인트 이후 로그 데이터만 디스크에 기록함으로써 인스턴스를 복구할 수 있도록 하는 용도로 사용된다.이 정보를 갱신하는 주기가 길수록 장애 발생시 인스턴스 복구 시간도길어진다.
Distributed Transaction Coordinator(DTC)	분산 트랜잭션 과정에 발생한 문제를 해결한다

 

페이지 및 익스텐트 아키텍처

페이지 

page = 8kb, 128 page = 1MB

데이터베이스에서 데이터 파일(.mdf 또는 .ndf)에 할당되는 디스크 공간은 논리적인 페이지로 나뉘어지며 0에서 n 사이의 숫자가 연속으로 페이지에 매겨집니다.

디스크 I/O 작업은 페이지 수준에서 수행됩니다. 즉 SQL Server는 전체 데이터 페이지를 읽거나 씁니다.

대부분의 페이지에는 사용자가 저장한 실제 데이터 행이 포함되어 있습니다. 이를 데이터 페이지 및 텍스트/이미지 페이지(특수한 경우)라고 합니다.

 

인덱스 페이지에는 데이터가 있는 위치에 대한 인덱스 참조가 포함되고,

마지막으로 데이터의 구성에 대한 다양한 메타데이터를 저장하는 시스템 페이지(PFS, GAM, SGAM, IAM, DCM, BCM 페이지)가 있습니다.

 각 페이지는 96바이트 머리글로 시작하는데 이 머리글은 페이지에 대한 시스템 정보를 저장하는 데 사용됩니다. 페이지 번호, 페이지 유형, 해당 페이지의 사용 가능한 공간 크기 그리고 해당 페이지를 소유하고 있는 개체의 할당 단위 ID와 같은 정보를 저장합니다.

 

익스텐트

익스텐트는 실제로 연속하는 8페이지를 모은 것으로 페이지를 효율적으로 관리하는 데 사용됩니다. 모든 페이지는 익스텐트로 저장됩니다.

extent = 8page = 64kb, 16extent = 1MB

혼합 익스텐트는 최대 8개의 개체가 공유할 수 있습니다. 익스텐트의 8페이지를 각각 다른 개체가 소유할 수 있습니다.

균일 익스텐트는 단일 개체가 소유합니다. 또한 익스텐트의 전체 8페이지는 소유하는 개체만 사용할 수 있습니다.

SQL 2014 버전 까지는 적은 크기의 데이터엔 혼합 익스텐트를 할당한 후 용량이 커지면 균일 익스텐트를 사용하도록 전환

SQL 2016 부터 사용자, tempdb에 균일 익스텐트 사용, master, smdb는 이전과 동일

SQL 2012 부터 시스템 함수(sys.dm_db_database_page_allocations)를 통해 데이터베이스, 테이블, 인덱스 및 파티션에 대한 페이지 할당 정보 확인 가능

SQL 2019 부터 시스템 함수(sys.dm_db_page_info)를 통해 데이터베이스의 페이지에 대한 정보 확인 가능. 페이지의 헤더 정보를 포함하는 하나의 행을 반환하여 편의성 증가.

 

힙/인덱스(세그먼트)

테이블, 인덱스, Undo 처럼 저장공간을 필요로하는 DB 오브젝트

파티션은 오브젝트와 세그먼트가 1:M

한 세그먼트에 할당된 익스텐트가 여러 데이터 파일에 흩어져서 저장됨 (I/O 분산 효과)

 

 

쿼리 처리 아키텍처

실행 모드에는 일괄처리와 행 모드 실행이 있다.

행 모드 실행은 OLTP에 매우 효율적임. 일괄처리는 DW같은 대용량 데이터에서 효율적.

논리 연산자는 NOT → AND → OR 순 괄호를 사용하는 습관을 통하여 가독성 및 실수를 방지 가능.

SELECT 쿼리의 최적화는 쿼리, DB스키마, 통계를 통하여 이루어 짐.

SQL Server 쿼리 최적화 프로그램은 비용을 기반으로 하는 최적화 프로그램.

SQL Server에서는 상수 값을 초기에 처리하여 성능을 향상 시키는데 이를 상수 폴딩이라고 함. ex) 1=1, '2021-01-01'

WORK TABLE : 중간 결과를 보관하는 데 사용되는 내부 테이블(GROUP BY, ORDER BY, UNION 등의 작업에 사용)

저장 프로시저 및 트리거는 원본이 저장되고 실행 계획이 메모리에서 남아있는 기간 동안 저장 프로시저나 트리거가 재 실행되면 기존 계획을 사용하여 성능을 높임.

계획 캐시 : 실행 계획을 저장하는 데 사용되는 메모리 풀 부분.

지속형 개체(저장 프로시저, 함수 및 트리거)에 관련된 계획에 사용되는 Object Plans 캐시 저장소(OBJCP).

자동으로 매개 변수화된 쿼리, 동적 쿼리 또는 준비된 쿼리에 관련된 계획에 사용되는 SQL Plans 캐시 저장소(SQLCP).

SELECT * FROM sys.dm_os_memory_clerks WHERE name LIKE '%plans%' 를 통해 메모리 사용량 확인 가능.

 

 

트랜잭션 로그 아키텍처

각 트랜잭션 로그 레코드는 LSN(Log Sequence Number)로 식별됨.

데이터 수정에 대한 로그 레코드는 수정 전후의 이미지를 모두 기록함.

트랜잭션 로그에 기록되는 작업 

• 각 트랜잭션의 시작과 끝
• 모든 데이터 수정 내용(삽입, 업데이트 또는 삭제).
• 모든 익스텐트 및 페이지 할당 또는 할당 취소
• 테이블이나 인덱스 만들기 또는 삭제
• 롤백 작업

트랜잭션 로그 파일에 대해 자동 증가를 설정할 경우 백분율 보다 크기로 설정하는 것이 좋음(전체 데이터 크기가 5TB 일 때 10% 자동 증가로 설정할 경우 5GB가 할당되는데 할당되는데 걸리는 시간이 크리티컬 할 수 있음).

트랜잭션 로그는 순환 파일구조.

End of logical log가 Start of logical log에 도달할 경우 2가지 상황이 발생

1. FILEGROWTH 설정에 맞춰 디스크에 여유 공간이 있는 경우 새 로그 레코드가 확장 부분에 추가됨.
2. FILEGROWTH 설정이 안되어 있거나 디스크에 여유 공간이 없는 경우 9002 오류가 발생.

 

로그백업

로그 백업은 전체 백업 후에 실시하는 것이 좋음.

로그 백업을 하면 백업 후에 트랜잭션 로그가 잘림.

 

CHECK POINT에는 성공적인 DB 롤백을 위해 MinLSN이 존재 해야함.

MinLSN : 검사점 시작의LSN, 가장 오래된 활성 트랜잭션 시작의 LSN, 배포 DB로 전달되지 않은 가장 오래된 복에 트랜잭션의 시작 LSN 중 최소값

 

CHECK POINT 가 발생하는 작업

• 명시적으로 실행된 경우
• 최소 로그 작업이 수행된 경우(대량 복사 작업)
• ALTER DATABASE를 사용하여 DB 파일을 추가 또는 제거한 경우
• SQL Server 인스턴스를 중지한 경우 또는 SHUTDOWN 문을 사용한 경우
• DB 복구에 걸리는 시간을 줄이기 위해 자동 검사점을 주기적으로 생성한 경우
• 백업 작업
• DB를 종료해야 하는 작업 후 AUTO_CLOSE 가 ON으로 설정되어있고 마지막 사용자 연결이 닫힌 경우 또는 DB를 재시작하는 경우

 

트랜잭션 잠금 및 행 관리 

분산 트랜잭션은 2단계 커밋(준비단계, 커밋단계)을 사용.

트랜잭션 처리 중 오류가 발생하면 SQL Sever에서 자동으로 롤백 후 트랜잭션에 보유 중인 리소스를 해제함.

일괄 처리에서는 런타임 오류 발생시 기본적으로 오류를 발생시킨 쿼리만 롤백하지만 SET_XACT_ABORT ON 으로 현재 트랜잭션이 롤백되도록 설정 가능.

 

격리 수준정의

 

격리 수준	설명
READ UNCOMMITTED	물리적으로 손상된 데이터만 읽지 않도록 트랜잭션을 격리하는 최하위 격리 수준. 이 수준에서는 더티 리드가 허용되므로 한 트랜잭션에서 변경한 아직 커밋되지 않은 내용을 다른 트랜잭션에서 확인 가능.
READ COMMITTED	트랜잭션에서는 처음 트랜잭션이 완료될 때까지 기다리지 않고 다른 트랜잭션에서 이전에 읽은 수정되지 않은 데이터를 읽을 수 있습니다. SQL Server 데이터베이스 엔진에서는 트랜잭션이 끝날 때까지 쓰기 잠금이 유지되지만(일부 데이터에서 적용됨) 읽기 잠금은 SELECT 작업이 수행되는 즉시 해제됩니다. 이 값은 SQL Server 데이터베이스 엔진 기본 수준입니다.
REPEATABLE READ	SQL Server 데이터베이스 엔진에서는 트랜잭션이 끝날 때까지 일부 데이터에서 획득되는 읽기 잠금 및 쓰기 잠금이 유지됩니다. 그러나 범위 잠금이 관리되지 않으므로 가상 읽기가 발생할 수 있습니다.
Serializable	트랜잭션이 서로 완전히 격리되는 최상위 수준입니다. SQL Server 데이터베이스 엔진에서는 일부 데이터에서 획득되는 읽기 잠금 및 쓰기 잠금이 유지되고 트랜잭션이 끝날 때 해제됩니다. 범위 잠금은 SELECT 작업에서 특히 가상 읽기를 방지하기 위해 범위가 지정된 WHERE 절을 사용할 때 필요합니다.

 

격리 수준에 따른 잘못된 읽기 가능여부

격리 수준	Dirty Read	Read Nonrepetable	Read Phantom
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshot	No	No	No
Serializable	No	No	No

 

LOCK 계층

리소스	설명
RID	행 식별자는 힙 내의 단일 행을 잠그는 데 사용됩니다.
KEY	인덱스 내의 행 잠금은 직렬화 가능한 트랜잭션에서 키 범위를 보호하는 데 사용됩니다.
PAGE	데이터 또는 인덱스 페이지와 같은 데이터베이스의 8KB 페이지입니다.
EXTENT	데이터 또는 인덱스 페이지와 같은 인접한 8개의 페이지 그룹입니다.
HoBT	힙 또는 B-트리입니다. 클러스터형 인덱스가 없는 테이블에서 힙 데이터 페이지나 B-트리(인덱스)를 보호하는 잠금입니다.
TABLE	모든 데이터와 인덱스가 포함된 전체 테이블입니다.
FILE	데이터베이스 파일입니다.
APPLICATION	애플리케이션이 지정한 리소스입니다.
METADATA	메타데이터 잠금입니다.
ALLOCATION_UNIT	할당 단위입니다.
DATABASE	전체 데이터베이스입니다.

 

잠금 호환성

	IS	S	U	IX	SIX	X
Intent shared (IS)	Yes	Yes	Yes	Yes	Yes	No
Shared (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intent exclusive (IX)	Yes	No	No	Yes	No	No
Shared with intent exclusive (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

 

큰 일괄 작업을 여러 개의 작은 작업으로 분할하여야 잠금 에스컬레이션을 방지할 수 있다.

EX)

DELETE FROM LogMessages WHERE LogDate < '2/1/2002' → 

 

SET ROWCOUNT 500

delete_more:

   DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

IF @@ROWCOUNT > 0 GOTO delete_more

SET ROWCOUNT 0

 

메모리 및 메모리 관리 아키텍처

버퍼캐시

데이터파일로부터 읽어드린 데이터를 담는 캐시영역.

디스크에서 읽을 때도 버퍼캐시에 적재한 후 읽음.

버퍼 상태

Free Buffer : 아직 데이터가 읽혀지지 않아 비어있거나 데이터파일과 서로 동기화되어 언제든지 덮어써도 되는 상태.

Dirty Buffer : 버퍼가 캐시된 이후 변경이 발생하지만, 아직 디스크에 기록되자 않아 데이터 파일 블록과 동기화가 필요한 버퍼 블록.

Pinned Buffer : 읽기 또는 쓰기 작업이 진행중인 버퍼 블록.

 

공유풀 : 딕셔너리 캐시와 라이브러리 캐시로 구성

딕셔너리 캐시 : 테이블, 인덱스 같은 오브젝트와 메타정보 저장

라이브러리 캐시 : SQL 실행에 관련된 모든 객체에 대한 정보, SQL에 대한 분석정보 및 실행계획 저장.

 

로그버퍼

Recovery를 위한 버퍼

DB버퍼에 가해지는 모든 변경 사항을 먼저 기록함.

 

메모리 할당 방식

SPA(단일 페이지 할당자) 는 SQL Server 프로세스에서 8KB 이하인 메모리 할당만 포함합니다.

다중 페이지 할당자(MPA) : 8KB 이상을 요청하는 메모리 할당입니다.

CLR 할당자: SQL CLR 힙 및 CLR 초기화 중에 생성되는 전역 할당을 포함합니다. (※ SQL CLR :  SQL Server 내에서 Microsoft .NET 공용 언어 런타임 엔진을 호스팅하기 위한 기술로  Microsoft SQL Server 환경에서 관리 코드를 호스팅하고 실행 SQL SERVER 2005에 도입 됨)

직접 Windows 할당(DWA) : Windows에 직접 요청된 메모리 할당입니다. 여기에는 Windows 힙 사용 및 SQL Server 프로세스로 로드되는 모듈에 의한 직접 가상 할당이 포함됩니다. 이러한 메모리 할당 요청의 예로는 확장 저장 프로시저 DLL의 할당, 자동화 프로시저(sp_OA 호출)를 사용하여 만든 개체 및 연결된 서버 공급자의 할당이 있습니다.

 

메모리 할당 유형	2012 이전	2012 이후
단일 페이지 할당	예	예, "임의 크기" 페이지 할당에 통합됨
다중 페이지 할당	예	예, "임의 크기" 페이지 할당에 통합됨
CLR 할당	아니요	예
스레드 스택 메모리	예	아니요
Windows에서 직접 할당	예	아니요

• 2005 : 32비트 버전의 운영체제 에서 실제 메모리를 4GB 이상 사용할 수 있도록 하는 AWE를 지원하여 최대 64G의 실제 메모리를 지원

 

SQL Server 메모리 제한을 따로 하지 않으면 최대 서버 메모리는 2,147,483,647(MB) 로 설정 됨.

물리적 메모리에 따른 권장 서버 메모리

Physical RAM (GB)	Max server memory (MB)	Physical RAM (GB)	Max server memory (MB)
2	1500	24	21500
4	3200	32	29000
6	4800	48	44000
8	6400	64	60000
12	10000	72	68000
16	13500	96	92000
24	21500	128	124000

 

메모리 크기 확인 방법

SELECT value,  minimum, maximum  
FROM sys.configurations   
WHERE name in ('min server memory (MB)','max server memory (MB)')

 

메모리 크기 설정 방법

EXEC SP_CONFIGURE 'MIN SERVER MEMORY (MB)', 0 
RECONFIGURE WITH OVERRIDE 
 
EXEC SP_CONFIGURE 'MAX SERVER MEMORY (MB)', 13500 
RECONFIGURE WITH OVERRIDE