Category: SQL Server

최지환 (데브피아)

데이터 베이스 디자인을 정규화하라.

논리적 데이터 베이스 디자인을 합리적으로 정규화하는 것이 최대의 성능 향상을 가져 온다는 것은 현업의 수많은 사례들이
충분히 증명한다. 필드의 갯수가 많은 커다란 테이블들을 단지 몇 개만 사용하여 만든 데이터 베이스들이 정규화되지 못한 데이터
베이스 디자인의 대표적인 보기이다.

고도로 정규화된 데이터 베이스들은 일반적으로 복잡한 관계적 Join으로 맺어져 있는데, 이 또한 성능을 잠식할 수 있다.
그러나, SQL 서버의 최적화 모듈은 효과적인 인덱스를 사용할 수 있는 한 매우 효과적으로 빠르고 효과적인 Join을 선택하는
데 효율적으로 작동한다. 정규화에 따른 효과들은 다음과 같다.

– 테이블들이 좁기 때문에 정렬과 인덱스 제작을 가속화한다.
– 더 많은 테이블들이 존재하기 때문에 더 많은 Clustered index를 만들 수 있다.
– 인덱스들이 보다 한정되고 집적된다.
– 테이블들을 물리적으로 배치하는 것을 조정하기 위해 세그먼트들을 보다 더 효과적으로 사용할 수 있게 한다.
– 하나의 테이블에 딸린 인덱스의 숫자가 적어짐으로써 UPDATE의 성능 향상을 가져온다.
– NULL과 중복된 데이터가 더 적어지기때문에 데이터 베이스의 집적도가 향상된다.

SQL 서버에서의 정규화는 종종 성능을 저하시키기보다는 오히려 향상시키는 결과를 가져온다. 정규화가 증가함에 따라 데이터를
얻어 내는 데 필요한 조인(join)의 숫자와 복잡도도 함께 증가한다. 많은 퀘리가 4방향 이 넘는 조인을 필요로 할 정도가
아니라면 정규화를 계속 진행하는 것이 바람직하다.

만일 논리적인 데이터베이스의 구성이 이미 고착되었고 전체적인 재설계가 그리 용이하지 않다면, 이런 경우에 병목의 주 요인이 되는 커다란 테이블의 정체가 확인되었을 경우 선택적으로 이들을 정규화하는 것도 가능하다.

만일 주어진 데이터베이스에 대한 접근이 Stored Procedure에 의해 수행된다 해도, 이러한 스키마의 변화는 응용
프로그램에 영향을 주지 않고서도 가능하다. 설령 그렇지 못한 상황이라 하더라도, 나뉘어진 테이블들을 마치 하나의 큰 테이블처럼
보이게 하는 새로운 뷰(view)를 만들어서 이러한 변화를 숨기는 것도 가능하다.

효율적인 인덱스 디자인을 하라.

많은 비관계형 시스템들과는 달리, 관계형 인덱스는 논리적 데이터베이스 설계의 부분으로 간주되지 않는다. 인덱스는
데이터베이스 스키마나 응용 프로그램 설계에 성능을 제외한 어떤 영향도 미치지 않고서도 제거할 수도 있고, 더하거나 변경할 수도
있다. 이러한 이유들로 인해, 여러 가지 다른 방식으로 인덱스를 구성해서 시험해 보는 데에 지체를 해서는 안된다.

현장에서의 사례들을 보면 대부분의 경우에 최적화기가 가장 효과적인 인덱스를 신뢰성 있게 선택한다. 전반적인 전략은 좋은
인덱스를 선택할 수 있는 상황을 최적화기에게 제공하는 것이고, 최적화기가 올바른 선택을 하도록 맏기는 것이다. 이렇게 하면 분석
시간을 줄일 수 있으며, 폭 넓고 다양한 상황에서 좋은 성능을 얻을 수 있다.

다음에 이어지는 내용은 인덱스를 구성할 때 생각해야 할 내용들이다. 우선 SQL 퀘리 문장에서 WHERE 문이 나오는
부분을 분석해야 하는데, 왜냐하면 바로 이 부분이 최적화기가 우선적으로 주목하는 촛점이기 때문이다. WHERE문에 나오는 각각의
컬럼은 인덱싱의 후보가 된다. 분석해야 할 퀘리가 너무 많다면, 대표적인 것들이나 혹은 느린 것들만 분석해도 된다.

