SQL Server 인덱스 활성 / 비활성 하기

SQL Server 인덱스 활성 / 비활성 하기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

데이터베이스를 운영하다 보면 수 많은 인덱스를 접하게 된다. 하지만 여기 있는 인덱스가 모두 사용 되는 것일까? 인덱스의 유용 여부를 파악하기란 쉽지 않다. 그렇다고 잘 쓰지 않을 것 같다고 생각되는 인덱스를 무심코 삭제 했다가는 정말 난감한 상황이 발생 한다. 만약 어떤 인덱스가 있을 때 이 인덱스의 유용 여부에 대해서 판단하고 싶을 때 어떻게 할까?

SQL Server 2005 이상 버전에서는 테이블 또는 뷰에 대해서 인덱스를 활성 / 비활성 할 수 있다. 인덱스 사용을 비활성화 하면 인덱스를 사용하지 않아 인덱스에 따른 영향을 평가 할 수 있다.

 

우선 현재 인덱스를 살펴 보자. 다음과 같이 클러스터드 인덱스가 생성되어 있으며 활성 상태인 것을 확인 할 수 있다.

SELECT      NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,     CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'          WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE] FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID('TBL_A') GO

 

 

[인덱스 비활성화 만들기]

T-SQL을 이용하여 비활성 상태로 변경하자.

ALTER INDEX CI_TBL_A_COL1 ON TBL_A DISABLE GO   SELECT      NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,     CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'          WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE] FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID('TBL_A') GO

 

 

SSMS에서 UI로도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [사용 안 함]을 선택 한다.

 

또는 인덱스 속성 창에서 [옵션] – [인덱스 사용] 체크 박스를 해제 하면 비활성화 된다.

 

 

[인덱스 활성화 만들기]

인덱스를 활성화 하는 방법에는 어떤 방법이 있으까?

 

T-SQL을 이용하여 활성화 상태로 변경하자. 비활성 상태로 만들기 위해서 DISABLE 명령어를 사용하였다면 활성하는 REBUILD 명령어를 사용해야 한다.

ALTER INDEX CI_TBL_A_COL1 ON TBL_A REBUILD GO   SELECT      NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,     CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'          WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE] FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID('TBL_A') GO

 

 

 

또는 인덱스를 새로 만들어야 한다.

CREATE CLUSTERED INDEX CI_TBL_A_COL1 ON TBL_A (COL1) WITH (DROP_EXISTING = ON) GO   SELECT      NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,     CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'          WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE] FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID('TBL_A') GO

 

 

 

SSMS의 UI에서도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [다시 작성]을 선택 한다.

 

 

또는 인덱스 속성 창에서 [옵션] – [인덱스 사용] 체크박스를 선택하면 인덱스가 활성화 된다.

 

 

위의 기능을 잘 활용하면 인덱스를 삭제 하지 않고도 인덱스의 사용 여부와 이에 따른 성능 문제를 판단할 수 있을 듯 하다.

 

2012-12-04 / 강성욱 / http://sqlmvp.kr