찐빵의 IT World

SQL Server Database Engine 은 두 가지 주요한 구성요소가 있다.

1. Storage Engine

2. Query Processor (Relational Engine)

1. Storage Engine 은 데이터 읽기를 관여한다. 디스크와 메모리 사이에서 데이터 무결성을 유지하면서 그럭저럭 동시성을 최적화한다.

2. Query Processor 는 SQL Server 쿼리들의 최적의 실행을 고안하고, 그 계획을 실행하고, 결과를 반환한다.

Parsing and binding :

쿼리가 구문분석이 되고, 묶인다.

쿼리가 타당하면, 이 단계의 결과물은 논리적 Tree이고, 반드시 실행되어야 하는 쿼리의 논리적 운영을 보여주는 트리안의 각 노드 예를 들면, 특정 테이블을 읽는다던지, 조인을 수행한다던지

논리적 Tree 는

특

바인딩 단계에서

SQL Server 는 object name들이 존재하는지, 관련된 모든 parse tree에 있는 테이블이름과 컬럼이름이 system catalog상 일치하는지 확실히 한다. 이 단계의 결과물을 algebrized Tree라고 하며, 이 것이 Query Optimizer 에게 보내진다.

Procedure Cache(또는, Plan Cache라고도 함)는 Query Plan이 저장되는 메모리 공간으로 Query Plan이 재사용될 수 있도록 함으로써 쿼리문을 Compile하는데 드는 비용을 최소화하기 위해 사용됩니다. 일반적으로 SP(Stored Procedure) 사용을 권장하는 가장 큰 이유는 SP에 대한 Query Plan의 재사용을 통해 성능 향상을 기대할 수 있기 때문입니다. 반면에, Adhoc 쿼리문은 쿼리문 전체가 동일하지 않은 이상 Query Plan이 재사용되지 못하기 때문에(SQL Server에서 제공하는 '자동 매개변수화(Auto Parameterization)' 기능을 배제했을 때) 실행될 때마다 Compile 비용이 발생하여 성능에 악영향을 미치게 됩니다. 또한, Adhoc 쿼리문을 과도하게 사용하는 환경에서는 Adhoc 쿼리문에 대한 Query Plan들에 의해서 Procedure Cache의 크기가 일정 수준 이상으로 커지는 현상이 발생하기도 하며 이로 인해 사용 가능한 Buffer Pool 메모리 영역의 크기가 감소하여 SQL 서버 전체의 성능이 나빠지는 현상이 발생할 수 있습니다.
 
SQL Server 2005부터는 X64 버전도 지원되어 64-bit 환경이 보편화되었습니다(IA64 버전은 SQL Server 2000부터 지원되었습니다). 64-bit 환경에서는 메모리 주소 공간(Address Space)의 크기가 늘어남에 따라 32-bit 환경에서는 잘 발생하지 않던 현상이 발생하기도 하는데 그 중 대표적인 것이 Procedure Cache의 크기가 수 GB 이상으로 늘어나는 현상입니다(실제 사례 중에는 Procedure Cache의 크기가 10GB를 초과하는 사례도 있었습니다). 이런 현상에 대한 해결책으로 SQL Server 2005 Service Pack 2 이후에는 Procedure Cache의 최대 크기에 대한 제약을 강화하였으며 이를 통해 어느 정도의 성능 개선을 가져왔습니다. 하지만, 이는 완전한 해결책은 아니며 근본적으로 SQL Server를 최적화된 상태에서 운영하려면 Procedure Cache를 최대한으로 활용할 수 있도록 쿼리문을 작성하여 사용해야 합니다.
 
Procedure Cache로 인해 발생하는 성능 문제를 해결하기 위해서는 물론이고, 문제 발생을 미연에 방지하고 SQL Server를 최적화하기 위해 Procedure Cache의 구조와 동작 방식을 이해하는 것은 중요합니다. 특히, SQL Server 2005/2008의 Procedure Cache는 SQL Server 2000과는 현저하게 다르므로 SQL Server 2000의 Procedure Cache에 대해 알고 있었다 해도 새롭게 이해해야 할 필요가 있습니다. 그럼, SQL Server 2005/2008의 Procedure Cache에 대해서 살펴보도록 하겠습니다.
 
SQL Server 2005/2008에서 Procedure Cache 메모리 영역은 다음과 같이 크게 5개의 영역으로 세분화됩니다. 이 중 CACHESTORE_OBJCP와 CACHESTORE_SQLCP가 Compiled Plan(또는, Query Plan)이 저장되는 영역이며 Procedure Cache의 대부분을 차지합니다. CACHESTORE_OBJCP에는 Stored Procedure, Trigger, Function 등의 Compiled Plan이 저장되고 CACHESTORE_SQLCP에는 Adhoc query, Prepared query 등에 대한 Compiled Plan이 저장됩니다.

