보미네

정규화 법칙은 업데이트 이상과 데이터 비일관성을 막기위해 디자인 되었다. 성능 Tradeoff 에 대해서, 이 가이드는 Non-key 속성은 자주 업데이트 된다고 가정한다. 이런 가정은 데이터 추출에는 비 효율적이다. 왜냐하면, 정규화되지 않은 경우 하나의 레코드에서 추출 될 수 있고, 정규화 폼에서는 아마도 여러 레코드에서 추출해 와야 할 수 있기 때문이다. 실제 성능 요구사항을 고려 할 때 모든 레코드를 완전히 정규화 할 의무는 없다.

삽입시 의도하지 않은 값들도 함께 삽입

삭제시 의도하지 않은 값들도 함께 삭제 (값 자체가 없어져 버린다.)

갱신시 전체 갱신되지 않고 일부만 갱신되는 것

1 정규화
1 정규형은 레코드 타입의 모양을 다룬다.
1 정규화는 하나의 레코드는 반드시 같은 필드 숫자로 이루어져야 한다.
1 정규화는 반복되는 필드와 그룹을 없애는 것이다. 정의의 문제지 디자인 가이드라인이 아니다. 관계형 데이터베이스 이론은 레코드들이 가변 필드를 갖는 것을 다루지 않는다.
참고) 하나의 필드에 여러값을 갖는 경우도 1정규화 위반 이라고 하지만, 이것은 데이터베이스 이론의 위반이다. (도메인 원자값)

2 정규화
non-key 필드 팩트가 전체 key 중 일부 key 의 팩터 일 때 발생한다. 이것은 여러 필드의 조합과 같이 key 가 복합키 일때만 일어난다. (부분적 함수 종속 제거)

3 정규화
non-key 필드가 다른 non-key 필드의 팩트일 때 발생한다. (이행적 함수 종속 제거)

BCNF (BoyceCodd Normal Form)
모든 결정자가 후보키 (결정자 이면서 후보키가 아닌것 제거)

4 정규화
서로 독립인 다중치 속성일 경우 발생한다. (다치 종속 제거)

5 정규화 (PJNF, Project-Join Normal Form)
서로 독립이 아닌 다중치 속성일 경우 발생한다. (조인 종속성 제거)

6 정규화 (DKNF, Domain-Key Normal Form)
계좌의 Account 가 9로 시작하고, 잔고가 2500불 이상이면 다른 테이블에 들어간다.

http://blog.naver.com/jinsol1/100024608148


아주 심플하게 정리 했다. 그 이상의 정규형이 있으면 좀 알려주세요~ ^.^ 이것보다 더 쉽게 설명 할 수는 없을듯...
이거 강의하고 조광원 아저씨처럼 쉽게 설명한다는 이야기를 들어 매우 기분이 좋았습니다.





A Simple Guide to Five Normal Forms in Relational Database Theory 에 자세히 설명되어 있으며 매우 쉽게 예제와 함께 설명한다.

위 내용을 다 이해한다면, 3정규화 까지만 해라는 말을 못 할 듯 하다.


이번 정규화 발표가 데이터베이스 디자인시 도움이 되었으면 합니다.
수고하세요~

첨부파일은 원본 입니다.

A Simple Guide to Five Normal Forms in Relational Database .pdf

한글자료 
          http://yuhani.springnote.com/pages/917834

어드벤스드 자료로 5정규화와 6정규화의 내용은 다음을 참고한다.

정규화 adx.ppt

정규화 adx 한글판.ppt

William Kent, "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM 26(2), Feb. 1983, 120-125. Also IBM Technical Report TR03.159, Aug. 1981. Also presented at SHARE 62, March 1984, Anaheim, California. Also in A.R. Hurson, L.L. Miller and S.H. Pakzad, Parallel Architectures for Database Systems, IEEE Computer Society Press, 1989. [12 pp]

A Simple Guide to Five Normal Forms in Relational Database Theory
관계형데이터베이스 이론에서의 5정규화의 간단한 가이드

1 소개 **************
관계형 데이터베이스 이론에서 정규화란 레코드 디자인에 대한 가이드라인이다. 본 가이드라인은 1정규화 부터 5정규화 까지를 다룬다. 용어는 관계이론의 이해 없이 볼 수 있는 것들이다. 이 디자인 가이드라인들은 관계형 데이터베이스를 사용하지 않더라도 충분히 의미 있는 것이다. 일반화를 강조하기 위해, 관계형 모델의 컨셉의 참조 없이 가이드라인을 제시한다. 더불어 좀 더 이해하기 쉽게 하기 위해서이다. 이 프리젠테이션은 레코드 디자인의 의도된 제약을 직관적으로 전달한다. 약식(비공식적)이지만 몇몇 기술적 세부사항은 부정확 할 수 있다. 이 주제를 포괄적으로 다루는 것은 Date [4] 에서 다룬다.

정규화 법칙은 업데이트 이상과 데이터 비일관성을 막기위해 디자인 되었다. 성능 Tradeoff 에 대해서, 이 가이드는 Non-key 속성은 자주 업데이트 된다는 가정한다. 이런 가정은 데이터 추출에는 비 효율적이다. 왜냐하면, 정규화되지 않은 경우 하나의 레코드에서 추출 될 수 있고, 정규화 폼에서는 아마도 여러 레코드에서 추출해 와야 할 수 있기 때문이다. 실제 성능 요구사항을 고려 할 때 모든 레코드를 완전히 정규화 할 의무는 없다.

