성능 조정의 목적은 네트워크 소통량과 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의 등록 상표입니다.

목표

• 쿼리 처리 단계를 나열합니다.
• 검색 인수와 비검색 인수를 구분합니다.
• 쿼리 최적화 프로그램에서 쿼리 계획을 작성하기 위해 인덱스 통계를 사용하는 방법을 설명합니다.
• 쿼리 최적화 프로그램에서 쿼리 비용을 계산하는 방법을 설명합니다.
• 조인을 처리하는 방법을 설명합니다.

쿼리 최적화 프로그램 개요

쿼리 최적화 프로그램은 각 SQL 문을 평가하여 최적의 실행 계획을 결정합니다.

쿼리 최적화 프로그램이 작동하는 방식을 이해하는 것이 더 나은 쿼리를 작성하고 유용한 인덱스를 만드는 데 도움이 됩니다.

쿼리 최적화 프로그램의 목적

가장 효율적인 계획 결정

쿼리 최적화 프로그램은 데이터 검색과 SELECT, INSERT, UPDATE 및 DELETE와 같은 데이터 조작 쿼리의 실행 순서에 대한 최적의 계획을 결정합니다. 쿼리 최적화 프로그램은 쿼리를 수행하기 위해 필요한 일련의 개략적인 단계를 생성합니다. 최적화 프로그램은 행 찾기, 행 조인 및 행 정렬의 과정만 최적화할 수 있습니다.

비용 기준 최적화를 사용하는 SQL Server

비용 기준 최적화 프로그램은 생성된 각 쿼리 계획을 평가하여 반환된 행 수와 각 관계형 작업에 필요한 실제 디스크 입출력 양으로 계획 실행 비용을 예측합니다.

비용 기준 최적화 프로그램은 기반 데이터와 테이블 크기, 테이블 구조 및 사용 가능한 인덱스와 같은 저장소 구조에 대한 지식을 사용합니다. 또한 각 인덱스에 유지된 통계에 기준한 각 관계형 작업의 선택도를 예측합니다.

비용 기준 최적화 프로그램은 결과 집합을 생성하는 데 필요한 관계형 작업의 다양한 순서를 평가하여 시스템 오버헤드의 형태로 가장 낮은 예측 비용을 갖는 실행 계획에 도달합니다.

비용 예측은 각 관계형 작업의 선택도를 예측하는 통계 데이터를 많이 사용할수록 정확해집니다.

질의 성능은 개별 기술의 속도와 효율적인 조인 순서 선택 여부에 따라 결정됩니다. 쿼리 최적화 프로그램은 합리적인 시간 내에 실행할 수 있도록 고려할 선택의 수를 제한합니다.

또한 성능은 논리 및 실제 페이지 액세스의 양으로도 측정됩니다.

쿼리 최적화 프로그램은 쿼리 계획의 비용을 평가하기 위해 논리 및 실제 액세스를 모두 고려합니다. 또한 고정된 비율의 페이지가 캐시에 있다는 것도 고려합니다.

쿼리 최적화 프로그램은 전략을 평가하여 누적 CPU 및 입출력 시간이 가장 작은 것을 찾습니다. 이 측정에는 실제 입출력 양이 사용됩니다. 목표는 실제 입출력 양을 줄이는 것입니다.

쿼리 최적화 프로그램이 작동하는 방식을 이해하면 더 나은 쿼리를 작성하고 더 좋은 인덱스를 선택하고 성능 문제를 감지하는 데 유용한 세부 사항을 파악할 수 있습니다.

최적화가 적합한 단계

쿼리는 SQL Server로 제출된 후 몇 단계에 걸쳐 원본 쿼리를 최적화 프로그램이 해석할 수 있는 형식으로 변환됩니다. 다음은 쿼리를 처리하고 결과 집합을 반환하기 위해 발생해야 하는 단계의 개요입니다.

구문 분석 과정

구문 분석 과정에서는 수신 쿼리의 구문이 올바른지 확인하고 구문을 관계형 데이터베이스 관리 시스템에서 이해할 수 있는 구성 요소 부분으로 분해합니다. 이 단계의 출력은 구문 분석된 쿼리 트리입니다.

표준화 과정

표준화 과정에서 쿼리는 최적화에 유용한 형식으로 전환합니다. 발견된 모든 필요 없는 구문 절은 제거됩니다. 가능하다면 하위 쿼리는 평면화됩니다. 이 단계의 출력은 표준화된 쿼리 트리입니다.

쿼리 최적화

쿼리 최적화 프로그램은 쿼리를 처리하는 효율적인 쿼리 계획을 생성합니다. 이 단계에는 3개의 하위 단계(쿼리 분석, 인덱스 선택 및 조인 선택)가 있습니다. 쿼리 최적화 프로그램은 자동으로 가능한 실행 계획의 수를 제한합니다. 이 단계의 출력을 실행 계획 또는 쿼리 계획이라고 합니다.

컴파일

코드가 실행 가능한 코드로 컴파일됩니다.

데이터베이스 액세스 루틴

최적화 프로그램은 선택적으로 테이블 검색을 수행하거나 사용 가능한 인덱스를 사용하여 데이터를 액세스하는 가장 좋은 방법을 결정합니다. 그 방법이 이제 적용됩니다.

쿼리 최적화 프로그램 정보

쿼리 최적화 프로그램은 사용 가능한 정보를 분석하여 가장 좋은 쿼리 계획을 결정합니다.

sysindexes 테이블

최적화 프로그램은 sysindexes에서 다음과 같은 정보를 사용할 수 있습니다.