만일 개발 툴이 자동적으로 SQL 문을 생성해 낸다면, 이 일은 보다 더 어려워질 수 있다. 대부분의 이러한 툴들은 생성된
SQL 구문을 디버깅을 위해 파일이나 스크린에 기록해준다. 이러한 기능이 제공되는 지를 공급자에게 문의해 보는 것이 바람직하다.

인덱스를 좁게 구성하라. 좁은 인덱스들은 종종 다중 컬럼의 복잡한 인덱스들보다 더욱 효과적이다. 좁은 인덱스는 페이지당
행이 더 많으며, 인덱스 레벨이 낮기때문에 성능 향상을 촉발한다. SQL 서버의 최적화기는 복잡한 인덱스를 만나면 단지 가장
우선적인 컬럼에 대한 통계만을 유지할 따름이다. 따라서, 복잡한 인덱스의 첫번째 컬럼이 그리 신통한 선택이 아니라면, 최적화기는
그 인덱스를 사용하지 않을 수도 있다.

최적화기는 수백, 혹은 심지어는 수천개의 인덱스와 조인의 가능성들을 효과적이고 신속하게 분석할 수 있다. 좁은 인덱스를
사용함으로써 더 많은 인덱스가 만들어지면 최적화기의 선택의 폭이 넓어짐과 동시에 이로 인해 일반적으로 성능이 보다 향상된다.
여러 컬럼에 걸친 폭이 넓은 인덱스를 단지 몇 개만 사용하는 경우에는 이와 반대로 최적화기의 선택의 폭이 좁아져서 성능의 저하를
가져 올 수 있다.

그렇다고 해서 적정한 읽기 성능을 얻는 데 필요한 인덱스의 숫자보다 더 많은 인덱스를 생성해서는 안되는데, 이는 이러한
인덱스들을 갱신하는 데 요구되는 부하 때문이다. 그러나, 심지어는 갱신이 가장 빈번하게 일어나는 작업들조차도 쓰기보다는 읽기를
훨씬 더 많이 요구한다. 따라서, 어떤 새로운 인덱스가 도움이 될 것이라고 생각되면 이를 시험해 보는 것을 주저해서는 안된다.
바라는 결과가 나오지 않을 경우에는 추후 언제라도 삭제를 할 수 있기 때문이다.

Clustered Index를 사용하라. Clustered Index를 적절히 사용하는 것 하나만으로도 대단한 성능 향상을
가져올 있다. UPDATE와 DELETE 작업조차도 종종 clustered index에 의해 속도가 빨라진다. 왜냐하면 이
작업들은 우선 많이 읽어야 하기 때문이다. 한 테이블당 clustered index는 하나밖에 있을 수 없기 때문에, 이를
사용할 때는 매우 현명하게 생각해야 한다.

수많은 행을 돌려주는 퀘리나 또는 어떤 영역에 걸친 값들과 관계된 퀘리들은 clustered index를 사용하여 속도를 개선할 수 있는 좋은 후보가 된다. 예를 들자면 다음과 같은 것들이다.

SELECT * FROM PHONEBOOK WHERE LASTNAME=’SMITH’, 또는

SELECT * FROM MEMBERTABLE WHERE MEMBER_NO > 5000 AND MEMBER_NO < 6000

두 경우를 비교해서 말한다면, 위에 언급된 LASTNAME 이나 MEMBER_NO의 행들은 non-clustered
index를 사용하기엔 바람직하지 않다. 결과가 몇 행 밖에 안되는 상황에서는 non-clustered index를 사용하도록
노력해야 한다.

행 유일성을 조사하라. 이렇게 하면 어떤 행이 clustered index의 후보로서 알맞은 지, 어떤 행이
non-clustered index의 후보로서 알맞은 지, 혹은 아예 인덱싱을 하지 않는 것이 좋은 지를 결정하는 데 도움이
된다. 행 유일성을 조사해야 할 예제 퀘리로는 다음과 같은 것이 있다.

SELECT COUNT (DISTINCT COLNAME) FROM TABLENAME.

이 퀘리는 컬럼 내의 유일한 값들의 숫자를 보여 준다. 이 숫자를 테이블 내의 행 전체의 갯수와 비교를 해 본다.
10,000개의 행이 있는 테이블에서 5000개 정도의 유일한 값이 있는 행은 non-clustered index를 하기에
적당하다 하겠다. 같은 테이블에서, 20개 정도의 유일한 값이 존재하는 행은 clustered index로 구성하는 데 더욱
적합할 것이다.