2. Frist Normal Form **************
E.F. Codd, "A Relational Model of Data for Large Shared Data Banks", Comm. ACM 13 (6), June 1970, pp. 377-387.
The original paper introducing the relational data model.

1 정규형은 레코드 타입의 모양을 다룬다.
1 정규화는 하나의 레코드는 반드시 같은 필드 숫자로 이루어져야 한다.
1 정규화는 반복되는 필드와 그룹을 없애는 것이다. 정의의 문제지 디자인 가이드라인 이 아니다. 관계형 데이터베이스 이론은 레코드들이 가변 필드를 갖는 것을 다루지 않는다.

1정규화 위반사례 1 (데이터베이스 이론의 정의 위반)
아이디, 취미1, 취미2, 취미3
민석, 싸이클, 영화감상, SQL Server
만철, 오락실게임, SQL Server Bulk Operation,null
산아, SQL Server, null, null

1정규화 위반사례 2 (반복 그룹 - 업데이트 어떻게 할꺼야?)
아이디, 취미
민석, 싸이클
민석, 영화감상
민석, SQL Server
만철, 오락실 게임
만철, SQL Server Bulk Operation
산아, SQL Server
참고) 하나의 필드에 여러값을 갖는 경우도 1정규화 위반 이라고 하지만, 이것은 데이터베이스 이론의 위반이다.

3. SECOND AND THIRD NORMAL FORMS **************
E.F. Codd, "Normalized Data Base Structure: A Brief Tutorial", ACM SIGFIDET Workshop on Data Description, Access, and Control, Nov. 11-12, 1971, San Diego, California, E.F. Codd and A.L. Dean (eds.).
An early tutorial on the relational model and normalization.

E.F. Codd, "Further Normalization of the Data Base Relational Model", R. Rustin (ed.), Data Base Systems (Courant Computer Science Symposia 6), Prentice-Hall, 1972. Also IBM Research Report RJ909.

W. Kent, "A Primer of Normal Forms", IBM Technical Report TR02.600, Dec. 1973.
An early, formal tutorial on first, second, and third normal forms.

2 정규화와 3정규화는 관계에서 non-key 와 key 필드를 다룬다.
2 정규화와 3정규화 에서는 하나의 non-key 필드는 key 에 대해서 반드시 하나의 팩트를 제공해야 한다. 복합키의 경우도 전체 키에 하나의 팩터를 제공해야 한다. 그리고, 이 레코드들은 1 정규화를 준수해야 한다.

이제부터 단지 하나의 값만을 가지는 팩트를 다룰 것이다. 팩트는 부서와 사원과 같이, one-to-many 관계를 가질것이다. 또는, 사원과 배우자와 같이 one-to-one 관계이다. "X 는 Y 의 팩터이다." 라는 것은 X 와 Y 에 대해 one-to-one 이나 one-to-many 관계이다.
Y 는 하나 이상의 컬럼을 가지고, X 는 그렇지 않을 것이다. 수량은 파트와 창고 조합의 팩트이다.

3.1 Second Normal Form
2 정규화 위반은 non-key 필트 팩트가 전체 key 중 일부 key 의 팩터일때 발생한다. 이것은 여러 필드의 조합과 같이 key 가 복합키 일때만 일어난다. 아래와 같은 인벤토리 레코드를 생각해 보자.

---------------------------------------------------
| PART | WAREHOUSE | QUANTITY | WAREHOUSE-ADDRESS |
====================-------------------------------

key 는 part 와 warehouse 두개의 조합으로 이루어져 있다. 하지만 warehouse-address 는 warehose 하나에만 속하는 팩터이다. 이러한 디자인의 기본적인 문제는 다음과 같다.

* warehose 에 있는 part별 warehouse address 는 모든 행에서 반복된다.
* 만약 warehose-address가 갱신되면,그 warehouse 에 있는 모든 part 의 warehouse-address 가 업데이트 되어야 한다.
* 중복으로 들어가 있기 때문에 데이터 일관성을 잃을 수 도 있다. 같은 warehose 에 다른 warehouse-address 가 보여질 수 있다.
* 만약 어떤 시간에서 warehose 에 part 가 없으면, warehouse-address 를 유지 할 수 없다.

2 정규화의 문제에서 안전하기 위해서는 위 예제의 레코드는 두개의 레코드로 분리 되어야 한다.

-------------------------------  ---------------------------------
| PART | WAREHOUSE | QUANTITY |  | WAREHOUSE | WAREHOUSE-ADDRESS |
====================-----------  =============--------------------

데이터 디자인이 이런 방법으로 변화되면 정규화 되지 않은 레코드들은 정규화된 레코드로 된다. 이러한 프로세스를 정규화라 한다. 정규화(normalization)의 용어는 때때로 특정 정규 폼에 상대적으로 사용된다. 그래서, 하나의 레코드셋이 2 정규화에 대해서는 정규 폼을 지키고 3 정규형에 대해서는 아닐 수 있다.

중복과 비일관성을 최소화 함으로 정규화된 디자인은 데이터 완전성을 향상시킨다. 하지만 특정 검색 어플리케이션의 성능 비용이 더 야기되기도 한다. 어떤 어플리케이션이 warehose 에 있는 part 의 warehouse-address 를 보려고 할 때, 비 정규화된 폼에서는 하나의 레코드만 보면되나, 정규화된 디자인에서는 그 어플리케이션은 2개의 레코드 타입을 조사한 후 적절한 패어로 연결해야 한다.

