DBCC DBREINDEX 란 무엇인가?
DBCC DBREINDEX
지정한 데이터베이스의 테이블에 대해 하나 이상의 인덱스를 다시 작성합니다.
DBCC DBREINDEX는 테이블의 특정 인덱스나 테이블에 정의된 모든 인덱스를 다시 작성합니다. DBCC DBREINDEX는 인덱스를 동적으로 다시 작성함으로써 PRIMARY KEY나 UNIQUE 제약 조건을 보장하는 인덱스를 다시 작성할 때 해당 제약 조건을 삭제했다가 다시 만들 필요가 없습니다.
DBCC DBREINDEX를 사용하면 하나의 명령문에서 테이블의 모든 인덱스를 다시 작성할 수 있습니다. 각 DROP INDEX와 CREATE INDEX 문이 원자성을 가지려면 트랜잭션을 사용해야 하는 반면, DBCC DBREINDEX는 하나의 명령문에서 작업이 수행되므로 자동으로 원자성을 갖습니다. 또한 DBCC DBREINDEX를 사용하면 각 DROP INDEX와 CREATE INDEX 문을 사용할 때보다 최적화를 더 많이 활용할 수 있습니다.
DBCC DBREINDEX는 시스템 테이블에 대해 사용할 수 없습니다.
구문
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
인수
'database.owner.table_name'
지정한 인덱스를 다시 작성할 테이블의 이름입니다. 데이터베이스, 소유자, 테이블 이름은 식별자에 대한 규칙을 따라야 합니다. 자세한 내용은 식별자 사용을 참조하십시오. database나 owner 부분이 제공된 경우 전체 database.owner.table_name을 작은따옴표(')로 묶어야 합니다. table_name만 지정할 경우에는 작은따옴표를 사용할 필요가 없습니다.
index_name
다시 작성할 인덱스의 이름입니다. 인덱스 이름은 식별자에 대한 규칙을 따라야 합니다. index_name을 지정하지 않거나 ' '로 지정하면 테이블의 모든 인덱스가 다시 작성됩니다.
fillfactor
인덱스를 만들 때 각 인덱스 페이지에서 데이터 저장에 사용되는 공간의 비율입니다. 클러스터된 인덱스가 다시 작성되므로 fillfactor는 원래 채우기 비율을 다시 작성된 인덱스와 다른 클러스터되지 않은 인덱스의 새 기본값으로 대체합니다. fillfactor가 0이면 DBCC DBREINDEX는 인덱스가 만들어질 때 지정된 원래 fillfactor를 사용합니다.
WITH NO_INFOMSGS
심각도가 0에서 10 사이인 모든 정보 메시지를 표시하지 않습니다.
인덱스도 데이터입니다. 그래서 데이터가 추가되고 삭제되고 수정 됨에 따라서 인덱스 정보도 변경됩니다.
그러면 인덱스가 조각조각 찢어지는 현상이 발생하는데, 윈도우의 조각 모음과 비슷한 일을 하는게 DBREINDEX입니다.
다음은 pubs 데이터베이스의 authors 테이블에서 채우기 비율을 80으로 설정하여 au_nmind 클러스터되지 않은 인덱스를 다시 작성하는 예제입니다.
DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
다음은 fillfactor 값을 70으로 사용하여 authors 테이블의 모든 인덱스를 다시 작성하는 예제입니다.
DBCC DBREINDEX (authors, '', 70)
한번에 한 서버의 모든 데이터베이스의 인덱스 재 작성하기
----------------------------------------------------------------------
/*
sp_name : usp_allReindex
sp_Explanation : 디비 서버의 모든 테이블에 대해 DBCC DBREINDEX를 실행합니다.
Input Parameters : None
Output Parameters : None
Usage : exec usp_allReindex
*/
ALTER PROCEDURE usp_allReindex
AS
-- 변수 선언
DECLARE @SQLString varchar(300), @dbname varchar(30), @tblname varchar(30)
SET NOCOUNT ON
-- 테이블 리스트 저장 테이블
CREATE TABLE #tables
(
tblname varchar(30)
)
-- 한 서버의 디비 목록을 위한 커서 시작
DECLARE cur_dbList CURSOR
FOR
SELECT name FROM master..sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') ---- (1)
OPEN cur_dbList
FETCH cur_dbList INTO @dbname
WHILE @@fetch_status = 0
BEGIN
TRUNCATE TABLE #tables
SET @SQLString = 'insert into #tables select name from ' + @dbname + '..sysobjects where type = ''U'''
EXEC (@SQLString)
-- 각 디비의 테이블 목록을 위한 커서 시작
DECLARE cur_tblList CURSOR
FOR
SELECT tblname FROM #tables
OPEN cur_tblList
FETCH cur_tblList INTO @tblname
WHILE @@fetch_status = 0
BEGIN
SET @SQLString = 'DBCC DBREINDEX (''' + @dbname + '..' + @tblname + ''', '''', 90)' ---- (2)
EXEC (@SQLString)
FETCH cur_tblList INTO @tblname
END
CLOSE cur_tblList
DEALLOCATE cur_tblList
FETCH cur_dbList INTO @dbname
END
CLOSE cur_dbList
DEALLOCATE cur_dbList
DROP TABLE #tables
---------------------------------------------------------------------------------------
1) 에서 시스템 데이터베이스의 dbid 는 4번까지 고정적입니다.
사용자 데이터베이스는 dbid가 유동적입니다. 즉 삭제하고 다시 만들면 dbid를 재 사용합니다.
그래서 명확하게 시스템 데이터베이스의 이름을 지정해서 사용자 데이터베이스를 추출 하는게 좋을 것 같습니다.
(2) 에서 DBCC DBREINDEX의 사용법을 활용해서 원하는 방식으로 인자를 주시면 됩니다.
'데이터베이스 > SQL Server' 카테고리의 다른 글
| 사용안된 index 목록 뽑아내기 (0) | 2008.04.28 |
|---|---|
| INDEX (0) | 2008.04.28 |
| [SQL 서버 2005 실전 활용] ④ 새로운 DB 보호·복구 모델 (0) | 2008.04.28 |
| 논리적 파일명 변경하기 (0) | 2008.04.28 |
| SQL Server 2005 Query optimizer의 변경된 내용 (0) | 2008.04.28 |
