좀 더 빠르게 데이터를 저장하기 위해 최소 로깅을 하는 것은 필수입니다. 데이터를 저장하는데 초점을 맞추기 때문에 로그를 쓰는 부분을 줄여 보자는 이야기죠~!

이러한 최소 로깅을 하기 위한 기본 조건들을 정리해 보았습니다.

 

1.       복구 모델
전체 복구 모델의 경우 모든 변경작업을 로깅 하므로 대량 로그 복구 모델또는 단순 복구 모델이 필요합니다.

2.      이미 생성된 테이블에 데이터를 입력하는 경우 요구조건.

A.       테이블이 복제되고 있지 않아야 합니다.

B.        TABLOCK 잠금을 사용하여 데이터를 입력해야 합니다.

C.       데이터 입력시 로그에 익스텐트 할당에 대한 기록을 합니다.

 

         3.    인덱스 상황에 따른 최소 로깅 여부

 

인덱스 페이지 로깅

데이터 페이지 로깅

힙 테이블

없음

최소

비어있는 테이블 + 넌클러스터드 인덱스

최소

최소

데이터가 있는 테이블 + 넌클러스터드 인덱스

전체

최소

비어있는 테이블 + 클러스터드 인덱스

최소

최소

데이터가 있는 테이블 + 클러스터드 인덱스

전체

전체

 

@@ 실제 테스트 

위 내용을 토대로 실제 테스트를 해 보았습니다.

500 MB , 3000만건 데이터 입력시 ldf 크기.

 

-- 테이블 생성

create table t1 (col1 bigint)

 

-- 각 경우에 따라 인덱스 생성

create index NC_t1 on t1 (col1)
create clustered index NC_t1 on t1 (col1)

 

-- 약 500MB 입력

insert into t1 with (tablock)
select top 30840470 ROW_NUMBER() over(order by (select 1))
from sysindexes a
 , sysindexes b
 , sysindexes c
 , sysindexes d

 

-- 각 사이즈 확인 및 초기화

dbcc showfilestats
dbcc sqlperf(logspace)
checkpoint
dbcc shrinkfile ('DB1_log', 1)

 

Ldf 크기(MB)

힙 테이블

7.56

비어있는 테이블 + 넌클러스터드 인덱스

최소로깅이 안됨. -_- 뭐지??

데이터가 있는 테이블 + 넌클러스터드 인덱스

위처럼 최소로깅 안됨.

비어있는 테이블 + 클러스터드 인덱스

2.75

(하지만 tempdb 사용하며, tempdb 사이즈가 입력데이터만큼 커짐. 정렬하여 익스텐트 단위로 데이터를 넣어야 하기 때문으로 생각됨.)

데이터가 있는 테이블 + 클러스터드 인덱스

위처럼 tempdb 사용도 하고 ldf에 전체로그를 기록함.

 

결과적으로 힙 테이블 이외에는 BOL을 보고 생각했던것만큼 좋은 결과가 나오지는 않았습니다. 

힙 테이블이 아닌 이상 만족할만한 성능을 보이지는 않았기에 bcp, bulk insert등의 방식으로도 테스트 해 보았지만 동일한 결과가 나왔습니다.

다음 포스팅에서는 좀 더 여러가지 테스트를 해보기로 하겠습니다. ^_^

하만철 / Ha Man-cheol

AND


sql 2000에서 mdf 파일 확장 중 checkpoint 가 발생하는지 간단히 확인해 보았습니다.

select @@version
Microsoft SQL Server  2000 - 8.00.2273 (Intel X86)
 Mar  7 2008 22:19:58
 Copyright (c) 1988-2003 Microsoft Corporation
 Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

@@ 사용 쿼리

-- @@ 1번 세션
-- 테이블 생성
select top 0 *
into t1
from sysobjects

-- 루프 돌면서 데이터 입력
declare @cnt int
set @cnt = 1

while @cnt < 1000000 begin -- 100만번
         insert into t1
         select *
         from sysobjects
end


-- @@ 2번 세션
-- 파일 확장 시작
alter database testDB modify file (name = 'testDB', size =15000)

-- @@ 3번 세션
-- checkpoint 강제 발생
checkpoint