3.2 Third Normal Form
3 정규화 위반은 non-key 필드가 다른 non-key 필드의 팩트일 때 발생한다.

------------------------------------
| EMPLOYEE | DEPARTMENT | LOCATION |
============------------------------

employee 필드는 키 필드이다. 만약 각 department 가 한 곳에 있다면, location 필드는 department 의 팩트이다. 더해서 employee 도 팩트가 된다. 이러한 디자인의 문제는 아래와 같이 2 정규화 위반과 같다.

* department 에 할당된 모든 employee 에 department의 location 이 중복되어 나타난다.
* 만약 department 의 location 이 변경되면, 이러한 모든 레코드가 업데이트 되어야 한다.
* 중복 때문에 데이터는 일관정을 잃을 수 있고, 같은 department 에 대해 다른 location 을 보일 수 있다.
* 만약 department 에 employee 가 없으면, department 의 location 정보를 보존 할 수 없다.

요약하면, 모든 필드는 키의 부분이나 하나의 팩트를 전달 할때 전체키에 연관되어야 한다. 다른건 없다. 이렇때 레코드는 2,3 정규화의 폼이다.

3.3 Functional Dependencies
데이터베이스 이론에서, 2,3 정규화 폼은 함수 종속으로 정의된다. 우리의 싱글 밸류 팩트에 일치한다. 어떤 필드 Y 는 X 필드(들)에 "함수 종속" 이며 만약 두개의 레코드가 있을 때 같은 X 가 다른 Y 값을 가지면 이것은 종속이 아니다. 다르게 말하면, 주어진 X 값은 항상 같은 Y 값을 가진다. X 가 key 이면, 모든 필드들은 정의에 의해 X 에 종속된다. 그렇기 때문에 같은 X 값에 대해 2개의 레코드를 가질 수 없다.

여기 함수 종속과 우리가 보여준 단일값 팩트는 기술적으로 약간 틀린점이 있다. 함수 종속은 유니크 와 단일 식별자에서만 존재한다. 예를 들자면 어떤 사람의 주소가 단일값 팩트 일때를 가정해 보자. 그 사람은 하나의 주소만 가지고 있다. 만약 우리가 사람에 대해 unique 식별자를 제공하지 못하면, 함수 종속되지 못한다.

(동명 2인)
------------------------------------------
|   PERSON   |       ADDRESS                 |
-------------+--------------------------------
| John Smith | 123 Main St., New York        |
| John Smith | 321 Center St., San Francisco |
----------------------------------------------

사람이 단일 주소를 가지지만, 주어진 이름이 여러 다른 주소를 나타낼 수 있다. 그렇기 때문에 우리의 단일값 팩트는 함수 종속에 해당하지 않는다.

마찬가지로, address는 함수 종속성을 만족하기 위해 동일한 철자를 써야 한다. 다음과 같은 경우는 같은 사람이 두개의 다른 주소를 가지는 것 처럼 보인다. 다시 말해 함수 종속성을 불가능 하게 한다.

(같은 사람이며, 주소를 다르게 표시함)
---------------------------------------
|   PERSON   |       ADDRESS          |
-------------+-------------------------
| John Smith | 123 Main St., New York |
| John Smith | 123 Main Street, NYC   |
---------------------------------------

유니크하지 않거나 비 단일 식별자를 사용하면 보호하지 못한다. 이런 예제는 종종 데이터 관리 문제를 유발 시킨다.  지적 하고자 하는것은, 기능적 종속과 다양한 정규화들은 유니크나 단일 식별자가 있을때만 정의된다. 그래서, 디자인 가이드라인은 정규화 공식 정의에 적용된 것 보다 좀 더 암시적이다.

예를 들어, 디자이너는 다음 예제에서 어떤 non-key 필드가 단일값 팩터 임을 안다. 이 디자인은 위에서 언급한 모든 업데이트 이상에 해당된다.   

----------------------------------------------------------
| EMPLOYEE  |  FATHER    |  FATHER'S-ADDRESS             |
|============------------+-------------------------------|
| Art Smith | John Smith | 123 Main St., New York        |
| Bob Smith | John Smith | 123 Main Street, NYC          |
| Cal Smith | John Smith | 321 Center St., San Francisco |
----------------------------------------------------------

하지만, 공식 용어에서, father's-address 와 father 사이에는 함수 종속이 없다. 그러므로, 3정규화 위반이 아니다.

1nf 하나의 컬럼에 한개의 값만 있을것

2nf 키 컬럼에 종속되는 컬럼은 테이블 분리 

3nf 키가 아닌 컬럼에 종속되는 컬럼은 테이블 분리

bcnf 일반컬럼이 복합키 컬럼 일부에 종속되는 경우 테이블 분리

4nf 복합키가 다른 주제영역을 다루고 있을경우 1:n 으로 분리한다. 

5nf 4nf 에서 주제 영역간 n:m 관계가 있을때 각각을 1:n 으로 분리해 join 종속성을 제거한다. 

1~3까지만 실무에 쓰임 

