삽질삽질삽질/DB2014. 11. 5. 17:54

출처 : http://115.136.65.150/?document_srl=2175&mid=tip&sort_index=readed_count&order_type=desc


인덱스 조각화 정보 확인 (DBCC SHOWCONTIG)
- 정기 적인 조각화 정보 확인을 통한 인덱스 재구성 주기 결정
- 적절한 채우기 비율 (fillfactor) 결정

- DBCC SHOWCONTIG('테이블명', '인덱스명')

 

[T_FLOWART 테이블]

DBCC SHOWCONTIG('T_FLOWART', 'PK_T_FLOWART')

 

(결과 출력)

DBCC SHOWCONTIG이(가) 'T_FLOWART' 테이블을 검색하는 중...
테이블: 'T_FLOWART'(423672557); 인덱스 ID: 1, 데이터베이스 ID: 5
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 33649 (현재 33649 페이지를 사용하고 있다)
- 검색한 익스텐트 ..............................: 4231 (사용하는 익스텐트)
- 익스텐트 스위치..............................: 10337

최적화 익스텐트 스위치 갯수는 검색한 익스텐트 - 1 한 결과 값이어야 한다.

즉, 4231 - 1 = 4230 이어야지 최적화되었다고 말할수 있다.


- 익스텐트당 평균 페이지 수........................: 8.0
- 검색 밀도[최적:실제].......: 40.69% [4207:10338]

Best CountActual Count의 비율입니다.

모든 데이터가 인접한 경우 이 값은 100이고 이 값이 100보다 작으면 일부 데이터가 조각화된

것입니다.

사용할 익스텐트의 갯수가 4207이면 충분한데 10338 갯수를 사용하고

있다라는 뜻이다. 즉, 조각화가 많이 발생되어 있다라는 말이다.(리소스 낭비)

 


- 논리 검색 조각화 상태 ..................: 20.97%
- 익스텐트 검색 조각화 상태 ...................: 0.57%
- 페이지당 사용 가능한 평균 바이트 수.....................: 935.5
- 평균 페이지 밀도(전체).....................: 88.44%

평균 페이지 밀도입니다(백분율). 이 값은 행 크기의 영향을 받습니다. 따라서 이 값은 페이지의 꽉 찬 정도를 보다 정확하게 반영합니다. 이 백분율 값이 클수록 좋습니다.

 

 

 

[인덱스 재구성 3가지 방법]
WITH DROP_EXISTING
- 페이지 압축
- 컬럼 변경
- FILLFACTOR, PAD_INDEX 변경
- 비클러스터형 인덱스를 클러스터형 인덱스로  변경
DBCC DBREINDEX
- 페이지 압축
- FILLFACTOR 변경
- 테이블의 모든 인덱스 일괄 적용 가능
DBCC INDEXDEFRAG
- 물리적인 리프 페이지의 순서를 논리적인 순서로 재정렬
- 기존에 지정된 FILLFACTOR 적용

 

 

[인덱스 재구성 구문]

WITH DROP_EXISTING

CREATE CLUSTERED INDEX IDX_EMP_01
ON EMP (DEPTNO ASC, TITLE DESC, SAL DESC )
WITH DROP_EXISTING
ON HR_FILEGROUP

DBCC DBREINDEX
DBCC DBREINDEX ('테이블명','인덱스명', 80)

DBCC INDEXDEFRAG

DBCC  INDEXDEFRAG ('DB명','테이블명','인덱스명')

 

 

[SQL Server 2005에서 인덱스 조각화 정보 확인 방법]

 

[구문]

sys.dm_db_index_physical_stats (

    { database_id | NULL }               /* NULL: 서버내의 모든 데이터베이스 */

    , { object_id | NULL }                /* NULL: 해당 데이터베이스내의 모든 오브젝트 */

    , { index_id | NULL | 0 }              /* NULL: 해당 테이블의 모든 인덱스 */

    , { partition_number | NULL } /* NULL: 해당 오브젝트의 모든 인덱스 */

    , { mode | NULL | DEFAULT } /* NULL: LIMIT */

)

 