@@ 결과
checkpoint 가 정상적으로 발생하는 것을 확인.
기존의 데이터의 변경내역이 반영되는 것이어서 예상했던 것처럼 잘 동작 하였습니다.
그렇다면 sql 2000의 운영중 DBA가 수동으로 mdf를 확장시킬 때 어떤걸 조심해야 할까요?
디스크 성능만 빵빵하면 그냥 암무때나 확장해도 되는건가? ㅎㅎ

하만철 / Ha Man-cheol
EMail : feisia@hanmail.net

AND


게시에서 SP 실행을 복제할 수 있습니다.
아래 그림처럼 SP를 복제하면서 복제 방식을 "저장 프로시저 실행" or "SP의 직렬화된 트랜잭션에서 실행"을 선택하면 게시에서 실행한 SP가 구독에서 그대로 실행되게 됩니다.


복제되는 SP를 실행할 경우 구독에서 다음과 같이 SP를 그대로 수행하는것을 확인할 수 있습니다.

SQL 2000에서부터 사용 가능한 기능입니다. :)

하만철 / Ha Man cheol
EMail :
feisia@hanmail.net
AND


ENT 에디션에서 만든 파티션 테이블을 STD 에디션에 복구하면 어떻게 될까?

SQL은 오류나면서 복원이 안되네요; 쩝;;
(오라클은 복원하여 사용은 가능하지만 추가로 파티션 테이블을 생성하는건 안되지만 합니다. ㅎㅎ~ 참고링크)
복원을 모두 수행한 다음 파티션 테이블이 있는지를 체크하는 것 같습니다.
또, 복원후 DB파일과 DB는 존재하지만 사용은 할 수 없는 상태가 됩니다. (뭔가 좀;; ^^; )

파티션 테이블을 포함한 DB 버전
select @@version
Microsoft SQL Server 2005 - 9.00.3310.00 (Intel X86)
 Dec 19 2008 00:58:18
 Copyright (c) 1988-2005 Microsoft Corporation
 Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

파티션 테이블을 포함한 DB 를 복원한 DB 버전
select @@version
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
 Nov 24 2008 13:01:59
 Copyright (c) 1988-2005 Microsoft Corporation
 Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

 @@ 파티션 테이블을 포함하는 DB 생성
-- drop database partTest
CREATE DATABASE [partTest]
ON  PRIMARY
( NAME = N'partTest', FILENAME = N'E:\SQL2K5\partTest.mdf'
 , SIZE = 3 , MAXSIZE = UNLIMITED, FILEGROWTH = 1 )
, FILEGROUP fg1
( NAME = 'partTest1', FILENAME = N'E:\SQL2K5\partTest1.ndf'
    , SIZE = 3,  MAXSIZE = UNLIMITED, FILEGROWTH = 1 )
, FILEGROUP fg2
( NAME = 'partTest2', FILENAME = N'E:\SQL2K5\partTest2.ndf'
    , SIZE = 3,  MAXSIZE = UNLIMITED, FILEGROWTH = 1 )
, FILEGROUP fg3
( NAME = 'partTest3', FILENAME = N'E:\SQL2K5\partTest3.ndf'
    , SIZE = 3,  MAXSIZE = UNLIMITED, FILEGROWTH = 1 )
, FILEGROUP fg4
( NAME = 'partTest4', FILENAME = N'E:\SQL2K5\partTest4.ndf'
    , SIZE = 3,  MAXSIZE = UNLIMITED, FILEGROWTH = 1 )
 LOG ON
( NAME = N'partTest_log', FILENAME = N'E:\SQL2K5\partTest_log.LDF'
 , SIZE = 1 , MAXSIZE = UNLIMITED , FILEGROWTH = 1)
go
use partTest
GO

 -- drop partition function PF1
CREATE PARTITION FUNCTION PF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

 -- drop partition scheme Ps1
CREATE PARTITION SCHEME PS1
AS PARTITION PF1
TO (fg1, fg2, fg3, fg4) ;
GO

 CREATE TABLE PT1 (col1 int, col2 int)
ON PS1 (col1)
GO