Procedure Cache 메모리 영역 
   CACHESTORE_OBJCP : Object Plans
   CACHESTORE_SQLCP : SQL Plans
   CACHESTORE_PHDR : Bound Trees
   CACHESTORE_XPROC : Extended Stored Procedures
   CACHESTORE_TEMPTABLES : Temporary Tables & Table Variables
 
CACHESTORE_SQLCP 영역에 Compiled Plan이 저장되는 쿼리문의 유형을 좀더 구체적으로 살펴 보면 다음과 같으며, 이 중 Auto-parameterized queries, sp_executesql procedure, Prepared queries에 대한 Compiled Plan은 'sys.dm_exec_cached_plans' DMV를 통해 조회했을 때 objtype이 ‘Prepared’로 표시되고 나머지 쿼리문 유형들에 대한 Compiled Plan은 objtype이 ‘Adhoc’으로 표시됩니다.

Procedure Cache 메모리 영역 안의
CACHESTORE_SQLCP 영역에 Compiled Plan이 저장되는 쿼리문의 유형 
1. Ad-hoc queries : 하나의 DML(SELECT, INSERT, UPDATE, DELETE)로 이루어진 쿼리
2. Auto-parameterized queries : SQL서버에서 제공하는 Simple Parameterization이나 Forced Parameterization의 적용을 받는 쿼리
3. sp_executesql procedure : sp_executesql을 사용하여 Parametrized된 쿼리
4. Prepared queries : OLEDB나 ODBC에서 제공하는 SQLPrepare/SQLExecute 기능이 사용된 쿼리
5. Batches : 여러 개의 쿼리문이 하나의 Batch로 실행되는 경우
6. Dynamic SQL : EXEC(...)을 통해 실행된 쿼리
 
'sys.dm_os_memory_clerks' DMV를 조회하면 다음과 같이 Procedure Cache를 구성하는 5개의 영역에 대한 메모리 사용 정보를 확인해 볼 수 있습니다. 이런 정보는 'dbcc memorystatus' 실행 결과나 성능 카운터를 통해서도 확인할 수 있으며 이 정보를 통해 메모리를 많이 차지하는 Procedure Cache의 영역을 확인할 수 있습니다.

 
Procedure Cache가 과도하게 커져서 성능 문제가 발생했을 때, SQL Server 2000에서는 'dbcc freeproccache' 명령을 통해 Procedure Cache 전체를 비우는 방법을 사용했었습니다. 이 방법은 Procedure Cache에서 사용하던 메모리를 반환하는 효과는 있었지만 유형에 관계없이 모든 Query Plan이 다시 만들어 져야 하기 때문에 급격한 CPU 사용량 증가 등의 부작용을 유발하였습니다. SQL Server 2005/2008에서는 Procedure Cache를 세분화된 영역별로 비울 수 있는 방법이 추가적으로 제공되어 좀더 유연하게 대처할 수 있습니다. 예를 들어, Adhoc이나 Prepared Query에 대한 Compiled Plan이 저장되는 CACHESTORE_SQLCP 영역을 비우고자 할 때에는 다음의 명령을 실행하면 됩니다.
 
   dbcc freesystemcache('SQL Plans')
 
SQL Server 2005/2008에서 Procedure Cache에 저장된 항목(Entry)들은 SQL Server가 메모리 부족을 경험하지 않는 이상 자동으로 지워지지 않습니다. 이런 이유로 물리적인 메모리가 큰 시스템(특히, 64-bit 시스템)에서는 Procedure Cache가 수십 GB 이상으로 커지는 현상이 발생할 수도 있었으며, 이로 인해 발생하는 성능 저하 현상을 예방하기 위해 SQL Server 2005 Service Pack 2 이후부터는 다음의 공식에 의해 계산된 값이 Procedure Cache의 Soft Limit 값으로 사용되도록 보완되었습니다. 예를 들어, SQL서버의 ‘max server memory’를 100GB로 설정한 시스템일 경우 Procedure Cache의 Soft Limit 값은 10.8GB(4*0.75 + 60*0.1 + 36*0.05)가 됩니다.  Soft Limit 값은 단어가 의미하는 것처럼 Limit 값을 절대로 넘을 수 없는 것(Soft Limit과는 다른 개념으로 Hard Limit이 존재합니다)을 의미하는 것은 아니며 Limit 이상에 도달했을 때 Limit 아래로 내려가도록 조절하겠다는 의미입니다. SQL Server 2005/2008에서는 Procedure Cache 메모리가 Soft Limit에 도달하게 되면 Clock Algorithm에 의해서 Procedure Cache에 저장된 항목들이 제거되고 이를 통해 Procedure Cache에 의해 사용된 메모리를 Soft Limit 이하로 낮추게 됩니다.
 