메모리 상황을 보기위해 현재 가장 편한것은 dbcc memorystatus 이다. 이것을 이용해 어떤 메모리 영역에 문제가 있을 수 있는지 한번에 알아볼 수 있는 좋은 방법을 소개한다.

DMV 는 Session 당 할당 메모리를 보거나 뭐 더 좋은 많은 결과를 볼 수 있는 쿼리가 존재 한다. 그러나 DMV 는 모든 정보를 제공하지 않고 오직 dbcc memorystatus 만 모든 정보를 제공한다.

과거 버전의 서버에서 토탈 어떻게 사용하고 있는지 편리하게 제공하는 방법이 없다. 그래서 set nocount on 과 dbcc memorystatus 결과를 c:\dbcc.txt 라는 파일로 만들어 sql 로 파싱해서 편리하게 분석해 보자
눈으로 살펴보다가 내 눈이 심히 피곤했고, 이 결과를 파일로 만들어 넣은것은 헤더 정보를 같이 볼 방법은 이 방법밖에 없었다. 2005 이상에서는 편리하게 쿼리 할 수 있지만 2000 일 수 도 있어 쿼리를 과거 버전과 호환성 있게 만드느라 이렇게 만들었다.

동작 방법

모니터링 방법

결과 예제 

SQL Server 2000 DBCC MEMORYSTATUS 에 대한 기술 정보
http://support.microsoft.com/?id=271624
SQL Server 2005 DBCC MEMORYSTATUS 에 대한 기술 정보
http://support.microsoft.com/?id=907877 



 

나는 이것을 프로시저와 메모리 그랜트 이슈라고 부르기 보다. "쿼리를 여러개로 분리하면 좋은 경우다" 라고 말하고 싶다.
실제 많은 이와 같은 프로시저는 경우에 따라 여러 sub procedure 를 콜 하도록 구성한다. steatement level recompile 도 용서 할 수 없는 경우가 있기 때문이다. 

이 문서에는 recompile 하는 것이 좋겠다고 하지만, 우리는 이런경우 여러개로 분리해 쓴다.
아래 이유가 잘 설명되어 있지만. 쿼리플랜이 만들어질때 메모리 할당량이 설정되기 때문에 sort warning 이 생길수 있다는 관점에서 recompile 을 해 적당한 메모리를 할당 받으라는 것이다. 

그러나, 실제 운영을 해보면, 이런 이슈보다 기간에 따라 쿼리 플랜이 달라져야 하고 인덱스를 다르게 써야 하는경우가 더 많았다. 그걸 수용하기 위해서는 statement level 의 recompile 이나, 분리된 다른 sub procedure 를 힌트로 박아 운영하는 것이 더 보편적인 이슈 해결 방법 이였다.

다음 사이트를 참고해서 학습하면 좋을듯 하다.
http://www.sqlworkshops.com/plancachingandquerymemory.htm

참고로 이분이 소개한 대부분의 내용이 옵티마이저를 소개한 블로그에서 먼저 소개되어 있다. 하지만, 현실적으로 어떻게 이러한 문제를 해결하는게 좋을지에 대한 해답은 여기가 가장 좋은듯 하다. 

배껴서 강의하면 재미 날듯 해 한국어로 완벽하게 이해하는 강의를 해봐야지....SQLTAG 에서도 TeamView + 게임톡 강의로 많은 사람들과 이런 이슈를 하나 하나 나누어 보아야겠다. 언어의 장벽을 SQL에는 없도록 해봐야지.

비슷한 주제로 김정선 강사님이 강의 했다는데 들어 두었으면 좋았을걸..하는 아쉬움이 남는다. 아쉽게 스터디 날짜랑 같아 모두 한배타고 아무도 못 들었다. -_- 제가 책임지고 같은 주제로 더 심도깊게 강의 해 드리겠습니다. 운영의 경험 + 수많은 KB 조사까지 넣어 (알지 ?).

모두들 즐거운 SQL Time 되세요~

이번것은 넘 쉬어서 변역 안할란다.




Let’s create a stored procedure that sorts customers by name within certain date range.

To observe Sort Warnings, enable 'Sort Warnings' in SQL Profiler under Events 'Errors and Warnings'.

--Example provided by www.sqlworkshops.com

create proc CustomersByCreationDate @CreationDateFrom datetime, @CreationDateTo datetime as

begin

      declare @CustomerID int, @CustomerName varchar(48), @CreationDate datetime

      select @CustomerName = c.CustomerName, @CreationDate = c.CreationDate from Customers c

            where c.CreationDate between @CreationDateFrom and @CreationDateTo

            order by c.CustomerName

      option (maxdop 1)

      end

go

Let’s execute the stored procedure initially with 1 month date range.

set statistics time on

go

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-01-31'

go

The stored procedure took 48 ms to complete.

The stored procedure was granted 6656 KB based on 43199.9 rows being estimated.

The estimated number of rows, 43199.9 is similar to actual number of rows 43200 and hence the memory estimation should be ok.

There was no Sort Warnings in SQL Profiler. To observe Sort Warnings, enable 'Sort Warnings' in SQL Profiler under Events 'Errors and Warnings'.

Now let’s execute the stored procedure with 6 month date range.

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-06-30'

go

The stored procedure took 679 ms to complete.

The stored procedure was granted 6656 KB based on 43199.9 rows being estimated.

The estimated number of rows, 43199.9 is way different from the actual number of rows 259200 because the estimation is based on the first set of parameter value supplied to the stored procedure which is 1 month in our case. This underestimation will lead to sort spill over tempdb, resulting in poor performance.

