ROW 사이즈에 따른 업데이트 성능에 대한 고민

2009/06/26 09:44 from MS-SQL Server

ROW 사이즈에 따른 업데이트 성능에 대한 고민

 

[하나의 Row 사이즈가 50Byte일 경우]

-- 테이블생성

drop table tbl_A;

create table tbl_A

(

             col_A     int identity(1, 1)

,            col_B     char (38)

,            col_C     datetime

);

 

insert into tbl_A(col_B, col_C) select '', getdate() from master.dbo.sysobjects;

 

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

 

-- 클러스터인덱스생성

create unique clustered index PK_tbl_A on tbl_A(col_A)

 

sp_spaceused tbl_A;

name    rows        reserved    data        index   unused

tbl_A      940032              55048 KB             54896 KB             112 KB   40 KB

 

DBCC SHOWCONTIG ('tbl_A')

- 검색한페이지................................: 6862

- 검색한익스텐트..............................: 858

- 익스텐트스위치..............................: 857

- 익스텐트당평균페이지수........................: 8.0

- 검색밀도[최적:실제].......: 100.00% [858:858]

- 논리검색조각화상태..................: 0.00%

- 익스텐트검색조각화상태...................: 0.23%

- 페이지당사용가능한평균바이트수.....................: 13.5

- 평균페이지밀도(전체).....................: 99.83%

 

-- 컬럼추가

alter table tbl_A add col_D datetime null;

 

-- 컬럼업데이트

set statistics io on

update tbl_A set col_D = col_C + 1

set statistics io off

 

-- 50 Byte

테이블'tbl_A'. 검색수1, 논리적읽기수75577, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

(940032개행적용됨)

 

DBCC SHOWCONTIG ('tbl_A')

- 검색한페이지................................: 13723

- 검색한익스텐트..............................: 1725

- 익스텐트스위치..............................: 13722

- 익스텐트당평균페이지수........................: 8.0

- 검색밀도[최적:실제].......: 12.50% [1716:13723]

- 논리검색조각화상태..................: 99.74%

- 익스텐트검색조각화상태...................: 0.52%

- 페이지당사용가능한평균바이트수.....................: 3506.5

- 평균페이지밀도(전체).....................: 56.68%

 

sp_spaceused tbl_A;

name    rows        reserved    data        index   unused

tbl_A      940032              110088 KB           109784 KB           288 KB   16 KB

 

[하나의 Row 사이즈가 200Byte일 경우]

-- 테이블생성

drop table tbl_A;

create table tbl_A

(

             col_A     int identity(1, 1)

,            col_B     char (188)

,            col_C     datetime

);

 

insert into tbl_A(col_B, col_C) select '', getdate() from master.dbo.sysobjects;

 

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

 

-- 클러스터인덱스생성

create unique clustered index PK_tbl_A on tbl_A(col_A)

 

sp_spaceused tbl_A;

name    rows        reserved    data        index   unused

tbl_A      940032              198344 KB           197904 KB           336 KB   104 KB

 

DBCC SHOWCONTIG ('tbl_A')

- 검색한페이지................................: 24738

- 검색한익스텐트..............................: 3093

- 익스텐트스위치..............................: 3092

- 익스텐트당평균페이지수........................: 8.0

- 검색밀도[최적:실제].......: 100.00% [3093:3093]

- 논리검색조각화상태..................: 0.00%

- 익스텐트검색조각화상태...................: 0.13%

- 페이지당사용가능한평균바이트수.....................: 154.1

- 평균페이지밀도(전체).....................: 98.10%

 

-- 컬럼추가

alter table tbl_A add col_D datetime null;

 

-- 컬럼업데이트

set statistics io on

update tbl_A set col_D = col_C + 1

set statistics io off

 

-- 200 Byte

테이블'tbl_A'. 검색수1, 논리적읽기수247841, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

(940032개행적용됨)

 

DBCC SHOWCONTIG ('tbl_A')

- 검색한페이지................................: 49475

- 검색한익스텐트..............................: 6201

- 익스텐트스위치..............................: 49474

- 익스텐트당평균페이지수........................: 8.0

- 검색밀도[최적:실제].......: 12.50% [6185:49475]