Procedure Cache Memory Soft Limit = 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB
 
SQL Server 2005/2008에서는 Procedure Cache의 크기를 조절하기 위해 Internal Clock Hand와 External Clock Hand의 두 가지 Clock Algorithm이 사용됩니다. Internal Clock Hand는 일반 사용자 Thread에서 쿼리문 실행을 위해 Procedure Cache를 접근(Lookup, Insert 모두)할 때 조건이 발생하게 되면 실행되며 CacheStore별로 한번에 하나의 Thread에서만 Internal Clock Hand가 실행됩니다. External Clock Hand는 Resource Monitor에서 조건이 발생하게 되면 모든 CacheStore에 대해서 실행되며 각 CacheStore별로 실행되는 Internal Clock Hand와 동시에 실행될 수 있습니다. Internal Clock Hand와 External Clock Hand가 실행되는 조건은 다르지만, 두 경우 모두 동일하게 Cache로부터 비용이 0인 항목(Entry)을 지우는 작업이 수행됩니다(SQL Server 2000에서는 Lazy Writer에 의해 동일한 기능이 수행되었습니다).
 
[Internal Clock Hand]
각 CacheStore 단위(CACHESTORE_OBJCP, CACHESTORE_SQLCP, CACHESTORE_PHDR, CACHESTORE_XPROC, CACHESTORE_TEMPTABLES 별로)에서 다음의 조건 중 하나가 충족되면
해당 CacheStore에서 실행됨.
- 특정 CacheStore의 항목(Entry) 수가 SoftQuota(SQL Server 내부적으로 각 CacheStore별로 정의되어 있는 값으로 64-bit의 경우 CACHESTORE_SQLCP에 대해서 160,036이 지정되어 있음)에 도달
- 특정 CacheStore가 Single Page Allocator(Buffer Pool)에서 할당 받은 Memory가 Procedure Cache의 Soft Limit값의 75%에 도달
- 특정 CacheStore가 Multi Page Allocator(MemToLeave)에서 할당 받은 Memory가 Procedure Cache의 Soft Limit값의 50%에 도달
 
[External Clock Hand]
SQL Server나 시스템에서 메모리 부족이 발생하거나 Procedure Cache 전체 메모리가 Soft Limit에 도달할 때 Resource Monitor(SQL Server의 메모리 사용량을 조절하는 시스템 Task)에 의해서 모든 CacheStore에서 실행됨.
 
'sys.dm_os_memory_cache_clock_hands' DMV를 조회해 보면 다음과 같이 Internal Clock Hand/External Clock Hand가 어떤 CacheStore에서 얼마나 실행되었는지를 확인해 볼 수 있습니다. 다음의 결과에서 External Clock Hand(HAND_EXTERNAL)는 전체 CacheStore에 대해서 실행되기 때문에 모든 CacheStore에 대해 항상 동일한 값을 갖게 됩니다.

Internal Clock Hand/External Clock Hand는 Procedure Cache의 항목을 지울 때 비용을 기준으로 지우게 되며 비용이 0인 항목들을 지우게 됩니다. Clock Hand가 실행될 때, Cache의 항목들을 체크하여 비용이 0인 항목은 제거하고 비용이 0보다 큰 항목은 1/2만큼 감소시킵니다. 1/2만큼 감소된 결과로 비용이 0이 된 항목들(즉, 기존의 비용이 1이었던 항목들)은 다음 번 Clock Hand가 실행될 때 제거 대상이 됩니다.
 
Compiled Plan에 대한 비용은 기본적으로 다음과 같이 Compiled Plan을 만드는 데 소요된 I/O, CPU, 메모리를 기준으로 정해집니다. Cost 값은 아래의 기준에 의해서 각 항목들에 대한 Tick 값을 합산하여 결정되는데, 최대 31의 Tick 값을 가질 수 있습니다. 계산된 Tick 값은 Compiled Plan에 대한 비용 값으로 저장될 때 ‘2의 n승(2n)’으로 계산되어 저장되므로 Compiled Plan은 최대 2147483648(231)의 비용 값을 가질 수 있습니다. 참고적으로, 아래의 기준으로부터 I/O 비용이 가장 큰 비중을 차지(최대 19 ticks)하고 CPU 비용이 그 다음의 비중을 차지(최대 8 ticks)하며 메모리 비용이 제일 작은 비중을 차지(최대 4 ticks)함을 알 수 있습니다.
 
Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost
- Two I/Os cost 1 tick, with a maximum of 19 ticks.
- Two context switches cost 1 tick, with a maximum of 8 ticks.
- Sixteen memory pages (128 KB) cost 1 tick, with a maximum of 4 ticks.
 
