SQL Server에서 조각화는 크게 3가지로 구분지을 수 있습니다.
첫번째 논리적 조각화
두번째 익스텐트 조각화
세번째 물리적 조각화
논리적 조각화, 익스텐트 조각화 상태는 DBCC SHOWCONTIG 구문으로 확인 할 수 있으며,
주기적으로 인덱스 재구성 작업등으로 이러한 문제를 해결하고 있습니다.
(SQL Server 2005 이후부터는 sys.dm_db_index_physical_stats)
하지만 물리적 조각화는 확인 할 수 없기에, 많은 운용환경에서 물리적 조각화 상태에 대해 크게 고민하지 않고 사용하는 경우가 많습니다.
대부분의 OLTP환경에서는 물리적 조각화로 인한 성능 차이는 크지 않을 것입니다.
하지만 대용량의 데이터를 읽어야 하는 DW환경이라면 물리적 조각화로 인한 성능차이가
있을 것입니다.
랜덤 IO작업의 경우는 조각화로 인한 성능 차이는 순차 IO작업보다 크지 않기에,
이번에는 물리적 조각화로 인한 순차 읽기 작업을 성능 비교 하였습니다.
테스트 결과
조각화 상태인 경우 테이블 SCAN작업이 11초 정도 걸렸으나,
조각모음을 한 후에는 대략 6초 정도에 끝나는 것을 확인 할 수 있습니다.
이러한 편차는 환경마다 달라 질 수 있습니다
여담으로, 이론상 물리적 조각화로 인한 영향도가 전혀 없는 SSD가 많은 환경에 보급된다면
자세한건 아래 스크립트를 참고 하세요~
/*
버전
*/
select @@version
Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86)
Aug 5 2008 01:01:05
Copyright (c) 1988-2005 Microsoft Corporation
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
GO
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.
/*
물리적 조각 모음 후
논리적 조각모음은 여전하다.
*/
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.
*/
Table 'tbl'. Scan count 9, logical reads 579712, physical reads 0, read-ahead reads 579327, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 10922 ms, elapsed time = 6452 ms.
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
