MS-SQL7.0 Implementation 강의 노트 Written by 남현주 Written by 남현주

제6장 인덱스 계획 및 생성 인덱스의 기본 개념 인덱스은 데이터를 찾을 때 빨리 찾기 위해서 사용한다. 인덱스가 없다면 특정한 값을 찾기 위해 모든 데이터 페이지를 다 뒤져야 한다. 이것을 table scan이라고 한다. 인덱스가 찾고자 하는 컬럼이나 표현식에 대해 존재하고, 인덱스를 사용하는 것이 더 효과적이라면, SQL서버는 모든 페이지를 뒤지지 않고 인덱스 페이지를 찾아서 쉽게 데이터를 가져온다. 이것을 Index Serch라고 한다.

(인덱스의 유용성) 데이터 정렬, 그룹단위로 계산함수 사용, 유일성 검사 * 인덱스 설정시 고려사항 .만드는데 시간이 걸린다.   * 인덱스 설정시 고려사항 .만드는데 시간이 걸린다. .만드는데 많은 공간이 필요하고, 만들고 난 후에도 추가적인 공간이 필요하다. .데이터를 수정하는 시간은 오히려 더 많이 걸린다.

(기본문법) * 인덱스를 하지 말아야 할 컬럼 . 좀처럼 검색되지 않는 컬럼 . 전체 중 상당 부분을 가져오는 질의에 사용되는 컬 럼 . 유일성, 또는 같은 값이 많은 컬럼들 . SELECT 속도보다 데이터의 변경 속도가 훨씬 중요 할 때는 되도록 인덱스의 수를 최소화한다.   (기본문법) 예) CREATE INDEX mytable_PK ON mytable (id)

(인덱스의 종류) 클러스터 인덱스와 넌클러스터 인덱스   인덱스는 B-Tree(Balanced Tree)구조로 되어 있다. 제일 하위 레벨을 리프 레벨(leaflevel)이라고 부른다 클러스터 인덱스는 1.  물리적인 행의 순서가 인덱스의 순서와 동일하다 2. 인덱스의 맨 마지막 단계인 리프 레벨이 곧 데이터 페이지이다. 3.  오직 하나의 클러스터 인덱스를 가진다.

4. 클러스터 인덱스는 테이블 내에서 행들의 물리적. 정렬 순서를 변화시키기 때문에 넌클러스터 인덱스를 4.  클러스터 인덱스는 테이블 내에서 행들의 물리적 정렬 순서를 변화시키기 때문에 넌클러스터 인덱스를 생성하기 전에 반드시 클러스터 인덱스를 먼저 생성 해야 한다. 5.   클러스터 인덱스 내의 키 값은 고유해야 한다. 인덱스를 생성할 때 UNIQUE 키워드를 사용하지 않 으면 SQL 서버는 중복되는 값을 갖는 행들에 고 유한 내부 식별자를 붙여서 유일성을 암시적으 로 유지한다. 6. 클러스터 인덱스의 평균 크기는 테이블 크기의 약 5% 7.  인덱스를 생성하는 과정에서 SQL 서버는 테이블을 복사하고 정렬하기 위해 데이터베이스 내의 공간 을 임시적으로 사용하게 된다. 인덱스가 만들어 질 때 테이블 크기의 12.배 정도가 작업 공간으로 사용된다.

넌클러스터 인덱스는 1. 물리적인 행의 순서가 인덱스 순서와 동일하지 않다. 2.   리프 레벨은 데이터 페이지가 아니다. 3.   SQL 서버의 디폴트이다. 4. 테이블 당 넌클러스터 인덱스는 249개까지 만들 수 있다. 5. 클러스터 인덱스가 테이블에 정의되어 있지 않는 한 SQL 서버는 힙에 데이터 페이지를 유지한다.

-> 힙에서의 넌클러스터 인덱스 구조 힙(클러스터 인덱스가 없는 테이블)의 최상위에서 넌클러스터 인덱스가 생성될 때 SQL 서버는 해당 데이터 페이지 내의 행을 가리키는 인덱스 페이지 내의 행 식별자를 사용한다. 행 식별자는 데이터 위치 정보를 저장하고 있다. 힙은 IAM(Index Allocation Maps) 페이지를 사용하여 유지가 된다. IAM페이지는 힙에 할당된 익스텐트의 주소를 담고 있다. IAM페이지 안에 있는 데이터 페이지와 행들은 특정한 순서가 없으며 서로 링크로 연결되어 있지도 않다. 데이터 페이지들 간의 유일한 논리적 연결은 IAM페이지 내에 기록되어 있는 것 뿐이다. 페이지들이 링크로 연결되어 있지 않기 때문에 힙에서 페이지 분할은 절대 일어나지 않는다.