There was Sort Warnings in SQL Profiler. To observe Sort Warnings, enable 'Sort Warnings' in SQL Profiler under Events 'Errors and Warnings'.

To monitor the amount of data written and read from tempdb, one can execute select num_of_bytes_written, num_of_bytes_read from sys.dm_io_virtual_file_stats(2, NULL) before and after the stored procedure execution, for additional information refer to the webcast: www.sqlworkshops.com/webcasts.

Let’s recompile the stored procedure and then let’s first execute the stored procedure with 6 month date range.

In a production instance it is not advisable to use sp_recompile instead one should use DBCC FREEPROCCACHE (plan_handle). This is due to locking issues involved with sp_recompile, refer to our webcasts for further details.

exec sp_recompile CustomersByCreationDate

go

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-06-30'

go

Now the stored procedure took only 294 ms instead of 679 ms.

The stored procedure was granted 26832 KB of memory.

The estimated number of rows, 259200 is similar to actual number of rows of 259200. Better performance of this stored procedure is due to better estimation of memory and avoiding sort spill over tempdb.

There was no Sort Warnings in SQL Profiler.

Now let’s execute the stored procedure with 1 month date range.

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-01-31'

go

The stored procedure took 49 ms to complete, similar to our very first stored procedure execution.

This stored procedure was granted more memory (26832 KB) than necessary memory (6656 KB) based on 6 months of data estimation (259200 rows) instead of 1 month of data estimation (43199.9 rows). This is because the estimation is based on the first set of parameter value supplied to the stored procedure which is 6 months in this case. This overestimation did not affect performance, but it might affect performance of other concurrent queries requiring memory and hence overestimation is not recommended. This overestimation might affect performance Hash Match operations, refer to article Plan Caching and Query Memory Part II for further details.

Let’s recompile the stored procedure and then let’s first execute the stored procedure with 2 day date range.

exec sp_recompile CustomersByCreationDate

go

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-01-02'

go

The stored procedure took 1 ms.

The stored procedure was granted 1024 KB based on 1440 rows being estimated.

There was no Sort Warnings in SQL Profiler.

Now let’s execute the stored procedure with 6 month date range.

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-06-30'

go

The stored procedure took 955 ms to complete, way higher than 679 ms or 294ms we noticed before.

The stored procedure was granted 1024 KB based on 1440 rows being estimated. But we noticed in the past this stored procedure with 6 month date range needed 26832 KB of memory to execute optimally without spill over tempdb. This is clear underestimation of memory and the reason for the very poor performance.

There was Sort Warnings in SQL Profiler. Unlike before this was a Multiple pass sort instead of Single pass sort. This occurs when granted memory is too low.

Intermediate Summary: This issue can be avoided by not caching the plan for memory allocating queries. Other possibility is to use recompile hint or optimize for hint to allocate memory for predefined date range.

Let’s recreate the stored procedure with recompile hint.

--Example provided by www.sqlworkshops.com

drop proc CustomersByCreationDate

go

create proc CustomersByCreationDate @CreationDateFrom datetime, @CreationDateTo datetime as

begin

      declare @CustomerID int, @CustomerName varchar(48), @CreationDate datetime

      select @CustomerName = c.CustomerName, @CreationDate = c.CreationDate from Customers c

            where c.CreationDate between @CreationDateFrom and @CreationDateTo

            order by c.CustomerName

      option (maxdop 1, recompile)

      end

go

Let’s execute the stored procedure initially with 1 month date range and then with 6 month date range.

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-01-30'

exec CustomersByCreationDate '2001-01-01', '2001-06-30'

go

The stored procedure took 48ms and 291 ms in line with previous optimal execution times.

The stored procedure with 1 month date range has good estimation like before.

The stored procedure with 6 month date range also has good estimation and memory grant like before because the query was recompiled with current set of parameter values.

The compilation time and compilation CPU of 1 ms is not expensive in this case compared to the performance benefit.

Let’s recreate the stored procedure with optimize for hint of 6 month date range.

--Example provided by www.sqlworkshops.com

drop proc CustomersByCreationDate

go

create proc CustomersByCreationDate @CreationDateFrom datetime, @CreationDateTo datetime as

begin

      declare @CustomerID int, @CustomerName varchar(48), @CreationDate datetime

      select @CustomerName = c.CustomerName, @CreationDate = c.CreationDate from Customers c

            where c.CreationDate between @CreationDateFrom and @CreationDateTo

            order by c.CustomerName

      option (maxdop 1, optimize for (@CreationDateFrom = '2001-01-01', @CreationDateTo ='2001-06-30'))

      end

go

Let’s execute the stored procedure initially with 1 month date range and then with 6 month date range.

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-01-30'

exec CustomersByCreationDate '2001-01-01', '2001-06-30'

go

The stored procedure took 48ms and 291 ms in line with previous optimal execution times.

The stored procedure with 1 month date range has overestimation of rows and memory. This is because we provided hint to optimize for 6 months of data.

The stored procedure with 6 month date range has good estimation and memory grant because we provided hint to optimize for 6 months of data.

Let’s execute the stored procedure with 12 month date range using the currently cashed plan for 6 month date range.

--Example provided by www.sqlworkshops.com

exec CustomersByCreationDate '2001-01-01', '2001-12-31'