키 값의 통계적 배포

이 정보는 배포 페이지에 있습니다.

실행할 쿼리

쿼리는 최적화 프로그램에서 가장 유용한 인덱스를 결정하는 데 필요한 선택 기준을 제공합니다. 쿼리에 표현된 행 한정 방법이 최적화 프로그램의 결정에 영향을 미칠 수 있습니다.

SHOWPLAN

이 SET 문 옵션은 각 테이블에 사용하기 위해 선택된 인덱스에 대한 최적화 프로그램의 최종 결정, 테이블을 조인할 순서 및 선택한 업데이트 모드 등을 보고합니다. 작업 테이블과 다른 전략 또한 SHOWPLAN 출력에 보고됩니다.

1단계: 쿼리 분석

쿼리 최적화 프로그램의 첫째 단계를 쿼리 분석이라고 합니다. 이 단계에서 최적화 프로그램은 구문 분석된 각 절을 조사하여 최적화될 수 있는지 결정합니다. 최적화할 수 있는 절은 검색 인수나 조인 절을 포함한 절과 같이 스캔을 제한하는 절입니다. 최적화 프로그램은 최적화할 수 있는 이러한 절에 대해서 적절한 인덱스가 있는지 결정합니다.

검색 인수

검색 인수는 특정 정보를 요청하는 것이기 때문에 검색을 제한합니다. 정확히 일치, 값의 범위 또는 AND 연산자로 조인된 둘 이상의 항목 결합을 지정합니다. 검색 인수에는 연산자를 사용하여 열에 작동하는 상수 식을 포함합니다.

• 검색 인수의 형식은 다음과 같습니다.

  <열> <포함 연산자> <상수> [AND...]
  또는
  <상수> <포함 연산자> <열> [AND...]

• 공용 연산자는 >, <, =, BETWEEN, LIKE를 포함합니다.
• 모든 열은 같은 테이블에 있어야 합니다.
• AND와 연결된 경우, 검색 인수는 여러 조건을 포함할 수 있습니다.

예제

name = 'jones'

salary > 40000

60000 < salary

department = 'sales'

name = 'jones' AND salary > 100000

비검색 인수

식이 검색을 제한하지 않으면 비검색 인수로 취급됩니다. 이것에는 포함 식보다는 단독 식이 포함됩니다.

예를 들어, 같지 않음(!=) 식은 검색 기준에 맞지 않는 데이터를 결정하려면 먼저 데이터를 조사해야 합니다.

다른 예는 다음과 같은 열 간의 비교입니다.

salary = commission

두 열이 테이블 자체에 포함되기 때문에 인덱스가 유용하지 않을 수 있습니다.

또 다른 예는 데이터 액세스에 앞서 계산을 필요로 합니다. 다음 예를 참조하십시오.

salary * 12 > 36000

이 경우, SQL Server에서 행의 자격 여부를 결정하려면 먼저 salary 열을 액세스하여 계산을 수행해야 합니다.

토론 과제

다음 쿼리에서 검색 인수는 무엇입니까?

SELECT COUNT(*)
FROM dept, empl, job
WHERE empl.age > 30
AND (dept.floor = 2 OR dept.floor = 3)
AND job.rate > $20.00
AND empl.jobno = job.jobno

비검색 인수 변환

많은 경우에 비검색 인수는 검색 인수로 다시 작성할 수 있습니다. 검색 인수를 포함하는 쿼리는 최적화 프로그램이 인덱스를 선택하는 기회를 늘려줍니다.

열에 대한 계산과 관련된 식은 열을 격리하여 검색 인수로 변환될 수 있습니다.

팁 쿼리를 작성할 때 연산자의 한 쪽에 열 정보를 유지하고 다른 쪽에 검색 기준을 유지합니다.

BETWEEN과 LIKE와 같은 일부 식은 쿼리 최적화 프로그램에 의해 내부적으로 검색 인수로 수정됩니다.

• BETWEEN 절은 경계를 정의하는 > = 및 < =로 표현된 범위와 동등합니다.
• LIKE 식은 name LIKE 'jo%'의 예와 같이 식의 첫째 문자가 상수라면 인덱스에 의해 처리될 수 있습니다. 이것은 name >= 'jo' AND name < 'jp'와 같습니다. name LIKE '%jo' 식은 검색을 제한하지 않기 때문에 검색 인수가 아닙니다.

인덱스 사용을 피하기 위한 비검색 인수의 사용

검색 절에 비검색 인수를 사용하면 쿼리 최적화 프로그램에서 특정 인덱스를 선택하지 않게 할 수 있습니다. 예를 들어, 다음과 같이 열에 0을 추가합니다.

salary + 0 > 30000

이 문은 최적화 프로그램에서 salary에 대한 인덱스를 평가하지 않도록 보장합니다.

OR 절

OR 절은 쿼리 분석의 일부로 간주되기 때문에 여기서 설명합니다. 하지만 이후에 더 자세히 다룰 것입니다.

조인 절

둘 이상의 테이블에서 데이터를 검색하려면 조인 절이 필요합니다. 조인 절은 같은 데이터베이스나 다른 데이터베이스에 있는 다양한 테이블에서 데이트를 연결합니다.

자기 조인 역시 조인 절의 예입니다.

예제

SELECT e1.manager_name, e2.name
FROM empl e1, empl e2
WHERE e1.emplno = e2.manager_no

2단계: 인덱스 선택