insert into pt1 select 1, 1
insert into pt1 select 2, 1
insert into pt1 select 101, 1
insert into pt1 select 102, 1
insert into pt1 select 1001, 1
insert into pt1 select 1002, 1

set statistics profile on
select * from pt1 where col1 = 101
select * from pt1 where col1 = 1001
set statistics profile off

  |--Table Scan(OBJECT:([partTest].[dbo].[PT1]), WHERE:([partTest].[dbo].[PT1].[col1]=(101)) PARTITION ID:((3)))
  |--Table Scan(OBJECT:([partTest].[dbo].[PT1]), WHERE:([partTest].[dbo].[PT1].[col1]=(1001)) PARTITION ID:((4)))

 backup database partTest to disk = 'e:\sql2k5\partTest.BAK'

 @@ 파티션 테이블을 포함하는 DB를 복사해 가서 복원

restore database partTest
from disk = 'D:\Backup\partTest.BAK'
with move 'partTest' to 'D:\mssql\data\partTest.mdf'
,move 'partTest1' to 'D:\mssql\data\partTest1.ndf'
,move 'partTest2' to 'D:\mssql\data\partTest2.ndf'
,move 'partTest3' to 'D:\mssql\data\partTest3.ndf'
,move 'partTest4' to 'D:\mssql\data\partTest4.ndf'
,move 'partTest_log' to 'D:\mssql\data\partTest_log.LDF'

파일 1에서 데이터베이스 'partTest', 파일 'partTest'에 대해 176개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'partTest', 파일 'partTest1'에 대해 16개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'partTest', 파일 'partTest2'에 대해 16개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'partTest', 파일 'partTest3'에 대해 16개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'partTest', 파일 'partTest4'에 대해 16개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'partTest', 파일 'partTest_log'에 대해 7개의 페이지를 처리했습니다 .
메시지 3167, 수준 16, 상태 1, 줄 1
RESTORE에서 데이터베이스 'partTest'을(를) 시작할 수 없습니다.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE DATABASE이(가) 비정상적으로 종료됩니다.
메시지 905, 수준 21, 상태 1, 줄 1
데이터베이스 'partTest'은(는) 파티션 함수 'PF1'을(를) 포함하므로 이 버전의 SQL Server에서 시작할 수 없습니다. 분할은 SQL Server Enterprise Edition에서만 지원됩니다.

AND


이전에 유니크 제약조건이 있는 게시 데이터 update시 구독에 반영하는 방식에 대해 이야기 한적이 있습니다.
찾아보니 이렇게 유니크인덱스 또는 PK 컬럼이 update 되었을때 delete / insert 로 반영되는것을 "deferred update" 라고 한다는 것을 알았습니다.

          - http://support.microsoft.com/kb/238254
          - http://support.microsoft.com/kb/302341/EN-US/
          - http://support.microsoft.com/kb/160181/EN-US/

그리고 구독의 SP를 변경하여 사용하는 경우 update 명령이 delete / insert로 변경되어 적용될 경우 문제가 될수도 있기 때문에 다음 trace flag를 통하여 어떤 방식으로 update 명령을 구독에 반영할 지 제어 할 수 있습니다.

-- update로 동작
dbcc traceon (8207)

-- delete / insert로 동작
dbcc traceon (8202)

이 중 8207 flag의 경우 다음 경우에 문제가 될 수 있으므로 주의해야 한다고 말하고 있습니다.

From this point forward, an update to a unique column affects only one row (a singleton update) and is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair.
(여러개 row가 업데이트 되는 경우는 delete / insert로 동작함 (민석형님 감사합니다. ㅎㅎ~))