go

The stored procedure took 1138 ms to complete.

2592000 rows were estimated based on optimize for hint value for 6 month date range. Actual number of rows is 524160 due to 12 month date range.

The stored procedure was granted enough memory to sort 6 month date range and not 12 month date range, so there will be spill over tempdb.

There was Sort Warnings in SQL Profiler.

As we see above, optimize for hint cannot guarantee enough memory and optimal performance compared to recompile hint.

This article covers underestimation / overestimation of memory for Sort. Plan Caching and Query Memory Part II covers underestimation / overestimation for Hash Match operation. It is important to note that underestimation of memory for Sort and Hash Match operations lead to spill over tempdb and hence negatively impact performance. Overestimation of memory affects the memory needs of other concurrently executing queries. In addition, it is important to note, with Hash Match operations, overestimation of memory can actually lead to poor performance.

Summary: Cached plan might lead to underestimation or overestimation of memory because the memory is estimated based on first set of execution parameters. It is recommended not to cache the plan if the amount of memory required to execute the stored procedure has a wide range of possibilities. One can mitigate this by using recompile hint, but that will lead to compilation overhead. However, in most cases it might be ok to pay for compilation rather than spilling sort over tempdb which could be very expensive compared to compilation cost. The other possibility is to use optimize for hint, but in case one sorts more data than hinted by optimize for hint, this will still lead to spill. On the other side there is also the possibility of overestimation leading to unnecessary memory issues for other concurrently executing queries. In case of Hash Match operations, this overestimation of memory might lead to poor performance. When the values used in optimize for hint are archived from the database, the estimation will be wrong leading to worst performance, so one has to exercise caution before using optimize for hint, recompile hint is better in this case.

해당 기능은 데이터베이스 이동을 쉽게 해주고 독립적인 데이터베이스 사용을 가능하게 해주는 것을 목표로 하고 있다. 그러나 현재까지는 부분적인 Contained Database 모드만 지원하며, 아직은 실험적이며, Replication, CDC, Database mail, Service Broker 등 주요한 기능을 지원하지 않고 Job Agent 도 지원하지 않아 2011 정식 릴리즈가 나올때도 이 상황이라면 실제 사용에는 무리가 있어 보인다. 하지만, 향 후 메이저 릴리즈를 몇번 거치면 분명 훌륭한 솔루션으로 완성될 것이다. (개인적으로는 소규모 데이터베이스를 위한 기능 인듯 한데... 이런데 좀 그만 투자하고 힌트 좀 더 만들고 좀 더 가볍게 돌고, TCO 관점에서 메리트 있는 프러덕트를 만들었으면 한다. SSMS 도 좀 고치고......) 


원본 아티클

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/16/sql-server-v-next-denali-contained-databases.aspx

번역본 (by 김민석)

Aaron Bertrand

Aaron 은 SQL Sentry 의 시니어 컨설턴트이다. SQL Server, Analysis Services 와 Windows 의 성능 모니터링 과 이벤트 관리 소프트웨어 개발자 이다. 관리, 성능, 신 기능에 관심을 두고 2006년 부터 sqlblog.com 에서 블로깅 하고 있다. 1997년 부터 SQL Server MVP 이다. 트위터 아이디는 @AaronBertrand 이며, SQL Saturday 와 유저 그룹에서 종종 스피커로 활동한다.

SQL Server V.Next (Denali) : Contained Databases

SQL Server 데이터베이스가 포터블하지 않은 것은 오랫동안 골치꺼리 였다. 문론 백업 리스토어나 디테치 어테치 등의 방법으로 다른 위치에 이동은 가능하다. 그러나 그러한 작업을 할 때, 어플리케이션의 한 부분인 데이터베이스의 많은 부분이 누락된다. 그리고 많은 부분들은 어플리케이션 파트가 아니라 관리영역이다. 그리고 데이터베이스 밖에 있는 부분들에 대해 동기화 하지 못한다. 예를 들자면, Security, roles, linked servers, CLR, Database mail, service broker, replication, Agent job. 또한 대상 서버가 다른 정렬을 사용하면, 임시테이블을 사용하거나 다른 데이터베이스과 join 할 때 문제가 생긴다. 초기 데이터베이스 이관 후, 호환되지 않는 많은 오류 이슈를 알고 있을것이라 생각된다.

"Contained Databases" 속으로~

Denali 에서는 몇 몇 이슈가 Contained database 로 소개 되었다. 정식 버전이 나올때 이것이 마켓팅 용어가 될지 확신하지 못하겠지만, 한번 살펴보자. (DMF) 동적 관리 프레임워크에서 (PBM) 정책 기반 관리로 바꿀 때 처럼 DDL 이 내장된 이후는 그 이름을 더 고치기 힘들다. 기본적으로 Contained database 는 해당 데이터베이스를 좀 더 black box 화 하고, 서버나 인스턴스 수준의 관리에서 어플리케이션과 특정 데이터베이스로 기능으로 분리시킨다.

Denali 첫번째 버전에서 Contained database 기능은 다음과 같은 솔루션을 제공한다.