인덱스 선택은 쿼리 최적화의 둘째 단계입니다. 이 단계 중에 쿼리 최적화 프로그램은 절을 위한 인덱스가 있는지 확인하고 절의 선택도를 확인하여 유용성을 평가(반환되는 열의 수)하고 한정된 열을 찾는 데 필요한 페이지 액세스 수(논리 및 실제 모두)를 예측합니다.

유용한 인덱스의 유무 결정

유용한 인덱스가 있는지 파악하는 첫째 단계는 인덱스가 절과 일치하는지 확인하는 것입니다.

다음과 같은 경우에 인덱스가 유용합니다.

• 인덱스의 첫째 열은 검색 인수에 사용됩니다.
• 검색 인수는 검색을 제한하기 위해 하한, 상한 또는 둘 모두를 설정합니다.

고려 사항

WHERE 절에 고차(high-order) 열이 지정된 경우, 쿼리 최적화 프로그램은 클러스터되지 않은 인덱스를 사용하여 평가할 수 있습니다.

쿼리 최적화 프로그램은 인덱스된 열이 WHERE 절에 지정되어 있는지 여부에 관계 없이 항상 관련 인덱스를 평가합니다.

절의 선택도 결정

통계를 사용할 수 있는 경우

절과 일치하는 유용한 인덱스를 찾은 후에 그 유용성은 절의 선택도를 결정하여 평가됩니다. 유용한 인덱스가 존재하는 경우에도 최적화 프로그램에서 해당 인덱스 액세스가 최선의 액세스 방법이 아니라고 결정하면 사용되지 않을 수 있습니다. 선택도는 절을 만족시키는 행의 수를 예측하여 결정됩니다. 통계를 사용할 수 있으면 서버는 배포 단계를 사용하여 인덱스를 평가합니다.

통계를 사용할 수 없는 경우

사용할 수 있는 통계가 없는 경우, 서버는 연산자에 따라 고정된 비율을 사용합니다.

통계를 사용할 수 없다면 최적화 프로그램은 다음과 같은 기본값을 사용합니다.

특수한 경우는 최적화 프로그램이 WHERE 절에 등가성이 있다는 것과 인덱스가 고유하다는 것을 인식한 경우입니다. 이것은 정확히 일치하고 항상 한 열만 반환하기 때문에 최적화 프로그램은 통계를 사용할 필요가 없습니다.

테이블에 데이터가 없을 때 또는 테이블이 잘린 후에 만들어진 인덱스라면 사용할 수 있는 통계가 없을 것입니다.

인덱스 통계

최적화 프로그램이 인덱스의 유용성을 예측하기 위해 사용하는 통계는 검색을 제한하거나 여러 테이블 쿼리를 위한 조인 순서를 결정합니다. 통계는 모든 인덱스에 대해 유지되어 주어진 인덱스의 분산 값에 대한 정보를 제공합니다.

SQL Server에서는 인덱스 통계를 위해 분산 값이 유지됩니다. 단계 당 행 수는 키 값 범위가 변경되어도 일정하게 유지됩니다. 표준 분산에서 키 값 범위는 범위 당 숫자가 변경되어도 일정하게 유지됩니다. 균일한 분산을 사용하면 쿼리 최적화 프로그램에서 테이블 전체 행의 비율로 자격이 있는 열의 수를 추정하여 쉽게 쿼리의 선택도를 결정할 수 있습니다.

분산 페이지

분산 페이지는 인덱스에 포함된 값의 표본 추출을 나타냅니다.

인덱스에 대한 분산 페이지가 만들어졌는지 확인하려면 sysindexes 테이블에서 distribution 열을 쿼리합니다. distribution 열 값이 0이면 해당 인덱스에 사용할 수 있는 통계가 없다는 것을 나타냅니다. 그 외의 다른 숫자는 분산 페이지의 위치를 나타냅니다.

UPDATE STATISTICS를 실행하여 테이블에서 각 인덱스에 대한 분산 페이지를 만듭니다.

조밀도는 중복의 평균 값을 나타냅니다. 남아 있는 각 열에 기반한 하위 집합에 대한 개별 값이 복합 인덱스에 유지됩니다.

분산 단계

인덱스 키의 크기는 각 인덱스를 위한 분산 단계의 총 수를 결정합니다. 분산 페이지에 적합한 값의 수로 제한이 부과됩니다. 인덱스의 첫째 및 마지막 키 값은 항상 포함됩니다.

• 데이터는 동등한 단계로 나누어지고 그 각각은 같은 수의 행을 포함합니다.
• 단계의 수는 인덱스 키의 크기에 따릅니다.
• 단계 당 하나의 인덱스 키가 있습니다.

참고 첫째 인덱스 키가 항상 분산 페이지에 포함되기 때문에 페이지 당 인덱스 키의 수에서 하나를 빼면 분산 단계의 총 수를 구할 수 있습니다.

그런 다음 분산 단계의 총 수는 인덱스 키의 총 수로 나뉘어 각 단계에 포함될 키의 수를 결정합니다. 각 단계에서 분산 페이지에 대한 하나의 인덱스 키가 기록됩니다.

SQL Server는 단계의 크기를 계산합니다.

페이지 당 인덱스 키의 수에서 1을 빼면 분산 단계의 수와 같습니다. 단계의 수가 많을수록 정보가 더 정확합니다. 인덱스의 키가 작을수록 통계가 더 정확합니다. 단계의 수가 행의 총 수와 같으면 완전한 정보입니다.

복합 인덱스의 경우, 첫째 열의 키만 분산 단계 결정에 사용됩니다.

분산 단계: 예제

분산 단계의 수 계산

인덱스 키 크기 = 250바이트