-> 클러스터 인덱스를 가진 테이블 상에서의 넌클러스터 인덱스의 구조 데이터 페이지의 행들을 가리키기 위해 인덱스 페이지 내의 클러스터 인덱스 키를 사용한다. DROP INDEX문을 사용해서 기존의 클러스터 인덱스를 삭제한 경우, 클러스터 인덱스를 생성한 경우, DROP_EXISTING 옵션을 사용하여 클러스터 인덱스 컬럼의 정의를 변경했을 경우, 넌클러스터 인덱스는 자동으로 재구성된다. (참고 : 책에서의 차례가 클러스터 인덱스, 찾아보기가 넌클러스터 인덱스) 

PRIMARY KEY제약 또는 UNIQUE 제약이 테이블에 만들어지면 SQL 서버는 자동으로 인덱스를 생성한다.   클러스터 인덱스 넌클러스터 인덱스 인덱스 만든 후 크기 증가 테이블의 1~5% 10~20% 하나의 값을 주고 찾을 때 상당히 빠르다. 클러스터 보다 아주 약간 느리다.(거의 차이가 없다) 여러 값(범위)를 주고 찾을 때 여전히 빠르다. 일반적으로 테이블 스캔보다 느리다. 데이터의 수정이 일어날 때 제법 부하가 걸린다. 클러스터 보다 부하가 적다 Covered Query의 경우 별로 도움되지 않는다. 매우 도움된다. (인덱스 생성 및 삭제하기) PRIMARY KEY제약 또는 UNIQUE 제약이 테이블에 만들어지면 SQL 서버는 자동으로 인덱스를 생성한다.

(인덱스 생성 및 삭제하기) PRIMARY KEY제약 또는 UNIQUE 제약이 테이블에 만들어지면 SQL 서버는 자동으로 인덱스를 생성한다. DROP INDEX문 사용하기 .PRIMARY KEY 또는 UNIQUE 제약에 의해 생성된 인덱스 에 대해서는 DROP INDEX문을 사용할 수 없다. 먼 저 제약을 삭제한 후에야 이런 인덱스는 삭제가 가능하다. .테이블을 삭제할 때 해당 테이블에 대한 모든 인덱스 또한 삭제된다. .클러스터 인덱스를 삭제할 때 해당 테이블의 모든 넌클러스터 인덱스는 자동으로 재구성된다. .DROP INDEX문은 시스템 테이블에서 사용할 수 없다.

(유일 인덱스와 중복 허용 인덱스) 유일 인덱스는 중복된 키 값을 허용하지 않는다. CREATE INDEX UNIQUE, UNIQUE CONSTRAINT로 만들 수 있다.   CREATE INDEX문 PRIMARY KEY CONSTRAINT 클러스터와 넌클러스터 지정이 없으면 디폴트로 넌클러스터 인덱스 클러스터 인덱스 UNIQUE 지정이 없으면 디폴트로 중복허용 중복허용 않음. 아예 중복 허용으로 설정할 수 없음 NULL 허용 여부 허용 그러나, UNIQUE로 지정했을 때는 단 한 행만이 NULL을 사용할 수 있다. 허용하지 않음

기본적으로 기본 키는 클러스터 인덱스에 유일 인덱스가 디폴트이다. 중복을 허용하는 넌클러스터 인덱스 생성 예) CREATE INDEX mytable_emp_id ON mydb.dbo.mytable(name) 중복을 허용하지 않는 클러스터 인덱스 생성 예) CREATE UNIQUE CLUSTERED INDEX mytable_emp_id

(단일 컬럼 인덱스와 복합 컬럼 인덱스) (인덱스 옵션 생성하기) 한 컬럼에만 인덱스를 만든 것을 단일 컬럼 인덱스, 둘 이상의 컬럼에 인덱스를 만드는 것을 복합 컬럼 인덱스 또는 복합 인덱스이라고 한다.   (인덱스 옵션 생성하기) - FILLFACTOR 옵션 사용하기 FILLFACTOR 옵션을 사용해서 클러스터나 넌클러스터 인덱스를 가지고 있는 테이블에서 INSERT 와 UPDATE 문의 성능을 최적화할 수 있다. FILLFACTOR 옵션은 얼마나 많은 리프 레벨 페이지를 채울 것인지를 퍼센트 단위로 설정한다. 인덱슥가 생성되거나 재구성될 때 FILLFACTOR 옵션이 적용된다.

