728x90
[SQL 서버 2005 실전 활용] ④ 새로운 DB 보호·복구 모델

한용희 (롯데정보통신)   2005/08/03
연재순서
1. T-SQL의 새로운 모습
2. VS.NET으로 개발하는 SQL 서버 2005
3. SQL 서버 2005 관리자를 위한 변화
4. DB 보호와 복구를 위한 새로운 모델
SQL 서버 2005가 나오면서 개인적으로 가장 크게 관심을 보인 부분이 바로 테이블 파티셔닝이다.

현재 롯데칠성음료에서도 매달 100만 건의 거래명세표 내역이 쌓이면서 이를 처리하기 위한 대용량 데이터베이스에 대해 높은 관심을 기울이고 있다. 이번 글에서는 대용량 데이터베이스를 위한 테이블 파티셔닝과 가용성을 높이기 위한 스냅샷 그리고 미러링에 대해 알아 볼 것이다.

연재 가이드운영체제 : 윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구 : MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식 : MS SQL 서버 2000, C#
응용분야 : MS SQL 서버 2005 관리와 개발
지난 3회에 걸쳐 SQL 서버 2005의 새로운 특징들에 대해 알아봤다. 이번 글에서는 마지막으로 대용량 데이터베이스를 위한 기존의 파티션뷰를 대체하는 테이블 파티셔닝과 데이터베이스 이력 관리를 위한 스냅샷, 그리고 가용성을 높이기 위한 클러스터링에 견줄 수 있는 미러링에 대해 알아 볼 것이다.

기업의 데이터는 시간이 지날수록 점점 많아지고 있다. 분석을 위한 데이터는 점점 더 쌓여만 가고 더 이상 하나의 테이블에 이 모든 정보를 담아 두는 것이 비효율적일 때가 있다. 보통 기가나 테라 단위의 데이터를 하나의 테이블에 담아 두게 되면 테이블 유지 보수가 힘들며 성능 또한 느려지게 된다.

이러한 데이터는 대부분 과거의 데이터가 함께 있어서 그러는데, 아마 몇 년 전의 데이터는 거의 사용하지 않을 것이다. 이럴 때에는 테이블을 나누어서 최근의 데이터는 높은 성능을 내는 I/O에 담아 두고, 예전의 데이터는 비교적 낮은 성능의 저렴한 I/O 장치에 담아 두는 것이 효율적일 것이다. 이럴 때 사용하는 것이 바로 파티셔닝이다.

SQL 서버 7.0/2000에서 분할된 뷰
파티셔닝을 위한 전략은 SQL 서버 7.0에서부터 지원했다. 분할된 뷰(partitioned view)를 이용하여 각각의 테이블을 UNION으로 묶어서 마치 하나의 테이블로 볼 수 있도록 했다.

CREATE VIEW Production.vTransaction
AS
SELECT * FROM Production.Transaction_2003_09 UNION ALL
SELECT * FROM Production.Transaction_2003_10 UNION ALL
SELECT * FROM Production.Transaction_2003_11

이와 같이 2003년 9월의 테이블과 2003년 10월의 테이블, 2003년 11월의 테이블을 UNION으로 결합함으로써 분할된 뷰를 만들 수 있다. 이 때 각 테이블은 파티셔닝 컬럼을 CHECK 조건을 이용하여 미리 제한해둬야 한다. 예를 들면 앞의 각 테이블에 TransactionDate라는 날짜 컬럼이 있다면 제한 조건으로 다음과 같이 줘야 한다.

CHECK ( TransactionDate between '2003-09-01' AND '2003-09-30')

이렇게 파티셔닝 컬럼을 정의하고 이 컬럼에 INDEX를 걸어 두면 분할된 뷰를 이용하여 테이블에 접근할 때 다른 날짜의 테이블은 읽지 않게 된다. SQL 서버 2000에서는 분할 뷰를 이용하여 데이터 갱신 작업이 효과적으로 수행하도록 지원하였으며 분산 분할된 뷰(distributed partitioned view)로까지 발전을 하여 각각의 테이블이 한 서버가 아닌 다른 서버에 있어도 가능하도록 발전했다.

하지만 분할된 뷰 방식의 파티셔닝은 여러 테이블을 하나의 뷰로 모았기 때문에 관리상 불편한 점이 많았다. 예를 들면 테이블 구조를 바꾼 다거나 인덱스를 재생성하거나 변경하는 경우 각각의 테이블을 모두 반영해줘야 하기 때문이다.

SQL 서버 2005의 테이블 파티셔닝
SQL 서버 2005에서는 뷰를 통한 파티셔닝이 아닌 테이블 단위의 파티셔닝을 지원한다. 즉 하나의 테이블을 여러 조각으로 쪼개어 관리하는 것이 가능하다. 그러므로 분할된 뷰처럼 각 테이블을 따로 관리할 필요가 없다. 예를 들면 인덱스를 만드는데 있어서 하나의 테이블만 만들면 되므로 관리상 이점이 있다. 또한 성능에 있어서도 더 좋은 성능을 보여준다.

분할된 뷰에서는 각각의 테이블을 보고 나중에 합치는 방식으로 진행되었지만, 테이블 파티셔닝에서는 멀티 CPU 환경이라면 병렬처리(demand parallelism)를 이용하여 보다 빠른 쿼리를 수행할 수도 있다. 쿼리를 컴파일하는데 있어서도 분할된 뷰에서는 테이블이 많을수록 느렸지만, 테이블 파티셔닝에서는 파티션 개수에 상관없이 빠른 속도를 보장한다.

<그림 1> 테이블 파티셔닝

테이블 파티셔닝은 파티셔닝 함수와 스키마를 이용하여 구현을 한다. 파티셔닝 함수로는 경계 영역을 구분하고 스키마로는 실제 물리적인 파일 그룹에 각 파티션을 맵핑한다.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000)

이 예제는 myRangePF1이라는 파티션 함수를 정의하는데 있어 경계 부분을 왼쪽에 포함하는 함수를 만들고 있다. 이와 같이 실행하면 다음과 같이 4개의 파티션 영역을 정의한다.

파티션 1 2 3 4
값 col <= 1 col > 1 and col <= 100 col > 100 AND col <= 1,000 col > 1,000

즉, 경계를 왼쪽 부분에 포함하기 때문에 1,100,1000은 각각 왼쪽 파티션에 포함하게 된다. 만약 LEFT 대신에 RIGHT라고 쓴다면 1,100,1000은 각각 오른쪽 파티션에 포함하게 된다. 파티션 함수를 만들었으면 실제 물리적인 영역에 맵핑할 수 있는 스키마를 정의해야 한다.

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg)

이 구문은 앞에서 정의한 파티션 함수를 바탕으로 각각 4개의 파일 그룹에 맵핑하고 있다. 따라서 이런 경우는 각각의 파티션이 별개의 물리적인 공간에 저장되게 된다. 물론 하나의 파일 그룹에 담을 수도 있다. 그럼 이제 SQL 서버 2000의 분할된 뷰와 SQL 서버 2005의 테이블 파티셔닝의 차이점에 대해 알아보자.