Compiled Plan이 Cache에 저장될 때 type에 따라서 최대 비용이 제한되게 되는데, Adhoc은 비용이 0으로 설정되고 Prepared는 비용이 최대 256으로 제한되며 Proc은 제한 없이 계산된 비용이 그대로 사용됩니다. 이렇게 type별로 비용의 최대값을 다르게 함으로써 Procedure Cache에서 Clock Hand에 의해 Compiled Plan이 지워질 때, Adhoc이 우선적으로 지워지고 Prepared와 Proc의 순서로 지워지도록 합니다.
 
Compiled Plan에 대한 비용 값은 original_cost 값과 current_cost 값으로 설정되는데, Compiled Plan이 Cache에 처음 저장될 때 앞에서 설명 드린 방식에 의해서 계산된 비용 값(original_cost)이 current_cost로 설정됩니다. Clock Hand가 수행될 때마다 current_cost 값은 1/2씩 감소하게 되고 Compiled Plan이 재사용(usecounts가 증가)되게 되면 current_cost 값은 다시 original_cost 값으로 설정됩니다. Adhoc Compiled Plan의 경우에는 Prepared나 Proc과는 달리 original_cost와 current_cost가 0으로 시작하게 되며 재사용될 때마다 original_cost가 1부터 시작하여 2배씩 증가하여 최대 16까지 증가(original_cost가 1, 2, 4, 8, 16의 순으로 증가)하게 됩니다. 따라서, Adhoc의 경우에도 재사용이 많이 되는 Compiled Plan은 original_cost가 16이 될 수 있으며 좀더 오랫동안 Cache에 머물 수 있게 됩니다.
 
다음의 쿼리문을 실행하면 Procedure Cache에 저장된 모든 Compiled Plan에 대한 상세 정보를 확인해 볼 수 있습니다.
 

SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count, 
           pages_allocated_count, original_cost, current_cost 
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address 
WHERE cacheobjtype = 'Compiled Plan' AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

지금까지 SQL Server 2005/2008의 Procedure Cache의 구조와 동작 방식에 대해서 살펴봤습니다. Procedure Cache의 동작 방식을 고려했을 때 Adhoc 쿼리문을 과도하게 사용하는 것은 SQL Server의 성능에 전혀 도움이 되지 않음을 알 수 있습니다. 따라서, 실행 횟수가 많은 쿼리문은 Stored Procedure를 사용하는 것을 우선적으로 고려하고 Stored Procedure 사용이 여의치 않을 경우에는 Prepared 쿼리문을 사용하는 것이 필요합니다. 간혹 Stored Procedure를 여러 개의 쿼리문이 하나의 Batch를 구성할 경우에만 사용하는 것으로 생각하여 단일 쿼리문은 Adhoc 쿼리문으로 처리하는 경우가 많은데 이는 잘못된 생각입니다. 단일 쿼리문이라고 하더라도 Stored Procedure로 처리함으로써 SQL Server의 성능 향상을 가져올 수 있다는 점을 명심하기 바랍니다.
 
SQL Server 2005/2008에서 Procedure Cache에 실행 계획이 저장/재사용/제거되는 방식에 대해서는 다음의 문서로부터 좀더 자세한 내용을 참고할 수 있습니다.
SQL Server 2008 Books Online - Execution Plan Caching and Reuse
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Plan Cache Internals

출처 : http://blog.daum.net/partsofmemory/28

 실행 계획 캐시(프로시저 캐시)

1. 정의

   SQL Server가 컴파일된 쿼리 실행 계획을 저장하는 저장소

   (성능 향상을 위해 재사용 목적으로 컴파일 된 실행 계획을 저장함)

2. 특징

   - 메모리에 저장

     SQL Server 인스턴스의 서비스 재시작 및 서버 재부팅시 초기화

   - 지속적 변경

     사용 빈도가 낮은 실행 계획 삭제됨

     저장된 실행 계획에 의해 사용되는 데이터베이스 개체 변경시, 해당 개체와 관련된 실행 계획도 삭제됨  

   - 모든 실행 계획이 저장되는 것은 아님

     RECOMPILE 옵션을 포함하는 실행 계획은 캐시에 저장되지 않음

     비용이 0인 실행 계획도 저장안함

   - SQL Server 2005 에서 동적 관리 개체(DMOs, Dynamic Management Objects)가 추가됨

     동적 관리 개체를 통해 실행 계획 캐시에 있는 실행 계획을 SHOW PLAN XML 형식으로 출력가능

