MS SQL Server 성능 조정 및 최적화, 제 1부: 성능 문제 개요

Reference URL :

http://www.microsoft.com/korea/TechNet/sql/Technote/dat410ef.asp?bPrint=True

개발자를 위한 MS SQL Server 성능 조정 및 최적화, 제 1부: 성능 문제 개요

DAT 410
Tech-Ed 97에 소개됨

Adam Shapiro
Program Manager
Microsoft Corporation

성능 조정의 목적

성능 조정의 목적은 네트워크 소통량과 CPU 시간을 최소화하고 디스크 I/O를 줄여 쿼리마다 적절한 응답 시간을 제공함으로써 모든 사용자의 프로세스에 대해 최대 처리량을 제공하는 것입니다. 이 목적을 달성하기 위해 응용 프로그램 요구 사항을 철저하게 분석하고, 데이터의 논리적 구조와 실제 구조를 파악하고, 온라인 트랜잭션 처리(OLTP)와 의사 결정 지원 서비스처럼 서로 충돌하는 데이터베이스 사용에 대해 그 기회 비용을 평가하고 협상합니다.

응답 시간 대 처리량

응답 시간이란 결과 집합의 첫째 행을 반환하는 데 필요한 시간을 말합니다. 일반적으로 응답 시간은 쿼리가 처리되고 있다는 것을 사용자가 가시적으로 확인받을 때까지의 시간입니다.

처리량이란 주어진 시간 동안 서버가 처리할 수 있는 전체 쿼리 수를 말합니다.

사용자 수가 늘어나면 사용자 간의 경쟁이 커지며 이 때문에 응답 시간이 느려지고 전체적인 처리량이 줄어듭니다.

성능 측정 방법

성능은 트랜잭션을 처리하는 데 필요한 I/O 양, CPU 시간 및 응답 시간을 사용하여 측정할 수 있습니다. 성능은 각각의 환경에 따라 그리고 응용 프로그램, 아키텍처 및 리소스, 서버, 동시 작업 등에 따라 달라집니다.

성능에 영향을 미치는 요소

시스템 리소스

메모리

SQL Server의 성능을 높이려면 RAM이 충분히 있어야 합니다.

프로세서

프로세서의 개수와 속도는 전체적인 성능에 직접적인 영향을 미칩니다.

디스크

디스크 드라이브의 개수, 속도, 종류를 비롯하여 사용하는 컨트롤러 종류도 성능에 영향을 미칩니다.

네트워크

동시에 이뤄지는 네트워크 작업이 SQL Server의 성능에 영향을 미칠 수 있습니다. 네트워크 대역폭과 데이터 전송률도 중요합니다.

Windows NT 운영 체제

스레드

스레드 우선 순위를 조정하면 SQL Server가 자신의 요구 사항을 다른 서비스의 요구 사항 그리고 Microsoft Windows NT® 운영 체제 자체의 요구 사항과 조화시킬 수 있습니다. SQL Server가 할당하는 스레드 수가 성능에 영향을 미칠 수 있습니다.

페이징 파일

페이징 파일의 개수, 크기, 위치가 시스템 성능에 큰 영향을 미칠 수 있습니다.

서비스

Windows NT에서 실행되는 다른 서비스들과 SQL Server는 서로 리소스를 사용하기 위해 경쟁합니다. 따라서 불필요한 서비스를 종료하면 성능이 향상될 수 있습니다.

디스크 관리

Windows NT는 성능에 영향을 미칠 수 있는 스트라이프, 미러링 등 많은 디스크 관리 기능을 제공합니다. 이들이 항상 성능을 향상시키는 것은 아닙니다.

동시 작업

클라이언트 프로그램이나 컴파일러 등에서 수행하는 다른 작업들과 SQL Server는 서로 CPU 주기, 디스크 액세스 및 네트워크 대역폭을 사용하기 위해 경쟁할 수 있습니다.

SQL Server

구성

많은 SQL Server 구성 변수가 서버 성능에 직접적인 영향을 미칠 수 있습니다.

잠금

데이터베이스 리소스(테이블 및 개별 페이지)를 사용하려는 경합 때문에 프로세스가 차단될 수 있고 다중 사용자 시스템의 전체 성능이 큰 영향을 받을 수 있습니다.

로깅

로그에 기록되지 않는 특정 동작을 제외하고 모든 데이터베이스에 대한 수정 사항은 로그에 기록되어야 합니다. 로그 쓰기 자체가 성능에 영향을 미칠 수 있으며 트랜잭션 로그(syslogs)가 경합의 진원지가 될 수 있습니다.

동시 작업

백업 및 복원, DBCC, 인덱스 구축 같은 유지 관리 작업이 시스템 동작을 방해할 수 있습니다.

데이터베이스 응용 프로그램

논리 설계 및 실제 설계

정규화 수준 및 비정규화 수준이 쿼리 성능에 영향을 미칠 수 있습니다. 실제 설계는 인덱스 선택을 포함하며 아주 상세하게 정의됩니다.

교착 상태 방지

반복적인 교착 상태 때문에 응용 프로그램 속도가 떨어질 수 있습니다. 교착 상태의 발생 가능성을 줄일 수 있는 프로그래밍 방법이 있습니다.

트랜잭션 제어