페이지 당 인덱스 키 8개 - 1 = 7개의 분산 페이지

인덱스 키의 총 수 = 22

단계 당 3개의 키가 있는 총 7단계가 있습니다.

테이블의 1/7이 각 단계에 있습니다.

각 단계(세째 행마다)에서 하나의 인덱스 키가 분산 페이지에 놓입니다.

클래스 예제

인덱스 키 크기 = 18바이트

페이지 당 인덱스 키의 수는?__________________________________

분산 단계의 수는?_____________________________________

인덱스 키의 총 수 = 94,795

단계 당 키의 수는?________________________________________

각 단계에서 테이블이 포함되는 양은?______________________________

분산 단계 조사

구문

DBCC SHOW_STATISTICS (테이블_이름, 인덱스_이름)

지정한 테이블(테이블_이름)에 대한 인덱스(인덱스_이름)의 분산 페이지에서 모든 통계 정보를 표시합니다. 반환된 결과는 인덱스의 선택도를 나타내며(반환된 조밀도가 낮을수록 선택도는 높음) 최적화 프로그램에서 인덱스가 유용한지 여부를 결정하는 기준을 제공합니다.

엔터프라이즈 관리자

SQL 엔터프라이즈 관리자를 사용하여 Manage 메뉴에서 Indexes를 누르거나 테이블 이름으로 드릴다운하여 이름을 마우스 오른쪽 단추로 누른 다음 Indexes를 누릅니다. 두 경우 모두에 데이터베이스의 모든 테이블에 대한 모든 인덱스를 검사할 수 있는 대화 상자가 나타납니다.

Distribution 단추를 누르면 DBCC SHOW_STATISTICS가 제공하는 정보와 같은 양의 정보를 볼 수 있습니다.

UPDATE STATISTICS

UPDATE STATISTICS [[데이터베이스.]소유자.]테이블_이름 [인덱스_이름]

테이블_이름 매개 변수는 인덱스가 관련된 테이블을 지정합니다. SQL Server에서는 데이터베이스의 인덱스 이름이 고유할 필요가 없기 때문에 이 매개 변수가 필요합니다.

인덱스_이름 매개 변수는 업데이트될 인덱스를 지정합니다. 인덱스 이름을 지정하지 않으면 지정한 테이블의 모든 인덱스를 위한 분산 통계가 업데이트됩니다. 인덱스 이름과 설명의 목록을 보려면 테이블 이름과 함께 sp_helpindex 시스템 저장 프로시저를 실행합니다.

구문

STATS_DATE (테이블_id, 인덱스_id)

이 함수는 지정한 인덱스(인덱스_id)에 대한 통계가 최종적으로 업데이트된 날짜를 반환합니다.

예제

테이블에서 모든 인덱스에 대해 통계가 업데이트된 날짜를 보려면 다음과 같은 명령을 사용합니다.