3. DMV에서 실행 계획 캐시 확인시 주의사항

   - XQuery 관련 지식 필요

     실행 계획이 SHOW PLAN XML 형식으로 반환되기 때문   

   - 더티 리드 사용

     실행 계획 캐시에 대해 쿼리를 실행하는 경우 최소 트랜잭션 격리수준을 사용 권장

     (TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)

   - 부하 테스트 추천

     운영환경의 실행 계획 캐시 에 대한 쿼리 실행전 시스템 부하에 대한 테스트 권장

     (테스트 환경이 있을 경우 거기서 먼저 해보기 바람)

   - 자동 수집 금지

     실행 계획 캐시의 정보를 반환하는 쿼리는 많은 비용을 소모하기 때문에 자동으로 수집하는 Job을 생성하지 않은것이 좋음

4. 부록 (실행 계획 재사용에 영향을 주는 요소)

   - 저장 프로시저가 수행되는 데이터베이스

   - 트리거 실행 시, 영향 받는 행 수(inserted, deleted 테이블의 행 수 변경에 영향)

   - INSTEAD OF 트리거 ? 0행, 1행 모두 “1-plan” 공유

   - AFTER 트리거 ? 1행 “1-plan”, 0행 or n행 “n-plan” (n>1)

   - Bulk Insert문 실행 계획 캐싱 안됨

   - 8KB 이상의 일괄 처리 명령은 캐싱 안됨

   - SET 옵션

   - 테이블의 소유자 지정 여부

   - sys.syscacheobjects 의 uid

   - CREATE PROCEDURE … WITH RECOMPILE 옵션

   - EXEC (프로시저 명) WITH RECOMPILE 옵션

   - 데이터 변경으로 인한 통계 갱신 발생

   - 임시 테이블 사용시 통계 갱신 발생

   - 오브젝트의 스키마 변경으로 인한 재컴파일

   - 데이터베이스 수준의 변경 작업

   - 재컴파일 임계값 (통계 정보 갱신)

     : 일반 테이블(RT : Recompile Threshold)

       Row수가 500을 넘지 않을 경우 500 Row 이상 변경시

       Row수가 500을 넘을 경우 500 + 전체 Row의 20% 변경시

     : 임시 테이블

       Row수가 500을 넘지 않을 경우 500 Row 이상 변경시

       Row수가 6을 넘을 경우 6 + 전체 Row의 20% 변경시

     : 테이블 변수

       통계 갱신으로 인한 재 컴파일이 발생하지 않음

출처 : http://kalva.tistory.com/entry/실행-계획-캐시프로시저-캐시

행의 이동(Forward Record)(Forwarded Record)

행이 이동하는 경우 : 힙(Heap)에서 가변 컬럼을 현재 값보다 더 큰 크기의 값으로 갱신할 경우

이 때 기존의 페이지에는 더 커진 행의 값에 대한 공간이 없어서 새로운 값을 넣을 수 없으므로 새로운 행을 다른 페이지에서 할당 받아 값을 넣고 기존의 행에서 이 새로운 행을 가리키는 포워딩(forwarding)을 하게 된다. 포워드 된 행은 DBCC PAGE 명령으로 보면 FORWARDED_RECORD 라고 나타나고, 원래 그 행이 있던 자리에는 새로운 포워드 된 행을 가리키는 FORWARDING_STUB 이 나타난다.

"AA"  ->  "kkkkkk"  ->  "ffffffffffffffffffff" 의 경우 "AA" 가 직접 "fffffffffffffff"를 가리키도록 바꾼다.

언제 제자리로 돌아올까?

1. "ffffffffffffffffff" 가 다시 "AA"로 줄어들면 된다. 이동 전의 크기 혹은 그 이하로 줄어들 때

2. DBCC SHRINKDATABASE (책에 될 때도 있고, 안될 때도 있으며 정확히 모르겠다고 함)

3. 클러스터드 인덱스가 없기 때문에 생기는 것이므로 클러스터드 인덱스를 만든다. (좋은 해결책 이라고 함)

 Ref . server 2000/2005 튜닝 / SQL Server 완벽 튜닝 - 정원혁, 손광수 공저. 대림 출판사. 148p

Ghost Record

클러스터드 인덱스에서 행의 이동이 일어나는 경우 Ghost Record가 일어난다.

페이지 분할