FILLFACTOR값이 낮으면, 페이지 분할이 줄어들기 때문에 UPDATE와 INSERT 문의 성능이 향상된다 FILLFACTOR값이 낮으면, 페이지 분할이 줄어들기 때문에 UPDATE와 INSERT 문의 성능이 향상된다. 낮은 FILLFACTOR값은 온라인 트랜잭션 처리(OLTP) 환경에 적합하다. 높은 FILLFACTOR값은 더 적은 수의 페이지에서 행을 읽어 들이기 때문에 쿼리 또는 읽기 성능을 향상시킨다. 높은 FILLFACTOR 값은 DSS 환경에 적합하다.   - PAD_INDEX 옵션 사용하기 PAD_INDEX 옵션은 비리프 레벨의 인덱스 페이지에 빈공간을 남기도록 설정한다. FILLFACTOR 옵션의 퍼센티지 값에 의해 PAD_INDEX의 퍼센티지 값이 결정되기 때문에 PAD_INDEX옵션은 FILLFACTOR가 지정된 때에만 사용해야 한다.

FILLFACTOR의 퍼센티지 리프 레벨의 페이지 PAD_INDEX 설정 여부 비리프 페이지 (설정안됨, 디폴트) 완전히 채움 아니오 인덱스 엔트리 하나가 들어갈 공간을 비움 예 1~99 설정된 페이지까지 채움 퍼센티지까지 채움 100  

제7장 인덱스 관리하기 인덱스 유지하기 인덱스를 생성한 후에는 최적의 성능을 보장하기 위해서 그것을 관리해야 한다. 데이터가 수정될 때 페이지 분할에 의해 데이터가 점점 조각화된다. Fragmented 상태는 쿼리 과정의 속도를 저하시킨다. SQL 서버는 조각 상태를 감시하기 위한 DBCC SHOWCONTIG문을 제공한다. 조각 상태가 성능에 영향을 미칠 때는 인덱스를 재구성해야 한다.

인덱스 재구성하는 방법) 1. CREATE INDEX문을 DROP_EXISTRING옵션과 함께 사용 2.  DBCC DBREINDEX문을 사용하여 3.  먼저 인덱스를 삭제하고 나서 다시 생성   DBCC SHOWCONTIG문은 지정된 테이블의 데이터와 인덱스에 대한 조각 상태 정보를 보여준다. 과도하게 수정되는 테이블, 임포트된 데이터를 담고 있는 테이블, 또는 낮은 쿼리 성능을 유발하는 것으로 보이는 테이블에서 사용한다.

통계 설명 스캔된 페이지(Pages Scanned) 테이블 또는 인덱스의 페이지 개수 스캔된 익스텐트(Extents Scanned) 테이블 또는 인덱스의 익스텐트 개수 익스텐트 스위치(Extent Switches) 테이블 또는 인덱스의 페이지들을 가로질러 검색하는 동안 DBCC문이 하나의 익스텐트에서 다른 익스텐트로 이동해야 하는 횟수 익스텐트 당 평균 페이지 수 (Avg.Pages per Extent) 페이지 체인 내의 익스텐트 당 페이지 개수 스캔 밀도[최상치:실제치] (Scan Density[Best Count:Actual Count]) 실제치는 DBCC문이 테이블 또는 인덱스를 가로질러 검색할 때 발생한 익스텐트 스위치의 실제 개수이다. 최상치는 모든 페이지가 순차적으로 연결되고 최소 개수의 익스텐트 할당되었을 때 익스텐트 스위치의 이상적인 개수이다. 스캔 밀도는 실제치의 퍼센트 단위의 최상치이다. 만약 조각 상태가 하나도 없다면 최고치 = 실제치가 되고 스캔 밀도는 100%가 된다.

논리적 스캔 조각 상태 (Logical Scan Fragmentation) 인덱스의 리프 페이지를 스캔할 때 순서에서 벗어난 페이지들의 퍼센티지. 적을수록 좋다. 익스텐트 스캔 조각 상태 (Extent Scan Fragmentation) 인덱스의 리프 페이지를 검색할 때 익스텐트의 퍼센티지는 물리적으로 연속적이지 않다. 숫자가 낮을수록 좋다. 페이지 당 남은 바이트 수 (Avg. Bytes Free per Page[jjb1]) 스캔되는 페이지에 남은 바이트의 평균. 숫자가 높을수록 페이지에 남은 공간이 많다. 낮을수록 좋다. 평균 페이지 밀도 (Avg. Pages Density (fully)) 평균 페이지 밀도 값은 행의 크기와 관련이 있기 때문에 얼마나 페이지가 채워졌는지 알 수 있는 정확한 지표가 된다. 퍼센트가 높을수록 좋다.

통계 정보 예) member 테이블의 테이블 ID에 액세스하는 문을 실 행한다. USE library DECLARE @table_id int SET @table_id = OBJECT_ID(‘member’) DBCC SHOWCONTIG (@table_id) 통계 정보 인덱스가 있는 컬럼에 대해(복합 인덱스의 경우 첫 번째 컬럼만) 통계 정보는 자동으로 생성되고 관리된다. CREATE STATICS문을 써서 SQL 서버로 하여금 다른 컬럼에 대해서도 통계를 생성하게 할 수 있다.