분할된 뷰 vs. 테이블 파티셔닝
먼저 분할된 뷰를 만들어보자. 기본적으로 SQL 서버 2005 베타2를 설치하면 AdventureWorks에 TransactionHistory라는 큰 테이블이 존재한다. 이를 먼저 분할된 뷰로 만들기 위해 다음과 같이 여러 개의 테이블로 나누고 각각 CHECK 제약 조간을 주고 인덱스를 생성해보자. 전체 코드는 ‘이달의 디스켓’에 있다.

테이블 분할
SELECT *
INTO Production.Transaction_2003_09
FROM Production.TransactionHistory
WHERE TransactionDate between '2003-09-01' and '2003-09-30';

SELECT *
INTO Production.Transaction_2003_10
FROM Production.TransactionHistory
WHERE TransactionDate between '2003-10-01' and '2003-10-31';
...

체크 제약 조건 삽입
ALTER TABLE [Production].[Transaction_2003_09]
WITH CHECK ADD CONSTRAINT [CK_Transaction_2003_09]
CHECK (TransactionDate between '2003-09-01' AND '2003-09-30');

ALTER TABLE [Production].[Transaction_2003_10]
WITH CHECK ADD CONSTRAINT [CK_Transaction_2003_10]
CHECK ( TransactionDate between '2003-10-01' AND '2003-10-31');
...

인덱스 만들기
CREATE CLUSTERED INDEX [IX_Transaction_2003_09_TransactionDate] ON
[Production].[Transaction_2003_09]([TransactionDate]);
CREATE CLUSTERED INDEX [IX_Transaction_2003_10_TransactionDate] ON
[Production].[Transaction_2003_10]([TransactionDate]);
...

뷰 만들기
CREATE VIEW Production.vTransaction
AS
SELECT * FROM Production.Transaction_2003_09 UNION ALL
SELECT * FROM Production.Transaction_2003_10 UNION ALL
...

이제 다 만들었으면 과연 잘 만들었는지 샘플 쿼리를 실행해보자.

SELECT * FROM Production.vTransaction
WHERE TransactionDate between '2003-09-01' and '2003-10-31';

-----------------------------------------------------------------
(20494 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction_2003_10'. Scan count 1, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction_2003_09'. Scan count 1, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

이 결과를 보면 예상대로 다른 테이블은 읽기가 없고 해당 테이블에서만 읽기가 일어난 것을 볼 수 있을 것이다. 그럼 실제 실행 계획을 보자.

<화면 1> 분할된 뷰를 이용한 실행 계획

실행 계획을 보면 다른 테이블을 모두 본 다음에 필터로 걸러서 나중에 결합(concatenation) 연산을 이용하여 합치는 것을 볼 수 있다. 앞에서는 PK(Primary Key)가 없어서 그런 것이고, 만약 파티셔닝 컬럼이 PK라면 다른 테이블은 아예 보지도 않고 필터링도 없어 바로 데이터를 가져온다.

하지만 PK가 있는 테이블도 저장 프로시저를 이용하여 쿼리를 하면 어차피 현재와 똑같이 필터링하여 결합하므로 큰 차이는 없다고 봐도 된다. 읽기 수를 보더라도 다른 테이블은 필터링을 하므로 0이 나온다. 이제는 테이블 파티셔닝을 이용해보자. SQL 서버 2005 베타2에서 엔진 예시(Engine Example)를 설치하여 다음 폴더에 가보면 테이블 파티셔닝 예제가 있다.

C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Administration\Partitioning\Scripts
\PartitionAW.sql

이 예제를 실행시키면 TransactionHistory 테이블을 파티셔닝을 하는데, 2003년 10월 이전부터 2004년 8월 이후까지 12개의 파티션으로 나누어서 만든다. 다음은 주요 코드 중에 하나이다.

-- Range partition table TransactionHistory
CREATE PARTITION FUNCTION TransactionRangePF1 (datetime)
AS RANGE RIGHT FOR VALUES ('10/01/2003', '11/01/2003', '12/01/2003',
    '1/01/2004', '2/01/2004', '3/01/2004', '4/01/2004',
    '5/01/2004', '6/01/2004', '7/01/2004', '8/01/2004');
GO

파티션 함수를 만드는데 있어 월별로 총 12개의 파티션으로 나누고 있다.

CREATE PARTITION SCHEME TransactionsPS1
AS PARTITION TransactionRangePF1
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY]);

여기에서 만든 함수를 스키마를 이용하여 물리적인 공간에 맵핑하는 데 있어 하나의 파일 그룹에 맵핑하고 있다.

