인덱스 조각화

* 인덱스 및 데이터 페이지의 조각화란 

조각화는 디스크 상에 페이지들이 연속적으로 위치해 있지 않고 공간을 두고 떨어져 있는 현상을 말한다.

인덱스를 처음 생성했을 때는 연속해서 페이지들이 위치해 있지만 데이터의 변경(INSERT,UPDATE,DELETE)이 

일어나다 보면 나중에는 페이지가 비연속적으로 위치하게 되어 성능을 떨어뜨리게 되는 경우가 있다.

따라서 이러한 데이터 페이지 및 인덱스 페이지들은 다시 순차적인 데이터 저장 형태로 재조정할 필요가 생긴다. 

데이터 및 인덱스 페이지를 순차적인 순서로 바꾸는 방법은 다음의 5가지가 있다.

1. 해당 테이블 및 데이터베이스를 백업한 후 리스토어 한다.

2. DROP INDEX, CREATE INDEX를 이용해 인덱스를 재생성한다.

3. CREATE INDEX시 DROP_EXISTING 옵션으로 재생성한다.

4. ALTER INDEX 인덱스명 ON 테이블명 REBUILD 로 인덱스를 재구축한다. 

5. ALTER INDEX 인덱스명 ON 테이블명 REORGANIZE 로 인덱스 조각화를 조절한다.

* DMV를 사용하여 인덱스 조각화 조회

sys.dm_db_index_physical_stats 시스템 함수를 사용하여 조각화 정보를 볼수있는데 추가적으로 인덱스명과 

인덱스키  컬럼을 보여주기위해 sp_helpindex프로시저와 sys.sysindexes 시스템 테이블을 사용하였다.

 

USE AdventureWorks2008

go

 

SET NOCOUNT ON

 

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

       DROP TABLE #temp

           

CREATE TABLE #temp(

        DBName VARCHAR(100)

       ,TableName VARCHAR(100)

       ,IndexName VARCHAR(100)

       ,IndexKey VARCHAR(100)

       ,IndexTypeDesc VARCHAR(100)

       ,IndexId INT

       ,IndexLevel INT

       ,AvgFragmentationInPercent FLOAT

       ,FragmentCount INT

       ,AvgFragmentSizeInPages INT

)

 

-- 필요변수선언

DECLARE @tblname VARCHAR(100)

 

-- 커서선언

DECLARE test_cursor cursor for

             select a.name+'.'+b.name 'tblname'

             from sys.schemas as a join sys.tables as b

             on a.schema_id = b.schema_id

 

-- 커서열기

OPEN test_cursor

 

-- 커서사용

-- 커서에서읽어와변수에대입

fetch next from test_cursor

       into @tblname

 

WHILE @@FETCH_STATUS = 0

BEGIN

       IF OBJECT_ID('tempdb..#IndexInfo') IS NOT NULL

              DROP TABLE #IndexInfo

 

       CREATE TABLE #IndexInfo(

       index_name NVARCHAR(100)

       , index_description NVARCHAR(100)

       , index_keys NVARCHAR(100)

       )

 

       INSERT INTO #IndexInfo

       EXEC sp_helpindex @tblname

 

       INSERT INTO #temp

       SELECT DB_NAME(database_id),@tblname as 'TableName',ISNULL(index_name,''),ISNULL(index_keys,'')

                    ,index_type_desc,index_id, index_level,avg_fragmentation_in_percent,fragment_count

                    ,avg_fragment_size_in_pages           

       FROM #IndexInfo AS A RIGHT JOIN

       (

             SELECT b.name,database_id,object_id,index_type_desc,index_id,

                           index_level,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages   

             FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(@tblname),NULL,NULL,'DETAILED')AS A

             INNER JOIN sys.sysindexes AS B

             ON A.index_id = B.indid

             WHERE b.id =OBJECT_ID(@tblname)

             AND alloc_unit_type_desc <> 'LOB_DATA'

       ) AS B

       ON A.index_name = B.name

      

       fetch next from test_cursor

       into @tblname