트랜잭션, 잠금 및 교착 상태는 서로 아주 긴밀하게 관련되어 있습니다. 응용 프로그램이 사용하는 트랜잭션 제어 수준이 잠금 지속 시간 및 응용 프로그램의 전체 처리량에 큰 영향을 미칠 수 있습니다.

쿼리

저장 프로시저에서 개별 쿼리를 캡슐화하는지 여부를 포함하여 개별 쿼리를 쓰는 방식에 따라 최적의 성능을 제공하는 최적의 계획이 사용되느냐 그렇지 않느냐가 결정될 수 있습니다.

클라이언트 응용 프로그램

사용자 요구 사항

필요한 수정 사항 및 실행할 쿼리에 대한 사용자 요구 사항이 응용 프로그램의 성능에 큰 영향을 미칠 수 있습니다.

교착 상태 처리

클라이언트 프로그램은 다양한 방식으로 SQL Server 교착 상태에 응답할 수 있습니다. 가장 효율적인 응답 방식을 사용하면 클라이언트 시스템의 성능을 크게 향상시킬 수 있습니다.

트랜잭션 제어

클라이언트 응용 프로그램에서도 트랜잭션을 제어할 수 있습니다. 또한 일부 클라이언트 응용 프로그램은 프로그래머나 사용자가 인식하지 못하는 상태에서 트랜잭션 제어 문을 발급할 수 있습니다.

커서

다양한 방법을 사용하여 커서를 정의하고 조작할 수 있으며 각 방법마다 성능에 미치는 영향이 다릅니다.

성능을 향상시키는 방법

성능 조정 작업은 과학보다 예술에 가깝습니다. 성능 조정의 목적은 I/O, CPU 또는 네트워크와 관련된 병목 현상을 제거하여 성능을 향상시키는 것입니다. 이를 위해 서버 조정, 데이터베이스 조정, 프로세스 조정, 데이터에 대한 경합 최소화 등의 방법을 사용하여 시스템 처리 시간을 줄일 수 있습니다.

하드웨어 추가

• 보통 이 방법보다 비용이 적게 드는 방법이 있겠지만 이 방법이 유용할 때도 있습니다.

SQL Server 조정

• 구성 옵션 값을 조정합니다.

데이터베이스 조정

• 논리 설계와 실제 설계를 개선합니다.
• 더 나은 쿼리를 씁니다.
• 유용한 인덱스를 만듭니다.

경합 및 동시 작업 문제 해결

• 읽기 작업과 쓰기 작업을 조화시킵니다.
• 잠금 경합을 최소화합니다.
• 교착 상태를 방지합니다.

클라이언트 응용 프로그램 조정

• 임의 쿼리 대신 저장 프로시저를 사용합니다.
• 트랜잭션 믹스를 분석하고 우선 순위를 지정합니다.
• 해당되는 경우 서버에서 데이터 및 프로세스를 오프로드합니다.

Windows NT의 이점

SQL Server는 Windows NT 운영 체제에 제공되는 향상된 기능을 이용합니다.

확장 가능한 아키텍처

SQL Server는 노트북 컴퓨터로부터 Intel® 및 RISC(간략화된 명령어 집합 컴퓨팅) 프로세서를 지원하는 대칭 다중 프로세서 슈퍼 서버에 이르기까지 확장할 수 있도록 하는 Windows NT의 확장성을 이용할 수 있습니다.

대용량

SQL Server는 Windows NT가 사용자 프로세스에 허용하는 최대 2GB의 메모리를 사용할 수 있습니다. 하드 디스크 파티션의 크기는 대략 170억 GB(NTFS 사용)까지 가능합니다.

SMP(대칭 다중 프로세싱)

Windows NT는 SMP가 가능한 운영 체제입니다. Windows NT는 사용 가능한 모든 프로세서에서 운영 체제 코드와 사용자 코드 모두를 실행할 수 있습니다. 실행할 스레드가 실행에 사용할 프로세서보다 많으면 SMP 운영 체제는 멀티태스킹을 수행하여 대기 중인 모든 스레드 간에 각 프로세서의 시간을 분할합니다.

SQL Server는 Windows NT의 멀티스레딩 기능을 이용합니다. SQL Server는 자체의 스레딩 엔진을 구현하는 대신 별도의 Windows NT 기반 스레드를 사용하여 각 클라이언트에 서비스를 제공합니다. Windows NT는 자동으로 프로세서 간의 스레드 로드를 조정하고 일정을 설정합니다.

SMP 컴퓨터에서는 SQL Server를 사용하여 모든 CPU 리소스를 SQL Server 전용으로 지정할 수 있습니다.

멀티스레드 단일 프로세스 아키텍처

SQL Server는 스레드 수준에서 SMP를 지원하며 아래와 같은 측면에서 Windows NT 스레딩의 혜택을 받습니다.

• 멀티스레드 단일 프로세스 아키텍처는 시스템 오버헤드와 메모리 요구 사항을 줄입니다.
• 멀티스레드 응용 프로그램은 단일 주소 공간을 사용합니다. 모든 스레드가 같은 프로세스에 속하기 때문에 공유 메모리 프로세스를 조정할 필요가 없습니다.

비동기 I/O