CREATE TABLE [Production].[TransactionHistory](
  [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
  [ProductID] [int] NOT NULL,
  [ReferenceOrderID] [int] NOT NULL,
  [ReferenceOrderLineNumber] [smallint] NOT NULL ,
  [TransactionDate] [datetime] NOT NULL ,
  [TransactionType] [nchar](1) NOT NULL,
  [Quantity] [int] NOT NULL,
  [ActualCost] [money] NOT NULL,
  [ModifiedDate] [datetime] NOT NULL
) ON TransactionsPS1 (TransactionDate);

테이블을 생성할 때 앞에서 만든 스키마 위에 만들고 있다. 다 만들었으면 잘 만들었는지 예제 쿼리를 실행해보자.

SELECT * FROM Production.TransactionHistory
WHERE TransactionDate between '2003-09-01' and '2003-10-31';

-------------------------------------------------------------------
(20494 row(s) affected)
Table 'TransactionHistory'. Scan count 2, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

테이블이 하나이므로 하나의 테이블에서 두 번의 스캔이 일어났다. 이제 실행 계획을 보자.

<화면 2> 테이블 파티셔닝을 이용한 실행 계획

실행 계획을 보면 ‘Nested Loops Join’을 이용하여 해당 테이블을 ‘Index Seek’하여 데이터를 가져오고 있다. 이 쿼리에서는 두 달 치의 데이터를 읽으므로 두 번의 스캔이 일어남을 확인할 수 있다.

슬라이딩 윈도우 구현
파티션된 테이블을 관리하다 보면 오래된 데이터는 거의 사용을 하지 않게 된다. 어쩌다 한 번씩 통계 자료용으로 사용하는 경우가 대부분이다. 이러한 데이터를 계속 고성능의 I/O 장치에 담아 두는 것은 비효율적이다. 따라서 오래된 데이터는 더 이상의 트랜잭션이 일어나지 않으므로 비교적 낮은 성능의 저렴한 I/O 장치로 이식하는 것이 효율적이다.

이러한 과정을 ‘슬라이딩 윈도우(파티션 스위칭)’라고 한다. 슬라이딩 윈도우의 우리말 뜻은 ‘미닫이창’이다. 즉 밀어서 여닫는 창이라는 뜻인데, 오래된 데이터는 밀어서 내보내고 대신 새 데이터를 받아들인다는 의미로 보면 될 것이다.

슬라이딩 윈도우를 구현하는데 있어 대량의 데이터가 이동하므로 느릴 것이라고 생각 할 수 있으나, 실제로는 메타 데이터만 이동하므로 상당히 빠르게 작업할 수 있다. 다음 예제 역시 SQL 서버 2005를 설치한 다음 폴더에 가면 슬라이딩 윈도우 예제가 있다.

C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Administration\SlidingWindow\Scripts
\sliding.sql

이번 예제에서는 2003년도 9월의 데이터를 TransactionHistory 테이블에서 떼어 내어 TransactionHistoryArchive 테이블로 옮기는 작업이다. 초기 상태는 <그림 2>와 같다.

<그림 2> 초기 상태

TransactionHistory에는 12개의 파티션이 있고 TransactionHistoryArchive에는 2개의 파티션이 있다. TransactionHistoryArchive는 자주 사용하지 않는 데이터를 모아 두는 곳이므로 두 개의 파티션만을 만들었다. 여기에서 TransactionHistory 테이블에서 2004년도 9월 데이터를 위한 새로운 데이터를 위한 공간을 확보하자.

ALTER PARTITION FUNCTION TransactionRangePF1() SPLIT RANGE ('9/01/2004');


<그림 3> TransactionHistory에 2004년도 9월 1일로 분할

새로운 공간을 확보하기 위하여 기존 공간을 분할하여 총 13개의 파티션을 만들었다. 이제는 TransactionHistoryArchive에도 역시 새로운 공간을 확보하자.

ALTER PARTITION FUNCTION TransactionArchivePF2() SPLIT RANGE ('10/01/2003');


<그림 4> TransactionHistoryArchive에 2003년도 10월 1일로 분할

각각의 테이블에 새로운 공간을 할당하였으니 이제 파티션을 옮겨 보자.

ALTER TABLE [Production].[TransactionHistory] SWITCH PARTITION 1 to [Production].[TransactionHistoryArchive] PARTITION 2;

각각의 테이블에 새로운 공간을 할당하였으니 이제 파티션을 옮겨 보자.

<그림 5> TransactionHistory의 파티션 1을 TransactionHistoryArchive의 파티션 2로 이동

옮겼으면 이제 기존 파티션을 병합하여 초기 상태로 만들어줘야 한다. 먼저 TransactionHistory부터 병합하자. 병합하기 전에 sys.partition_range_values라는 테이블을 조회하여 파티션 정보를 조회해보자.

function_id boundary_id parameter_id value
------------- ------------- -------------  
65536 1 1 2003-10-01 00:00:00.000
65536 2 1 2003-11-01 00:00:00.000
65536 3 1 2003-12-01 00:00:00.000
65536 4 1 2004-01-01 00:00:00.000
65536 5 1 2004-02-01 00:00:00.000
65536 6 1 2004-03-01 00:00:00.000
65536 7 1 2004-05-01 00:00:00.000
65536 8 1 2004-05-01 00:00:00.000
65536 9 1 2004-06-01 00:00:00.000
65536 10 1 2004-07-01 00:00:00.000
65536 11 1 2004-08-01 00:00:00.000
65536 12 1 2004-09-01 00:00:00.000
65536 1 1 2003-09-01 00:00:00.000
65537 2 1 2003-10-01 00:00:00.000
(14 row(s) affected)

이제까지 제대로 작업을 했다면 총 14개의 행이 있을 것이다. 이제 다음과 같이 병합을 하자.

ALTER PARTITION FUNCTION TransactionRangePF1() MERGE RANGE ('10/01/2003');


<그림 6> TransactionHistory에서 2003년 10월 1일 병합

병합을 한 후 다시 테이블 파티션 정보를 보자.

function_id boundary_id parameter_id value
------------- ------------- -------------  
65536 1 1 2003-11-01 00:00:00.000
65536 2 1 2003-12-01 00:00:00.000
65536 3 1 2004-01-01 00:00:00.000
65536 4 1 2004-02-01 00:00:00.000
65536 5 1 2004-03-01 00:00:00.000
65536 6 1 2004-04-01 00:00:00.000
65536 7 1 2004-05-01 00:00:00.000
65536 8 1 2004-06-01 00:00:00.000
65536 9 1 2004-07-01 00:00:00.000
65536 10 1 2004-08-01 00:00:00.000
65536 11 1 2004-09-01 00:00:00.000
65537 1 1 2003-09-01 00:00:00.000
65537 2 1 2003-10-01 00:00:00.000
(13 row(s) affected)

병합을 했으므로 총 13개의 행이 생겼다. 이제 TransactionHistoryArchive도 병합을 하자.

ALTER PARTITION FUNCTION TransactionArchivePF2() MERGE RANGE ('9/01/2003');


<그림 7> TransactionHistoryArchive에서 2003년 9월 1일 병합

이렇게 함으로써 슬라이딩 윈도우 작업을 완료할 수 있다. 작업이 간단하지는 않지만 이런 일련의 작업들이 실제로는 메타 데이터를 가지고 작업을 하기 때문에 상당히 빠르게 수행된다.

<그림 8> 슬라이딩 윈도우 구현이 완료된 상태

간편한 이력 관리를 위한 데이터베이스 스냅샷
SQL 서버 2005에서는 간단하게 데이터베이스에 대한 백업본을 만들 수 있다. 보통 개발자가 어떤 간단한 작업을 할 때 실수를 할까봐 트랜잭션을 걸고 작업을 종종 한다. 그러다가 실수를 하면 롤백하면 되기 때문이다. 그런데 이렇게 작업을 하면 잠금이 걸리기 때문에 다른 사용자들은 대기하고 기다려야 하는 불편이 있다. 하지만 이제는 데이터베이스 스냅샷을 사용하면 현재 데이터베이스의 내용을 간단하게 백업을 할 수 있기 때문에 트랜잭션을 걸지 않아도 된다. 만약 실수를 하게 되면 간단하게 복구를 할 수 있다.

스냅샷은 읽기만 할 수 있는 데이터베이스이다. 만들 때에는 실제 데이터의 복사본을 만드는 것이 아니고 메타 데이터만으로 만들기 때문에 상당히 빠르고 적은 용량으로 만들 수 있다. 실제 구현을 보면 먼저 스냅샷은 현재 데이터베이스와 동일한 저장 공간을 예약하고 원본 데이터베이스에서 변경이 일어나면 먼저 스냅샷 데이터베이스에 복사를 한 후 원본 데이터베이스를 변경한다. 이를 복사-쓰기(copy-on-write) 기술이라고 부른다.

<그림 9> 복사-쓰기 기술

<그림 9>를 보면 원본 데이터베이스의 2라는 값이 10으로 바뀔 때 먼저 2라는 값을 스냅샷 데이터베이스에 복사를 하고 자기 자신의 값을 10으로 바꾸고 있다. 스냅샷 데이터베이스는 결국 원본 데이터베이스에서 바뀌기 전의 상태 값만 가지고 있고, 나머지는 원본 데이터베이스를 참조한다. 그래서 생성 시간이 빠르고 공간도 적게 차지하는 것이다. 그럼 직접 실습을 해보자.

CREATE DATABASE Test;
USE Test;

CREATE TABLE Dummy
(
  Data int
);

INSERT INTO Dummy VALUES (1);
INSERT INTO Dummy VALUES (2);
INSERT INTO Dummy VALUES (3);
INSERT INTO Dummy VALUES (4);

스냅샷 생성
CREATE DATABASE Test_01 ON
(
NAME = Test,
FILENAME = 'c:\program files\microsoft sql server\mssql.1\mssql\data\Test_01.ss'
)
AS SNAPSHOT OF Test;

Test라는 데이터베이스를 만들고 Dummy라는 테이블을 만들어서 1,2,34라는 값을 넣고 Test_01이라는 Test 데이터베이스의 스냅샷을 만들었다. 앞에서 만든 Test_01.ss 라는 파일의 실제 크기를 보면 다음과 같다.

<화면 3> Test_01.ss의 파일 크기

크기는 1.56MB를 할당했지만 실제 사용하는 크기는 128KB밖에 안 된다는 것을 확인할 수 있을 것이다. 이제 Test 테이블에서 2라는 값을 10으로 바꾸고 스냅샷에서 제대로 값을 보존하고 있는지 확인해보자.

UPDATE Dummy
SET Data = 10
WHERE Data = 2;

SELECT * FROM dummy;

USE Test_01;

SELECT * FROM dummy;

-----------------------
Data
-----------------------
1
10
3
4
(4 row(s) affected)

Data
-----------------------
1
2
3
4
(4 row(s) affected)

스냅샷 테이블이 이전 값을 잘 간직하고 있음을 확인할 수 있을 것이다. 다시 Test_01.ss의 파일 크기를 보면 384KB로 그 크기가 커져 있는 것을 확인해 볼 수 있다. 즉 2라는 값을 저장하므로 그만큼의 공간이 늘어난 것이다. 이번에는 원본 데이터베이스를 복구해보자.

USE master;
RESTORE DATABASE Test
FROM DATABASE_SNAPSHOT = 'Test_01';

USE Test;
SELECT * FROM dummy;

----------------------
Data
----------------------
1
2
3
4
(4 row(s) affected)

제대로 복구된 것을 확인할 수 있다.

멈추지 않는 시스템을 위한 DB 미러링
SQL 서버 2000에는 서버가 도중에 다운되더라도 다른 서버가 대신 작동하게 하는 기능으로 클러스터링을 이용하였다. 그러나 클러스터링을 구축하기 위해서는 공유 디스크와 같은 별도의 하드웨어가 필요했다. 또한 디스크 자체는 공유를 하므로 디스크가 깨지는 경우에는 좋은 해결책이 아니었다. 그리고 광케이블로 서로 연결해야 하므로 100마일이라는 거리의 제한도 있었다. SQL 서버 2005에서는 또 다른 해결책으로 미러링이라는 것을 지원한다. 미러링은 두 대의 SQL 서버를 운영하면서 서로 로그 정보를 주고받으면서 동일한 데이터를 유지한다.

따라서 별도의 공유 디스크가 필요 없으며, 디스크 자체가 깨지더라도 서로 디스크 복사본을 유지하기 때문에 문제가 안 된다. 또한 별도의 광케이블이 아닌 일반 네트워크 선을 사용하므로 거리 제한도 없다. 여기에 클러스터링은 서버에 문제가 생겨 교체되는데 있어 30초 이상의 시간이 걸리지만 미러링은 2～3초면 서버가 교체되어 자동으로 작동한다.

그렇다고 미러링이 클러스터링의 대안은 될 수 없다. 서로 장단점이 있기 때문이다. 미러링은 시스템 데이터베이스에는 사용하지 못한다. 단지 사용자 DB만 사용할 수 있다. 따라서 클러스터링은 전체 시스템을 보호하는 용도로 사용하고 미러링은 중요한 사용자 데이터베이스를 보호하는 용도로 사용하는 것이 적당할 것이다. <그림 10>은 미러링의 동작 방법이다.

<그림 10> 미러링 동작 방법

미러링은 데이터 자체를 서로 전송하는 것이 아니라 로그만을 서로 전달한다. 애플리케이션으로부터 데이터 수정 작업이 들어오면 이를 먼저 로그에 기록한 다음 미러 서버에게도 그 로그 정보를 전달하여 미러 데이터베이스에도 동일 정보를 유지하도록 해준다. 이러한 동작은 감시 서버(witness server)가 계속 감시하고 있다가 만약 주 서버가 다운이 되면 바로 미러 서버를 주 서버로 바꾸어 동작하게 한다. 그동안 애플리케이션은 별도의 프로그램 수정 없이도 자동으로 미러 서버를 주 서버로 간주하여 접속을 유지한다. 그럼 직접 실습을 해보자.

원래는 제대로 된 실습을 위해서는 주 서버(Principal Server), 미러 서버(Mirror Server), 감시 서버(Witness Server) 이렇게 3대가 있어야만 하지만, 간단한 실습을 위하여 한 서버에 이 세 개의 서버를 인스턴스 이름만 달리하여 설치하면 테스트가 가능하다. 3개의 서버를 모두 설치한 후 다음 같이 종단점을 만들자.

CREATE ENDPOINT EndPoint_Principal
  STATE=STARTED
  AS TCP (LISTENER_PORT=5055)
  FOR DATABASE_MIRRORING (ROLE=ALL);

종단점은 외부에서 이 서버에 접근할 수 있는 문을 열어주는 의미이다. TCP 프로토콜을 사용하여 5055포트를 열어 주었다. 마찬가지로 다른 미러 서버와 감시 서버도 종단점을 만든다. 단 이 때 한 서버에서 테스트를 하는 것이므로 서로 다른 포트 번호를 부여해줘야 한다. 미러 서버는 5056, 감시 서버는 5057 이런 식으로 다른 포트 번호를 부여하자. 그런 다음 앞서 스냅샷에서 실습한 Test DB를 주 서버에서 백업하여 미러 서버에 복구를 한다. 따라서 주 서버와 미러 서버는 동일한 Test DB를 가지게 된다. 그런 다음 미러링을 위한 파트너를 아래와 같이 미러 서버에서부터 작업을 한다.

ALTER DATABASE [Test] SET PARTNER = 'TCP://WIN2003-SQLServer2005:5055'

마찬가지로 주 서버에서는 미러 서버와 감시 서버를 연결한다.

-- mirrored 서버 지정
ALTER DATABASE [Test] SET PARTNER = 'TCP://WIN2003-SQLServer2005:5056'
-- witness 서버 지정
ALTER DATABASE [Test] SET WITNESS = 'TCP://WIN2003-SQLServer2005:5057'

이제 주 서버에서 다음과 같은 데이터 작업을 해보자.

INSERT INTO Dummy VALUES (11);
INSERT INTO Dummy VALUES (12);

이제 미러 서버에서 동일한 데이터가 존재하는지 확인해 보자. 이 때 미러 서버는 항상 읽기 전용의 복구 모드로 동작을 하기 때문에 접근을 할 수가 없다. 접근을 하면 다음과 같은 에러 메시지가 나온다.

Database Test cannot be opened - it is acting as a mirror database.

따라서 앞서 실습한 스냅샷을 이용하여 접근을 해야 한다. 미러 서버의 스냅샷을 만들고 Dummy 테이블을 조회해 보자.

SELECT * FROM dummy;

-----------
1
2
3
4
11
12

값이 제대로 들어가 있는 것을 확인해 볼 수 있을 것이다. 이제 주 서버를 한 번 다운시켜 보자. SQL Computer Manager에서 주 서버의 동작을 멈춘다. 그러면 자동으로 미러 서버가 주 서버가 되고 기존의 주 서버는 미러 서버로 서로 스위치가 된다. 새로운 주 서버에서 Dummy 테이블을 조회해 보자. 이전까지는 스냅샷 없이는 조회가 안 되던 것이 이제는 잘 될 것이다.

정식 SQL 서버 2005를 기다리며
지난 4회 동안 SQL 서버 2005의 새로운 모습을 살펴보았다. 지금까지는 베타2를 기존으로 살펴보았고 앞으로는 베타3도 나와야 하고 정식버전도 나와야 한다. 앞으로 새로운 버전이 나오면서 또 어떻게 바뀔지는 모르겠다. 하지만 베타2 정도면 어느 정도 SQL 서버 2005에서 구현하려는 핵심 기능은 대부분 들어 있다고 봐도 될 것이다.

그 핵심 기능을 요약해 보면 개발자의 관점에서는 닷넷과의 통함이 될 수 있을 것이고, 관리자의 관점에서는 향상된 가용성(availability)이라고 할 수 있을 것이다. 이제 올 하반기에 나올 정식 SQL 서버 2005를 기다리면서 또 다른 항해를 준비하려고 한다.@

* 이 기사는 ZDNet Korea의 제휴매체인 마이크로소프트웨어에 게재된 내용입니다.

[출처] [SQL 서버 2005 실전 활용] ④ 새로운 DB 보호·복구 모델|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

INDEX  (0) 2008.04.28
DBCC DBREINDEX를 이용해서 한번에 한 서버의 모든 데이터베이스의 인덱스 재 작성하기  (0) 2008.04.28
논리적 파일명 변경하기  (0) 2008.04.28
SQL Server 2005 Query optimizer의 변경된 내용  (0) 2008.04.28
MSSQL MDF, LDF 파일 위치 변경 방법  (0) 2008.04.28
728x90

ALTER DATABASE database_name

MODIFY FILE (NAME = old_file_name, NEWNAME = new_file_name)

GO

[출처] 논리적 파일명 변경하기|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

DBCC DBREINDEX를 이용해서 한번에 한 서버의 모든 데이터베이스의 인덱스 재 작성하기  (0) 2008.04.28
[SQL 서버 2005 실전 활용] ④ 새로운 DB 보호·복구 모델  (0) 2008.04.28
SQL Server 2005 Query optimizer의 변경된 내용  (0) 2008.04.28
MSSQL MDF, LDF 파일 위치 변경 방법  (0) 2008.04.28
table 내의 모든 제약조건, index 이름, 해당에 index가 걸린 column 조회  (0) 2008.04.28
728x90

송혁 : SQL Server 2005 Query optimizer의 변경된 내용

제 1강 : SQL Server 재 컴파일

이름: 송혁
Email : hyok81@nate.com
넥슨 DSM팀 DBA로 근무


1. SQL Server의 재 컴파일이란?

SQL Server 는 비용 기반의 최적화(cost based optimizer)를 기반으로 쿼리의 실행 계획을 생성하며, 생성시 통계 정보, 테이블 스키마 정보 및 쿼리에 대한 내용을 바탕으로 실행 계획을 생성합니다. 이 작업은 대부분 CPU 리소스를 사용하고 있습니다.

매번 쿼리 요청이 될 때 마다 쿼리의 실행 계획을 계속 생성한다는 작업한다면 많은 성능 적 문제를 야기 할 수 있어 SQL Server에는 프로시져 캐시라는 메모리 공간에 쿼리가 수행된 실행 계획을 메모리에 적재 후 추후 동일한 쿼리요청이 들어온다면 캐시에 있는 내용을 재사용을 하게 됩니다. 이러한 것을 실행 계획 재사용이라고 부르며, 기존의 실행계획을 사용하면 안될 경우 및 사용할 수 없는 경우에 새로운 실행 계획을 만들기 위해 재 컴파일이라는 것을 하게 됩니다.

2. OLTP 서비스 환경에서의 재 컴파일이 주는 장점 및 단점

OLTP환경 즉 많은 트랙잭션이 빠른 시간 안에 처리되어야 하는 환경에서는 OLAP환경 보다 재 컴파일에 대해서 보다 민감하고, 성능에 많은 영향을 줄 수 있습니다.

위에서 설명 하고 있듯이 SQL Server는 프로시져 캐시라는 곳에 실행계획을 저장하고 있어 재사용을 할 수 있습니다. 재사용으로 인해 매번 실행계획을 다시 생성하지 않아도 되어 보다 적은 리소스로 쿼리에 대한 응답을 할 수 있습니다.

그러나 예전에 생성된 실행 계획을 재 사용할 경우 문제가 발생 할 수도 있습니다.

기존에 실행계획은 최초 실행계획의 생성시 받는 매개변수에 대해서 실행 계획을 만드는 것이 일반적이며, 기존에 실행계획을 생성한 매개변수와 나중에 수행되는 매개변수에 대한 카디널리티 편차가 크다면 실행 계획 재사용으로 인해 적절한 실행 계획을 찾지 못하고 기존의 실행 계획을 이용하여 성능상 문제를 야기 할 수 있습니다.

실행계획의 재사용이란 항상 좋은 것만이 아닌 필요에 따라서는 재 컴파일을 통해 새로운 실행 계획을 생성 하는 것이 성능상 이점을 가질 수 있습니다.

아래의 링크에서 이러한 실행계획의 재사용으로 인해서 발생 될 수 있는 문제에 대해서 간략히 설명 하고 있습니다.

프로시져 parameter sniffing에 의한 실행계획의 차이점

3. 강제 매개변수화, 자동 매개변수화란.

SQL Server 2005에서는 자동 매개 변수화 만이 아닌 강제매개변수화 라는 기능이 추가 되었습니다. 강제 매개변수화의 경우 실행 계획을 생성할 때 사용자가 설정한 매개변수로 해당 실행계획을 생성할 수 있습니다.

이러한 강제 매개변수화를 잘 사용한다면 특정 환경에서 카디널리티 편차가 심한 데이터에 대해서 재 컴파일로 최적의 실행 계획을 만드는 것이 아닌 변수의 분포도에 따라 몇 가지 실행 계획을 생성 하여 보다 효율적으로 작성 할 수 도 있습니다.

SQL Server 2005 예전 버전에도 존재하던 기능 중에 하나인 자동매개변수화(단순매개변수화)는 실행 계획을 재사용하기 위해서 단순한 구문 및 아래의 조건에 매칭되지 않는 경우에 대해서 상수를 변수로 변환하여 실행계획을 생성 할 수 도 있습니다.

이러한 내용들은 보면 최대한 재 컴파일을 막기 위한 SQL Server 개발진들의 노력이 보입니다. 이러한 노력을 보면 재 컴파일이 성능에 얼만큼 영향을 주는 부분인지 단편적으로 생각 해 볼 수 있을 것 같습니다.

자동 매개변수화가 될 수 없는 조건

   1. BULK INSERT

   2. IN 구문 또는 OR

   3. UPDATE 절에 SET @변수 = col

   4. SELECT 절에 UNION구문

   5. SELECT 절에 INTO구문

   6. SELECT/UPDATE 절에 FOR BROWSE

   7. SELECT/UPDATE/DELETE 에 OPTION 쿼리 힌트

   8. SELECT 절에 DISTINCT 구문

   9. SELECT/UPDATE/DELETE/INSERT절에 TOP구문

   10. WAITFOR 구문

   11. SELECT절에 GROUP BY, HAVING, COMPUTE 구문

   12. DELETE/UPDATE 절에 FROM CLAUSE 구문

   13. 전체 검색, 연결된 서버, 테이블 변수 참조

   14. 서브쿼리

   15. 조건 절에 <> 상수

   16. 조인이 포함된 구문

   17. 두 개 이상의 테이블을 참조

   18. DELETE, UPDATE 에 FROM절

4. SQL Server 2005에서 변경된 구문단위 재 컴파일

보통 SQL Server을 사용할 때 주로 프로시져 단위로 SQL구문을 작성 하여 개발 하는 경우가 많습니다. SQL Server 2000에서는 프로시져 내부에 재 컴파일이 해야 하는 경우 프로시져 내부에 존재 하는 모든 구문에 대해서 재 컴파일을 하였습니다.

만약 수만 줄의 쿼리 가 있었다면 이것을 재 컴파일 한다는 것은 그 작업만으로도 상당한 리소스를 소비 하게 됩니다. 그래서 이러한 경우를 대처 하기 위해서 자주 재 컴파일 되는 부분을 다른 프로시져로 만들고 원본 프로시져에서 호출 하는 방식을 사용하였습니다.

이러한 방법으로 전체 재 컴파일이 되는 것을 막을 수 는 있었지만 많이 번거로운 작업 이었습니다.

그러나 SQL Server 2005에서는 구문 단위의 재 컴파일이 도입 되어 SQL Serve 2000에서 사용하던 것처럼 따로 프로시져를 만들어 호출 하지 않아도 구문단위로 재 컴파일을 할 수 있습니다.

테스트!!

5. 프로시져 캐시 에서의 지연기록기의 역할

SQL Server에서는 프로시져 캐시라는 곳에 예전에 실행된 실행 계획을 가지고 있어 재사용한다고 하였습니다. 그렇다면 이 프로시져 캐시에 존재 하는 것들에 대해서 사용되지 않거나 사용빈도가 낮은 것에 대해서는 메모리에서 제거를 해야 하는 작업이 필요합니다.

만약 이러한 작업이 없다면 계속 프로시져 캐시의 증가로 인해 서버 메모리에 문제가 발생 할 수 있습니다. 그러나 SQL Server에서는 이러한 문제가 거의 발생 되지 않습니다.

지연기록기(LazyWriter)라는 백그라운드 프로세스가 존재 하기 때문입니다. 지연기록기 는 free page를 확보하기 위해 주기적으로 버퍼캐시를 검색 합니다.

이러한 작업을 수행 하면서 해당 조건에 보다 낮거나 같은 수치를 가지는 것을 캐시영역에서 제거 하여 free page를 확보 하게 됩니다.

그리고 지연기록기가 하는 일중 다른 하나는 운영체제의 메모리가 부족 하다면 지연기록기에서 SQL Server에서 사용되는 버퍼 풀 메모리의 일부분을 free하여 운영체제에게 반환하는 역할을 합니다.

SQL Server 2005에서 지원이 보다 강화된 NUMA(Non-Uniform Memory Access )에 대해서는 각 노드에 대해 지연기록기 프로세스가 존재 하게 됩니다.

NUMA(Non-Uniform Memory Access) 이해

(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/2a77699c-e06b-4377-8acf-4d565d225f3c.htm)

아래의 DMV를 이용하여 현재 재사용 되는 실행계획의 개수 및 재사용되는 상위 50개의 구문이 확인이 가능 합니다.

--Compare Single-Use and Re-Used Plans
declare @single int, @reused int, @total int
select @single=sum(case(usecounts) when 1 then 1 else 0 end), @reused= sum(case(usecounts) when 1 then 0 else 1 end),
     @total=count(usecounts)
from sys.dm_exec_cached_plans

select
'Single use plans (usecounts=1)'= @single,
'Re-used plans (usecounts>1),'= @reused,
're-use %'=cast(100.0*@reused / @total as dec(5,2)),
'total usecounts'=@total

select 'single use plan size'=sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1

--List Statements By Plan Re-Use Count
SELECT TOP 50
         qs.sql_handle
                 ,qs.plan_handle
                 ,cp.cacheobjtype
                 ,cp.usecounts
                 ,cp.size_in_bytes
                 ,qs.statement_start_offset
                 ,qs.statement_end_offset
                 ,qt.dbid
                 ,qt.objectid
                 ,qt.text
                 ,SUBSTRING(qt.text,qs.statement_start_offset/2,
                         (case when qs.statement_end_offset = -1
                         then len(convert(nvarchar(max), qt.text)) * 2
                         else qs.statement_end_offset end -qs.statement_start_offset)/2)
                 as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [Usecounts] DESC


6. 재 컴파일의 원인 및 프로시져 캐시 사라지는 경우

위에서 보듯이 쿼리의 자주 실행 되는 것이 아니라면 지연기록기에 의해 프로시져 캐시에서 실행계획이 제거 될 수 있습니다. 이렇게 되면 다시 쿼리가 호출 되면 다시 컴파일을 수행 하여야 합니다.

여기서는 지연기록기에 프로시져 캐시가 사라지는 경우가 아닌 다른 작업으로 사라지는 경우와, 기존의 실행계획이 있지만 다시 재 컴파일을 해야 하는 경우 대해서 알아보도록 하겠습니다.

재 컴파일의 원인은 아래와 같이 스키마 변경, 통계정보 업데이트 등으로 발생 될 수 있으며, ALTER DATABASE구문이나 DBCC CHECKDB구문 등으로 프로시져 캐시의 내용이 모두 사라져 모든 쿼리에 대해서 다시 컴파일을 하여야 합니다.

하나의 테이블에 통계정보가 변경되어 해당 테이블과 연관된 프로시져를 재 컴파일을 하는 것은 오히려 좋을 수 있습니다. 보다 최신의 통계정보를 바탕으로 오히려 예전 보다 더욱 효율적인 실행계획을 가지고 처리 할 수 있기 때문입니다.

그러나 ALTER DATABASE, RESTORE DATEBASE같은 경우는 서비스 운영 중에 많이 사용하고 있지 않는 구문이지만 만약 이 구문들이 사용된다면 전체 프로시져 캐시에 존재하는 실행계획이 사라져서 다시 모든 쿼리에 대해서 컴파일을 하여야 합니다.

많은 트랜잭션을 가지고 있는 사이트라면 순간적으로 CPU 리소스가 높이 사용 될 것입니다. 그러기에 이러한 동작을 유발 하는 구문에 대해서 서비스 중 작업이 필요하다면 충분한 주의가 필요 합니다.

재 컴파일 원인

  • 스키마 변경(ALTER TABLE, ALTER VIEW, Alter Index)
  • 통계정보 변경
  • 지연된 컴파일
  • SET 옵션 변경
  • 임시 테이블 변경
  • OPTION(RECOMPILE) 쿼리 힌트
  • sp_recompile 호출

프로시져 캐쉬가 사라지는 작업

  • ALTER DATABASE [dbName] SET ONLINE
  • ALTER DATABASE [dbName] SET OFFLINE
  • ALTER DATABASE [dbName] SET READ_ONLY
  • ALTER DATABASE [dbName] SET READ_WRITE
  • ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name]
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY
  • ALTER DATABASE [dbName] COLLATE Collation_Name
  • DROP DATABASE [db_Snapshot_Name]
  • Restore database
  • Detach database
  • DBCC FREEPROCCACHE
  • DBCC FREESYSTEMCACHE
  • DBCC CHECKDB (참고 : SQL Server 2005 SP2 이전에는 DBCC CHECKDB를 수행하면 프로시져의 캐시가 사라지게 되나 SP2에서는 제거되지 않음.)

