물리적 조각화에 의한 SCAN 성능 비교

알 수 없는 사용자 2009. 6. 20. 20:41

2009. 6. 20. 20:41

SQL Server에서 조각화는 크게 3가지로 구분지을 수 있습니다.

첫번째 논리적 조각화

두번째 익스텐트 조각화

세번째 물리적 조각화

논리적 조각화, 익스텐트 조각화 상태는 DBCC SHOWCONTIG 구문으로 확인 할 수 있으며,
주기적으로 인덱스 재구성 작업등으로 이러한 문제를 해결하고 있습니다.
(SQL Server 2005 이후부터는 sys.dm_db_index_physical_stats)

하지만 물리적 조각화는 확인 할 수 없기에, 많은 운용환경에서 물리적 조각화 상태에 대해 크게 고민하지 않고 사용하는 경우가 많습니다.

대부분의 OLTP환경에서는 물리적 조각화로 인한 성능 차이는 크지 않을 것입니다.
하지만 대용량의 데이터를 읽어야 하는 DW환경이라면 물리적 조각화로 인한 성능차이가
있을 것입니다.

그래서 이번에는 물리적 조각화로 인한 SQL Server의 성능적 영향에 대해서 알아보았습니다.

랜덤 IO작업의 경우는 조각화로 인한 성능 차이는 순차 IO작업보다 크지 않기에,

이번에는 물리적 조각화로 인한 순차 읽기 작업을 성능 비교 하였습니다.

테스트 결과
조각화 상태인 경우 테이블 SCAN작업이 11초 정도 걸렸으나,

조각모음을 한 후에는 대략 6초 정도에 끝나는 것을 확인 할 수 있습니다.

이러한 편차는 환경마다 달라 질 수 있습니다

여담으로, 이론상 물리적 조각화로 인한 영향도가 전혀 없는 SSD가 많은 환경에 보급된다면

이러한 고민도 사라지겠죠?

자세한건 아래 스크립트를 참고 하세요~

버전

select @@version

Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86)

Aug 5 2008 01:01:05

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

순차적으로 두개의 DB의 파일을 1MB 단위씩 증가 시켜 물리적 조각화 상태를 만듬

물리적 조각화가 발생

DECLARE @I VARCHAR(100)

SET @I= '4'

WHILE 1=1

BEGIN

EXEC ('ALTER DATABASE TEST MODIFY FILE(NAME = ''TEST'',SIZE='+@I+')')

EXEC ('ALTER DATABASE TEST1 MODIFY FILE(NAME = ''TEST1'',SIZE='+@I+')')

SET @I=@I+1

IF @I=5000 BREAK

END

테스트를 위해 4000만건 대략 5GB 정도의 데이터를 입력한다.

USE TEST

SELECT TOP 40000000

row_number() over(order by (select 1)) as col1

,cast('' as char(100)) col2

INTO tbl

FROM master.dbo.spt_values A,master.dbo.spt_values A1,master.dbo.spt_values A2,master.dbo.spt_values A3

SHOWCONTIG를 통해 논리적 조각화 상태를 본다.

방금 만들어서, 깔끔하다!!

dbcc showcontig(tbl)

DBCC SHOWCONTIG scanning 'tbl' table...

Table: 'tbl' (2105058535); index ID: 0, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 579712

- Extents Scanned..............................: 72465

- Extent Switches..............................: 72464

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [72464:72465]

- Extent Scan Fragmentation ...................: 0.10%

- Avg. Bytes Free per Page.....................: 23.0

- Avg. Page Density (full).....................: 99.72%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In-memory로 인해 테스트에 영향을 줄 수 있으니~ BP에 있는건 모두 날리자.

exec sp_msforeachtable 'checkpoint 1'

dbcc dropcleanbuffers

--2

SET STATISTICS TIME ON

SET STATISTICS IO ON

SELECT COUNT(*) FROM tbl

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

처음 데이터를 SCAN(물리적 조각화 상태)

Table 'tbl'. Scan count 9, logical reads 579712, physical reads 0, read-ahead reads 579647, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CPU time = 9875 ms, elapsed time = 10829 ms.

물리적 조각 모음 후

논리적 조각모음은 여전하다.