쿼리 계획 이해

  • 11분

실행 계획 세부 정보를 살펴보기 전에 데이터베이스 최적화 프로그램의 작동 방식을 이해해야 합니다. SQL Server는 사용 중인 열에 대한 통계 및 쿼리 계획의 각 작업에 대한 잠재적 인덱스에 따라 가능한 여러 계획의 비용을 계산하는 비용 기반 쿼리 최적화 관리자를 사용합니다. 이 정보는 최적화 프로그램에서 각 계획의 총 비용을 결정하는 데 도움이 됩니다. 복잡한 쿼리에는 수천 개의 가능한 실행 계획이 있을 수 있지만 최적화 프로그램은 모든 실행 계획을 평가하지는 않습니다. 대신 추론을 사용하여 잘 수행될 가능성이 있는 계획을 식별한 다음 평가된 계획 중에서 가장 낮은 비용 계획을 선택합니다.

쿼리 최적화 프로그램은 비용 기반이므로 의사 결정을 위한 정확한 입력을 제공하는 것이 중요합니다. SQL Server는 통계를 사용하여 열 및 인덱스의 데이터 분포를 추적하며, 최적이 아니면 실행 계획이 생성되지 않도록 이러한 통계를 최신 상태로 유지해야 합니다. SQL Server는 테이블의 데이터가 변경될 때 통계를 자동으로 업데이트하지만 데이터를 빠르게 변경하려면 더 자주 업데이트해야 할 수 있습니다. 최적화 프로그램은 데이터베이스의 호환성 수준, 통계 기반 행 예측 및 사용 가능한 인덱스를 포함하여 계획을 작성할 때 많은 요소를 고려합니다.

사용자가 데이터베이스 엔진에 쿼리를 제출하면 다음 프로세스가 수행됩니다.

  1. 쿼리는 적절한 구문을 위해 구문 분석되며 올바른 경우 데이터베이스 개체의 구문 분석 트리가 생성됩니다.
  2. 그런 다음 구문 분석 트리는 바인딩을 위해 Algebrizer 라는 데이터베이스 엔진 구성 요소에 입력됩니다. 이 단계에서는 쿼리의 열과 개체가 있는지 확인하고 처리 중인 데이터 형식을 식별합니다. 출력은 다음 단계의 입력 역할을 하는 쿼리 프로세서 트리입니다.
  3. 쿼리 최적화는 CPU를 많이 사용하므로 데이터베이스 엔진은 계획 캐시라는 특수 메모리 영역에서 실행 계획을 캐시합니다. 쿼리에 대한 계획이 이미 있는 경우 캐시에서 검색됩니다. 캐시의 각 쿼리에는 query_hash이라고 하는 쿼리의 T-SQL을 기반으로 생성된 해시 값이 있습니다. 엔진은 현재 쿼리에 대한 query_hash 생성하고 계획 캐시에서 일치 항목을 확인합니다.
  4. 계획이 없는 경우 쿼리 최적화 프로그램은 비용 기반 최적화 관리자를 사용하여 쿼리에 사용되는 열, 테이블 및 인덱스에 대한 통계에 따라 여러 실행 계획 옵션을 생성합니다. 출력은 쿼리 실행 계획입니다.
  5. 쿼리는 계획 캐시의 실행 계획 또는 이전 단계에서 생성된 새 계획을 사용하여 실행됩니다. 출력은 쿼리의 결과입니다.

참고

쿼리 프로세서의 작동 방식에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

예를 살펴보겠습니다. 다음과 같은 쿼리를 고려해 보세요.

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

이 예제에서 SQL Server는 FactResellerSales 테이블에 OrderDate, ShipDate 및 SalesAmount 열이 있는지 확인합니다. 이러한 열이 있는 경우 SQL Server는 쿼리에 대한 해시 값을 생성하고 계획 캐시에서 일치하는 해시 값을 검사합니다. 일치하는 해시 값이 발견되면 엔진은 계획을 다시 사용하려고 시도합니다. 일치하는 해시 값이 없으면 SQL Server는 OrderDate 및 ShipDate 열에서 사용 가능한 통계를 검사합니다.

WHERE ShipDate 열을 참조하는 절을 이 쿼리의 조건자로 알려져 있습니다. ShipDate 열을 포함하는 비클러스터형 인덱스가 있는 경우 비용이 클러스터형 인덱스에서 데이터를 검색하는 것보다 낮으면 SQL Server에서 계획에 포함할 가능성이 높습니다. 그런 다음 최적화 프로그램은 사용 가능한 옵션에서 가장 낮은 비용 계획을 선택하고 쿼리를 실행합니다.