SQL Server 2005 SP2에서는 프로시져 캐시가 사라지는 작업 시 SQL 에러로그에 아래와 같은 메시지가 추가 되었습니다.

2007-01-19 14:29:07.39 spid54       Starting up database 'R'.
2007-01-19 14:29:23.84 spid54       Setting database option OFFLINE to ON for database R.
2007-01-19 14:29:24.06 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.06 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.


7. 재 컴파일 발생 원인 및 빈도 찾기

이러한 재 컴파일이 문제가 될 수 있다는 것을 위에서 알아 보았습니다. 그러면 이제는 현재 운영하고 있는 사이트에서 얼마나 많은 재 컴파일이 발생하는지 그리고 이유는 무엇인지 확인할 필요가 있습니다.

많은 트랜잭션이 발생하지 않는다면, 요즘은 워낙 CPU사양도 좋고 해서 재 컴파일에 의한 문제는 그리 크게 사람에게 다가오지 않을 수도 있습니다. 그러나 많은 트랜잭션이 발생 하는 환경이라면 분명 확인 할 가치는 있을 겁니다.

보통 재 컴파일에 대한 문제로 원인 및 빈도를 확인 하기 위해서는 프로파일러와 성능 모니터를 통해 확인 합니다.

프로파일러의 경우 모든 SQL Server로 들어오는 쿼리에 대해서 재 컴파일이 발생 되었다면 SP: Recompile, SQL: StmtRecompile 이벤트 및 EventSubClass컬럼을 추가 하여 원인 및 빈도를 확인 할 수 있습니다.