* login 없이 특정 데이터베이스 user를 만들 수 있다. (그리고 다른 데이터베이스를 위해 같은 이름으로 이런 다중 user 를 만들수 있다)
* 정렬이 다른 데이터베이스 환경에서 tempdb 호환성(완벽하지 않음)을 유지할 수 있다. 왜냐하면, 임시테이블은 데이터베이스 context 정렬로 만들어 지기 때문이다.
* DMV 를 통해 Contained database (Containment) 에 위협을 가하는 (호환되지 않는) 모든 개체와 코드를 볼 수 있다.
* XEvents 로 동작중 일때만 알 수 있는것도 인식 할 수 있다. (김민석 추가)

개체와 엔티티들은 두개의 다른 카테고리로 나눌 수 있다.
Contained 와 UnContained 로 나눌 수 있다. Containted 엔티티는 외부 의존이 없는 것 또는 적어도 서버나 인스턴스간 의존성이 없는 것이다. Contained 개체의 예는 데이터베이스 내에서 외부 참조가 없는 것이다. 전체 리스트는 BOL의 다음 토픽을 참고 한다.

Features Within the Application Model

Uncontained 오브젝트는 명시적으로 외부 의존성이 있거나 (Three Four part names) 나 판단할 수 없는것 (Dynamic SQL) 이다. 명시적으로 uncontained 로 고려되는 것은 다음 bol 토픽을 참고한다.

Features Outside of the Application Model

몇몇 것들은 빠져 있다. 예를 들자면 HOST_NAME 이 목록에 없다. (이것은 Connect 에 리포트 되었다) 마지막으로, containment 를 수용하기 위해 변경된 것은 bol 의 다음 토픽을 참고한다.

Modified Features (Contained Databases)

가장 흥미로운것은 CREATE / ALTER DATABASE 변경이다. 이들은 Contained Database 에서는 다르게 동작한다. 새로운 옵션인 CURRENT 가 포함되었다. 만약 해당 데이터베이스를 새로운 인스턴스로 옮기거나 데이터베이스 이름을 바꿀때 이 명령이 사용된다. 명령어 설명 문서에 contained database 에서는 반드시 ALTER DATABASE CURRENT 를 사용하고 uncontainted database 에서는 ALTER DATABASE <database name> 을 사용하라고 되어있다. 하지만, 현재 CTP 판에서는 적용되어 있지 않다. 다른 흥미있는 토픽은 Collation Type (정렬), 임시 테이블의 제한사항, user option 이 있다.

Contained database 로 변경하기 (Turning containment on)

Denali 에서는 부분적 Containment 만 지원한다. 이말은 uncontained 엔티티를 만들 수 있다는 것이다. 그리고 이것을 DMV 를 통해 구분 할 수 있다. 하지만 엔진은 이러한 것들을 막지 못한다. 다음 버전의 SQL Server 에서는 완전한 Contained database 를 지원할 것이다. 이말은 containment 를 강제화 할 수 없다는 말이다.

정렬이 다른 여러 데이터베이스를 만들고 이런 기능을 사용해 보자. 주의할 것은 서버레벨 Configuration 옵션을 변경해야 이러한 Contanment DDL 을 사용할 수 있다.

로그인 없이 유저 만들기

이제 로그인에 묶이지 않은 유저를 만들 수 있다. 문법은 CREATE LOGIN 과 유사하다. password 호환성 default_schema default_language 등 대부분의 기능을 사용할 수 있다. 여기 각각의 데이터베이스별 유저를 만든다. 이름은 동일하게 MyUser 이고 암호는 다르다.

uncontainted database 에서 위 명령을 실행하면 다음과 같은 에러가 발생한다.

Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.

자신에게 물어봐라, 두명의 유저가 같은 이름이라면 SQL Server 가 어떻게 니가 원하는 유저를 구분하겠는가? 유저는 이제 database 레벨에서 먼저 인증된다. (접속 문자열에 데이터베이스 이름이 명시되어 있다.) 그리고 만약 해당 database 에 유저를 찾지 못하면, 같은 이름의 로그인을 찾는다. 이는 성능 관점에서 고려되었으며, 반대로는 하지 않는다 -> 로그인을 찾고, 실패하면 각각의 모든 데이터베이스에서 같은 이름의 유저가 있는지 찾는것. 데이터베이스가 명시되어 있지 않으면 인증은 바로 실패한다. 그래서 만약 정확한 contained database name 유저와 database context 를 기술하지 않으면, 암호가 일치해도 인증은 실패한다. 이말은 default database 에 의존하지 말라는 것이다. (이러한 것은 Contained database 에는 없다.) 접속 문자열에 "Initial Catalog" 속성을 명시적으로 적어야 한다.

Contained database 에 "SQL user with password" 라는 새로운 속성 페이지를 볼 수 있다.

큰 차이점은 이제 이러한 데이터베이스를 다른 서버로 옮길때 단지 접속 문자열만 다시 가르키면 된다. 로그인 이나 SID 매칭 작업은 필요 없다. 그리고 Windows principals 도 가능하다. 그러나 이것은 별로 흥미롭지 않다. 왜냐하면, 이들 데이터베이스는 로그인에 종속적이지 않기 때문이다.

하지만, contained database 에서 주의할 것은 Windows principals 는 자동으로 엔진과 현재 데이터베이스에 인증된다. 반면 uncontained database 에서는 관련된 로그인이나 Windows group 을 통해 인증 된다. 더 많은 정보는 업데이트된 CREATE USER 구문과, BOL 토픽을 살펴봐라. 그리고 새로운 시스템 저장 프로시저인 sys.sp_migrate_user_to_contained 는 기존의 SQL 인증 유저를 암호와 함께 contained 데이터베이스 유저로의 마이그레이션을 돕는다.