Important: Typically, you use trace flag 8207 with read-only transactional replication. Do not use trace flag 8207 with updatable subscriptions if:
  • A primary key update can occur at the subscriber. 
        (구독에서 PK가 업데이트 되는 경우)
  • An update to a column that is included in a unique constraint can occur at the subscriber. 
       (구독의 유니크 제약조건에 포함된 컬럼이 업데이트 되는 경우)
  • An update to a column that is included in a unique index can occur at the subscriber.
       (구독의 유니크 인덱스에 포함된 컬럼이 업데이트 되는 경우)
  • 이상한점은, 복제의 경우 로그를 읽어 데이터를 반영하는 방식이기 때문에 동일한 update문을 수행했더라도 유니크 제약조건에 의해 update 와 delete / insert의 경우 로그가 다르게 쌓일거라고 생각했지만 동일하게 쌓인것을 확인할 수 있었습니다.
    확인은 ::fn_dblog 명령을 통해 하였고 엑셀에 붙여서 확인해본 결과 완전히 동일하게 로그가 쌓인것을 확인할 수 있었습니다.



    혹시 "해당 테이블의 인덱스를 참조 하는건가?" 하는 생각이 들어 다음과 같이 데이터 변경 후 인덱스를 변경하는 방식으로 수행해 보았는데 update 구문이 실행 당시의 인덱스 상황에 의해 update 또는 delete / insert로 풀리는것을 확인할 수 있었습니다.
    즉, 로그리더는 게시의 로그파일만 읽어서 배포로 넘기지 테이블의 인덱스 정보를 참조하지는 않았습니다.

    update t1 set col2 = 4 where col2 = 3
    create unique index ix_t1_col2 on t1 (col2)

    update t1 set col2 = 3 where col2 = 4
    drop index t1.ix_t1_col2

    혹시 로그리더가 읽어가는 데이터에 대해 좀 더 자세히 아시거나 좀더 상세히 로그를 확인할 수 있는 방법을 알고 계신분이 계시다면 알려 주시면 감사하겠습니다. ^^


    하만철 / Ha Man cheol
    EMail : feisia@hanmail.net

    AND


    트랜잭션 복제 환경에서 게시자에서 변경된 데이터는 SP 또는 ad-hoc 쿼리의 형태로 구독자에 반영하게 된다. 예를들어 2개의 row를 한번에 업데이트 하는 구문이 게시자에서 수행되면 구독자에서는 각각의 row 를 업데이트하는 SP가 2번 수행되어 변경된 값을 반영하게 된다.

    이때 게시자에서 변경된 데이터가 유니크 인덱스의 컬럼중 하나라면 유니크 제약조건을 보장해 주는데 문제가 발생할 수 있다. 예를 들어 게시자에서 다음과 같은 스크립트를 수행하여 데이터를 업데이트 한다고 가정해 보자.

    -- 테이블 생성. 이 테이블이 복제에 사용된다고 가정
    create table t1 (
     col1 int not null constraint PK_t1 primary key
    , col2 int
    )

    -- 테스트 데이터 입력.
    insert into t1 select 1, 1
    insert into t1 select 2, -1

    -- 유니크 인덱스 생성
    create unique index uni_t1_col2 on t1 (col2)

    -- 데이터 업데이트.
    update t1 set col2 = col2*(-1)

    마지막 라인에서 데이터 업데이트시 "한번에 2개 row를 업데이트 했다"는 내용을 2개의 SP에 나눠서 구독에 적용할 경우 유니크 조건을 만족시키기 위하여 구독에서는 update 대신 delete 후 insert 의 동작으로 변경하여 반영한다. 따라서 구독에서는 다음과 같은 동작으로 변경된다.

    delete t1 where col1 = 1
    delete t1 where col1 = 2
    insert into t1 (col1, col2) values (1, -1)
    insert into t1 (col1, col2) values (1, 1)

    따라서 트랜잭션 복제 환경에서 아티클에 유니크 제약조건이 걸려있는 컬럼을 업데이트 하는 경우 동기화에 좀 더 많은 비용이 든다는것을 고려해야 한다.

    select @@version
    Microsoft SQL Server  2000 - 8.00.2282 (Intel X86)
            Dec 30 2008 02:22:41 
            Copyright (c) 1988-2003 Microsoft Corporation
            Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)


    하만철 / Ha Man cheol
    EMail : feisia@hanmail.net

    AND

    로그 축소 테스트

    SQL Server 2008. 12. 15. 02:41


    예전에
    로그백업 로그를 축소했는데 용량이 원하는 만큼 줄어들지 않고 다시한번 로그백업 축소를 하면 원하는대로 줄어드는 현상이 있어 확인해본 결과 로그 축소시 활성 VLF 뒷부분만 축소가 된다 것을 알게 되었었다.

    오늘은 부분에 대해 실제로 테스트를 보았다.

     

    select @@version

     

    -- 집에 있는 노트북에 패치가 안되어 있었네요; 인터넷이 열악한 상황이라 패치하기가 힘드니 뭐라하지 마세요~ ^^;

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

           Oct 14 2005 00:33:37

           Copyright (c) 1988-2005 Microsoft Corporation

           Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

     

    -- drop database logtest

    CREATE DATABASE [logTest] ON  PRIMARY

    ( NAME = N'logTest', FILENAME = N'D:\logTest.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB )

     LOG ON

    ( NAME = N'logTest_log', FILENAME = N'D:\logTest_log.LDF' , SIZE = 1MB , MAXSIZE = 2048GB , FILEGROWTH = 1MB)

    go

    use logTest

    go

     

    -- Full backup 한번 한다

    backup database logTest to disk = 'd:\logtest.bak' with init

     

    -- 로그 파일을 1M 2 증가 시킨다.

    dbcc loginfo

    alter database [logTest] modify file (name = 'logTest_log', SIZE = 2MB)

    alter database [logTest] modify file (name = 'logTest_log', SIZE = 3MB)

    dbcc loginfo

     

    -- 로그를 적당히 쌓고 한다. 2.3M정도 로그가 쌓인 것을 확인할 있다.

    create table t1 (col1 int, col2 char(7000))

    go

    insert into t1 select 1,''

    go 260

    dbcc loginfo

     

    -- 로그백업 변화를 확인한다. 논리로그 끝을 포함한 VLF 활성상태로 남았다.

    backup log logtest to disk = 'd:\logtest.trn' with init

    dbcc loginfo

     

    -- 로그 축소를 하면 확성 VLF뒷부분만 잘린 것을 확인할 있다. 뒤쪽 3개의 VLF 반환된 것을 확인할 있다.

    dbcc shrinkfile (logTest_log, 1)

     

    -- 다시한번 로그백업 다음

    backup log logtest to disk = 'd:\logtest.trn' with init

    dbcc loginfo

     

    -- 다시 로그 축소를 하면 역시 뒷부분만 잘려 원하는 크기로 줄어든 것을 확인할 있다.

    dbcc shrinkfile (logTest_log, 1)

     

     

    하만철 / Ha Man cheol

    AND


    출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&pg=3&idx=873

    SSIS의 FastLoadOptions 를 확인하다 발견한 테스트 자료.
    많은 시간을 들여 테스트 한 자료를 공유해 주신 한대성님께 감사드립니다~ ^^

    --------------------------------------------------------------------------------

    Bulk Insert 옵션에 따른 성능 비교

     

    한대성

    MS SQL Server MVP

    에이디컨설팅 | SQLLeader.com 운영자

     

     

     

    이전에 SQL Server 2005에서의 대량의 데이터를 로딩한 방법 별 수행 성능을 비교한 글을 올린 적이 있습니다. OPENROWSET, BCP, BULK INSERT, SSIS를 이용하여 데이터를 로딩하는 속도 비교를 측정한 글입니다.  (http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005TSQL&intSeq=351)

     

     

    이번 글은 BULK INSERT 명령을 이용할 때 여러 경우에 대한 성능 비교 결과를 정리한 것입니다.

     

    다음과 같은 형태의 10,000,000 건의 텍스트 파일을 Bulk Insert 명령을 이용하여 테이블로 로딩할 때 옵션에 따른 수행 시간 비교입니다. 각 경우에 대해 3회 반복 수행을 한 후, 평균 소요 시간을 이용하여 비교하였습니다.

     

     

    [테스트 파일 - TestFile.txt]

    Int형 순번 열, 100자리 문자 열 : 10,000,000

     

     

    [저장 테이블 - TESTTABLE]

    USE TEMPDB

    GO

     

    ----------------------------------------

    --TEST TABLE

    -----------------------------------------

    CREATE TABLE TESTTABLE

    (

            SEQ INT,

            COL CHAR(100)

    )

    GO

       ※ DB File Size 증가로 인한 지연을 막기 위해 DB Size를 충분히 크게 늘린 후 수행함.

     

     

     

    TEST 1) 인덱스가 없는 테이블에 로딩

    --인덱스가 없는 테이블에 로딩

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n'

          )

    GO

    처리 시간

                  1- 00:01:48

                  2- 00:01:50

                  3- 00:01:49

                 평균 - 00:01:49 (109)

     

     

     

    TEST 2) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용

    --인덱스가 없는 테이블에 로딩 + TABLOCK 옵션

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             TABLOCK

          )

    GO

    처리 시간

                  1- 00:00:34

                  2- 00:00:33

                  3- 00:00:34

                 평균 - 00:00:34 (34)

     

     

     

    TEST 3) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용 + 일괄 처리 크기 100,000으로 설정

    --인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 + RPB 100,000

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             TABLOCK,

             ROWS_PER_BATCH = 100000

          )

    GO

    처리 시간

                  1- 00:00:33

                  2- 00:00:33

                  3- 00:00:34

                 평균 - 00:00:33 (33)

     

     

     

     

    [Clustered Index 생성]

    --Seq 컬럼에 Clustered Index 생성

    CREATE CLUSTERED INDEX CIX_TESTTABLE ON TESTTABLE(SEQ)

    GO

     

     

     

    TEST 4) Clustered Index가 있는 테이블에 로딩

    --Clustered Index 있는 테이블에 로딩

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n'

          )

    GO

    처리 시간

                  1- 00:03:40

                  2- 00:03:39

                  3- 00:03:39

                 평균 - 00:03:39 (219)

     

     

     

    TEST 5) Clustered Index가 있는 테이블에 로딩 + 일괄 처리 크기 100,000

    -- Clustered Index 있는 테이블에 로딩

    -- RPB 100,000

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             ROWS_PER_BATCH = 100000

          )

    GO

    처리 시간

                  1- 00:02:49

                  2- 00:02:47

                  3- 00:02:48

                 평균 - 00:02:48 (168)

     

     

     

    TEST 6) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션

    -- Clustered Index 있는 테이블에 로딩

    -- ORDER 옵션

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             ORDER(SEQ ASC)

          )

    GO

    처리 시간

                  1- 00:02:21

                  2- 00:02:21

                  3- 00:02:20

                 평균 - 00:02:21 (141)

     

     

     

    TEST 7) Clustered Index가 있는 테이블에 로딩 + 일괄 처리 크기 100,000 + ORDER 옵션

    -- Clustered Index 있는 테이블에 로딩

    -- RPB 100,000 + ORDER 옵션

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             ROWS_PER_BATCH = 100000,

             ORDER(SEQ ASC)

          )

    GO

    처리 시간

                  1- 00:02:20

                  2- 00:02:21

                  3- 00:02:21

                 평균 - 00:02:21 (141)

     

     

     

    TEST 8) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션 + TABLOCK 옵션

    -- Clustered Index 있는 테이블에 로딩

    -- ORDER 옵션 + TABLOCK 옵션

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             ORDER(SEQ ASC),

             TABLOCK

          )

    GO

    처리 시간

                  1- 00:00:37

                  2- 00:00:37

                  3- 00:00:37

                 평균 - 00:00:37 (37)

     

     

     

    TEST 9) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션 + TABLOCK 옵션 + 일괄 처리 크기 100,000

    -- Clustered Index 있는 테이블에 로딩

    -- RPB 100,000 + ORDER 옵션 + TABLOCK 옵션

    BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

    WITH

          (

             FIELDTERMINATOR =',',

             ROWTERMINATOR ='\n',

             ROWS_PER_BATCH = 100000,

             ORDER(SEQ ASC),

             TABLOCK

          )

    GO

    처리 시간

                  1- 00:00:36

                  2- 00:00:37

                  3- 00:00:37

                 평균 - 00:00:37 (37)

     

     

    Clustered Index가 없는 힙(Heap) 테이블에 Bulk Insert 명령을 이용하여 로딩할 경우, TABLOCK의 옵션 설정에 따라 3배 정도의 속도 차이가 있습니다. 하지만, 일괄 처리 크기(Rows Per Batch) 설정은 성능에 별 영향을 미치지 않습니다. (34 : 33)

    TABLOCK 옵션은 Bulk Insert 작업 수행 시 해당 테이블에 테이블 수준의 잠금을 설정함으로써, 하위 수준의 잠금 사용 시 발생하는 잠금 경합(Escalation)을 줄일 수 있을 뿐만 아니라 잠금을 설정하고 해제하는 단계를 줄여주기 때문에 처리 성능이 크게 향상될 수 있습니다. (옵션 적용 전 - 109, 옵션 적용 - 34) 하지만, 이 옵션을 적용하여 로딩할 경우에는 WITH (NOLOCK) 옵션 또는 WITH (READUNCOMMITTED) 옵션을 사용하더라도 로딩 중에 해당 테이블의 데이터를 읽을 수 없습니다. 따라서 로딩 중에 다른 부분에서 사용되는 테이블인 경우에는 이 옵션을 적용할 경우 블로킹을 발생시킬 수 있기 때문에 주의해야 합니다.

     

     

     

     

     

     

    Clustered Index가 설정되어 있는 테이블인 경우에는 Heap 테이블과는 달리 몇 가지 옵션을 더 설정할 수 있습니다.

    우선 옵션 없이 단순히 Bulk Insert를 수행할 경우, 입력된 데이터에 대해 인덱스 구성 작업을 수행해야 하기 때문에 힙 테이블의 로딩과 비교했을 때 훨씬 더 많은 시간이 소요됩니다. (Heap - 109, Clustered Index - 219)

    일괄 처리 크기(Rows Per Batch) 100,000건으로 설정하게 되면 전체 건(10,000,000)의 인덱스를 한 번에 구성하는 대신 100,000건씩 나누어서 구성하기 때문에 처리 시간이 단축될 수 있습니다. (일괄 처리 - 219, RPB 100,000 168)

    만약 입력되는 데이터가 적재될 테이블의 Clustered 열과 동일한 순서로 정렬된 데이터인 경우에는 ORDER(열 이름 [ASC | DESC]) 옵션을 이용하여 처리 시간을 단축 시킬 수 있습니다. 본 테스트에서는 텍스트 파일의 첫 번째 열로 정렬된 데이터이며, 이 데이터가 테이블의 SEQ열로 입력되기 때문에 ORDER 옵션을 적용할 경우, 처리 시간이 단축됩니다. (옵션 미 적용 - 219, ORDER 옵션 적용 - 141)

    또한 Heap에서와 마찬가지로 TABLOCK 옵션을 설정하여 처리할 경우, 처리 시간이 크게 단축됩니다. 10,000,000건의 텍스트 파일에 대해 아무런 옵션 없이 Bulk Insert 작업을 수행한 경우에 비해 ORDER, TABLOCK 옵션을 이용하여 처리한 수행 시간이 6배 정도 빠르게 나타났습니다. (219 : 37)

     

     

    참고로, 이러한 사항은 BULK INSERT 명령뿐만 아니라 SSIS(Integration Services) 패키지의 데이터 로딩 작업에서도 설정할 수 있는 사항입니다.

     

    - OLE DB 대상에서 빠른 로드 설정 후, 고급 OLE DB 대상 편집기FastLoadOptions 부분에서 설정

        

     

    - SQL Server 대상고급 Server 대상 편집기, BulkInsertOrder 부분에서 설정

       

     

     

     

     

    본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 글의 링크를 밝혀주셔야 합니다. 

    AND

    SQL Leader 에서 재미있는 이슈가 있어 간단히 테스트한 내용을 옮겨둠~
    http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intPage=2&intCategory=0&strSearchCategory=|s_name|s_subject|&strSearchWord=&intSeq=2369
    결국 질문에 대한 답은 아니었지만 한가지 알게 되었으니 기념으로!! ^^

    =============================================================================

    DB에 데이터가 들어가면 내부적으로 페이지를 할당하게 됩니다.
    반대로 데이터가 삭제될 경우 삭제 즉시 할당된 페이지가 반환되는게 아니라 비동기적으로 반환이 이루어지는것 같습니다.

    자.. 이제 쭈아님께서 작업하시는 스토리대로 예제를 한번 만들어 보겠습니다.

    예를들어 테이블에 1 ~ 1000 까지 데이터를 넣어서 1000개의 페이지가 할당되었다고 가정하고,
    (사실 1페이지에는 더 많은 데이터가 들어가겠지만 계산을 쉽게 하기 위해.. ^^; )

    먼저 SELECT를 해보면 정상적으로 읽기가 수행이 됩니다.

    하지만 1~900까지를 삭제한 다음 잽싸게 다시 SELECT를 해보면..
    ASC로 읽었을때 훨씬 많은 페이지를 읽게 됩니다.
    데이터가 삭제되어서 없지만 유령 페이지가 남아있어서 실제 데이터가 나올때까지 순차적으로 읽어나가게 됩니다.
    (앞쪽 데이터를 삭제해서 앞쪽에 유령 페이지가 우루루 몰려 있는 상황입니다.)

    물론 몇초후 다시 조회를 해보면 할당된 페이지를 반환하고 정상적인 조회가 이루어 지는것을 확인할 수 있습니다.


    아래는 테스트에 사용한 쿼리를 간략하게 정리한 내용입니다.


    그럼 좋은 하루 되세요~!
     

    use master

    -- drop database db2

    create database db2

     

    use db2

     

    -- drop table tbl

    create table tbl (col1 int, col2 char(8000))

    create clustered index ci_col1 on tbl (col1)

     

    insert into tbl

    select top 1000 row_number() over (order by (select 1)) , 'a'

    from sysindexes a, sysindexes b, sysindexes c

     

     

    set statistics io on

     

    select top 5 * from tbl with(nolock) order by col1 asc

    select top 5 * from tbl with(nolock) order by col1 desc

     

     

    (5개행적용됨)

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

     

    (5개행적용됨)

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

     

    -- 삭제후 잽싸게 SELECT 해본다

    delete tbl where col1 < 900

     

    select top 5 * from tbl with(nolock) order by col1 asc

    select top 5 * from tbl with(nolock) order by col1 desc

     

    (5개행적용됨)

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

     

    (5개행적용됨)

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

     

     

    select top 5 * from tbl order by col1 asc

    select top 5 * from tbl order by col1 desc

     

    (5개행적용됨)

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

     

    (5개행적용됨)

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

     

     

    dbcc ind (DB2, tbl, -1)

     

    -- 몇초후~ 다시해보면!!!! 오오~

     

    (5개행적용됨)

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

     

    (5개행적용됨)

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

     

    AND

     

    간단하게 SERIALIZABLE REPEATABLE READ Lock이 어떻게 걸리는지를 테스트 해 보았다.

     

    -- 데이터를준비한다.

    -- drop table tA

    create table tA (col1 int)

     

    insert into tA select 1

    insert into tA select 5

    insert into tA select 10

      

    -- repeatable read TEST

    set transaction isolation level repeatable read

    begin tran

     

           select col1 from tA where col1 between 2 and 4

           -- 여기까지수행후 다른 spid로 접속해 sp_lock 수행

    사용자 삽입 이미지

           select col1 from tA where col1 between 2 and 6
           -- 여기까지수행후 다른 spid로 접속해 sp_lock 수행
    사용자 삽입 이미지

    REPEATABLE READ의 경우 데이터가 있는 경우 SELECT를 하면 Row Lock 이 걸린다.

     

    commit

     

     

    -- serializable TEST

    set transaction isolation level serializable

    begin tran

     

           select col1 from tA where col1 between 2 and 4

           -- 여기까지수행후 다른 spid로 접속해 sp_lock 수행

    사용자 삽입 이미지
     

           select col1 from tA where col1 between 2 and 6

           -- 여기까지수행후 다른 spid로 접속해 sp_lock 수행

    사용자 삽입 이미지

                 데이터가 있으나 없으나 무조건 Table Lock을 걸어 버린다.


    commit


    인덱스를 추가하면 Table Lock 대신 Range Lock을 건다!

     
    하만철 / Ha Man cheol

    AND