3개의 유일한 값이 나온다면 인덱스를 아예 하지 않는 것이 좋다. 이는 단지 보기일 따름이며, 결코 엄격하고 고정된 규칙은 아니다. 검색문의 WHERE 구문에 나타난 개개의 열들을 인덱싱 하는 것을 잊지 말아야 한다.

검색으로 나타나는 행의 갯수 또한 인덱스 선택의 중요한 요소이다. 최적화기가 볼 때, non-clustered
index에서는 한 행이 리턴될 때마다 적어도 한 페이지의 I/O가 소요된다. 이러한 비율로는, 머지 않아 차라리 그냥 전체
테이블을 스캔하는 것이 더욱 더 효과적이 된다. 이것은 결과 집합의 크기를 제한하거나 혹은 큰 결과를 clustered
index로써 잡아 내는 또 다른 이유가 된다.

성능 저하를 분석하는 방법들

우선 느린 쿼리를 찾아내도록 한다. 단지 몇 개의 SQL 쿼리가 느린 경우에도 종종 전체 응용 프로그램이 느린 것처럼 보일
때가 있다. SQL 구문을 자동으로 생성해 주는 개발툴을 사용하는 경우에는, 이 툴에서 가능한 모든 진단 모드나 디버깅 모드를
사용하여 생성된 SQL 구문을 잡아 내도록 한다. SQL 구문을 삽입해 주는 개발도구를 사용하는 경우에는 이 작업은 훨씬 쉽다.
느린 쿼리가 무엇인 지를 파악한 후에는 다음의 과정들을 따르면 된다.

– ISQL이나 SAF 등의 쿼리 툴을 사용하여 느리다고 의심이 가는 쿼리를 따로 떼어서 실행해 본 다음 실제로 느린 지를 확인한다.
– SET STATISTICS IO ON과 SET SHOWPLAN ON을 사용하여 쿼리에 의해 소비되는 I/O와 선택된
Query plan을 검사한다. 논리적 페이지 I/O의 숫자에 주목한다. 논리적 I/O 숫자를 기록한다. 이는 성능 향상을
측정할 비교 수치가 된다.
– 만일 쿼리가 뷰나 stored procedure를 포함하고 있으면, 쿼리를 따로 추출하여 독립적으로 실행한다. 이렇게 하면 서로 다른 인덱스를 사용할 때 접근 계획을 바꿀 수 있도록 해 준다.
– 관계되는 테이블에 트리거가 실행되면서 자동적으로 I/O를 생성하는 트리거가 뷰가 있는 지를 확인한다.
– 느린 쿼리에 의해 사용되는 테이블의 인덱스들을 조사한다. 위에 나열된 방법들을 사용하여 이들이 좋은 인덱스인 지를 검사한다. 그리고 필요하면 변화를 준다.
– 인덱스에 변화를 준 다음 쿼리를 다시 실행하여 I/O 숫자나 접근 계획에 변화가 있는 지를 관찰한다.
– 성능이 향상된 것을 확인하였으면, 주 응용 프로그램을 실행하여 전체적인 성능이 향상되었는 지를 확인한다.

프로그램이 I/O나 CPU 한계치에 도달해서 동작하는 지를 관찰한다. 일반적으로 쿼리가 I/O나 CPU에 의해 성능이
제한되는 지를 조사하는 것은 많은 도움을 준다. 이러한 촛점은 진정한 병목이 일어나는 부분에 대해 노력을 집중할 수 있도록 해
준다. 예를 들어, 어떤 쿼리가 CPU에 의해 성능의 제약을 받으면, SQL 서버에 메모리를 더 설치한다고 해서 성능의 향상을
기대할 수는 없는데, 왜냐하면 메모리를 더 많이 설치할 경우에는 단지 캐시 적중률만 높여주며, 이는 이 경우 이미 충분히 높기
때문이다. SQL 서버가 I/O에 의해 제약을 받는 지, 아니면 CPU에 의해 제약을 받는 지를 분석하는 과정은 다음과 같다.

– OS/2 CPU monitoring program을 사용한다. CompuServe IBMOS2 forum에서 여러 가지를 구할 수 있다.
– 쿼리가 수행되는 동안 CPU 그래프가 계속 높은 상태를 유지한다면(예를 들어, >70%) 이는 CPU에 의해 제약을 받는 상태이다.
– 쿼리가 수행되는 동안 CPU 그래프가 계속 낮은 상태를 유지한다면(예를 들어, <50%) 이는I/O 병목 상태이다.
– CPU 그래프를 STATISTICS IO의 정보와 비교해 본다.