- 논리검색조각화상태..................: 99.27%

- 익스텐트검색조각화상태...................: 0.18%

- 페이지당사용가능한평균바이트수.....................: 3973.0

- 평균페이지밀도(전체).....................: 50.91%

 

sp_spaceused tbl_A;

name    rows        reserved    data        index   unused

tbl_A      940032              396808 KB           395800 KB           976 KB   32 KB

위의 결과를 보면 50Byte에 비해서 대략 4배정도의 차이가 나는 것을 확인할 수 있다. (예상대로임)

 

[하나의 Row 사이즈가 500Byte일 경우]

-- 테이블생성

drop table tbl_A;

create table tbl_A

(

             col_A     int identity(1, 1)

,            col_B     char (488)

,            col_C     datetime

);

 

insert into tbl_A(col_B, col_C) select '', getdate() from master.dbo.sysobjects;

 

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

insert into tbl_A(col_B, col_C) select '', getdate() from tbl_A;

 

-- 클러스터인덱스생성

create unique clustered index PK_tbl_A on tbl_A(col_A)

 

sp_spaceused tbl_A;

name    rows        reserved    data        index   unused

tbl_A      940032              502216 KB           501352 KB           824 KB   40 KB

 

DBCC SHOWCONTIG ('tbl_A')

- 검색한페이지................................: 62669

- 검색한익스텐트..............................: 7834

- 익스텐트스위치..............................: 7833

- 익스텐트당평균페이지수........................: 8.0

- 검색밀도[최적:실제].......: 100.00% [7834:7834]

- 논리검색조각화상태..................: 0.01%

- 익스텐트검색조각화상태...................: 0.13%

- 페이지당사용가능한평균바이트수.....................: 461.0

- 평균페이지밀도(전체).....................: 94.30%

 

-- 컬럼추가

alter table tbl_A add col_D datetime null;

 

-- 컬럼업데이트

set statistics io on

update tbl_A set col_D = col_C + 1

set statistics io off

 

-- 500 Byte

테이블'tbl_A'. 검색수1, 논리적읽기수62772, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

(940032개행적용됨)

 

DBCC SHOWCONTIG ('tbl_A')

- 검색한페이지................................: 62669

- 검색한익스텐트..............................: 7834

- 익스텐트스위치..............................: 7833

- 익스텐트당평균페이지수........................: 8.0

- 검색밀도[최적:실제].......: 100.00% [7834:7834]

- 논리검색조각화상태..................: 0.01%

- 익스텐트검색조각화상태...................: 0.13%

- 페이지당사용가능한평균바이트수.....................: 341.0

- 평균페이지밀도(전체).....................: 95.79%

 

sp_spaceused tbl_A;

name    rows        reserved    data        index   unused

tbl_A      940032              502216 KB           501352 KB           824 KB   40 KB

용량이 10배 증가하였는데 왜? 훨씬 적은 I/O가 발생하였을까요? ^^

 

저작자 표시 비영리 동일 조건 변경 허락
이올린에 북마크하기
Posted by 안보갑 트랙백 0 : 댓글 2

Trackback : http://nexondbteam.tistory.com/trackback/53 관련글 쓰기

댓글을 달아 주세요

  1. addr | edit/del | reply 르매 2009/06/26 21:00

    50byte인 경우 페이지 밀도가 99.83%이니 페이지당 약 160 row 미만이 저장되어있을테고, 8byte컬럼이 추가되면 해당 페이지에서 추가로 필요한 공간은 1280byte죠... 하지만 실제 여유 공간은 13.5byte뿐이므로... page가 split되면서 많은 I/O가 발생합니다.

    200byte일때도 계산해보면 비슷한 상황이네요.

    하지만, 500byte일 때는 한 페이지에 15개의 row가 들어가 있고, 컬럼 추가로 인해 새로 필요한 공간은 120byte입니다. 그런데 페이지 당 평균 341byte가 남아 있으니 page split이 발생하지 않습니다.

    실제로 500byte인 경우 update 전/후의 페이지 수를 비교하면 62669로 동일하고, 페이지 밀도만 증가했음을 볼 수 있네요. ^^