그렇지만 프로파일러를 서비스중인 서버에 사용하는 것도 힘든 곳도 많을 것입니다. 그리고 구문단위까지 이벤트를 걸게 되면 추적 파일 크기는 금방 커질 것 입니다.

비쥬얼하게 보여지는 프로파일러 대신 SQLDIAG나 저장 프로시져를 사용하는 것이 서버의 리소스 소비에 대해서 보다 효과 적입니다.

SQLDIAG는 기존 SQL Server 2000에도 존재 하였지만 일반적인 에러로그 및 수행 당시의 시스템 테이블 정보 등 이었지만 SQL Server 2005의 SQLDIAG는 기존에 Microsoft PSS팀에서 사용하던 PSSDIAG의 기능을 포함 하고 있어 운영체제 이벤트 로그, 추적, 성능 카운터 등을 한번에 수집이 가능 하여 보다 쉽게 관련 데이터를 수집 할 수 있습니다. 그러나 이러한 이벤트나 카운터를 수정 해야 한다면 XML파일을 직접 수정 해야 하는 번거로움은 있습니다.

단순히 재 컴파일에 대해서 어떠한 원인으로 발생 되었는지 확인 하려면 아래와 같이 열 필터의 EventSubClass의 “값이 없는 행 제외”를 추가하여 추적하여 성능 모니터의 수치와 비교해 본다면 보다 쉽게 재 컴파일의 원인을 확인 할 수 있습니다.