END

 

-- 커서닫기

close test_cursor

deallocate test_cursor

 

DROP TABLE #IndexInfo

 

SELECT * FROM #temp

ORDER BY TableName

GO

avg_fragmentation_in_percent :   논리적 조각화(인덱스에서 순서가 잘못된 페이지) 비율

fragment_count :                       인덱스의 조각(물리적으로 연속되는 리프 페이지) 수

avg_fragment_size_in_pages :    인덱스 한 조각의 평균 페이지 수

 

 

* split 현상으로 인한 조각화 확인

use testdb

GO

 

SET NOCOUNT ON;

 

CREATE TABLE TBL(

col1 INT ,

col2 CHAR(800),

col3 CHAR(3000)

)

GO

 

DECLARE @i INT

SET @i = 1

WHILE 100 >= @i

BEGIN

INSERT INTO TBL(col1,col2,col3) VALUES(@i,NEWID(),replicate ('a', 3000))

SET @i = @i + 2

END

GO

 

-- 인덱스 생성

CREATE UNIQUE CLUSTERED INDEX ix on TBL(col1)

 

-- 3604 추적 플래그 설정

DBCC TRACEON(3604)

 

-- 익스텐트 확인. 

DBCC EXTENTINFO(testdb,tbl,-1)

page_id 컬럼에 1016페이지에 있는 데이터를 Split 할 것이다.

  

-- 1016페이지에 어떤 데이터가 있는지 확인한다.

DBCC PAGE(testdb,1,1016,3) WITH TABLERESULTS

1016 페이지에는 col1칼럼 61과 63의 데이터가 들어있는 상태에서 62를 INSERT 한다면 Split이 발생하여 새로운 페이지를 할당하고,

할당 받은 페이지에 데이터가 입력이 될 것이다. 

-- DMV를 통해조각화 살펴보기

SELECT index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,fragment_count

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('tbl'),NULL,NULL,'DETAILED')

avg_fragmentation_in_percent 컬럼이 조각화의 백분율 나타낸다. 

  

-- split을 위한 데이터 INSERT

INSERT INTO TBL(col1,col2,col3) VALUES(62,NEWID(),replicate ('a', 3000))

 

-- 익스텐트 확인.

DBCC EXTENTINFO(testdb,tbl,-1)

익스텐트를 다시 확인 해보니 1024 페이지에 pg_alloc 컬럼이 2에서 3으로 증가 했다.

이는 1024~1031을 담당하는 익스텐트에서 1026페이지가 추가되었고 split 된 데이터들은 1026 페이지에 데이터가 들어간 것이다.

 

-- 1016페이지에 어떤 데이터가 있는지 확인한다.

DBCC PAGE(testdb,1,1016,3) WITH TABLERESULTS

1016페이지에는 col1 칼럼의 61과 마지막으로 INSERT 한 62 데이터가 저장되어 있다. 

 

-- 1026페이지에 어떤 데이터가 있는지 확인한다.

DBCC PAGE(testdb,1,1026,3) WITH TABLERESULTS

1016페이지에 있던 63 데이터는 스플릿이 발생하여 1024페이지로 이동되었다.

 

-- DMV를 통한 조각화 살펴보기

SELECT index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,fragment_count

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('tbl'),NULL,NULL,'DETAILED')

다시 조각화를 살펴보면 avg_fragmentation_in_percent 컬럼의 수치가 올라갔다.

 

 

SET STATISTICS IO ON

SELECT * FROM TBL

WHERE col1 BETWEEN 57 AND 61

-- 테이블 'TBL'. 검색 수 1, 논리적 읽기 수 3,

 

SELECT * FROM TBL

WHERE col1 BETWEEN 62 AND 65

-- 테이블 'TBL'. 검색 수 1, 논리적 읽기 수 5

 

split 으로인해 논리적 읽기 수가 달라진다. 

 

 

[참조]

http://msdn.microsoft.com/ko-kr/library/ms188917.aspx