Windows NT는 비동기 I/O를 사용합니다. 비동기 I/O에서는 응용 프로그램이 I/O 요청을 발급한 다음 장치가 데이터를 전송하는 동안에도 계속 실행됩니다. 이는 I/O 요청이 완료될 때까지 응용 프로그램에 제어를 반환하지 않는 동기 I/O 시스템과 구별됩니다. SQL Server는 Windows NT의 비동기 I/O를 이용하여 처리량을 증가시킵니다.

Windows NT 서비스 사용

SQL Server는 스레딩, 일정 설정, 이벤트 알림, 프로세스 동기화, 비동기 I/O, 예외 처리, 통합 보안 등에 Windows NT 서비스를 사용합니다.

SQL Server는 아래와 같은 특징을 가집니다.

• SQL Server 오류 로그 외에 Windows NT 이벤트 로깅도 사용합니다.
• Windows NT의 자동 로드 조정을 이용합니다.
• Windows NT 성능 모니터와 완전히 통합되어 있습니다.
• Windows NT 보안을 사용하여 통합 로그인 및 암호를 제공할 수 있습니다.

성능 조정 방법

이 성능 조정 방법을 출발점으로 삼아 데이터베이스를 성공적으로 조정하여 성능을 향상시킬 수 있습니다. 또한 이 방법은 이 과정에 포함된 항목들의 기본 토대 역할도 합니다.

성능 조정 방법(계속)

데이터베이스 환경의 운용 단계에 따라 이 방법에 포함된 단계를 여기에 소개된 순서와 다르게 수행하거나 일부 단계를 생략할 수 있습니다.

성능 조정에 대한 접근 방법

두 가지 방식으로 성능 조정에 접근할 수 있습니다.

이 과정에서는 SQL Server가 어떻게 데이터에 액세스하고, 여러 사용자의 동시 작업을 제어하고, 운영 체제와 상호 작용하는지에 대해 설명합니다. 여기에서 얻은 지식을 토대로 최적의 성능을 얻을 수 있도록 논리 설계와 실제 설계를 계획하고, SQL Server를 구성하고, 트랜잭션 계획을 수립하고, 쿼리를 쓸 수 있습니다.

또는 특정 문제를 다룸으로써 성능 조정에 접근할 수도 있습니다. 예를 들어, 쿼리 실행 속도와 처리량이 필요한 것보다 낮을 수 있습니다. 그러면 SQL Server의 동작 방식에 대한 정보를 수집하고 쿼리와 시스템 구성을 필요에 맞게 조정하여 최적의 성능을 얻을 수 있습니다.

두 접근 방식이 모두 필요합니다. 서버, 사용자, 데이터, 프로세스 등에 대한 세부 지식은 있지만 성능에 대한 정보가 없다면 이론적으로 잘 설계된 응용 프로그램이 최적의 성능을 발휘하지 못하더라도 그 사실을 인식하지 못할 것입니다. 반대로, 성능 정보는 빠짐없이 가지고 있지만 응용 프로그램이나 서버에 대한 정보가 없다면 성능 문제를 인식할 수는 있어도 그 해결 방법을 찾지 못할 것입니다.

개요 – 인덱싱 전략

목표

• 다양한 쿼리 유형에 유용하게 사용되는 인덱스를 선택합니다.
• 의사 결정 지원 시스템(DSS)과 온라인 트랜잭션 처리(OLTP)에 대한 인덱싱 전략을 비교합니다.
• 효과적인 인덱스를 만듭니다.
• 선택도와 조인 밀도를 서로 구분합니다.
• 인덱스가 도움이 되지 않는 상황을 확인합니다.
• 인덱스의 유용성을 테스트합니다.

DSS 및 OLTP

대부분의 쿼리는 이 두 범주 중 하나에 속합니다. 이들의 인덱싱 문제가 크게 다를 수 있기 때문에 두 범주에 대해 따로 설명합니다.

의사 결정 지원 시스템

의사 결정 지원에는 대개 여러 검색 인수와 테이블이 관련됩니다. 쿼리는 집계, 그룹화, CUBE 및 ROLLUP 연산을 사용하는 매우 복잡한 쿼리가 될 수 있습니다. 이를 온라인 분석 처리(OLAP)라고도 합니다.

쿼리는 예측할 수 없는 임의적인 쿼리일 수 있으며 원하는 행을 지정하기 위해 거의 모든 열을 사용할 수 있습니다.

이런 종류의 쿼리에서는 가져오기 및 결과 반환 속도가 가장 중요한 요소입니다.

온라인 트랜잭션 처리(OLTP)

OLTP에는 주로 단일 테이블만 관련되며 대개 소수의 행만 영향을 받습니다. INSERT의 경우 OLTP 응용 프로그램 트랜잭션은 단일 행만 삽입할 것입니다.

의사 결정 지원에 비해 OLTP에서는 보통 쿼리의 예측 가능성이 높습니다.

OLTP 쿼리에서는 데이터 수정 속도가 가장 중요한 요소입니다.

가져오기를 위한 인덱싱

유용한 인덱스 만들기