생성 예) USE library CREATE STATISTICS stats_outdate ON loanhist (out_date) 삭제 예) DROP STATISTICS loanhist.stats_outdate

통계 정보 * 통계 갱신하기 빈 테이블에 인덱스를 생성하고 테이블을 나중에 채우는 경우, TRUNCATE TABLE문을 실행하고 나중에 그 테이블을 다시 채우는 경우, 좀더 자세한 인덱스 통계가 필요한 경우 등에는 UPDATE STATISTICS문을 사용하여 통계 정보를 수동으로 갱신할 수 있다. 또한, 통계의 자동 갱신 사용금지로 만들기 위해서는 (인덱스 통계를 정적으로 만들기) CREATE INDEX 문에 STATISTICS_NORECOMPUTE키워드를 설정한다.  

(인덱스 분석) 인덱스와 쿼리 성능을 분석하기 위하여 SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, SET STATISTICS IO문을 실행할 수 있다. (성능에 관하여) 1. SHOWPLAN SET STATISTICS IO ON : 페이지I/O 가 얼마나 발생했는지 보여준다. SET STATISTICS TIME ON : 실행하는데 걸린 시간을 보여준다. SET NOEXEC ON : 실제 쿼리를 실행하지 않고 문법 검사와 OPTIMIZER 작업만 한다.

2. OPTIMIZER HINT : 사용자가 임의의 방식을 옵티 마이저에게 지시할 수 있게 한다. 예) SELECT title_id, price FROM titles (index = 0)  테이블 스캔의 의미 WHERE title_id = ‘BU1032’ 3. POINT SEARCH : 한 행을 찾아 오는 것 4. RANGE SEARCH : 범위 안에 드는 모든 값을 가져 오는 것 5. 모든 기본 키는 클러스터 색인이다? 6. COVERED QUERY : 질의에 나타난 모든 컬럼이, 넌 클러스터 색인에 복합 키로 포함되어 있는 경우 를 covered query, covered index라고 말한다.

7. FILLFACTOR : 데이터 삽입과 숮ㅇ에 대한 속도를. 향상시키기 위해 약간의 빈 공간을 두기 위한. 옵션이다 7. FILLFACTOR : 데이터 삽입과 숮ㅇ에 대한 속도를 향상시키기 위해 약간의 빈 공간을 두기 위한 옵션이다. 그러나 FILLFACTOR를 사용하면 데이 터의 변경 속도는 빨라지지만, select속도는 오 히려 늦어진다. 8. WITH DROP EXISTING : SQL 서버에서는 클러스터 색인을 다시 만들면 조각 모임이 된다. 예) CREATE INDEX indexName ON tableName (ColumnName) WITH DROP EXISTING   DBCC DBREINDEX(authors, ‘’, 70)

9. UPDATE STATISTICS : 통계치를 수동으로 갱신할 때 10. DBCC(DataBase Consistency Checker) DBCC CHECKTABLE DBCC CHECKDB DBCC SHOWCONTIG DBCC SHOW_STATISTICS 11.     익스텐트와 페이지 테이터가 저장 될 때는 데이터 영역에 저장된다. (데이터베이스를 만들 때는 데이터 파일과 로그 파일을 지정한다.) 테이터 파일은 그 내부에 익 스텐트와 페이지로 구성되어 있다. (페이지는 8KB이다.)

이런 페이지가 연속으로 여덟 개가 모인 것을. 익스텐트라고 한다. 익스텐트는 테이블과 색인. 의 할당 단위가 된다 이런 페이지가 연속으로 여덟 개가 모인 것을 익스텐트라고 한다. 익스텐트는 테이블과 색인 의 할당 단위가 된다. 익스텐트는 사용도중 빈 공간이 생길 수 있다. 같은 테이블의 테이터만 오기 때문에 한 페이지만 사용하고 나머지 일곱 페이지는 비어 있을 수 있다. 이런 것을 보여주 는 것이 Scan Density이다. 12. 스토어드 프로시저의 RECOMPILE 스토어드 프로시저는 처음 실행 이후로는, 컴파 일 된 것을 캐쉬에서 가져오기 때문에 빠르다. 그러나, 자료를 가져오는 경우에 따라서는 이미 컴파일 된 작업경로가 아닌 새로운 작업경로가 필요할 때가 있다. 이를 위해 사용하는 것이 RECOMPILE이다.

프로시저가 실행될 때 마다 재 컴파일 하는 예) CREATE PROC procByID … WITH RECOMPILE 실행할 때만 재 컴파일 하는 예) EXEC procBYID 10000 WITH RECOMPILE Table1과 관련된 모든 프로시저가 한번만 다시 컴파일 되는 예) sp_recompile table1