현업에서 사용하고 있는 DB에 대해 인덱스 조각모음을 해야 할 경우
인덱스들은 데이터를 추가,삭제시 마다
인덱스의 단편화가 생기고 이 단편화로 인해 인덱스 사용시 조회시간이 늘어나고 공간도
늘어나게 됩니다.
이를 해결할 방법은 인덱스를 재구성하거나(DBCC DBREINDEX)나 마치 디스크의
조각모음을 하는 것과 같은 인덱스 조각모음을(DBCC INDEXDEFRAG) 하는 것입니다.
두가지 방법 모두 나름대로 장단점이 있습니다.
인덱스를 재구성 할때에는(DBCC DBREINDEX) 해당 인덱스를 사용할 수가 없고
따라서 작업을 할 때에는 서비스를 막아놓고 작업을 해야 합니다. 하지만 인덱스를 새롭게
다시 구성하는 것인만큼 효과는 가장 좋습니다.
인덱스 조각모음은(DBCC INDEXDEFRAG) 작업하는 동안이라도 인덱스를 사용할 수 있습니다.
또한 락이 걸린 부분은 건너띄고 작업을 수행합니다. 따라서 인덱스를 재구성하는 것보다는
효과가 떨어집니다. 인덱스의 조각화가 심할 경우에는 조각 모음을 하는 것보다 새로 만드는 것이
시간적으로나 효과면에서 더 좋으나 실제 서비스를 중단해야 하므로 적합하지 않습니다.
INDEXDEFRAG 작업자체는 서버자체에 큰 부하를 주지 않는다고 합니다.
조각모음을 하게 될때에 가장 중요한 점은 트랜잭션 로그의 크기를 계속적으로 확인해야 합니다.
조각모음을 할 때에 로그 크기도 증가하게 되며 필요에 따라 로그 크기를 줄이기
위해 로그 백업을 자주 해줘야 합니다.
SQL도움말에는 "인덱스 조각모음 작업이 잠금을 오래 보유하지 않으므로 실행 중인 쿼리나 업데이트를
차단하지 않습니다." 라고 나와 있고 "조각 모음은 일련의 짧은 트랜잭션으로 수행되므로 로그 백업을
자주 하거나 복구 모델 설정이 SIMPLE인 경우에는 큰 로그가 필요하지 않습니다." 라고 나와 있습니다.
USE DB명
SP_HELPINDEX table명
--조회
DBCC SHOWCONTIG (table명) WITH TABLERESULTS
/*
Scan Density(logical extent density) 와 Avg Page Density(Avg free bytes/page) 확인.
30~50% under이면 Reorg가 필요
*/
--index defrag
DBCC INDEXDEFRAG(DB명, TABLE명, INDEX명)
--혹은 dbcc dbreindex(TABLE명, '', 100)
--reindex 후 다시 조회
DBCC SHOWCONTIG (SC_TRAN) WITH TABLERESULTS
출처 ::
http://www.microsoft.com/korea/technet/sql/tuning_guide_developer03.mspx
SQL Server DBA 가이드 3번
데이터를 입력,수정,삭제를 하다보면 인덱스에 조각화가 일어난다.
조각화가 많아지면 성능이 느려진다.
그럼, 다시 정리를 해줘야지!!
인덱스를 다시 작성하는 방법에는
-. DBCC DBREINDEX
-. DBCC INDEXDEFRAG
-. ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)
이 있다..
여기에서 속도도 가장 빠르고 효율적인것이..
MS-SQL 2005 이상에서 지원하는 아래의 쿼리다.
ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)
테이블 하나씩 하나씩 언제하냐고??
한방에 해당 데이터베이스의 인덱스를 정리하는 방법을 알려 달라고???
해당 데이터베이스의 모든 테이블의 모든 인덱스를 재정리하는 쿼리다.
------------------------------------------------------------------------------------
DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName varchar(1000)
SET @i = 1
DECLARE DB_Cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
EXEC (@sql)
PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
SET @i = @i + 1
FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
[출처] [MS-SQL] 인덱스를 다시 정리하자(조각모음)|작성자 inno
WRITTEN BY
- zippy
쓸만한것들 모아놓기..