데이터에 행을 힙(heap)에 삽입할 때에는 PFS 페이지의 정보를 이용해서 빈 페이지를 찾으므로 페이지 분할이 발생하지 않는다

페이지 분할

- 키 순으로 데이터를 정렬해야 하는 클러스터 인덱스에 행을 삽입할 때 페이지 분할이 발생할 가능성이 있다.

- 페이지 분할이 발생할 때 페이지의 절반은 비어있는 새 페이지로 이동된다. 이 때, 같은 익스텐트에서 새 페이지를 먼저 찾고, 없을 경우 새로운 익스텐트를 할당 받는다(GAM, SGAM 을 이용)

- 일단 페이지가 분할되면 롤백을 하더라도 분할 상태가 그대로 유지된다.

1. 자동 증가(Auto Growth) - 성능 하락

데이터베이스의 파일(데이터 및 로그 파일) 단위로 설정되는 옵션, 모든 Edition에서 기본적으로 설정된다.

이 옵션은 성능에 나쁜 영향을 미친다. 이유 : 파일이 증가할 때 증가되는 부분을 모두 0으로 채우는 초기화 작업(zero-out)을 거치므로. 게다가 간헐적으로 증가하다 보면 연속적인 공간사용을 못하게 되어 여기저기 조각이 나게 된다. 따라서 순차적 읽기 대신 랜덤 액세스를 하게 되어 성능이 저하.

EX) 아무 옵션 없이 데이터베이스를 생성하면 1MB로 시작해서 10%씩 자동 증가되도록 설정

SOL) FILEGROWTH 옵션을 0 으로 지정하여 자동증가 해제, DBA가 부하가 덜 한 시간에 ALTER DATABASE 문을 사용하여 파일 크기를 키우던가 파일을 추가해 준다.

저의 회사에서는 사용 & Fixed 용량으로 자동증가를 시켜 놓았다. (% 증가보다 Fixed 증가가 더 좋기 때문)

2. 자동 축소(Auto Shrink) - 성능 하락

데이터베이스의 autoshrink 옵션을 TRUE로 설정하면 주기적으로 데이터베이스 파일의 축소가 발생. SQL Server의 Personal 및 Desktop 판에서 이 옵션이 기본적으로 설정.

* 자동 축소 될 때 DB 잠금이 발생. 데이터 이동에 따른 디스크 I/O 가 발생하여 성능 저하.

3. 자동 닫기(Auto Close) - 성능 하락

- SQL Server의 Personal 및 Desktop 판에서 이 옵션은 기본적으로 설정된다.

- 데이터베이스의 Autoclose 옵션을 TRUE로 설정하면 이 데이터베이스에 대한 마지막 사용자가 연결을 끊을 때 데이터베이스를 메모리에서 내리고, 사용자가 다시 접속할 때 데이터베이스를 메모리로 다시 올린다.

메모리에서 내릴 때에 디스크와 동기화 안된 페이지(Dirty Page)를 기록 하느라 디스크 I/O 발생.

메모리에서 올릴 때에 디스크에서 메모리로 올리면서 디스크 I/O 발생.

4. 통계 자동 생성 및 갱신 - 성능 증가

Auto create statistics

Auto update statistics 옵션으로 설정한다.

1. 시스템 구조

SQL 서버의 시스템 아키텍쳐

1. 논리적 데이터베이스

데이터베이스의 테이블, 뷰, 저장 프로시저 등

2. 물리적인 데이터베이스

데이터베이스의 파일

1. 주 데이터 파일 (Primary Data Files, MDF)

기본적인 데이터 구조 및 데이터 들이 저장

2. 보조 데이터 파일 (Secondary Data Files, NDF)

여러개의 디바이스 파일을 운영할 때 보조 데이터 파일

목적 : 데이터 I/O를 병렬적으로 수행하여 I/O 병목현상 줄이고, 수행 속도 증가

3. 로그 파일 (Log Files, LDF)

데이터베이스를 복구하는데 필요한 정보를 저장

각데이터베이스마다 반드시 있어야 한다.

TIP : 주 데이터 파일의 경로 알아내기

(Master 데이터베이스의 Sysdatabases 검색)

Select * from SYS.DATABASES WHERE name='데이터베이스이름'

파일을 백업 복사하여 다시 첨부 하는 것만으로 그 당시까지의 데이터와 로그를 다시 불러올 수 있다.

이러한 작업에 사용되는 프로시저 = sp_attach_db , sp_attach_single_file_db

2. 데이터 파일 구조

1. 페이지(Page)

데이터를 저장하고 처리하는데 사용되는 가장 기본적인 입출력 단위

