망할고양이의 도피처

일반적으로 테이블을 생성할 때 아래와 같이 인덱스를 생성하게 된다.

ALTER TABLE [dbo].[REGION_MGMT] ADD  CONSTRAINT [PK_REGION_MGMT] PRIMARY KEY CLUSTERED 
(
	[REGION_CD] ASC,
	[NATION_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

WITH 다음에 오는 ON/OFF가 무슨 뜻인지 알아보도록 한다.

LOB_COMPACTION = { ON | OFF }

LOB(Large Object) 데이터가 포함된 모든 페이지를 압축하도록 지정합니다. 
LOB 데이터 형식은 image, text, ntext, varchar(max), nvarchar(max), varbinary(max) 및 xml입니다. 이 데이터를 압축하면 디스크 공간을 더 효율적으로 사용할 수 있습니다. 기본값은 ON입니다.

• OFF
  큰 개체 데이터가 포함된 페이지가 압축되지 않습니다.
  OFF를 지정해도 힙에는 아무 영향이 없습니다.
  LOB 열이 없는 경우 LOB_COMPACTION 절은 무시됩니다.

PAD_INDEX = { ON | OFF }

인덱스 패딩을 지정합니다. 기본값은 OFF입니다.

• ON
  FILLFACTOR로 지정된 사용 가능한 공간의 비율이 인덱스의 중간 수준 페이지에 적용됩니다. FILLFACTOR를 지정함과 동시에 PAD_INDEX를 ON으로 설정하지 않으면 sys.indexes에 저장된 채우기 비율 값이 사용됩니다.
  
  
• OFF 또는 fillfactor가 지정되지 않았습니다.
  중간 수준 페이지는 용량 한도 가까이 채워집니다. 이로 인해 중간 페이지의 키 집합을 기준으로 인덱스에 포함될 수 있는 최대 크기의 행 하나 이상을 위한 충분한 공간이 남겨집니다.

SORT_IN_TEMPDB = { ON | OFF }

tempdb에 정렬 결과를 저장할지 여부를 지정합니다. 기본값은 OFF입니다.

• ON
  인덱스 작성에 사용된 중간 정렬 결과가 tempdb에 저장됩니다. tempdb가 사용자 데이터베이스와는 다른 디스크 세트에 있으면 인덱스를 만드는 데 필요한 시간이 줄어들 수 있습니다. 그러나 인덱스 작성 중에 사용되는 디스크 공간의 크기는 커집니다.
  
  
• OFF
  중간 정렬 결과가 인덱스와 같은 데이터베이스에 저장됩니다.
  정렬 작업이 필요하지 않거나 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB 옵션이 무시됩니다.

IGNORE_DUP_KEY = { ON | OFF }

데이터 입력 시 PK(UNIQUE) 중복 오류가 발생할 경우 정상적인 데이터만 입력할 것인가 전부 롤백 할 것인가에 대한 설정입니다. 기본값은 OFF입니다.

• ON
  중복된 키 값이 고유 인덱스에 삽입되는 경우 경고 메시지가 나타나고 고유성 제약 조건을 위반하는 행만 실패합니다.
  
  
• OFF
  중복된 키 값이 고유 인덱스에 삽입되는 경우 오류 메시지가 나타나고 전체 INSERT 작업이 롤백됩니다.
  뷰, 고유하지 않은 인덱스, XML 인덱스, 공간 인덱스 및 필터링된 인덱스에 생성된 인덱스의 경우 IGNORE_DUP_KEY를 ON으로 설정할 수 없습니다.
  
  IGNORE_DUP_KEY를 보려면 sys.indexes를 사용하십시오.
  
  이전 버전과 호환되는 구문에서 WITH IGNORE_DUP_KEY는 WITH IGNORE_DUP_KEY = ON과 같습니다.

STATISTICS_NORECOMPUTE = { ON | OFF }

배포 통계를 다시 계산할지 여부를 지정합니다.
통계가 자동 생성 되지 않으면 데이터 증가 및 분포에 따라 인덱스의 영향도가 줄어 들 수 있으므로 OFF로 설정하는것이 일반적입니다.  기본값은 OFF입니다.

• ON
  이전 통계가 자동으로 다시 계산되지 않습니다.
• OFF
  자동 통계 업데이트가 설정됩니다.
  자동 통계 업데이트를 복원하려면 STATISTICS_NORECOMPUTE를 OFF로 설정하거나 NORECOMPUTE 절 없이 UPDATE STATISTICS를 실행합니다.
  배포 통계 자동 재계산 기능을 해제하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 대해 최적의 실행 계획을 선택할 수 없습니다.
  
  

ONLINE = { ON | OFF }

인덱스 작업 중에 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 
기본값은 OFF입니다.

XML 인덱스 또는 공간 인덱스의 경우 ONLINE = OFF만 지원되며 ONLINE을 ON으로 설정하면 오류가 발생합니다.

참고
온라인 인덱스 작업은 SQL Server Enterprise, Developer 및 Evaluation Edition에서만 사용할 수 있습니다.

• ON
  인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 중 내재된 공유(IS) 잠금만 원본 테이블에 유지됩니다. 따라서 기본 테이블 및 인덱스를 계속 쿼리 또는 업데이트할 수 있습니다. 작업이 시작될 때 아주 짧은 기간 동안 S(공유) 잠금이 원본 개체에서 유지됩니다. 작업이 끝날 때 짧은 기간 동안 비클러스터형 인덱스가 생성되는 경우에는 원본에 대해 S(공유) 잠금이 유지되고, 온라인 상태에서 클러스터형 인덱스가 생성 또는 삭제될 때나 클러스터형 또는 비클러스터형 인덱스가 다시 작성될 때는 SCH-M(스키마 수정) 잠금이 획득됩니다. 로컬 임시 테이블에서 인덱스를 생성하는 경우에는 ONLINE을 ON으로 설정할 수 없습니다.
  
  
• OFF
  인덱스 작업 중에 테이블 잠금이 적용됩니다. 클러스터형 인덱스, 공간 인덱스 또는 XML 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업은 테이블에 대해 SCH-M(스키마 수정) 잠금을 획득합니다. 이 경우 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업을 통해 테이블의 S(공유) 잠금을 획득합니다. 따라서 기본 테이블을 업데이트할 수 없지만 SELECT 문과 같은 읽기 작업은 허용됩니다.

ALLOW_ROW_LOCKS = { ON | OFF }

행 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

• ON
  인덱스에 액세스할 때 행 잠금이 허용됩니다. 행 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.
  
  
• OFF
  행 잠금이 사용되지 않습니다.
  
  

ALLOW_PAGE_LOCKS = { ON | OFF }

페이지 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

• ON
  인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.
  
  
• OFF
  페이지 잠금이 사용되지 않습니다.
  
  

참고
ALLOW_PAGE_LOCKS가 OFF로 설정되면 인덱스를 다시 구성할 수 없습니다.

Clustered Index & Non-Clustered Index 

테이블을 생성 후 Primary Key를 생성하면 기본적으로 Clutered Index로 지정이 됩니다.(클러스터형 인덱스가 없는 경우)

하지만, 데이터를 파악하고 인덱스를 걸면 가장 효율적인 컬럼을 클러스터 인덱스로 지정하는것을 권장합니다.

Clustered Index

• 해당 컬럼을 기준으로 정렬, 테이블당 1개씩만 허용
• Clustered Index가 적용된 컬롬은 CUD 작업이 진행될 경우 데이터의 정렬에 관여합니다. 
• 되도록이면 UNIQUE 해야합니다.  UNIQUE하지 않으면 안되는것이 아니라 UNIQUE하지 않는 경우 오버헤드가 발생하는 것을 인지해야합니다.
• SP_COLUMNS 명령을 이용해서 사용빈도(높음), 데이터길이(짧음), UNIQUE여부(되도록이면) 인 컬럼을 지정합니다.
• 목차를 보고 해당 페이지를 찾아 가는것이 아니라 바로 해당 페이지를 찾아가는 것입니다.

Non-Clustered Index

• 한 테이블에 여러개를 생성 할 수 있습니다. (최대 240개)
• 목차를 보고 해당 페이지를 찾아가는 형태의 검색 방식입니다
• 테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다.
• 쿼리의 조건자 및 조인 조건에서 자주 사용되는 열에 대해 비클러스터형 인덱스를 만듭니다.