성능 모니터를 통해 재 컴파일의 빈도를 찾기 위해서는 아래에 있는 카운터 중 SQL Re-Compilations/sec, Batch Requests/sec 을 추가하여 빈도를 확인 할 수 있습니다.

SQLDIAG, 저장 프로시져를 이용하여 추적 하는 방법은 온라인 설명서를 참조하시면 됩니다.

SQLdiag 유틸리티(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/sqlcmpt9/html/45ba1307-33d1-431e-872c-a6e4556f5ff2.htm)

SQL Server 프로파일러 저장 프로시저(Transact-SQL)(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/tsqlref9/html/8c99c3db-0b04-46c3-aa59-d6f680522fdd.htm)

<그림 : 열 필터 설정>

<그림: 열 필터 설정 후 프로파일러 모습>

EventSubClass 값

설명

1

스키마가 변경되었습니다.

2

통계가 변경되었습니다.

3

지연된 컴파일입니다.

4

SET 옵션이 변경되었습니다.

5

임시 테이블이 변경되었습니다.

6

원격 행 집합이 변경되었습니다.

7

FOR BROWSE 권한이 변경되었습니다.

8

쿼리 알림 환경이 변경되었습니다.

9

분할된 뷰가 변경되었습니다.

10

커서 옵션이 변경되었습니다.