페이지 헤더(96바이트)(페이지의 종류, 페이지의 여유공간정보, 페이지가 저장하는 데이터의 오브젝트ID) + 8096바이트 로 구성

- 데이터 파일 앞부분의 특수 페이지들

파일헤더 | PFS | GAM | SGAM | BCM | DCM | ...

* PFS (Page Free Space) - http://blog.naver.com/jumdol625/110169523098

- 페이지의 할당 여부 및 각 페이지에 있는 빈 공간의 크기를 기록하는 페이지

- 각 페이지에 1바이트를 사용하여 페이지의 할당 여부와, 페이지의 남은 공간을 기록

- 힙, 텍스트, 이미지 페이지에 대해서만 페이지 내의 빈 공간의 양이 관리 

- 새로 삽입된 행을 보관할 수 있는 빈 공간이 있는 페이지를 찾아야 할때 사용 

- GAM, SGAM, PFS를 이용해서 사용할 페이지 검색 

- 인덱스의 경우에는 새 행을 삽입할 지점이 인덱스 키 값에 의해 설정되므로 페이지의 빈 공간을 추적할 필요가 없음 

- 8088개의 페이지 관리, 8088개가 넘어가면 PFS페이지를 추가

* GAM (전역 할당 맵) : 어떤 익스텐트가 할당 되었는가

* SGAM (공유 전역 할당 맵) : 어떤 익스텐트가 혼합익스텐트인지, 빈페이지가 있는지, 4GB 관리.

http://blog.naver.com/jumdol625/110168581449 - GAM, SGAM 자세히 나와있는 곳

* BCM (Bulked Change Map)(대량 복사 변경 맵) : Backup Log 문 이후 대량 기록작업에 의해 수정된 익스텐트를 관리

* DCM (Differential Change Map)(차등 변경 맵) : 마지막 BACKUP DATABASE 문(FULL BACKUP) 이후에 변경된 익스텐트를 관리하는 페이지, 차등 백업의 대상 익스텐트를 찾기 위해 사용. 4GB 관리.

2. 익스텐트(Extent) : 8개의 연속된 페이지가 모여 이룬 단위

1. 혼합 익스텐트

8개의 페이지에 각각 다른 오브젝트의 데이터를 저장할 수 있다.

2. 단일 익스텐트

8개의 페이지에 모두 같은 오브젝트를 저장할 수 있다.

테이블이나 인덱스가

8 Page보다 커지기 전까지는 Mixed로 저장된다.(다른 개체에서도 추가되는 작업이 있다면 위의 그럼처럼 떨어져서....)

8 Page보다 커지는 순간

9번째 Page가 저장될때는 Uniform으로 8 Page의 공간이 할당되고 그 Extent에는 해당 객체만 들어갈수 있다!

처음부터 Uniform으로 8 KB씩 할당하지 않는 이유 : 데이터베이스가 많은 작은 테이블을 보유하고 있을때 

공간들이 낭비되지 않게하기 위해서이다. (공간을 효율적으로 사용!)

3. 로그 파일 구조

- 데이터 파일처럼 익스텐트로 구성 X, 가상 로그 파일(Virtual Log File, VLF)이라는 저장 단위로 구성.

- VLF는 트랜잭션 로그의 잘라내기 단위가 된다.

- 로그 파일은 Circular queue 방식으로 순차적으로 저장이 되기 때문에 여러개의 파일로 분할하여도 성능향상은 기대할 수 없다. Circular queue 방식의 경우 데이터가 최근 저장된 순서로 다시 인출될 수 있으므로 트랜잭션의 개념에 가장 잘 부합된다.

- Transaction Log : UPDATE/DELETE/INSERT 등과 같은 작업 시에 기록되는 로그, 임의의 시점에 Checkpoint가 발생하면 Physical database에 기록

- 가상 로그 파일의 성능 : 파일이 작고 많을 경우 성능을 저하시킨다.

- 가상 로그 파일 변경 방법

1. BACKUP LOG 문을 사용하여 로그 백업을 수행.

2. CHECKPOINT 문을 사용하여 메모리와 디스크를 동기화.

  DBCC SHRINKFILE( logfilename, TRUNCATEONLY ) 를 사용하여 트랜잭션 로그 파일 축소.

  Sp_helpdb 데이터베이스명  으로 logfilename을 알 수 있다.

3. 데이터베이스 변경을 하여 로그 파일 크기를 수정(충분히 크게 늘려서 가상로그 파일의 크기가 크고, 개수가 적도록 한다. 증가값 역시 충분히 크게 잡는다.) 구문은 다음과 같다.

