컴퓨토피아

create procedure psp_help_allidx7
as
/* Purpose: to list all indexes for each table
   Author : Eddy Djaja, Publix Super Markets, Inc.
   Revision: 12/07/1999 born date
*/
   
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1)
select @empty = ''
declare @des1  varchar(35), -- 35 matches spt_values
 @des2  varchar(35),
 @des4  varchar(35),
 @des32  varchar(35),
 @des64  varchar(35),
 @des2048 varchar(35),
 @des4096 varchar(35),
 @des8388608 varchar(35),
 @des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

select  o.name,
 i.name,
 'index description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
    case when (i.status & 16)<>0 then 'clustered' else 'nonclustered' end
    + case when (i.status & 1)<>0 then ', '+@des1 else @empty end
    + case when (i.status & 2)<>0 then ', '+@des2 else @empty end
    + case when (i.status & 4)<>0 then ', '+@des4 else @empty end
    + case when (i.status & 64)<>0 then ', '+@des64 else
      case when (i.status & 32)<>0 then ', '+@des32 else @empty end end
    + case when (i.status & 2048)<>0 then ', '+@des2048 else @empty end
    + case when (i.status & 4096)<>0 then ', '+@des4096 else @empty end
    + case when (i.status & 8388608)<>0 then ', '+@des8388608 else @empty end
    + case when (i.status & 16777216)<>0 then ', '+@des16777216 else @empty end),
 'index column 1' = index_col(o.name,indid, 1),
 'index column 2' = index_col(o.name,indid, 2),
 'index column 3' = index_col(o.name,indid, 3)
from sysindexes i, sysobjects o
where i.id = o.id
  and indid > 0
  and indid < 255
  and o.type = 'U'
  --exclude autostatistic index
  and (i.status & 64) = 0
  and (i.status & 8388608) = 0
  and (i.status & 16777216)= 0
  order by o.name
go

exec psp_help_allidx7
go

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]

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

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

처리 시간

              1회 - 00:01:48

              2회 - 00:01:50

              3회 - 00:01:49

             평균 - 00:01:49 (109초)

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

처리 시간

              1회 - 00:00:34

              2회 - 00:00:33

              3회 - 00:00:34

             평균 - 00:00:34 (34초)

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

처리 시간

              1회 - 00:00:33

              2회 - 00:00:33

              3회 - 00:00:34

             평균 - 00:00:33 (33초)

[Clustered Index 생성]

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

처리 시간

              1회 - 00:03:40

              2회 - 00:03:39

              3회 - 00:03:39

             평균 - 00:03:39 (219초)

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

처리 시간

              1회 - 00:02:49

              2회 - 00:02:47

              3회 - 00:02:48

             평균 - 00:02:48 (168초)

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

처리 시간

              1회 - 00:02:21

              2회 - 00:02:21

              3회 - 00:02:20

             평균 - 00:02:21 (141초)

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

처리 시간

              1회 - 00:02:20

              2회 - 00:02:21

              3회 - 00:02:21

             평균 - 00:02:21 (141초)

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

처리 시간

              1회 - 00:00:37

              2회 - 00:00:37

              3회 - 00:00:37

             평균 - 00:00:37 (37초)

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

처리 시간

              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) 및 글의 링크를 밝혀주셔야 합니다.  

SELECT  USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) ) AS Owner
   ,OBJECT_NAME( i.id ) AS [Table]
   , i.name AS [Index]
   ,CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')  WHEN 1 THEN 'Y'  ELSE ''   END AS IsClustered
   ,CASE INDEXPROPERTY( i.id , i.name , 'IsUnique'    )  WHEN 1 THEN 'Y'  ELSE ''   END AS IsUnique
   ,STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
   ,dPages * 8. /1024 AS MB
FROM sysindexes AS i
WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0
 AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics'   )
  , INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' )
  , INDEXPROPERTY( i.id , i.name , 'IsHypothetical'   )  )
 AND i.indid BETWEEN 1 And 250
-- AND dPages > 100 --작은 크기 테이블 무시
 AND (STATS_DATE( i.id , i.indid ) < getdate() - 15
  OR STATS_DATE( i.id , i.indid ) IS NULL) --15일 이전까지도 업데이트 안된 것
ORDER BY Owner, [Table], [Index]