데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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에서도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [다시 작성]을 선택 한다.

 

 

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

 

 

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

 



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1691 SQL Server에 연결된 어플리케이션 드라이버 버전 확인 jevida(강성욱) 2016.09.13 884
1690 SQL Server 성능 대시보드 jevida(강성욱) 2016.09.13 2216
1689 SSMS를 이용한 데이터베이스 복사 하기 jevida(강성욱) 2016.09.13 11538
1688 SQL Server에서 DB2 링크드 서버 연결 방법 jevida(강성욱) 2016.09.13 3288
» SQL Server 인덱스 활성 / 비활성 하기 jevida(강성욱) 2016.09.13 2352
1686 파티션 오프셋과 SQL Server에 대한 디스크 할당 단위 크기 jevida(강성욱) 2016.09.13 1687
1685 Read the End of a Large Error Log (에러로그 끝 부분 읽기) jevida(강성욱) 2016.09.13 1299
1684 데이터베이스 여유공간 확인 jevida(강성욱) 2016.09.13 1445
1683 FILE GROUP 간 데이터 이동 jevida(강성욱) 2016.09.13 1601
1682 문서화 되지 않은 sys.fn_PhysLocFormatter 함수 jevida(강성욱) 2016.09.13 948
1681 SQL Server 관리자 전용 연결(DAC) jevida(강성욱) 2016.09.13 3390
1680 파일 접근 권한으로 인한 DB복원 실패 작업 해결하기 jevida(강성욱) 2016.09.13 1934
1679 DBCC CHECKDB 실행과 히스토리 관리 jevida(강성욱) 2016.09.13 1846
1678 MDF를 이용한 데이터베이스 복원 jevida(강성욱) 2016.09.13 1174
1677 EventID 3041 Error jevida(강성욱) 2016.09.13 1417
1676 데이터베이스 미러 백업 jevida(강성욱) 2016.09.13 1313
1675 SNAPSHOT을 이용한 SQL Server 복원 jevida(강성욱) 2016.09.13 2315
1674 복사 전용 백업(COPY ONLY BACKUP) jevida(강성욱) 2016.09.13 3375
1673 SQL Server 기본 백업 폴더 변경하기 jevida(강성욱) 2016.09.13 4066
1672 sp_helpindex 의 향상된 버전 minsouk 2016.05.17 4562





XE Login