쿼리 계획은 일련의 관계형 연산자를 결합하여 데이터를 검색하고 예상 행 수와 같은 정보를 캡처합니다. 실행 계획의 또 다른 요소는 메모리 부여라고 하는 데이터 조인 또는 정렬과 같은 작업에 필요한 메모리입니다. 메모리 부여는 통계의 중요성을 강조 표시합니다. 실제로 100을 반환할 때 SQL Server에서 연산자가 10,000,000개의 행을 반환할 것으로 예상하는 경우 더 큰 메모리 부여가 쿼리에 할당됩니다. 너무 큰 메모리 부여로 인해 두 가지 문제가 발생할 수 있습니다. 먼저 SQL Server가 많은 양의 메모리를 RESOURCE_SEMAPHORE 할당할 때까지 대기 중임을 나타내는 쿼리가 대기 중일 수 있습니다. SQL Server는 기본적으로 실행 전에 쿼리 비용의 25배(초) 기간을 최대 24시간까지 대기하는 것으로 설정합니다. 둘째, 쿼리가 실행될 때 사용 가능한 메모리가 충분하지 않으면 tempdb로 분산되어 메모리에서 작동하는 것보다 느립니다.

또한 실행 계획은 데이터베이스 호환성 수준, 병렬 처리 수준 및 쿼리가 매개 변수화된 경우 제공된 매개 변수와 같은 쿼리에 대한 다른 메타데이터를 저장합니다.

쿼리 계획은 그래픽 표현 또는 텍스트 기반 형식으로 볼 수 있습니다. 텍스트 기반 옵션은 SET 명령을 사용하여 호출되며 현재 연결에만 적용됩니다. 이러한 계획은 T-SQL 쿼리를 실행할 수 있는 모든 위치에서 볼 수 있습니다.

대부분의 DBA는 계획의 모양을 포함하여 전체 계획을 볼 수 있기 때문에 그래픽 계획을 선호합니다. 그래픽 쿼리 계획을 보고 저장하는 방법에는 여러 가지가 있습니다. 이 용도의 가장 일반적인 도구는 SQL Server Management Studio입니다. 또한 그래픽 실행 계획 보기를 지원하는 타사 도구도 있습니다.

실행 계획에는 세 가지 유형이 있습니다.

예상 실행 계획

이 유형의 실행 계획은 쿼리 최적화 프로그램에서 생성됩니다. 쿼리 메모리 부여의 메타데이터 및 크기는 쿼리 컴파일 시 데이터베이스에 있는 통계의 추정치를 기반으로 합니다. 텍스트 기반 예상 계획을 보려면 쿼리를 실행하기 전에 명령을 SET SHOWPLAN_ALL ON 실행합니다. 쿼리를 실행하면 실행 계획의 단계가 표시되지만 쿼리는 실행되지 않으며 결과가 표시되지 않습니다. SET 옵션은 OFF로 설정할 때까지 계속 적용됩니다.

실제 실행 계획

이 유형의 계획은 예상 계획과 동일합니다. 그러나 쿼리에 대한 실행 컨텍스트도 포함됩니다. 이 컨텍스트에는 예상 및 실제 행 수, 실행 경고, 실제 병렬 처리 수준(사용된 프로세서 수) 및 실행 중에 사용된 경과 및 CPU 시간이 포함됩니다. 텍스트 기반 실제 계획을 보려면 쿼리를 실행하기 전에 명령을 SET STATISTICS PROFILE ON 실행합니다. 쿼리가 실행되고 계획과 결과가 모두 표시됩니다.

활성 쿼리 통계

이 계획 보기 옵션은 예상 계획과 실제 계획을 연산자를 통해 실행 진행률을 표시하는 애니메이션 플랜으로 결합합니다. 매초마다 새로 고쳐 연산자를 통해 진행 중인 실제 행 수를 표시합니다. 라이브 쿼리 통계의 또 다른 이점은 연산자에서 운영자로의 전달을 보여 주며 성능 문제를 해결하는 데 도움이 될 수 있다는 것입니다. 이 유형의 플랜은 애니메이션 효과를 주므로 그래픽 계획으로만 사용할 수 있습니다.