11

OPTION (RECOMPILE)이 요청되었습니다.

<EventSubClass 컬럼 설명>

성능 모니터 (perfmon)의 SQL Statistics 카운터를 통해 분석 및 빈도를 확인 할 수 있습니다.

SQL Server SQL Statistics 카운터

설명

Auto-Param Attempts/sec

초당 자동 매개 변수화 시도 수입니다. 합계는 자동 매개 변수화의 실패한 횟수, 안전한 횟수 그리고 안전하지 않은 횟수의 합과 같습니다. 자동 매개 변수화는 유사한 여러 요청을 처리할 때 캐시된 결과 실행 계획을 다시 사용하기 위해 SQL Server 인스턴스가 일부 리터럴을 매개 변수로 바꾸어 Transact-SQL 요청을 매개 변수화할 때 일어납니다. Microsoft SQL Server 2000 에서는 자동 매개 변수화를 단순 매개 변수화라고도 합니다. 이 카운터에는 강제 매개 변수화는 포함되지 않습니다.

Batch Requests/sec

초당 받는 Transact-SQL 명령 일괄 처리 수입니다. 이 통계는 모든 제약 조건(I/O, 사용자 수, 캐시 크기, 요청의 복잡도 등)의 영향을 받습니다. 높은 일괄 처리 수치는 높은 처리 효율을 의미합니다.