SELECT 'Index Name' = i.name, 'Statistics Updated' =
stats_date(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'charge' AND o.id = i.id

통계 사용에 대한 예외

통계를 사용하지 않는 일부 경우가 있습니다. 이러한 일은 통계를 사용할 수 없거나 WHERE 절에 알 수 없는 값이 있을 때 발생합니다. 테이블에 데이터가 입력되기 전에 인덱스를 만들고 UPDATE STATISTICS를 실행하지 않았거나 테이블이 잘렸다면 통계를 사용할 수 없습니다.

알 수 없는 값

예제

DECLARE @var int
SELECT @var = 15
SELECT X FROM Y WHERE col = @var

WHERE 절에 알 수 없는 값이 포함되기 때문에 인덱스 통계의 키 값을 사용할 수 없습니다. 하지만 연산자가 =라면 SQL Server는 조밀도 정보를 사용하여 자격이 있는 행의 수를 예측합니다.

통계가 없다면 고정 비율은 사용되는 기본값과 약간 다릅니다.

참고 통계를 사용할 수 있다는 것이 최신 정보라는 의미는 아닙니다.

페이지 액세스 양 결정

절의 선택도 결정의 두 번째 부분으로 쿼리 최적화 프로그램은 행 추정에 기준한 논리 페이지 추정을 계산합니다. 이것은 특정 절을 선택하기 위한 최선의 인덱스를 결정합니다. 클러스터된 인덱스와 클러스터되지 않은 인덱스에 대한 페이지 추정 간에 큰 차이가 있을 수 있습니다. 클러스터되지 않은 인덱스를 사용하면 쿼리 최적화 프로그램은 가장 나쁜 경우를 가정합니다. 그것은 각 행을 다른 페이지에서 찾게 되는 것입니다. 쿼리 최적화 프로그램은 이러한 요인을 비용 기준 최적화 계산에 고려합니다.

인덱스가 없는 경우

논리 페이지 액세스 = 테이블의 데이터 페이지 총 수

클러스터된 인덱스의 경우

논리 페이지 액세스 = 인덱스의 수준 수 + 스캔할 데이터 페이지 수(데이터 페이지 = 한정된 행 수/데이터 페이지 당 행)

클러스터되지 않은 인덱스의 경우

논리 페이지 액세스 = 인덱스의 수준 수 더하기 잎 페이지의 수(한정된 행/잎 페이지 당 행) 더하기 한정된 행 수(각 행이 별도의 페이지에 있다고 가정)

첨부 인덱스의 경우

논리 페이지 액세스 = 인덱스의 수준 수 더하기 잎 페이지의 수(한정된 행/잎 페이지 당 행)

고유 인덱스의 경우

쿼리가 고유 인덱스 키의 모든 부분에 대해 등가성을 검색 중인 경우, 논리 페이지 액세스 = 1 더하기 인덱스 수준 수

3단계: 조인 선택

조인 선택은 쿼리 최적화 단계의 세 번째 주요 단계입니다. 복수의 테이블 쿼리나 자기 조인이 있다면 최적화 프로그램은 조인 선택을 평가합니다. 최적화 프로그램은 절이 정렬되는 방법을 비교하여 논리 페이지 입출력의 형태로 추정 처리 비용이 가장 낮은 조인 계획을 선택합니다.

조인 절의 선택도 결정

조인 선택도는 테이블 A에서 테이블 B의 한 열과 조인되는 열 수를 결정합니다. 이것은 검색 인수와 일치하는 열 수를 결정하는 것과 다릅니다. 조인 선택도는 조인을 처리하는 순서를 결정하는 유용한 요소입니다.

통계를 사용할 수 있으면 조인 선택도는 인덱스 조밀도에 기반합니다. 통계를 사용할 수 없으면 추론은 1을 더 작은 테이블의 행 수로 나눈 것입니다.

조인 선택도는 조인 절에서 예상되는 행 수를 참고합니다. 이 값은 계산하여 얻거나 조밀도(중복 행의 평균 비율)에 기준합니다.

조인 절 예제

WHERE dept.deptno = empl.deptno

가정:

직관적으로 department 당 10명의 employee가 있다고 추정할 수 있습니다. 그러나 쿼리 최적화 프로그램은 직관이 없기 때문에 다른 방법을 통해 선택도를 계산해야 합니다.

위 절의 선택도는 1/100 또는 .01입니다.

department 테이블의 행이 주어지면 employee 테이블에서 조인될 행 수는 1,000 * .01 = 10입니다.

employee 테이블의 행이 주어지면 department 테이블에서 조인될 행 수는 100 * .01 = 1입니다.

조인의 중첩 반복

쿼리에 조인 절이 있으면 최적화 프로그램은 테이블, 인덱스 및 조인의 수를 평가하여 중첩 반복의 최적 순서를 결정합니다.

전략

• 최적 조인 순서를 결정하기 위해 한 번에 4개의 테이블을 평가합니다.
• 외부 테이블의 다음 한정된 행에서 내부 쿼리로 값을 투영합니다.
• 외부 테이블 검색 인수는 검색을 제한하므로 인덱스를 사용할 수 있습니다.
• 이전에 설명한 전술에 따라 단일 테이블 쿼리를 처리합니다.

지침

• 더 많은 조인 절을 추가할수록 최적화 프로그램에서 더 많은 쿼리 계획을 선택할 수 있습니다.
• 중복되는 절을 추가합니다.

조인의 중첩 반복: 예제

최적화 프로그램은 조인의 중첩 반복을 수행하도록 선택할 수 있습니다. 이 전략을 선택하면 SQL Server는 첫 번째 테이블에서 행을 찾은 후에 다음 테이블 스캔에 해당 행을 사용하고 일치된 결과가 마지막 테이블 스캔에 사용될 때까지 계속하여 중첩된 루프의 집합을 구성합니다. 반복하여 테이블에서 테이블로 진행됨에 따라 결과 집합은 더욱 좁혀집니다.

쿼리 계획은 사용할 중첩된 테이블의 정렬 집합을 지정합니다. 다른 가능한 계획의 수는 테이블, 인덱스 및 조인의 수와 관련됩니다.

titles의 titleauthor 조인: 예제

예제

SELECT title
FROM titles, titleauthor
WHERE titles.title_id = titleauthor.title_id
AND titleauthor.royaltyper > 50

처리 단계

1. titles의 다음 행을 가져옵니다.
2. title_id의 값을 가져옵니다.

   title_id에 대한 인덱스를 사용하여 titleauthor에서 각 일치하는 행을 찾습니다.

   royaltyper의 값을 비교하여 50보다 크면 행을 반환합니다.

3. 외부 테이블에서 한정된 행을 액세스할 수 있을 때까지 1단계와 2단계를 반복합니다.

titleauthor의 titles 조인: 예제

예제

SELECT title
FROM titles, titleauthor
WHERE titles.title_id = titleauthor.title_id
AND titleauthor.royaltyper > 50

처리 단계

1. titleauthor WHERE royaltyper > 50의 다음 행을 가져옵니다.
2. title_id에 대한 인덱스를 사용하여 titles를 검색하고 행을 반환합니다.
3. 외부 테이블에서 한정된 행을 액세스할 수 있을 때까지 1단계와 2단계를 반복합니다.

3방향 조인: 예제

예제

SELECT t.title, a.au_lname
FROM titles t, authors a, titleauthor ta
WHERE t.title_id = ta.title_id
AND a.au_id = ta.au_id
AND a.au_lname = 'Green'

예제 1

titles to ta (titleauthor) to authors (shown above)

3개의 titles가 있기 때문에 titleauthor를 세 번 검색하고 authors를 여섯 번 검색합니다.

예제 2

authors to ta (titleauthor) to titles (shown above)

하나의 author = Green만 있기 때문에 titleauthor를 한 번만 검색하고 titles를 두 번 검색합니다.

이 예제들의 차이점은 authors가 첫 번째 경우에는 여섯 번, 두 번째 경우에는 한 번만 검색된다는 것입니다.

핵심 사항

• 테이블 수가 증가함에 따라 내부 테이블의 반복 수가 증가합니다.
• 테이블 검색의 비용은 유용한 인덱스가 있는지 여부에 따라 달라집니다.
• 또한 검색 비용은 테이블의 크기와 각 수준에서 반환된 행 수에 관련됩니다.

최선의 계획 선택

총 비용 계산

• 각 치환에 대해 쿼리 최적화 프로그램은 최선의 인덱스와 조인 전략을 계산합니다.

  각 조인 순서에 대한 비용을 계산합니다.

• 각 테이블에 대해 쿼리 최적화 프로그램은 논리 페이지 액세스 수를 계산합니다.

  쿼리 최적화 프로그램은 인덱스가 고유하고 WHERE 절이 특정 값과 같은지 검사하여 비용이 항상 페이지 액세스 하나와 인덱스 수준의 수를 더한 값이 됩니다.

• 쿼리 최적화 프로그램은 캐시에 비례한 각 테이블의 크기를 고려합니다.

쿼리 처리 단계 요약

SHOWPLAN 출력

SET 문의 SHOWPLAN 매개 변수 출력은 쿼리 처리를 위해 쿼리 최적화 프로그램이 선택한 최종 액세스 방법을 자세히 설명합니다. 아래는 출력 메시지에 대한 설명입니다.

STEP n

이 문은 모든 쿼리에 대한 SHOWPLAN 출력에 포함됩니다. 일부 경우에 SQL Server는 단일 단계에서 유효한 결과를 검색할 수 없기 때문에 쿼리 계획을 여러 단계로 나눕니다.

The type of query is <쿼리 종류>

이 문은 각 단계에서 사용된 쿼리의 종류(SELECT, INSERT, UPDATE 또는 DELETE)를 설명합니다. 다른 명령을 호출하는 동안 SHOWPLAN을 켜면 <query type>이 호출된 명령을 반영합니다.

The update mode is deferred

이 문은 선택된 업데이트 모드가 지연된 것을 나타냅니다.

The update mode is direct

이 문은 선택된 업데이트 모드가 직접인 것을 나타냅니다.

GROUP BY

이것은 GROUP BY 절이 포함된 모든 쿼리에 대해 SHOWPLAN 출력에 나타납니다. GROUP BY는 항상 작업 테이블에서 한정된 행을 선택하여 그룹화하는 단계와 결과를 반환하는 단계의 최소 두 단계가 필요합니다.

Scalar Aggregate

이것은 SELECT 문에 집계 함수가 사용된 것을 나타냅니다. 단일 값이 반환되기 때문에 포함된 행 수에 관계 없이 첫째 단계는 집계를 계산하고 둘째 단계는 최종 값을 반환합니다.

Vector Aggregate

GROUP BY 절이 집계 함수와 함께 사용되면 질의 최적화 프로그램은 벡터 집계를 사용합니다. 각 그룹에 대해 단일 값이 반환됩니다.

FROM TABLE

이 문은 쿼리가 액세스 중인 테이블 이름을 나타냅니다. FROM TABLE 다음에 나열된 테이블의 순서는 쿼리를 처리하기 위해서 함께 조인된 테이블의 순서를 나타냅니다.

TO TABLE

이것은 수정될 대상 테이블을 나타냅니다. 일부 경우에 테이블은 데이터베이스의 실제 테이블이 아니라 작업 테이블입니다.

Worktable

이것은 쿼리의 중간 결과를 유지하기 위해 임시 테이블이 작성된 것을 나타냅니다. 이 출력은 행을 정렬해야 할 때 발생합니다. Worktable은 항상 tempdb 데이터베이스에 만들어지고 결과가 반환된 후에 자동으로 삭제됩니다.

Worktable created for <쿼리 종류>

이것은 쿼리를 처리하기 위해 worktable을 만들었다는 것을 나타냅니다. query type은 SELECT_INTO, DISTINCT 또는 ORDER BY가 될 수 있으며 또는 REFORMATTING의 목적으로 worktable을 만들 수 있습니다.

This step involves sorting

이것은 쿼리의 중간 결과가 사용자에게 반환되기 전에 정렬되어야 함을 나타냅니다. 이것은 DISTINCT를 지정하거나 또는 ORDER BY를 포함하는 쿼리에 대한 유용한 인덱스가 없을 때 발생합니다.

Using GETSORTED

이것은 SQL Server가 결과 집합에서 행을 정렬하기 위해 임시 worktable을 만들었다는 것을 나타냅니다. 정렬된 수선에서 행을 반환하는 모든 쿼리가 이 단계를 사용하는 것은 아닙니다.

Nested iteration

중첩 반복은 최적화 프로그램의 기본 기법이며 이 구는 모든 SHOWPLAN 출력에 나타납니다.

EXISTS TABLE: nested iteration

이 문은 존재 검사의 일부로 사용된 테이블에 대한 중첩 반복을 나타냅니다. Transact-SQL에서 존재 검사는 EXISTS, IN 또는 =ANY로 작성할 수 있습니다.

Table Scan

이것은 쿼리 최적화 프로그램에서 결과를 검색하기 위해 테이블 스캔 전략을 선택했다는 것을 나타냅니다.

Using Clustered Index

이것은 쿼리 최적화 프로그램에서 결과 집합 검색을 위해 클러스터된 인덱스를 사용하고 있다는 것을 나타냅니다.

Index: <인덱스 이름 >

이것은 쿼리 최적화 프로그램에서 결과 집합을 검색하기 위해 사용 중인 클러스터되지 않은 인덱스의 이름을 나카냅니다.

Using Dynamic Index

이것은 최적화 프로그램에서 OR 처리 전략의 일부로 고유한 인덱스를 작성하도록 선택했다는 것을 나타냅니다.

STATISTICS IO 출력

STATISTICS IO의 출력은 다음과 같은 값을 포함합니다.

• Logical Reads

  이 값은 해당 쿼리를 처리하기 위해 액세스된 페이지의 총 수를 나타냅니다. 모든 페이지 액세스는 데이터 캐시를 통해 액세스되기 때문에 캐시에서 아직 페이지를 사용할 수 없다면 읽어와야 합니다.

• Physical Reads

  이 값은 디스크에서 읽은 페이지의 수를 나타냅니다. 이 값은 항상 Logical Reads 값보다 작거나 같아야 합니다.

  Cache Hit Ratio의 값은 다음과 같이 위의 두 값에서 계산될 수 있습니다.

  Cache Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads

• Read Ahead Reads

  이 값은 Read Ahead Manager가 캐시로 읽은 페이지의 수를 나타냅니다. 이 값이 크다는 것은 Physical Reads에 대한 값이 더 낮다는 것을 의미하며 미리 읽기를 사용할 수 없다면 Cache Hit Ratio가 더 높습니다.

• Scan Count

  이 값은 대응하는 테이블을 액세스한 횟수를 나타냅니다. 외부 테이블은 항상 1의 스캔 카운트를 가집니다. 내부 테이블의 경우, Logical Reads의 수는 Scan Count에 각 스캔에서 액세스된 페이지의 수를 곱해 결정됩니다.

목표

• 최적화 프로그램에서 최선의 계획을 선택했는지 확인합니다.
• 사용 가능한 통계를 검사합니다.
• 추적 플래그를 사용하여 최적화 프로그램의 결정 방법을 확인합니다.
• 최적화 프로그램 참고를 사용하는 방법과 사용하는 시점을 설명합니다.
• FORCEPLAN을 사용하여 최적화 프로그램의 조인 순서를 무시할 수 있는지 결정합니다.

최적화 프로그램의 선택 분석

대부분의 경우에 최적화 프로그램은 처리할 쿼리에 대해 최선의 인덱스와 최선의 조인 순서를 선택합니다.

최적화 프로그램이 최선의 선택을 했는지 의심스러운 경우, 최적화 프로그램에서 선택한 이유를 분석하는 데 사용할 수 있는 도구가 있습니다. 때로는 이유를 아는 것만으로도 올바른 선택을 했다고 납득할 수 있습니다.

다른 경우에는 확신이 서지 않을 수도 있습니다. 따라서 최적화 프로그램을 무시하는 데 사용할 수 있는 도구도 있습니다. 이러한 도구들을 사용하여 사용자의 선택이 최적화 프로그램의 선택보다 더 나은지 확인할 수 있습니다.

통계 관리 도구

DBCC UPDATEUSAGE

이 명령은 sp_spaceused 시스템 저장 프로시저에서 잘못된 공간 사용량 보고서를 만들 수 있는 sysindexes 테이블의 부정확성을 보고하고 교정합니다.

이 문은 U(사용자 정의 테이블) 또는 S(시스템 테이블) 형식의 개체에 대한 모든 클러스터된 인덱스에 대해 sysindexes 테이블의 used, reserved 및 dpages 열을 고칩니다. 클러스터되지 않은 인덱스에 대해서는 크기 정보가 유지되지 않습니다. 이 문은 정확한 사용량 정보가 반환되도록 sysindexes의 공간 사용 카운터를 동기화하는 데 사용할 수 있습니다. 데이터베이스_이름 대신 0을 사용하면 현재 데이터베이스에서 업데이트가 수행됩니다.

구문

DBCC UPDATEUSAGE ({0 | 데이터베이스_이름} [, 테이블_이름 [, 인덱스_id]])
[WITH COUNT_ROWS]

WITH COUNT_ROWS 옵션은 sysindexes의 rows 열이 테이블에 있는 행 수의 현재 카운트로 업데이트되도록 지정합니다. 이것은 0이나 1의 인덱스_id를 갖는 sysindexes에 적용됩니다. 이 옵션은 큰 테이블에 대한 성능에 영향을 줄 수 있습니다.

참고 저장 프로시저 sp_spaceused는 @updateusage 한정자와 함께 사용되어 DBCC UPDATEUSAGE와 같은 기능을 제공할 수 있습니다. sp_spaceused 저장 프로시저는 실행에 더 오랜 시간이 걸립니다. 큰 테이블에 이 옵션을 사용하는 것은 테이블의 모든 행을 계산해야 하기 때문에 오랜 시간이 걸릴 수 있습니다.

DBCC SHOW_STATISTICS

이 명령은 지정한 테이블에 대한 인덱스의 분산 페이지에 모든 통계 정보를 표시합니다. 반환된 결과는 인덱스의 선택도를 나타내며(반환된 조밀도가 낮을수록 선택도는 높음) 최적화 프로그램에서 인덱스가 유용한지 여부를 결정하는 기준을 제공합니다. 반환된 결과는 인덱스의 분산 단계에 기준합니다.

구문

DBCC SHOW_STATISTICS (테이블_이름, 인덱스_이름)

STATS_DATE 함수

이 함수는 지정한 인덱스에 대한 통계가 최종적으로 업데이트된 날짜를 반환합니다.

구문

STATS_DATE (테이블_id, 인덱스_id)

위의 모든 정보는 엔터프라이즈 관리자에서 Manage Indexes 대화 상자를 통해 이용할 수 있습니다. (Manage 메뉴에서 Indexes를 누릅니다.)

추적 플래그

추적 플래그

SQL Server 추적 플래그는 SQL Server 작업에 대한 추가 정보를 제공하거나 대개 하위 호환성에 대한 것인 특정 작동을 변경합니다. 일반적으로 추적 플래그는 영구적인 해결 방법이 나타나기 전에 문제를 임시적으로 해결하는 방법으로만 사용해야 합니다. 추적 플래그가 제공하는 정보가 문제 진단을 도와줄 수는 있지만 추적 플래그가 지원되는 기능 집합의 일부가 아니라는 사실에 유념하십시오. 이것은 장래의 호환성이나 지속적인 사용이 보장되지 않는다는 것을 의미합니다. 또한 Microsoft를 포함한 기본 지원 공급자는 대개 자세한 정보가 없으며 추적 플래그나 그 출력에 관련된 질문에 응답할 수 없습니다. 즉, 이 절에서 제공하는 정보를 사용하는 것은 사용자에게 책임이 있습니다.

최적화 프로그램 추적 플래그

대체 시작 옵션

SQL Server를 설치할 때 setup 프로그램은 다음과 같은 키 아래에 있는 Windows NT 레지스트리에 기본 시작 옵션 집합을 기록합니다.

HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
\MSSQLServer

예를 들어, 단일 사용자 모드로 SQL Server를 시작하거나 특정 추적 플래그 집합으로 시작하기 위해 레지스트리에 시작 옵션의 대체 집합을 만들고 저장하려면 MSSQLServer 아래에서 MSSQLServer 키를 새로운 키로 복사한 다음 목적에 맞는 새 키로 옵션을 편집합니다. 각 추적 플래그를 포함한 각 시작 옵션은 SQLArg0, SQLArg1 등의 순서로 MSSQLServer 키의 Parameters 항목에서 별도의 매개 변수로 저장됩니다. 매개 변수의 순서는 중요하지 않습니다.

레지스트리 편집은 대개 좋은 방법은 아니며 부적당하거나 잘못된 변경은 시스템에 심각한 구성 문서를 일으킬 수 있습니다.

SingleUser라는 새 키를 만든 다음 이 항목을 편집하여,

HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
\SingleUser
\Parameters

추가적인 -m 시작 옵션을 넣을 수 있습니다. SingleUser 키에 대한 전체 Parameters 항목은 다음과 같아 보일 것입니다.

HKEY_LOCAL_MACHINE
\Software
\Microsoft
\MSSQLServer
\SingleUser
\Parameters
SQLArg0 : REG_SZ : -dC:\SQL\DAT\MASTER.DAT
SQLArg1 : REG_SZ : -eC:\SQL\LOG\ERRORLOG
SQLArg2 : REG_SZ : -m

이 대체 키를 사용하여 SQL Server를 시작하려면 다음 예제와 같이 -s 시작 옵션을 사용하여 명령 프롬프트에서 SQL Server를 시작합니다.

sqlservr -c -sSingleUser

최적화 프로그램 추적 플래그 사용

최적화 프로그램 추적 플래그는 대개 Microsoft 엔지니어를 위한 많은 정보를 제공합니다. 다음과 같이 조사해 볼 수 있는 특정 항목이 있습니다.

• 모든 검색 인수(SARG)가 올바른 연산자와 관련되어 있습니까?
• 모든 인덱스를 고려합니까?
• 통계 페이지를 사용할 수 있습니까?
• 행 추정이 실제 값과 유사합니까?
• 페이지 추정이 합리적입니까?
• 모든 조인 순서를 고려합니까?
• 각각의 서로 다른 조인 순서로 적절한 인덱스를 검사했습니까?

예제

다음은 Transact-SQL 코드의 일부와 그 출력입니다. 위 질문에 대해 답하는 출력은 굵은 글꼴로 표시됩니다.

DBCC TRACEON(3604, 302)
SET SHOWPLAN ON
SET NOEXEC ON
GO
SELECT * FROM charge
WHERE charge_no > 99950

DBCC execution completed.DBCC에서 오류 메시지를 표시하면 시스템 관리자에게 문의하십시오.

*******************************
Leaving q_init_sclause() for table 'charge' (varno 0).
The table has 100000 rows and 3408 pages.
Cheapest index is index 0, costing 3408 pages per scan.

*******************************
Entering q_score_index() for table 'charge' (varno 0).
The table has 100000 rows and 3408 pages.
Scoring the search clause:
AND (!:0xb8e492) (andstat:0xa)
GT (L:0xb8e47e) (rsltype:0x38 rsllen:4 rslprec:10 rslscale:0
opstat:0x0)
VAR (L:0xb8e4d0) (varname:charge_no varno:0 colid:1
coltype(0x38):INT4 colen:4 coloff:2 colprec:10 colscale:0
vartypeid:101 varusecnt:2 varstat:0x4001 varlevel:0 varsubq:0)
INT4 (R:0xb8e464) (left:0xb8e46c len:4 maxlen:4 prec:5 scale:0
value:99950)

Scoring clause for index 6
Relop bits are: 0x4000,0x80,0x10,0x1
Qualifying stat page; pgno: 10616 steps: 332
Search value: INT4 value:99950
No steps for search value--qualpage for LT search value finds
value between steps 330 and 331--use betweenSC
Estimate: indid 6, selectivity 4.513098e-003, rows 451 pages 457 Cheapest index is index 6, costing 457 pages and generating 451 rows
per scan.
Search argument selectivity is 0.004513.
*******************************
STEP 1
The type of query is SELECT
FROM TABLE
charge
Nested iteration
Index : charge_charge_amt

최적화 프로그램 무시