성능을 향상시키기 위해 할 수 있는 가장 중요한 작업은 아마 유용한 인덱스를 만드는 것일 것입니다. 사용자의 요구 사항과 데이터 자체에 대한 철저한 이해를 바탕으로 인덱스의 종류와 수 그리고 인덱싱할 열을 신중하게 선택해야 합니다. 간단하게 테이블을 쿼리하든 데이터를 수정하든 인덱스는 유용합니다. 어떤 경우이든 인덱스를 사용하면 읽기 또는 쓰기를 위해 데이터에 더 빠르게 액세스할 수 있습니다.

사용자 분석

일반적으로 수행되는 쿼리의 종류와 빈도 그리고 데이터에 대한 사용자 요구 사항을 파악해야 합니다. 사용자 요구 사항을 철저하게 파악하면 어떤 부분에 중점을 둬야 할지 결정하는 데 도움이 됩니다. 중요한 쿼리들의 성능 균형을 조정할 때는 특정 쿼리의 성능을 높이기 위해 다른 쿼리의 처리 속도를 어느 정도 희생해야 할 것입니다.

데이터 분석

논리 설계와 실제 설계에서 데이터 자체는 물론 그 데이터의 구성 방식을 이해해야 합니다.

SQL Server의 작업 방식 이해

SQL Server의 작업 방식을 깊이 이해할수록 시스템을 더 효율적으로 설계할 수 있고 더 적합한 결정을 내릴 수 있습니다. SQL Server가 데이터를 어떻게 저장하고 가져오는지 그리고 쿼리 최적화 프로그램이 어떻게 가장 효율적인 실행 계획을 선택하는지에 대해 이해하는 것이 여기에 포함됩니다.

일반적인 고려 사항

• 쿼리 최적화 프로그램은 대개 쿼리마다 테이블 당 하나의 인덱스만 사용합니다.
• 대형 테이블에 대한 쿼리를 빠르게 실행하려면 WHERE 절에 포함된 열을 인덱싱하는 것이 좋습니다.
• 적절한 인덱스 수를 결정하려면 업데이트 빈도 대 가져오기 빈도를 고려해야 합니다.
• 앞으로 실행할 WHERE 절 또는 조인의 종류에 따라 인덱스를 선택해야 합니다.
• 인덱스 선택에서 가장 중요한 것은 인덱스의 종류와 수를 적절히 선택하는 것입니다. 유지 관리로 인해 성능이 떨어지도록 방치하지 말고 만들어야 할 인덱스의 최소 개수를 미리 결정하십시오. 이를 위해서는 가장 유용한 인덱스를 결정해야 합니다.
• 사용하지 않을 인덱스는 만들지 마십시오.
• 쿼리 최적화 프로그램이 인덱스 사용 여부에 관한 최종 결정을 내립니다.

선택도

결과 집합을 추정하면 특정 트랜잭션 집합을 위해 테이블에 만들 인덱스 종류를 선택하는 데 도움이 됩니다.

쿼리 선택도란 SELECT, UPDATE 또는 DELETE 문이 액세스하는 테이블 행의 백분율입니다. 선택도가 높으면 검색 조건을 충족하는 단일 행이 반환될 수 있습니다. 선택도가 낮으면 식별력이 떨어지기 때문에 테이블의 수많은 행이 반환될 수 있습니다.

선택도와 관련된 개념으로 조밀도가 있으며 이는 인덱스 내 중복 행의 평균 백분율을 말합니다. 중복 행이 많이 포함된 인덱스는 조밀도가 높고 고유 인덱스는 조밀도가 낮습니다.

테이블 스캔

테이블 스캔은 결과 집합에 테이블이 많이 포함된(선택도가 낮은) 쿼리에 유용합니다.

데이터 분포

데이터 분포는 특정 테이블의 값 범위 그리고 그 범위에 포함되는 행 수를 나타냅니다. 대개의 경우 결과 집합에 반환될 데이터의 백분율을 대략적으로 계산할 수 있습니다. 예를 들어, 검색 조건이 남성/여성이라면 여성에 해당하는 결과 집합을 50% 정도로 추정할 수 있습니다.

아래와 같은 쿼리를 사용하여 열 분포를 확인할 수 있습니다.

SELECT column, count(*)
FROM table
GROUP BY column

선택도: 예제

위 예제에서, 두 결과 집합에 포함된 X의 개수는 같지만 그 백분율(선택도)은 서로 다릅니다.

이들 쿼리의 선택도를 계산해 보십시오. 예를 들어, member 테이블에 10,000개의 행이 있고 member의 범위는 1-10,000이며 모두 고유한 값이라고 가정합니다.

조인 밀도

조인 밀도란 외부 테이블의 특정 행과 일치하는 내부 테이블의 평균 행 수를 말합니다. 중복 행의 평균 개수를 조인 밀도라고 생각해도 됩니다.

고유 인덱스를 가진 열의 경우 조밀도는 낮고 조인 선택도는 높을 것입니다. 중복 행이 많이 포함된 열의 경우 조밀도는 높고 조인 선택도는 낮습니다.

조인 열의 인덱스는 조인의 내부 테이블에만 유용합니다. 조인 밀도가 낮으면 종류에 상관없이 모든 인덱스가 유용할 것입니다. 조인 밀도가 높으면 클러스터된 인덱스만 유용할 것입니다.

적절한 인덱스 종류 생성

클러스터된 인덱스가 유용한 상황

고려 사항

