(─━┘_└━─)/ 머리만1톤(Head1Ton)

앞에서 sys.dm_db_index_usage_stats 동적 관리 뷰에 대하여 설명하였습니다. sys.dm_db_index_usage_stats 동적 관리 뷰 외에 sys.dm_db_index_operational_stats 라는 동적 관리 함수가 있습니다.
이 sys.dm_db_index_operational_stats 는 현재 데이터베이스 또는 지정한 데이터베이스에 있는 테이블 또는 인덱스의 파티션별 I/O, 잠금, 래치 및 액세스 작업 방법에 대한 정보를 제공합니다.

sys.dm_db_index_operational_stats를 사용하여 사용자가 테이블, 인덱스 또는 파티션을 읽거나 쓰기 위해 대기해야 하는 시간을 추적하고 상당한 I/O 작업 또는 문제가 발생하고 있는 테이블이나 인덱스를 식별할 수 있습니다. 즉, 이러한 열을 사용하여 경합이 발생하고 있는 영역을 식별할 수 있습니다.

[구문] sys.dm_db_index_operational_stats (

    { database_id | NULL }

    , { object_id | NULL }

    , { index_id | NULL | 0 }

    , { partition_number | NULL }

)

실제 많은 수의 운영 환경들에서 테이블에 만들어져 있는 인덱스들의 일부는 실제로는 사용되지 않는 인덱스입니다. 실제로 사용되지 않는 인덱스를 관리하는 것은 불필요한 오버헤드만 유발하므로 인덱스 사용여부를 확인하고 실제로 사용되지 않는 불필요한 인덱스들을 구분해 내고 삭제하는 것이 필요합니다.
SQL Server 2005에서 새롭게 제공되는 DMV 중에서 sys.dm_db_index_usage_stats 동적 관리 뷰를 사용하여 사용된 인덱스와 그 빈도를 알 수 있습니다. 이 뷰를 사용하여 응용 프로그램에서 사용하지 않는 인덱스를 확인할 수 있으며, 또한 유지 관리 오버헤드를 유발하는 인덱스를 확인할 수도 있습니다.

SQL Server 서비스를 시작할 때마다 카운터는 빈 상태로 초기화되며, 데이터베이스가 분리되거나 종료될 때마다(예: AUTO_CLOSE가 ON으로 설정된 경우) 데이터베이스와 관련된 모든 행이 제거됩니다. 그러므로 DMV에 대한 스냅샷을 영구 테이블에 복사해 둠으로써 SQL Server가 재시작되기 이전의 데이터도 관리하는 것을 권고합니다.

[주의] 인덱스가 사용되지 않는 인덱스라고 확인되더라도 충분히 오랜 기간 동안 모니터링하지 않았다면 섣불리 인덱스를 삭제해도 무방하다고 판단해서는 안됩니다. 만일 크기가 큰 테이블에 대해서 어떤 응용 프로그램에서 사용하는 인덱스를 실수로 삭제하게 되면 심각한 블로킹과 성능 저하가 발생할 수 있습니다.

불필요한 인덱스를 삭제하지 않고 그대로 두면 오버헤드만 유발하므로, 인덱스가 불필요하다고 판단되면 삭제하는 것이 바람직합니다.

인덱스를 삭제하고자 하는 경우에는 DROP INDEX를 사용하면 됩니다.
DROP INDEX 구문도 SQL Server 2005에서 변경되었으므로 DROP INDEX를 사용하기 전에 SQL Server 2005 온라인 설명서에서 DROP INDEX 구문을 살펴 보시기 바랍니다.

이전 버전과의 호환성을 위해 SQL Server 2000의 구문이 지원되기는 하지만 이후 버전에서는 제거될 수 있으므로 SQL Server 2005 구문에 맞게 작성 또는 수정하실 것을 권고합니다.

-- SQL Server 2000 구문

DROP INDEX Employee.IX_Employee_ManagerID;

GO

-- SQL Server 2005 구문
DROP INDEX IX_Employee_ManagerID ON Employee;

GO

인덱스 관련 T-SQL 문 중에 ALTER INDEX 문이 있는데, ALTER INDEX로는 인덱스를 구성하는 열을 변경하거나 파일 그룹을 변경할 수 없습니다. 인덱스를 구성하는 열을 추가 또는 삭제, 순서를 변경하거나 인덱스가 저장된 파일 그룹을 변경하고자 하는 경우에는 인덱스를 삭제한 후 다시 생성해야 합니다.

 
만일 PRIMARY KEY 제약 조건이나 UNIQUE 제약 조건을 정의함으로써 생성된 인덱스는 DROP INDEX로 삭제할 수 없습니다.
이러한 제약 조건들은 ALTER TABLE 문의 DROP CONSTRAINT 절을 사용하여 삭제합니다.