ALTER DATABASE dbname

 MODIFY FILE ( NAME = file_name, SIZE = new_size, FILEGROWTH = XXMB )

4. 데이터 페이지의 구조

text, ntext, image의 데이터 형태를 제외한 모든 데이터 로우들이 저장된다.

페이지 헤더에는 전페이지와 후페이지의 관계에 대한 포인터를 가지고 있다.

Offset은 각 Row가 시작되는 위치(시작되는 바이트가 페이지의 시작에서 얼마나 떨어져 있는지)를 알려준다.

5. 데이터 페이지의 동작 원리 

1. 클러스터드 인덱스 테이블(Clustered Index Table)

데이터 페이지들이 순서대로 정렬이 되어 저장되어 있는 상태. 따라서 각 테이블에 클러스터드 인덱스는 1개만 존재가능.

2. 넌 클러스터드 인덱스 테이블(Non-Clustered Index Table)

3. 데이터페이지의 관리

클러스터드나 넌클러스터드 인덱스 모두 B-Tree(Balanced Tree)구조를 가진다.

인덱스 된 뷰(Indexed View)는 클러스터드 테이블과 동일한 저장구조를 가진다.

* 모든 테이블, 인덱스, 인덱스 된 뷰 는 SYSINDEXES 테이블에 페이지 포인터(Page Pointer)를 가지게 된다. SYSINDEXES 테입블은 각 인덱스에 존재. SYSINDEXES 테이블에는 각 객체들에 대한 ID와 인덱스에 대한 ID인 INDID가 존재

이를 조회하는 쿼리

SELECT ID, INDID, FirstIAM FROM SYSINDEXES

이를 조회했을 때

1. INDID = 0  : 클러스터드 인덱스가 아닐 때. 순서 없이 저장된 테이블 = 이를 힙(Heap)이라고 한다. 이러한 힙 형태의 정보를 찾기 위해서는 SYSINDEXES.FirstIAM을 통해 데이터를 검색한다.

- 데이터 페이지 검색 방법 : SYSINDEXES의 FirstIAM 컬럼의 주소 값을 기준으로 첫 번째 IAM 페이지를 찾아간다. 이 IAM 페이지를 기준으로 익스텐트를 찾고 IAM 페이지에 있는 익스텐트 들을 모두 스캔했으면 다음 IAM페이지로 가서 또 스캔한다.

2. INDID = 1 : 클러스터드 인덱스. SYSINDEXES.Root 컬럼에는 클러스터드 인덱스의 루트 인덱스 위치 값이 존재

- 데이터 페이지 검색 방법 : 데이터가 정렬되어 있으므로 B-Tree를 검색하면 원하는 데이터 페이지 바로 검색 가능

3. INDID = 2~250 : 넌클러스터드 인덱스. SYSINDEXES.Root 컬럼에 넌클러스터드 인덱스의 루트 인덱스 위치 값이 존재

- 데이터 페이지 검색 방법 : SYSINDEXES의 개체 아이디가 일치하는 것을 검색한 후에 Root 컬럼 값을 찾아내면 해당 넌클러스터드 인덱스의 루트 노드를 가리키는 주소 값을 찾을 수 있다. 동일하게 B-Tree를 검색하면 원하는 데이터 페이지의 주소를 검색할 수 있다.

4. INDID = 255 : 텍스트나 이미지 형태. 각 테이블당 하나의 텍스트 및 이미지 컬럼을 가질 수 있다.

4. IAM (Index Allocation Map Page)

힙이나 인덱스 된 형태, 텍스트와 이미지 등 모든 개체의 익스텐트들을 관리

IAM 페이지에는 익스텐트의 시작 지점을 가리키는 헤더 정보를 갖고있다.

개체가 커져서 하나의 IAM으로 관리할 수 없을 떄는 연결 리스트 구조로 새로운 IAM이 추가된다.

IAM 때문에 한 데이터베이스에 파일이 여러개 일 때 DBCC SHOWCONTIG를 하면 약간의 왜곡이 생긴다.

* SQL 서버가 새로운 데이터를 데이터 페이지에 할당할 때

- 먼저 IAM페이지를 통해 개체에 할당된 익스텐트를 참조하여 익스텐트가 비어있지 않다면, PFS 페이지 정보를 검색하여 새로운 데이터를 저장. 이때 IAM이나 PFS 페이지 모두 메모리 버퍼 풀(Memory Buffer Pool)에 상주하여 매우 빠른 속도로 검색된다) 그리고 이러한 익스텐트에 대한 검색은 데이터를 삽입할 때 충분한 공간이 존재하지 않았을 때만 발생.