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가 발생하였을까요? ^^