인덱스 비활성화는 사용자가 인덱스를 사용할 수 없도록 하며, 제약 조건을 포함한 모든 인덱스에 사용 가능합니다. 즉, 비활성화된 인덱스는 유지 관리 되지 않고, 쿼리 옵티마이저에 의해 고려 되지도 않습니다. 비클러스터형 인덱스 또는 클러스터형 인덱스를 비활성화하면 인덱스의 메타 데이터는 시스템 카탈로그에 남기지만, 물리적으로 인덱스 데이터를 삭제합니다. 그러므로, 비활성화된 인덱스와 동일한 이름으로 인덱스를 생성할 수 없습니다. 클러스터형 인덱스의 경우는 비활성화하면 데이터는 삭제되지 않고 남아 있지만, 해당 테이블의 데이터에 접근할 수 없어, 데이터의 수정은 물론 조회도 불가능합니다.

인덱스의 비활성화 여부는 sys.indexes 카탈로그 뷰의 is_disabled 열을 확인합니다. 인덱스의 비활성화는 ALTER INDEX DISABLE을 사용하고, 비활성화된 인덱스를 다시 활성화 시키기 위해서는 ALTER INDEX REBUILD 또는 CREATE INDEX WITH DROP_EXISTING 구문을 사용합니다. 비활성화된 클러스터형 인덱스를 재작성할 때에는 ONLINE 옵션을 ON으로 설정할 수 없으며, 비활성화된 비클러스터형 인덱스를 재작성할 때에는 ONLINE 옵션을 ON으로 설정할 수 있습니다.

[참고]

PRIMARY KEY 제약 조건을 FOREIGN KEY 제약 조건이 참조하고 있는 경우, 인덱스가 비활성화되면 FOREIGN KEY 제약 조건도 동시에 비활성화됩니다. 그러나, PRIMARY KEY 제약 조건을 재구성했다고 하더라도 FOREIGN KEY 제약 조건은 재구성되지 않으므로, PRIMARY KEY 제약 조건을 재구성한 후 수동으로 FOREIGN KEY 제약 조건을 재생성해야 합니다.

SQL Server 2005에서 인덱스와 관련하여 개선된 기능 중 하나인 온라인 인덱스 작업에 대하여 알아 보겠습니다. 온라인 인덱스 작업의 지원은 1년365일, 24시간 운영되어야 하는 시스템을 관리하는 DBA들이 학수고대하던 기능입니다. 온라인 인덱스 작업의 지원으로 인하여 인덱스 작업으로 인한 다운타임이 감소하고 인덱스를 재구성하고 싶어도 소요시간 때문에 인덱스 재구성 작업을 엄두도 내지 못하던 대용량 시스템에 도움이 될 것으로 생각됩니다.

SQL Server 2005에서는 온라인으로 인덱스를 만들고 다시 작성하고 삭제할 수 있게 되었습니다. ONLINE 옵션을 사용하면 인덱스 작업이 수행되는 동안 기본 테이블(BASE TABLE)이나 클러스터형 인덱스 데이터를 쿼리하고 업데이트할 수 있게 되었습니다.

ONLINE 옵션은 다음과 같은 Transact-SQL 문에서 사용할 수 있습니다.
- CREATE INDEX
- ALTER INDEX
- DROP INDEX
- ALTER TABLE (CLUSTERED 인덱스 옵션을 사용하는 UNIQUE 제약 조건이나 PRIMARY KEY 제약 조건을 추가하거나 삭제하는 경우)


참고로, 인덱스를 다시 구성하는 작업은 항상 온라인으로 수행됩니다만, 인덱스를 다시 작성하는 작업은 기본적으로 오프라인으로 수행됩니다. 인덱스를 다시 작성할 때에도 ONLINE 옵션을 ON으로 설정하면 변경 중에 아주 잠시 동안만 배타 테이블 잠금이 유지되기 때문에 인덱스 작성 중에도 데이터의 조회와 수정이 가능하게 됩니다.


온라인 인덱스 작업은 SQL Server 2005 Enterprise Edition에서만 가능합니다.

인덱스 옵션 중에 IGNORE_DUP_KEY 라는 옵션이 있습니다. 이 옵션은 기본 모드가 중복 키 무시인 이기종 DBMS에서 SQL Server 2005로 마이그레이션하는 경우에 유용하게 사용할 수 있는 옵션이므로, 여러분에게 소개 드리고자 합니다.

IGONORE_DUP_KEY 옵션을 활성화하면 다중 행 INSERT 작업 시 중복 키 값이 들어올 때 오류가 아닌 경고 메시지를 반환하고 고유 인덱스에 위배되는 행만 INSERT에서 제외됩니다. 다른 문제만 없다면 고유 인덱스에 위배되지 않는 나머지 행들은 성공적으로 INSERT가 이루어집니다.
 
IGNORE_DUP_KEY 옵션을 비활성화한 상태에서는 고유 인덱스에 위배되는 행이 존재하면 오류 메시지가 반환되며 전체 INSERT 트랜잭션이 롤백됩니다.

참고로, 인덱스 옵션은 WITH 절을 사용하여 기술하며 SQL Server 2005의 구문은 SQL Server 2000의 구문과 조금 달라졌습니다. SQL Server 2005에서 새롭게 지원되는 인덱스 옵션은 WITH (option_name = ON}OFF) 을 사용해야만 지정할 수 있습니다. 설사 SQL Server 2000과 호환되는 옵션이더라도 새롭게 스크립트를 작성하거나 기존 스크립트를 수정하는 경우에는 SQL Server 2005의 구문을 준수하여 작성할 것을 권고합니다.

