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에서만 지원됩니다.
이전에 유니크 제약조건이 있는 게시 데이터 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 명령을 구독에 반영할 지 제어 할 수 있습니다.
이 중 8207 flag의 경우 다음 경우에 문제가 될 수 있으므로 주의해야 한다고 말하고 있습니다.
이상한점은, 복제의 경우 로그를 읽어 데이터를 반영하는 방식이기 때문에 동일한 update문을 수행했더라도 유니크 제약조건에 의해 update 와 delete / insert의 경우 로그가 다르게 쌓일거라고 생각했지만 동일하게 쌓인것을 확인할 수 있었습니다.
확인은 ::fn_dblog 명령을 통해 하였고 엑셀에 붙여서 확인해본 결과 완전히 동일하게 로그가 쌓인것을 확인할 수 있었습니다.
혹시 로그리더가 읽어가는 데이터에 대해 좀 더 자세히 아시거나 좀더 상세히 로그를 확인할 수 있는 방법을 알고 계신분이 계시다면 알려 주시면 감사하겠습니다. ^^
하만철 / Ha Man cheol
EMail : feisia@hanmail.net
EMail : feisia@hanmail.net
예전에 로그백업 후 로그를 축소했는데 용량이 원하는 만큼 줄어들지 않고 다시한번 로그백업 후 축소를 하면 원하는대로 줄어드는 현상이 있어 확인해본 결과 로그 축소시 활성 VLF의 뒷부분만 축소가 된다는 것을 알게 되었었다.
오늘은 이 부분에 대해 실제로 테스트를 해 보았다.
select @@version
-- 집에 있는 노트북에 패치가 안되어 있었네요; 인터넷이 열악한 상황이라 패치하기가 힘드니 뭐라하지 마세요~ ^^;
Microsoft SQL
Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
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)
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, ) GO |
※ DB File Size 증가로 인한 지연을 막기 위해 DB Size를 충분히 크게 늘린 후 수행함.
TEST 1) 인덱스가 없는 테이블에 로딩
|
--인덱스가 없는 테이블에 로딩 BULK INSERT TESTTABLE FROM 'D:\TestFile.txt' WITH ( FIELDTERMINATOR =',', ROWTERMINATOR ='\n' ) GO |
처리 시간
1회 -
2회 -
3회 -
평균 -
TEST 2) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용
|
--인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 BULK INSERT TESTTABLE FROM 'D:\TestFile.txt' WITH ( FIELDTERMINATOR =',', ROWTERMINATOR ='\n', TABLOCK ) GO |
처리 시간
1회 -
2회 -
3회 -
평균 -
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회 -
2회 -
3회 -
평균 -
[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회 -
2회 -
3회 -
평균 -
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회 -
2회 -
3회 -
평균 -
TEST 6) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션
|
-- Clustered Index가 있는 테이블에 로딩 -- ORDER 옵션 BULK INSERT TESTTABLE FROM 'D:\TestFile.txt' WITH ( FIELDTERMINATOR =',', ROWTERMINATOR ='\n', ORDER(SEQ ASC) ) GO |
처리 시간
1회 -
2회 -
3회 -
평균 -
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회 -
2회 -
3회 -
평균 -
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회 -
2회 -
3회 -
평균 -
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회 -
2회 -
3회 -
평균 -
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) 및 글의 링크를 밝혀주셔야 합니다.
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.
|