클러스터된 인덱스에 사용되는 열이 데이터의 실제 순서를 결정합니다. 실제 순서에서 가장 자주 요구되는 데이터 열에 클러스터된 인덱스를 배치하십시오.

사용하는 SELECT 문의 종류에 따라 인덱스를 선택해야 합니다.

외래 키는 일반적으로 고유하지 않으므로 이러한 키에는 클러스터된 인덱스를 사용하는 것이 좋습니다.

클러스터된 인덱스는 테이블마다 하나만 가질 수 있습니다.

참고 특히 데이터가 단순한 경우, 기본 키에 클러스터된 인덱스를 배치하는 것은 좋은 방법이 아닙니다. 기본 키는 고유 인덱스를 가져야 합니다. 대부분의 경우, 클러스터되지 않은 고유 인덱스도 클러스터된 고유 인덱스만큼 효율성을 제공할 수 있습니다.

클러스터되지 않은 인덱스가 유용한 상황

데이터 수정 시 클러스터되지 않은 인덱스를 유지하려면 상당한 오버헤드가 필요하므로 이러한 인덱스는 반드시 필요할 때만 추가해야 합니다.

사용하지 않을 인덱스는 만들지 마십시오.

고려 사항

• 저장소 공간 요구 사항
• 데이터 수정의 영향
• 대상 열의 휘발성
• 쿼리의 선택도. 선택도가 낮은 쿼리에는 테이블 스캔이 더 적절합니다.
• 대상 열의 고유성. 중복 수준이 높으면 인덱스의 효율성이 떨어집니다.
• 범위가 테이블의 일부만 차지하는 경우를 제외하고 범위 쿼리에는 클러스터되지 않은 인덱스가 적합하지 않습니다.

복합 인덱스가 유용한 상황

복합 인덱스는 다중 열 정렬 키를 가진 인덱스일 수 있습니다. 클러스터된 인덱스와 클러스터되지 않은 인덱스 모두 복합 키를 가질 수 있습니다.

고려 사항

• 키의 첫째 열이 WHERE 절에 지정된 경우 복합 인덱스가 유용합니다.
• 범위가 너무 넓은 인덱스 그리고 WHERE 절에 둘째 열이나 셋째 열만 지정된 인덱스는 적절하지 않습니다.

예를 들어, lastname, firstname의 인덱스는 성과 성, 이름을 선택하는 데는 유용하지만 이름을 선택하는 데는 유용하지 않습니다.

복합 인덱스와 복수의 단일 열 인덱스

• 복수 인덱스를 사용하면 데이터 수정 문의 성능이 떨어질 수 있습니다.
• 일반적으로, 쿼리에서 테이블마다 하나의 인덱스만 사용되므로 복수 인덱스는 복합 인덱스만큼 유용하지 않습니다.
• 단일 테이블의 여러 열에 액세스하는 모든 쿼리에는 복합 인덱스가 더 유용할 수 있습니다.

참고 WHERE 절에 지정하는 열의 순서는 복합 인덱스의 사용 방식에 영향을 미치지 않습니다. 복합 인덱스의 맨 왼쪽 열이 WHERE 절에 포함된 경우에만 문제가 됩니다.

인덱스 정렬 키의 맨 왼쪽(순서가 빠른) 열이 WHERE 절에 포함되지 않은 경우에도 복합 인덱스를 사용할 수 있습니다. 이 경우 SELECT 목록과 WHERE 절에서 참조되는 모든 열이 인덱스 정렬 키에 있어야 합니다. 자세한 내용은 이어지는 포함 인덱스 부분을 참조하십시오.

포함 인덱스

고려 사항

• 일부 인덱스에 열을 추가하여 대부분의 쿼리를 포함합니다.
• 인덱스 키의 범위를 너무 넓게 지정하지 마십시오. 그렇지 않으면 인덱스 크기가 증가하여 성능이 떨어집니다. 행 범위가 너무 넓으면 전체 페이지 수와 마찬가지로 수준 수가 늘어납니다. 페이지 수가 늘어나면 인덱스를 스캔하는 데 필요한 시간도 늘어납니다.
• 클러스터되지 않은 인덱스를 더 많이 추가하면 업데이트 성능이 떨어집니다.
• 두 인덱스를 하나의 복합-포함 인덱스로 결합하는 것이 효과적인 경우도 있습니다.
• 선택도가 낮은 쿼리를 포함하는 클러스터되지 않은 인덱스의 경우, 데이터 페이지에 대한 액세스가 없고 그에 따라 테이블 스캔이 이루어지지 않기 때문에 처리 속도가 매우 빠릅니다.
• 순서가 빠른 인덱스 정렬 키의 열이 WHERE 절에 포함되지 않은 경우에도 포함 인덱스가 유용할 수 있습니다.

클러스터된 인덱스와 클러스터되지 않은 인덱스

쿼리에 맞게 인덱싱

일정 범위의 데이터에 대한 인덱싱: 예제

테이블에 인덱스가 없는 경우

• 테이블 스캔(53,000 I/O)이 클러스터되지 않은 인덱스보다 효율적입니다.

price 열에 클러스터된 인덱스가 있는 경우