select * from sys.dm_db_index_physical_stats (db_id('test'), object_id('t3'), null, null, null)

위의 쿼리를 통해 test 데이터베이스의 t3 테이블에 있는 모든 인덱스의 조각화 정보를 살펴볼 수 있다.

 

avg_fragmentation_in_percent 컬럼의 값이 조각화를 의미하는데,

이 조각화의 수치가 올라갈수록 성능에 나쁜 영향을 미칠 수 있다.

 

http://blog.naver.com/whwlfnsl?Redirect=Log&logNo=70025397241

Posted by 튱띡

댓글을 달아 주세요

삽질삽질삽질/DB2014. 11. 5. 17:42

출처 : http://text.zpne.net/6



현업에서 사용하고 있는 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번


Posted by 튱띡

댓글을 달아 주세요

삽질삽질삽질/DB2014. 11. 5. 17:38


출처 : http://zetus.tistory.com/32



몇천만건 정도되는 테이블을 정리하기 위해 삭제 작업을 하거나,

두 개 이상의 테이블을 조인해서 삭제하는 작업이 오래 소요될 것으로 예상될 경우

만건 단위나 십만건 단위로 작업을 하고 싶을 때가 있다.

 

이렇게 단위별로 나눠서 작업을 하게되면 아래와 같은 장점이 있다.

가령 특정 테이블을 삭제하는 Action을 했는데 예상외로 12시간 이상 삭제가 진행될 경우 이걸 Rollback 하기도, 끝나기를 기다리기도 애매하게 된다. (12시간 삭제 진행하다가 Rollback하게되면 거의 그만큼을 시간이 Rollback 하는데 소요된다.) 이럴 때 특정 건수 단위로 나눠서 했다면, 끊고 새로 시작한 시점까지만 Rollback 하면 된다.

 

 

SET ROWCOUNT 10000 --// 삭제할Row 갯수단위

SET NOCOUNT ON

 

DECLARE @deleted_rows AS INT  --// 한트랜잭션에삭제된Row

DECLARE @total_rows AS INT   --// 삭제된총Row

DECLARE @isStart AS tinyint

DECLARE @log AS VARCHAR(8000)

 

SET @deleted_rows = 999999

SET @total_rows = 0

SET @isStart = 1

 

WHILE (@deleted_rows > 0) BEGIN

    BEGIN TRAN

    --------------------------------------------------------

    DELETE FROM 삭제테이블 WHERE 조건 --// 이 부분 수정

    --------------------------------------------------------

   

    SET @deleted_rows = @@ROWCOUNT

    SET @total_rows = @total_rows + @deleted_rows

    IF (@deleted_rows = 0) BEGIN

        IF (@isStart = 1) BEGIN SET @log = @log + '삭제대상이없습니다.' PRINT @log BREAK END

        ELSE BEGIN CONTINUE END

    END

    ELSE BEGIN

        IF (@isStart = 1) BEGIN

            SET @log = @log + '삭제중... '+CONVERT(varchar(100),@deleted_rows)+' 행이삭제되었습니다. '

        END

        ELSE BEGIN

            SET @log = @log + '삭제중... '+CONVERT(varchar(100),@deleted_rows)+' 행이삭제되었습니다.    (TIME: ' + SUBSTRING(CONVERT(varchar, getdate(),120),12,8) + ')'

        END

    END

   

    PRINT @log

    SET @log = ''

    SET @isStart = 0

    COMMIT TRAN

END

 

 

이걸 실행하게 되면 [메시지] 탭에서 아래와 같은 로그를 볼 수 있습니다.

 

 

삭제중... 10000 행이 삭제되었습니다.
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:24)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:26)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:29)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:33)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:37)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:43)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:11:53)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:12:01)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:12:06)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:12:14)
삭제중... 10000 행이 삭제되었습니다.    (TIME: 15:12:24) 

 

Posted by 튱띡

댓글을 달아 주세요