데이터베이스 백업과 복원 작업은 데이터베이스 속성인 복구 모델 설정에 영향을 받으며 복구 모델이란 데이터 베이스의 트랜잭션 로그를 관리하는 방법의 유형이다.
복구 모델은 단순(Simple), 전체(Full), 대량 로그(Bulk Logged) 3가지가 있다.
※ 데이터베이스 복구 모델 확인 스크립트
SELECT Name,
(SELECT DATABASEPROPERTYEX(name,'RECOVERY') AS RecoverModel
FROM master..sysdatabases
ORDER BY Name
(1) 단순 (Simple)
체크포인트가 발생할 때마다 트랜잭션 로그가 비워지는 모델.
트랜잭션이 보관 되지 않으므로 트랜잭션 로그백업을 지원하지 않으며, 복원 시 전체 또는 차등 백업한 시점까지만 복원 할 수 있고 원하는 특정 시점이나 페이지 복원은 불가능 하다.
이에 트랜잭션 로그의 저장소 공간은 작지만 마지막 전체, 차등백업 이후 데이터는 손실 될 수 있다.
테스트 환경 또는 데이터 베이스 용량이 적을때 많이 사용한다.
단순 복구 모델 사용 시 데이터 손실을 방지하기 위해서 주기적인 전체 또는 차등 백업을 권장.
(2) 대량로그 복구 모델(Bulk Logged)
BCP, Bulk INSERT, SELECT INTO , CREATE INDEX, DBDCC DBREINDEX , DROP INDEX 등 대량의 변경 작업을 처리하는 대량(Bulk) 명령을 수행 할 때, 발생하는 모든 트랜잭션 로그를 기록하지 않고 최소한의 로깅을 제공하여 전체 복구 모델에 비해 빠른 성능을 가지고 있다.
(3) 전체 복구 모델(Full)
대량 작업, 인덱스 생성 정보까지 모든 트랜잭션을 기록하는 모델이다.
SQL SEVER에서 지원하는 모든 백업 유형을 사용할 수 있다. 특정 시점 복원이나 데이터 손실을 방지 할 수 있으나 그만큼 용량과 비용이 많이 발생된다.
SQL Server의 인덱스는 기본적으로 B-Tree(정확히는 B+Tree) 구조를 가진다.
최상위 레벨 Root Level , 최하위 레벨 Leaf Level, 중간 레벨 NonLeaf Level 각 레벨 페이지들은 더블 링크드 리스트로 연결 된다.
(2) 힙, 클러스터드 인덱스, 비클러스터드 인덱스
- 힙 : 테이블에 클러스터드 인덱스가 없으면 힙이다. 데이터는 추가된 순서대로 쌓이게 된다.
- 클러스터드 인덱스 : 데이터가 클러스터드 인덱스의 키로 정렬이 된다. 테이블 자체가 B-Tree 인덱스 구조를 이룬다.
테이블은 하나의 순서로만 정렬 가능하므로 클러스터드 인덱스는 한개만 가능하다.
- 비클러스터드 인덱스 : 테이블 구조가 변경 되는 것이 아니고, 테이블과 별개로 인덱스 키로만 구성된 별도의 B-Tree 인덱스 영역이 새로 생긴다.
SQL Server 2005 까지는 349개, 2008 부터는 999개 까지 가능하다.
비클러스터드 인덱스는 생성된 테이블의 구조에 따라 아래와 같이 조금 차이가 있다.
* 힙 테이블에서 비클러스터 인덱스 생성한 경우
=> 인덱스 페이지는 데이타의 위치를 가르키는 RID 값을 가지고 있고 이 RID 값으로 힙 영역에서 인덱스 레벨이 아닌 컬럼을 검색한다.
이것이 바로 RID Lookup이다.
* 클러스터드 인덱스가 있는 테이블에서 비클러스터드 인덱스를 생성한 경우
=> RID가 아닌 클러스터드 인덱스의 키가 기록된다. 그래서 이 넌클러스터드 인덱스로 데이터를 찾을 경우에는 넌클러스터드 인덱스의 리프레벨에서 클러스터드 인덱스의 키를 확인한 후 이 키를 이용해서 데이터를 찾는 "Key Lookup"이 발생합니다.
* Lookup
Lookup은 인덱스 페이지의 주소값을 이용한 랜덤 액세스 방식으로 실제 I/O 비용이 많이 발생하므로 주의하여 작성해야함.
(3) Index Scan과 Index Seek
- Index Scan
An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.
인덱스 Scan은 맞는 인덱스를 찾기 위해 전체 인덱스를 읽는 것. 소요 시간은 인덱스 크기에 비례한다.
테이블 전체 또는 인덱스의 리프 레벨을 모두 읽는다는 것.
- Index Seek
An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records - time taken is only proportional to the number of matching records.
인덱스 Seek는 B-Tree 구조에 맞는 인덱스를 바로 찾는 것. 소요시간은 일치하는 레코드의 수.
인덱스 트리를 타고 단 하나의 값을 찍어 검색하는 방식. 인덱스를 탄다. 루트->중간->리프로 Seek
In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
Note however that in certain situations an index scan can be faster than an index seek (sometimessignificantlyfaster) - usually when the table is very small, or when a large percentage of the records match the predicate.
(4) 인클루드 인덱스, 필터드 인덱스, 커버드 인덱스
- 인클루드 인덱스
인클루드 인덱스는 비클러스터드 인덱스 생성 시 인덱스 키가 아닌 컬럼도 리프 레벨에 같이 추가하는 것이다.
SQL Server 2005부터 가능하며 INCLUDE 절로 생성.
장점은 키가아닌 컬럼도 인덱스 수준에서 검색이 가능하므로 Lookup을 하지 않아 쿼리 성능이 향상되며, 리프 레벨에만 구성하므로 인덱스 크기에는 영향이 미미하다.
- 필터드 인덱스
SQL Server 2008부터 가능하며 특정 집합, 어떤 조건에 대해서만 인덱스를 생성한다.
인덱스 생성 시 where 조건을 주어서 특정 조건을 가진 인덱스이다.
장점은 인덱스 사이즈가 줄어들고 쿼리성능 향상, 인덱스 관리 비용이 줄어든다.
- 커버드 인덱스
커버드 인덱스는 쿼리에 사용된 모든 컬럼이 인덱스로 구성되었을때 그 인덱스를 커버드 인덱스라고 한다.
대용량 데이터베이스가 장애로 인해 전체 데이터베이스 복원을 해야한다면 소요시간이 길어지므로 파일 그룹이 나눠져있다면 파일그룹별로 백업을 하면 좀 더 빠르게 복원이 가능하다. 복원 시 PRIMARY 파일 그룹을 가장 먼저 복원하고 그 다음 파일 그룹을 순차적으로 복원 가능.
FILEGROUP = 옵션 지정으로 특정 FILEGROUP만 백업
1
BACKUP DATABASE OriDataBase FILEGROUP ='PRIMARY' TO DISK ='C:\SQL\OriDataBase_FILEGROUP.bak'
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.
로그파일이 큰사이즈로 많이 작은 증가? 이면 가상 로그 파일이 많이 생성됩니다.이로 인해 데이터베이스 시작뿐 아니라 로그 백업 및 복원 작업이 느려질 수 있습니다.반대로 로그 파일이 적거나 하나의 증분인 큰 크기로 설정된 경우 적은 수의 매우 큰 가상 로그 파일이 포함됩니다.
복제는 한 데이터베이스에서 다른 데이터베이스로 데이터와 데이터베이스 개체를 복사 및 배포한 다음 데이터베이스 간에 동기화를 수행하여 일관성을 유지하는 일련의 기술
(1) 스냅샷 복제 - 많은 양의 데이터가 변경되지만 자주 변경되지는 않을 때 가장 적합. 단독으로 사용할 수 있지만 게시에서 지정한 모든 개체 및 데이터의 복사본을 만드는 스냅샷 프로세스는 일반적으로 트랜잭션 및 병합 게시에 대한 초기 데이터 및 데이터베이스 개체 집합을 제공하는 데에도 사용
(2) 트랜잭션 복제 - 스냅샷 복제로 데이터 동기화 이후, 데이터 변경과 스키마 변경을 로그 판독기 에이전트가 수집하여 배포 DB에 기록하면 구독자에게 반영. 실시간 동기화가 된다. 일반적으로 확장성 및 가용성 향상, 데이터 웨어하우징 및 보고, 여러 사이트의 데이터 통합, 다른 유형의 데이터 통합, 일괄 처리 작업 오프로드 등을 포함하여 높은 처리량이 필요한 서버 간 시나리오에서 사용.
(3) 병합 복제 - 게시자와 구독자의 데이터 변경과 스키마 변경내용을 트리거로 추적해서 변경 추적 테이블에 기록하고, 그 내용을 병합해서 게시자와 구독자에 반영. 관리가 다소 복잡. 주로 데이터 충돌 가능성이 있는 모바일 애플리케이션이나 분산 서버 애플리케이션에 사용됩니다.일반적인 시나리오에는 모바일 사용자와 데이터 교환, 소비자 POS(Point of Sale) 애플리케이션, 여러 사이트의 데이터 통합 등이 있음.
(4) 피어 투 피어 복제 - 노드(Node)라고 하는 여러 SQL Server 인스턴스로 DB 복제본을 유지 관리. 확장성과 고가용성 이점. 모든 데이터를 복제. 거의 실시간으로 일관되게 전파.
동적 관리 뷰 및 함수는 서버 인스턴스 상태 모니터링, 문제 진단 및 성능 튜닝에 사용할 수 있는 서버 상태 정보를 반환합니다.
동적 관리 뷰 또는 함수를 쿼리하려면 개체에 대한 SELECT 권한과 VIEW SERVER STATE 또는 VIEW DATABASE STATE 권한이 필요합니다.
- 동적관리뷰 개수는? : 아래 쿼리를 활용하여 확인해보니 총 273개입니다.
SELECT N'SYS.' + NAME AS NAME, TYPE_DESC
FROM SYS.SYSTEM_OBJECTS
WHERE NAME LIKE N'DM[_]%'
ORDER BY NAME
모두를 알기는 불가능하니, 자주 쓰는 DMV에 대해서 알아보겠습니다.
(1) sys.dm_exec_query_stats - 캐시된 쿼리 계획에 대한 집계 성능 통계
ex - 평균 CPU 시간별로 상위 5개의 쿼리 찾기
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
(2) sys.dm_exec_sql_text(sql_handle) - 지정 된sql_handle에 의해 식별 되는 SQL 일괄 처리의 텍스트를 반환
- text 컬럼에 SQL 쿼리 실행 텍스트가 나옴.
SELECT B.text,*
FROM sys.dm_exec_query_stats AS A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B
ORDER BY A.creation_time DESC
(3) sys.dm_exec_procedure_stats - 저장 프로시저에 대한 집계 성능 통계
- 평균 경과 시간으로 식별된 상위 10개의 저장 프로시저 찾기 예제
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;
(4) sys.dm_exec_requests - 실행 되는 각 요청에 대한 정보를 반환
(5) sys.dm_exec_query_plan - 계획 핸들로 지정한 일괄 처리에 대한 XML 형식의 실행 계획을 반환