- SQL Server 2000 : WITH <index_option>
- SQL Server 2005 : WITH <index_option = ON | OFF>


다음에 IGNORE_DUP_KEY 옵션을 사용한 예제가 있습니다. 한번 따라해 보시면 쉽게 이해가 되실 겁니다.

-- 1. 테스트 테이블을 만듭니다.

CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);

GO

-- 2. 테스트 테이블에 IGNORE_DUP_KEY 옵션이 활성화된 인덱스를 생성합니다.

CREATE UNIQUE INDEX AK_Index ON #Test (C2)

    WITH (IGNORE_DUP_KEY = ON);

GO

-- 3. 하나의 일괄 처리(Batch) 안에서 Adventureworks.Production.UnitMeasure 테이블에 존재하는 행과 동일한 값을 가지는 한 행을 미리 INSERT 하고 Adventureworks.Production.UnitMeasure 테이블의 전체 데이터를 복사함으로써 중복  INSERT를 발생시킵니다. 오류 대신 경고 메시지가 반환되며 정상적으로 처리되는 것을 확인할 수 있습니다.
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());

INSERT INTO #Test

SELECT * FROM Adventureworks.Production.UnitMeasure;

GO

/*

중복 키가 무시되었습니다.

*/

-- 클린업
DROP TABLE #Test;

GO

SQL Server 2005에는 키가 아닌 열을 포함한 비클러스터형 인덱스를 생성할 수가 있습니다. 키가 아닌 열을 포함한 인덱스, 즉 포괄 열이 있는 인덱스는 인덱스 커버링 가능성을 높여 주기 때문에 쿼리의 성능을 개선시키는데 도움이 됩니다.
참고로 인덱스 커버링이랑 쿼리 옵티마이저가 테이블 데이터나 클러스터형 인덱스의 데이터를 참조하지 않고 비클러스터형 인덱스의 열 값만을 참조하여 원하는 데이터를 얻는 것을 말합니다. 이와 같이 데이터나 클러스터형 인덱스를 참조하지 않게 되면 적은 I/O로 원하는 결과를 얻게 되므로 성능적인 측면에서 도움이 됩니다.

포괄 열이 있는 인덱스는 다음과 같은 장점이 있습니다.

?         인덱스 키 열로 허용되지 않는 데이터 형식을 포괄 열에는 포함시킬 수 있습니다. 포괄 열 인덱스에는 text, ntext, image 데이터 형식을 제외한 모든 데이터 형식이 허용됩니다.

?         비클러스터형 인덱스를 구성하는 인덱스 키 열의 개수 또는 인덱스 키의 크기의 계산에서 제외됩니다.

다음 예제에서와 같이 CREATE INDEX 문에 INCLUDE 절을 추가하여 포괄 열 인덱스를 생성할 수 있습니다. 

인덱스는 테이블이나 뷰의 조회를 빠르게 하기 위한 데이터 구조입니다. 인덱스는 테이블이나 뷰의 한 개 또는 한 개 이상의 열로 구성되어 있는 키로 구성되어 있으며, 그 키는 B-트리 구조로 구성되어 있습니다. 인덱스는 조회의 속도는 증가 시키지만, INSERT 속도는 저하시키므로 적절한 인덱스를 생성하는 것이 중요합니다. 인덱스는 테이블, 뷰, 또는 테이블의 XML 인덱스에 생성할 수 있습니다.

인덱스의 종류

n       클러스터형 인덱스

클러스터형 인덱스를 구성하는 키 값의 순서에 의해 테이블 또는 뷰의 데이터가 물리적으로 정렬되어 있습니다. 그러므로, 클러스터형 인덱스는 한 테이블에 한 개만이 존재할 수 있습니다. 클러스터형 인덱스가 존재하여 클러스터형 인덱스의 키 값에 따라 데이터가 정렬되어 있는 테이블을 “클러스터형 테이블”이라고 하고, 클러스터형 인덱스가 없는 테이블을 “힙”이라고 합니다.

클러스터형 인덱스는 다음과 같은 선정 조건을 가지는 것이 좋습니다.

?         고유한 값을 가지는 열로 생성하는 것이 좋습니다. 중복값이 존재하는 경우에는 시스템 내부에서 고유한 값을 가지기 위하여 “uniqifier”가 추가되기 때문에 추가적인 오버 헤드가 발생합니다.

?         인덱스 길이는 짧은 것이 좋습니다. 클러스터형 인덱스가 길어지면 비클러스터형 인덱스의 길이도 길어지기 때문에 인덱스 크기가 커집니다.

?         정적인 열에 생성하는 것이 좋습니다. 자주 변경되는 열에 클러스터형 인덱스를 생성하면 키에 대한 업데이트 작업에 대한 비용이 증가합니다.

n       비클러스터형 인덱스

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005Admin&intSeq=96

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005Admin&intSeq=154