Failed Auto-Params/sec

실패한 자동 매개 변수화의 초당 시도 횟수입니다. 이 값은 작을수록 좋습니다.

Forced Parameterizations/sec

초당 성공한 강제 매개 변수화 수입니다.

Safe Auto-Params/sec

안전한 자동 매개 변수화의 초당 시도 횟수입니다. 안전한 자동 매개 변수화는 여러 유사한 Transact-SQL 문 간에 캐시된 실행 계획을 공유할 수 있도록 하는 결정을 참조하며 SQL Server 가 시도하는 많은 자동 매개 변수화 중 일부는 성공하고 나머지는 실패합니다. SQL Server 2005 에서는 자동 매개 변수화를 단순 매개 변수화라고도 합니다. 강제 매개 변수화는 포함되지 않습니다.

SQL Attention rate

초당 주의 수입니다. 주의는 현재 실행 중인 요청을 종료하기 위한 클라이언트의 요청입니다.

SQL Compilations/sec

초당 SQL 컴파일 수입니다. 컴파일 코드 경로를 입력한 횟수를 나타내며 SQL Server 2005 에서 문 수준의 다시 컴파일에 의한 컴파일을 포함합니다. SQL Server 사용자 작업이 안정되면 이 값은 일정한 수준에 이릅니다.

SQL Re-Compilations/sec

초당 문 다시 컴파일 수입니다. 문 다시 컴파일이 트리거된 횟수를 나타냅니다. 일반적으로 다시 컴파일하는 횟수는 적을수록 좋습니다. SQL Server 2000 에서 다시 컴파일은 Microsoft SQL Server 2005 의 일괄 처리 범위가 아니라 문 범위 다시 컴파일입니다. 따라서 SQL Server 2005 와 이전 버전 간에 이 카운터 값을 직접 비교할 수 없습니다.

Unsafe Auto-Params/sec

안전하지 않은 자동 매개 변수화의 초당 시도 횟수입니다. 예를 들어 쿼리는 캐시된 계획을 공유하지 않고 자동 매개변수화하는 특성이 있습니다. 이러한 자동 매개변수화는 안전하지 않은 것으로 지정됩니다. 강제 매개 변수화 수는 여기에 포함되지 않습니다.


드디어 “SQL Server의 재 컴파일이란?” 아티클에 마지막입니다.

여기서 제가 가장 하고 싶은 말은 지연기록기가 어떠한 연산을 하여 실행계획을 제거되고 서버의 메모리 중 얼마만큼 프로시져 캐시로 사용할 수 있는 그러한 내용이 아닌 “환경에 따라 독이 될 수도 약이 될 수도 있는 것이 재 컴파일이다.” 입니다.

그러나 여기서는 재 컴파일이 발생 하지 않게 모니터링 방법 등만을 기술 하였습니다. 이것은 대부분의 환경에서는 재 컴파일이라는 것이 약 이 되는 경우가 많다는 것이며 항상 재 컴파일이 나쁘다는 것은 아닙니다.

그렇지만 재 컴파일이라는 것을 약 또는 독을 만드는 것은 사람이라고 생각합니다. 만약 독이 라면 어떻게 찾고 해결할까? 그렇지만 찾는 방법만 있지 이것을 여기서 해결 하는 방법은 기술 되지 않았습니다.

이 부분은 직접 테스트를 해보면서 배우고 느끼는 것이 가장 좋다고 생각하는 개인적인 의견입니다. ^^

저는 SQL Server라는 DBMS를 접하게 된지 그리 오래 되지 않았습니다. 그래서 아직은 배워야 할 것 도 많다고 생각합니다. 그래서 더욱 재미있다고 생각합니다. DBMS라는 것에 대해서는 무엇보다 사람이 중요성이 다른 무엇보다 크다고 생각하기 때문입니다.

나중에 SQL Server 3000이 나와서 아무리 옵티마이져가 똑똑해졌다고 한다고 하더라도 사람이 해야 할 부분까지 SQL Server단에서 처리 하기는 힘들 거라고 생각합니다. 환경 및 비즈니스 로직에 따라서 사람의 단 하나의 판단으로 많은 차이가 날 수 있을 수 있기 때문입니다.

길지 않은 아티클을 작성하는데 생각보다 오랜 시간이 걸린 듯합니다. 지금 다시 보면 별 내용이 없는 것 같아 괜히 부끄러워지지만, 그래도 짧은 시간 동안 많은 공부를 할 수 있었고 정리를 하는 시간을 가져서 참 좋았습니다.

솔직히 이 재 컴파일이 주제가 정하기 전 여러 주제에 대해서 생각을 해보고 작성도 해보았지만 마음에 들지 않아 주제를 변경하기도 여러 번..아마 그 주제 들은 어딘가의 폴더 안에서 곤히 잠들고 있을 겁니다.

만약 다음에도 아티클로 다시 뵐 수 있는 기회가 온다면 더욱 열심히 준비하고 공부해서 좋은 아니 괜찮은 아티클로 다시 찾아 뵙겠습니다.

[출처] SQL Server 2005 Query optimizer의 변경된 내용|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

[SQL 서버 2005 실전 활용] ④ 새로운 DB 보호·복구 모델  (0) 2008.04.28
논리적 파일명 변경하기  (0) 2008.04.28
MSSQL MDF, LDF 파일 위치 변경 방법  (0) 2008.04.28
table 내의 모든 제약조건, index 이름, 해당에 index가 걸린 column 조회  (0) 2008.04.28
MSSQL 에서 XML 데이터 입력, 출력, 수정하는 방법  (0) 2008.04.28

+ Recent posts

티스토리툴바