• 클러스터된 인덱스에서 최소 값(여기서는 $20.00)을 검색합니다.
• $20.00로 시작하는 행을 읽고 $30.00에서 검색을 중지합니다.
• price 열이 클러스터되어 있기 때문에 데이터의 실제 순서는 가격에 따라 정렬됩니다. 이 범위에 속하는 모든 데이터가 이어지는 페이지에 순차적으로 정렬되어 있기 때문에 데이터를 쉽게 검색할 수 있습니다.
• 이 검색에서는 10,000페이지(190,000/페이지 당 19행)를 읽어야 합니다.

price 열에 클러스터되지 않은 인덱스가 있는 경우

• 각 행이 검색 조건에 맞는지 확인하기 위해 클러스터되지 않은 인덱스를 행 단위로 검색합니다.
• 조건에 맞는 모든 행에 대해 행이 저장된 각 페이지에서 데이터를 가져와야 합니다.
• 190,000개의 데이터 페이지(모든 인스턴스마다 하나씩)는 물론 인덱스의 잎 수준을 읽어야 하기 때문에 이 검색은 효율성이 가장 떨어집니다. 캐시에서 각 데이터 페이지가 여러 번 읽힙니다.

price, title 열에 포함 인덱스가 있는 경우

• price 및 title 열이 인덱스에 포함되어 있기 때문에 데이터 페이지를 검색할 필요가 없고 따라서 I/O가 줄어듭니다.
• price, title 인덱스에는 잎 페이지마다 평균 38개의 인덱스 행이 있습니다. 이 검색에서는 5,000페이지(190,000/페이지 당 38행)를 읽어야 합니다.

AND에 대한 인덱싱: 예제

위 예제를 대충 살펴보십시오. 연구 노트에 나열된 선택 항목을 사용하여 이 문에 가장 적합한 인덱스 종류를 확인할 수 있습니다. 조건에 맞는 모든 행이 포함될 것이므로 4번이 가장 적합합니다. 6번은 쿼리를 포괄하지만 키 범위가 넓기 때문에 인덱스가 커질 것입니다. 7번은 dept에 대한 클러스터된 인덱스와 같지만 훨씬 큽니다.

흔히 WHERE 절에 나열된 열 순서가 복합 인덱스의 사용 방식에 영향을 미친다고 생각하지만 이는 오해입니다. 사실은 그렇지 않습니다. 복합 인덱스의 맨 왼쪽 열이 WHERE 절 안에 있는 경우에만 문제가 됩니다.

또한 지금은 비교적 일반적인 데이터 분포를 가정하고 있다는 것도 고려해야 합니다. 모든 사람의 소득이 50000을 넘거나 반대로 50000을 넘는 사람이 없는 경우 또는 회사 전체를 조사 대상에 포함한 경우 이 인덱싱 전략은 바뀔 수 있습니다.

두 조건 모두 충족되면 행이 검색 조건을 만족하는 것입니다.

위 예제에서 평가할 선택 사항

1. dept에 대해 클러스터된 인덱스 또는 클러스터되지 않은 인덱스
2. salary에 대해 클러스터된 인덱스 또는 클러스터되지 않은 인덱스
3. dept에 대해 하나, salary에 대해 하나
4. dept, salary에 대해 클러스터된 복합 인덱스
5. salary, dept에 대해 클러스터된 복합 인덱스
6. dept, salary, name, address에 대해 클러스터되지 않은 인덱스(포함 인덱스)
7. dept, salary, name, address에 대해 클러스터된 인덱스

OR에 대한 인덱싱

OR는 AND와는 상황이 많이 다릅니다.

AND로 결합된 여러 조건은 필요한 데이터의 자격 조건을 조금씩 더 엄격하게 만듭니다. AND 조건 중 어느 하나를 만족하는 모든 행의 집합이 있다면 최종 결과 행이 그 집합에 있을 것입니다.

그러나 OR 조건에서는 그렇지 않습니다. OR 조건 중 어느 하나를 만족하는 모든 행의 집합이 있을 때, 이 결과 집합에 포함되지 않았어도 OR 조건 중 또 다른 조건을 만족하는 행들이 있을 수 있습니다.

OR에 대해서는 이 장의 뒷부분에서 자세히 설명합니다.

SELECT *에 대한 인덱싱: 예제

SELECT *는 모든 열의 정보를 반환합니다.

인덱스 선택과 선택되는 항목은 아무 관련이 없습니다. 인덱스 선택은 자격이 부여되는 항목에 대해서만 중요합니다. 인덱스는 행의 위치를 찾는 데 도움을 주지만 행의 정보를 선택하는 데는 영향을 미치지 않습니다. 모든 열이 정렬 키에 포함되지 않는 한 클러스터되지 않은 인덱스가 SELECT *를 포괄할 수 없습니다.

실제로 필요한 데이터보다 많은 데이터를 검색하려는 경우 SELECT * 쿼리 유형을 사용해서는 최적의 성능을 얻을 수 없습니다.

위 예제에서 평가할 선택 사항

• au_id 열에 대해 클러스터된 인덱스. au_id 열에 대해 클러스터된 인덱스를 만들면 행이 그 순서대로 저장됩니다.
• au_id 열에 대해 클러스터되지 않은 인덱스. 이를 위해서는 데이터 페이지에 액세스해야 하며 I/O가 또 하나 증가할 것입니다.
• au_id, au_lname에 대해 클러스터되지 않은 인덱스