다음을 주의해라. 데이터베이스를 암호 복잡성 룰이 다른 새 서버로 옮길때 로그인을 새로 만들거나 암호를 고치지 않는한 검사하지 않는다. 그래서 복잡한 암호 사용이 강제화된 서버에서 약한 암호가 사용될 수 있다.

보안은 큰 이슈이고, Contained 데이터베이스에서는 로그인과 유저가 어떻게 동시에 존재할 수 있고 없음을 대충 훓터보았다. 흥미로운 시나리오가 있으면 알려달라. 그리고 다음 BOL 토픽을 살펴 봐라.  "Threats Against Contained Databases."  또  하나의 흥미로운 토픽은 AUTO_CLOSE 이다. contained databases 와 AUTO_CLOSE 는 서비스 중단 공격이 되기도 한다. 왜냐하면, Contained database 유저 인증에 더많은 리소스가 필요하기 때문이다.

Tempdb 정렬 이슈 해결 (Resolving tempdb collation issues)

이 코드는 Denali 이전 버전이나 uncontained 데이터베이스에서는 흥미로운 점이 없다. (서버는 Finnish_Swedish_100_CI_AI 과 다른 정렬을 사용한다.)

이 조인 조건은 다음과 같은 에러를 발생시킨다. 왜냐하면, 임시테이블은 데이터베이스 기본 정렬이 아니라, 서버 기본 정렬로 생성되기 때문이다.

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_100_CI_AI" in the equal to operation.

Denali Contained database 에서는 임시 오브젝트가 해당 데이터베이스 정렬로 만들어진다. 그래서 Contained 데이터베이스를 만들고 위 코드를 다시 실행하면 잘 동작한다.

문론 이것이 모든 이런 이슈를 해결하지는 않는다. 여전히 다른 정렬에서는 에러가 발생할 수 있다. 그리고 일반적인 정렬이슈를 말하는 것이 아니다. 단지 많은 문제에 노출되지 않는다는 것을 말한다. 그래서 정렬이 민감한 환경에서는 문제 해결에 더 많은 시간을 보내야 한다. 하지만 사용하는 T-SQL 쿼리가 완전히 Contained 되고 정렬이슈 영향이 적다면 더 쉽게 데이터베이스를 옮길 수 있다.

데이터베이스 이식성 조사 (Discovering threats to database portability)

심지어 containemt 기능을 사용하지 않더라도, 이식 대상이 되는 데이터베이스에서 어떤 부분이 이식성에 문제가 있는지 알아내는 것은 중요하다. (contained 로 알려진) 그래서, sys.dm_db_uncontained_entities 라는 DMV 가 이것을 도와준다. objects 뿐만 아니라 프로시저 평선 뷰 트리거도 구분할 수 있다. 같은 모듈에서 여러 위반사항을 확인 할 수 있다. Contained 데이터베이스에서 아래 여러 오브젝트를 생성하고 containment 에 어떤 영향을 주는지 살펴보자.

나는 해당 DMV 를 통해 문제를 어떻게 해결하지는 보여주기 위해 아래 쿼리를 실행했다.

결과

dbo.foo 저장프로시저가 DMV 에 의해서 3번 호출된것을 볼 수 있다. 2번은 동적 SQL 에 의해서, 또 한번은 존재하지 않는 개체 때문이다. (이것이 갑자기 만들어 졌을때 uncontained 개체가 될 수 도 있기 때문에 출력되었다.) 라인 넘버도 매우 유용하다. 그래서 해당 개체 정의를 확인 할 때, 검색하지 않고 바로 그 라인으로 가 볼 수 있다.

이 DMV 가 SYNONYM 을 잡아내지 못했음에 주의 해라. 해당 SYNONYM 은 3부분으로 외부 참조를 한다. 그러므로 SYNONYM 을 모두 찾아 조사해야 한다. 이 건은 Connect #622368 로 보고 되었으며 향 후 고쳐지길 바란다.

그리고 table 이 function 을 사용할 때 function 내부에서 uncontained 참조가 있어도 잡아내지 못한다. 이 건은 Connect 에 올리지 않았다. 이건 잘 일어나지 않는 이슈이기 때문이다. 그래도 알고 있어야 한다.

마지막으로, AutoCreatedLocal 이 뭔지 잘 모르겠다. 
(Finally, I am not sure exactly what you can do about the AutoCreatedLocal route; this is the first time I've ever noticed this entity)

해당 기능은 Service Broker 기능으로 잘 모를수도 ...(김민석)

DMV 에서 Assembly , Type, Full-text Index, Database DDL Trigger 와 Audit Specification  등 다른 엔티티도 다루는데 여기서 다루지 않았다. 등이다.  하지만 만약 이러한 아이템을 사용한다면, 같이 살펴봐야 한다.

결론

버전을 올리면서 분명 다른 많은 방면에서 작업 해 연결서버나 데이터베이스 레벨의 에이전트 작업 같은것들도 되게 할것이며 이는 매우 흥미로울 것이다. 나는 이들의 연구 수준을 느낄 수 있었고, 첫번째 증인이 될것이다. 앞으로의 메이저 버전이 나올때 마다 해당기능의 발전을 살펴볼 것이다.

For more information on contained databases, there is a great Books Online topic in the works: