가끔은 한 테이블에서 검색을 할 때 무작위로 뽑아내고 싶은 경우가 있습니다. 예를 들어 추첨을 하는 경우 필요할 수 있는 요구사항입니다. 이에 대한 아주 간단한 방법을 소개해 드립니다.

1. newid() 함수

NEWID() 함수는 수행이 될 때마다 전혀 중복되지 않는 임의의 값을 만들어 리턴해줍니다. 만일 다음과 같이 newid() 함수의 결과를 SELECT 해보면

결과는 다음과 같습니다.

한번 두 수행해 보면 결과는 다음과 같습니다.

2. newid() 함수를 이용한 무작위 추출

위 내용을 이용하면 무작위로 테이블에서 행을 얻어 낼 수 있습니다. 만일 다음과 같이 Titles 테이블에서 5개의 title_id를 얻는 쿼리문이 있다고 가정하겠습니다.

위 SELECT 문이 수행이 되면 매번 동일한 결과를 얻게 됩니다. Titles 테이블의 값이 변경되지 않는한 똑같은 경과가 나올 수 밖에 없습니다.

하지만 다음과 같이 ORDER BY 문을 사용하게 되면 결과는 매번 달라집니다.

위와 같이 하면 쿼리가 수행되는 중에 newid() 함수에 의해 각 행에 임의의 값이 만들어지고 그 값을 기준으로 정렬해서 상위 5개의 행만을 얻게 되므로 결과가 매번 달라지게 됩니다. 우선 처음 수행을 시켜보면 결과는 다음과 같습니다.

한번 더 수행해보면 결과는 다음과 같습니다.

결과가 계속 달라짐을 알 수 있습니다. 확인해 보세요~

3. 정리

참 편리하죠? 만일 위 기능을 추첨에 사용한다고 한다면 한번 추첨된 값은 대상에서 제외하는 처리 루틴을 포함해야 합니다. SELECT 된 결과는 다른 테이블에 기록을 하고 그 테이블에 존재하는 대상을 제외하고 SELECT 되도록 하면 쉽게 해결이 됩니다.

개요

목표

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

쿼리 최적화 프로그램 개요

쿼리 최적화 프로그램은 각 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

최적화 프로그램 무시