복수 쿼리에 대한 인덱싱

이전 예제에서는 개별 쿼리에 따라 가장 적합한 인덱스 종류를 선택했습니다. 한 쿼리에 가장 적합한 인덱스가 다른 쿼리에는 그렇지 않을 수 있기 때문에 복수 쿼리에 대한 인덱싱은 더 복잡합니다. 목표는 우선 순위가 높은 모든 쿼리에 대해 적절한 성능을 얻는 것입니다.

위 예제에서 평가할 선택 사항

가정: 쿼리 1은 테이블의 15%를 사용합니다. 쿼리 2는 선택도가 높습니다. 즉, 한 행만 액세스합니다.

선택 사항 1

• price에 대해 클러스터된 인덱스
• title에 대해 클러스터되지 않은 인덱스

쿼리 1이 매우 빠르게 실행됩니다. 쿼리 2도 빠르지만 클러스터된 인덱스가 title 열에 있으면 I/O가 하나 더 필요합니다.

선택 사항 2

• price에 대해 클러스터되지 않은 인덱스
• title에 대해 클러스터된 인덱스

쿼리 1은 선택 사항 1보다 느리게 실행되고 쿼리 2는 매우 빠르게 실행됩니다.

선택 사항 3

• price에 대해 클러스터된 인덱스
• title, price에 대해 클러스터되지 않은 인덱스

쿼리 1은 빠르게 실행되고 쿼리 2는 매우 빠르게 실행됩니다.

선택 사항 4

• price, title에 대해 클러스터되지 않은 인덱스
• title에 대해 클러스터된 인덱스

이 선택 사항이 최적의 옵션입니다. 쿼리 1과 쿼리 2 모두 매우 빠르게 실행됩니다.

업데이트 고려 사항

온라인 트랜잭션 처리(OLTP) 쿼리는 행을 먼저 찾아야 수정할 수 있다는 점 때문에 데이터 검색 항목을 일부 포함합니다. 그러나 가장 중요한 쿼리가 OLTP 쿼리인 경우 아래 사항을 추가로 고려해야 합니다.

• 인덱스를 유지 관리해야 합니다. 인덱스된 테이블을 수정하면 최소한 하나의 인덱스 그리고 어쩌면 이보다 많은 인덱스를 업데이트해야 할 것입니다. 인덱스에 열이 많이 포함될수록 유지 관리 작업이 더 많이 필요합니다.
• 수정되는 각 데이터 행은 물론 수정되는 각 인덱스 행도 로그에 기록해야 합니다.

지침

• 주로 OLTP에 사용되는 응용 프로그램에 대해서는 인덱스 수를 최대한 줄입니다.
• 클러스터된 인덱스 열은 휘발성이 없어야 합니다.

인덱싱 지침

인덱스 유지 관리

클러스터된 인덱스

테이블에 클러스터된 인덱스가 있으면 클러스터된 인덱스 키의 순서대로 행을 삽입해야 합니다. 페이지에 공간이 없으면 페이지를 분할해야 하며 이 때문에 추가 오버헤드가 발생할 수 있습니다.

클러스터되지 않은 인덱스

클러스터되지 않은 인덱스는 모든 데이터 행에 대한 포인터를 가집니다. 행을 삽입하거나 삭제할 때마다 클러스터되지 않은 모든 인덱스를 업데이트해야 합니다.

UPDATE가 전체 DELETE/INSERT이거나 지연된 UPDATE인 경우, 삭제된 행과 삽입된 행 모두에 대해 클러스터되지 않은 모든 인덱스를 업데이트해야 합니다. UPDATE가 지정되어 있거나 같은 페이지에 있더라도 변경되는 열의 모든 인덱스를 업데이트해야 합니다. 광범위한 복합 인덱스가 있는 경우 이 작업 때문에 많은 오버헤드가 발생할 수 있습니다.

인덱스 생성에 관한 지침

모든 쿼리의 우선 순위 결정

• 데이터 및 데이터의 사용 방식을 철저히 파악합니다.
• 먼저 처리해야 할 데이터베이스 트랜잭션을 결정합니다.

각 쿼리의 선택도 결정

• WHERE 절의 각 부분에 대한 선택도를 결정합니다.

각 테이블에 대한 작업 차트 작성

• 테이블의 각 열에서 수행되는 작업을 분석합니다.

인덱싱해야 하는 열 결정

• WHERE 절에 해당 열이 사용됩니까?

  쿼리의 WHERE 절이나 데이터 수정 문에서 열이 전혀 참조되지 않는 경우 그 열에는 인덱스를 만들 필요가 없습니다.

• 열이 조인 키로 사용됩니까?

  조인 키로 사용되는 열에 인덱스를 만들면 쿼리 최적화 프로그램이 테이블 스캔을 수행하는 대신 인덱스를 사용할 수 있게 되므로 조인 성능이 향상됩니다.

• 자주 검색되는 열입니까?

클러스터된 인덱스에 가장 적합한 열 선택

• 일정 범위의 데이터에 액세스합니까? 트랜잭션 문에 LIKE 일치 조건이 포함됩니까?
• 클러스터된 인덱스는 일정 범위를 가진 쿼리에 가장 효과적으로 작용합니다.
• 데이터가 항상 정렬됩니까?

  특정 열의 데이터가 자주 정렬되는 경우 그 열에 클러스터된 인덱스를 배치하면 정렬 오버헤드가 줄어듭니다.

• 열에 고유한 값이 포함된 경우 고유 인덱스가 유용합니까?
• 클러스터되지 않은 인덱스를 만들기 전에 클러스터된 인덱스를 만듭니다.
• 특히 데이터가 단순한 경우, 클러스터된 인덱스를 기본 키에 배치하는 것이 반드시 최상의 선택은 아닙니다.
• 조인 키 열에는 클러스터된 인덱스가 필요하지 않습니다.

필요한 다른 인덱스 결정

• 각 테이블에 대해 만들 수 있는 최소 인덱스 수를 결정합니다.
• 인덱스의 성능 이득과 업데이트 유지 관리 비용 간에 균형을 유지합니다.
• 우선 순위가 가장 높은 쿼리의 WHERE 절에서 참조되는 열이 인덱스되었는지 확인합니다.
• 쿼리를 자주 실행하지 않는 경우 특정 작업 기간 동안 인덱스를 만든 다음 이를 삭제하는 방법도 고려할 수 있습니다. 예를 들어, 모든 보고 또는 요약 분석이 월말이나 연말에 이루어지는 경우 작업 기간 동안만 인덱스를 만든 다음 나중에 이를 삭제할 수 있습니다.

생성할 클러스터되지 않은 인덱스 종류 결정

• 복합 인덱스가 단일 열 인덱스보다 효율적입니까?
• 인덱스가 쿼리를 포괄할 수 있습니까?
• 쿼리의 선택도가 정확한 일치입니까?

  클러스터되지 않은 인덱스는 정확한 일치(한 행만 반환), 조인 또는 고유한 기본 키 열에 유용합니다.

• 열에 고유한 값이 포함된 경우 고유 인덱스가 유용합니까?

쿼리 성능 테스트

• 인덱스를 만든 후 우선 순위가 가장 높은 쿼리의 성능을 테스트합니다.
• SET SHOWPLAN ON, SET STATISTICS IO ON, SET STATISTICS TIME ON을 지정한 다음 각 쿼리를 실행합니다.

인덱싱하지 않아야 할 때

인덱싱하지 않아야 할 상황도 있습니다. 예를 들면 아래와 같습니다.

• 최적화 프로그램이 인덱스를 결코 사용하지 않는 경우
• 행의 10-20% 이상이 반환되는 경우
• 열에 1개에서 3개의 고유 값만 포함된 경우(선택도가 낮은 경우)
• 인덱스될 열이 20바이트보다 긴 경우
• 인덱스 유지 관리로 인한 오버헤드가 이득보다 큰 경우
• 테이블이 매우 작은 경우

DSS와 OLTP 간의 균형 유지

DSS 환경과 OLTP 환경의 인덱싱 요구 사항은 크게 다르기 때문에 두 환경 모두 필요한 경우 인덱싱 전략을 결정하기가 매우 어려울 수 있습니다.

완전히 똑같은 데이터에 대해 검색과 수정이 동시에 이루어지지 않도록 별도의 데이터 복사본을 만들 수 있습니다. 이 경우 데이터 조정 전략이 필요합니다. 서로 다른 두 환경에 가장 적합한 인덱스를 만드는 데 드는 비용과 이를 통해 얻을 수 있는 이점은 두 데이터 집합을 유지 관리하고 조정하는 데 드는 비용을 기준으로 측정해야 합니다.

쿼리 최적화 프로그램 소개

SQL Server 쿼리 최적화 프로그램은 어떤 인덱스가 정말로 유용한 인덱스인지 그리고 특정 쿼리에 대해 어떤 인덱스를 사용하는 것이 가장 좋은지 결정합니다. 또한 복수 테이블의 조인을 어떻게 처리할 것인지 결정하여 테이블 순서와 방법을 선택합니다. 또한 업데이트 작업을 수행하기 위한 최적의 방법도 결정합니다.

3부에서는 SQL Server 최적화 프로그램이 사용 가능한 정보를 어떻게 취사 선택하고 이를 사용하여 어떻게 최적의 실행 계획을 결정하는지에 대해 자세히 설명합니다.

© 1997 Microsoft Corporation. All rights reserved.

이 문서에 포함된 정보는 문서를 발행할 때 논의된 문제들에 대한 Microsoft Corporation의 당시 관점을 나타냅니다. Microsoft는 변화하는 시장 환경에 대처해야 하므로 이를 Microsoft 측의 책임으로 해석해서는 안 되며 발행일 이후 소개된 어떠한 정보에 대해서도 Microsoft는 그 정확성을 보장하지 않습니다.

이 설명서는 오직 정보를 제공하기 위한 것입니다. Microsoft는 이 요약에서 어떠한 명시적이거나 묵시적인 보증도 하지 않습니다.

Microsoft 및 Windows NT는 Microsoft Corporation의 등록 상표입니다. Intel은 Intel Corporation의 등록 상표입니다.

여기에 인용된 다른 제품이나 회사 이름은 해당 소유자의 상표일 수 있습니다.
 

최종 수정일 : 2000.5.12

[출처] MS SQL Server 성능 조정 및 최적화, 제 1부: 성능 문제 개요 |작성자 주디