컴퓨토피아

SQL Server 쿼리 성능 최적화

 데이터베이스 서버를 최적화할 때는 개별 쿼리의 성능을 튜닝해야 합니다. 이것은 하드웨어 및 소프트웨어 구성처럼 성능에 영향을 미치는 서버의 여타 다른 측면을 튜닝하는 것만큼, 나아가 오히려 그보다 더 중요할 수 있습니다.

 데이터베이스 서버의 하드웨어가 가장 강력한 것이라 해도, 아주 약간의 쿼리가 잘못 동작하는 것만으로 성능에 부정적 영향이 야기될 수 있습니다.

 실제로, 잘못된 쿼리 또는 일명 '제어 불능 쿼리' 하나로도 데이터베이스에 심각한 성능 문제를 야기할 수 있습니다. 역으로, 비용이 가장 높거나 가장 자주 실행되는 쿼리 집합을 튜닝하는 것으로도 데이터베이스의 전반적 성능이 크게 향상될 수 있습니다. 이 글에서는 서버에서 비용이 가능 높고 가장 성능이 나쁜 쿼리를 식별, 튜닝할 때 활용할 수 있는 몇 가지 기법을 살펴보겠습니다.

 

실행 계획의 분석  

개별 쿼리를 튜닝할 때는 일반적으로 그 쿼리의 실행 계획을 살펴보는 것부터 시작합니다. 실행 계획에는 SQL Server TM 에서 쿼리를 실행하여 원하는 결과 집합을 산출하기 위해 수행할 물리적, 논리적 연산의 순서가 기술됩니다. 실행 계획은 쿼리 처리의 최적화 단계에서 쿼리 최적화 프로그램이라는 데이터베이스 엔진 구성 요소에 의해 만들어지며, 이 작업에는 쿼리에 사용되는 검색 조건자, 관련된 테이블과 해당 조인 조건, 반환된 열의 목록, 데이터에 대한 효율적인 액세스 경로의 역할을 할 수 있는 유용한 인덱스의 존재 등 다양한 요소가 고려됩니다. 

복잡한 쿼리의 경우, 모든 가능한 순열의 수가 매우 클 수 있으므로 쿼리 최적화 프로그램은 모든 가능성을 평가하지 않고 주어진 쿼리에 '충분한' 계획을 찾게 됩니다. 이것은 완벽한 계획을 찾는 것이 항상 가능하지는 않기 때문이며, 그것이 가능하더라도 완벽한 계획을 찾기 위해 모든 가능성을 평가하는 데 따르는 비용이 그로 인한 성능상의 이득을 쉽게 상회할 정도로 크기 때문입니다. DBA의 관점에서 볼 때, 프로세스와 한계를 이해하는 것이 중요합니다.

쿼리의 실행 계획을 검색하는 방법은 여러 가지가 있습니다.

• Management Studio에서 제공하는 실제 실행 계획 표시 및 예상 실행 계획 표시 기능은 계획을 그래픽 방식으로 표현합니다. 이 기능들은 직접 검사에 가장 적합한 솔루션이며 실행 계획의 표시와 분석에 압도적으로 가장 자주 사용되는 방식입니다. 이 글에서는 이러한 방식으로 생성된 그래픽 계획을 사용하여 예제를 설명하겠습니다.
• SHOWPLAN_XML 및 SHOWPLAN_ALL과 같은 다양한 SET 옵션은 실행 계획을 특수 스키마를 사용하여 계획을 설명하는 XML 문서 또는 실행 계획의 각 작업에 대한 텍스트 설명이 있는 행 집합으로 반환합니다.
• Showplan XML과 같은 SQL Server 프로파일러 이벤트 클래스를 이용하면 추적을 통해 수집된 구문의 실행 계획을 모을 수 있습니다.

실행 계획의 XML 표현은 사람이 읽기 쉬운 형식은 아니지만, 이 옵션을 이용하면 실행 계획을 분석하여 성능 문제의 징후와 최적화되지 않은 계획을 조사하는 프로시저와 유틸리티를 만들 수 있습니다. XML 기반 표현 역시 확장명이 .sqlplan인 파일로 저장한 다음 Management Studio에서 열어 그래픽 표현으로 만들 수 있습니다. 또한 이 파일들을 저장하여 나중에 분석할 수 있으므로, 분석할 때마다 실행 계획을 다시 만들 필요가 없습니다. 이 점은 시간의 흐름에 따라 계획이 어떻게 변했는지 비교하고자 할 때 특히 유용합니다.

 

예상 실행 비용 

실행 계획에 대해 가장 먼저 이해해야 할 것은 그 생성 방식입니다. SQL Server는 비용 기반 쿼리 최적화 프로그램을 사용합니다. 즉, 예상 비용이 가장 낮은 실행 계획을 생성하려고 합니다. 이 예상은 최적화 프로그램이 쿼리에 관련된 각 테이블을 평가할 때 제공되는 데이터 분포 통계에 근거합니다. 그와 같은 통계가 누락되거나 너무 오래된 것이라면, 쿼리 최적화 프로그램이 쿼리 최적화에 필요한 중요 정보를 확보하지 못한 셈이므로 예상이 맞게 않게 됩니다. 이러한 경우 최적화 프로그램에서는 다양한 계획의 실행 비용을 과대 평가 또는 과소 평가하여 최적이 아닌 계획을 선택하게 됩니다. 

예상 실행 비용에 대한 몇 가지 잘못된 가정이 일반적으로 통용되고 있습니다. 특히 예상 실행 비용이 쿼리의 예상 실행 시간을 알 수 있는 좋은 지표이며 이 예상치를 통해 양호한 계획과 잘못된 계획을 구분할 수 있다고 생각하는 경우가 많습니다. 그것은 사실이 아닙니다. 첫째, 예상 비용을 나타내는 단위는 무엇이며 실행 시간과 직접적 관련이 있는지 여부는 충분히 문서화되어 있습니다.

둘째, 이것은 예상치이며 맞지 않을 수 있으므로 예상 비용이 더 높은 계획이 CPU, I/O, 실행 시간 측면에서 훨씬 더 효율적인 경우도 있습니다. 특히 테이블 변수를 포함하는 쿼리에서 이런 일이 자주 발생합니다. 테이블 변수에 대한 통계는 없기 때문에 쿼리 최적화 프로그램은 테이블 변수 하나에 실제로 행이 여러 개 들어 있더라도 하나만 포함되어 있다고 가정합니다. 따라서 쿼리 최적화 프로그램은 부정확한 예상치를 기반으로 계획을 선택하는 것입니다. 그러므로 쿼리의 실행 계획을 비교할 때는 예상 쿼리 비용 하나만을 기준으로 해서는 안 됩니다. 대신 STATISTICS I/O 및 STATISTICS TIME 옵션의 결과를 분석에 포함시켜 I/O 및 CPU 시간의 측면에서 그 실행의 실제 비용을 파악해야 합니다. 

병렬 계획이라는 특수한 유형의 실행 계획을 언급할 필요가 있습니다. CPU가 두 개 이상인 서버에서 쿼리를 실행하고 있고 이 쿼리가 병렬화가 가능하다면 병렬 계획을 선택할 수 있습니다. 일반적으로, 쿼리 최적화 프로그램은 비용이 설정 가능한 특정 임계값을 초과하는 쿼리에 대해서만 병렬 계획을 고려합니다. 여러 병렬 스레드 실행을 관리하기 위한 오버헤드(즉, 여러 스레드에 걸친 작업 배분, 동기화 수행, 결과 수집 등)로 인해 병렬 계획의 실행 비용은 더 높으며 예상 비용에 이러한 부분이 반영됩니다. 그렇다면 비용이 더 높은 병렬 계획을 사용하는 이유는 무엇일까요? 병렬 계획은 여러 CPU의 처리 능력을 활용할 수 있기 때문에 표준 계획보다 결과를 빨리 산출합니다. 가용 자원과 다른 쿼리로부터의 동시 로드 등의 변수들을 포함한 구체적 시나리오에 따라 병렬 계획이 더 바람직할 수 있습니다. 그와 같은 경우에는 쿼리 중 어느 것이 병렬 계획을 산출할 수 있는지, 그리고 각각이 몇 개의 CPU를 활용할 수 있는지 등을 제어해야 합니다. 이렇게 하려면 서버 수준에서 최대 병렬 처리 수준 옵션을 설정하고 필요에 따라 OPTION(MAXDOP n)을 통해 개별 쿼리 수준에서 이를 재정의하면 됩니다

 

실행 계획의 분석  

이제 간단한 쿼리와 그 실행 계획, 그리고 그 성능 향상을 위한 몇 가지 방법을 살펴보겠습니다. SQL Server 2005의 Adventure Works 예제 데이터베이스에서 실제 실행 계획 포함 옵션을 켜고 Management Studio를 사용하여 이 쿼리를 실행한다고 하겠습니다.  

SELECT c.CustomerID, SUM(LineTotal) FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID GROUP BY c.CustomerID

 그 결과로 생성된 실행 계획을 그림 1 에서 볼 수 있습니다. 이 간단한 쿼리는 Adventure Works의 각 고객들이 낸 주문의 총량을 계산하고 있습니다. 이 실행 계획을 보면 데이터베이스 엔진이 어떻게 쿼리를 처리하고 결과를 산출하는지 알 수 있습니다. 그래픽 실행 계획은 위에서 아래로, 오른쪽에서 왼쪽으로 읽어야 합니다. 각 아이콘은 수행된 논리적/물리적 작업을 나타내며 화살표는 작업 사이의 데이터 흐름을 나타냅니다. 화살표의 두께는 작업 사이를 오가는 행의 수를 나타냅니다. 화살표가 두꺼울수록 포함된 행이 더 많습니다. 포인터를 연산자 아이콘 중 하나 위로 올려 놓으면 노란색 도구 설명( 그림 2 에서 보이는 것과 유사한 것)을 통해 해당 작업의 세부 내용이 표시됩니다 

그림 1 샘플 실행 계획

 
그림 2 연산에 대한 세부 정보

 각 연산자를 살펴보면 수행된 절차의 순서를 분석할 수 있습니다.

1. 데이터베이스 엔진은 Sales.Customer 테이블에서 Clustered Index Scan 작업을 수행하고 해당 테이블의 모든 행에 대한 CustomerID 열을 반환합니다.
2. 그리고 Sales.SalesOrderHeader 테이블의 인덱스 중 하나에 대해 Index Scan(비클러스터형 인덱스 스캔을 의미함)을 수행합니다. 이것은 CustomerID 열의 인덱스이지만 또한 암시적으로 SalesOrderID 열(테이블 클러스터링 키)을 포함합니다. 이 두 열 모두의 값이 스캔에 의해 반환됩니다.
3. 두 스캔의 결과는 Merge Join 물리 연산자를 이용하여 CustomerID 열에서 조인됩니다. 이것은 논리적 조인 작업을 수행할 수 있는 세 가지 물리적 방법 가운데 하나입니다. 빠르지만 조인된 열에서 두 입력 모두 정렬되어야 합니다. 이 경우, 두 스캔 연산 모두 이미 CustomerID에서 정렬된 행들을 반환했으므로 추가로 정렬 연산을 수행할 필요가 없습니다.
4. 다음으로, 데이터베이스 엔진은 Sales.SalesOrderDetail 테이블에서 클러스터형 인덱스의 스캔을 수행하고 이 테이블의 모든 행으로부터 네 열(SalesOrderID, OrderQty, UnitPrice, UnitPriceDiscount)의 값을 검색합니다. 이 작업에서는 123,317개의 행이 반환될 것으로 예상되었으며 그림 2 의 예상 행 수 및 실제 행 수 속성에서 보이듯이 실제로도 그만큼 반환되었습니다. 따라서 이 예상은 매우 정확했습니다.
5. Clustered Index Scan으로 생성된 행은 첫 번째 Compute Scalar 연산자로 전달되어 수식에 포함된 OrderQty, UnitPrice 및 UnitPriceDiscount 열을 기반으로 각 행에 대해 계산 열 LineTotal의 값을 계산할 수 있게 됩니다.
6. 두 번째 Compute Scalar 연산자는 계산 열 수식에서 요구하는 대로 ISNULL 함수를 이전 계산의 결과에 적용합니다. 그에 따라 LineTotal 열의 계산이 완료되어 SalesOrderID 열과 함께 다음 연산자로 반환됩니다.
7. 3단계의 Merge Join 연산자의 출력은 Hash Match 물리 연산자를 사용하여 6단계의 Compute Scalar 연산자의 출력과 조인됩니다.
8. 그러면 또 하나의 Hash Match 연산자가, CustomerID 열 값과 LineTotal 열의 계산된 SUM 합에 의해 Merge Join으로부터 반환된 그룹 행에 적용됩니다.
9. 마지막 노드인 SELECT는 물리적 또는 논리 연산자가 아니라 전체 쿼리 결과와 비용을 나타내는 자리 표시자입니다.

필자의 노트북에서 이 실행 계획의 예상 비용은 3,31365로 나타났습니다( 그림 3 참고). STATISTICS I/O ON과 함께 실행했을 때 이 쿼리는 포함된 세 개의 테이블에서 총 1,388회의 논리적 읽기 작업을 수행한 것으로 보고했습니다. 각 연산자 아래에 표시되는 백분율은 전체 실행 계획의 총 예상 비용에 대한 각 개별 연산자의 비용의 비율을 나타냅니다. 그림 1 의 계획을 보면 전체 실행 계획의 총 비용 대부분이 Sales.SalesOrderDetail 테이블의 Clustered Index Scan 연산자와 2개의 Hash Match 연산자 등의 세 연산자와 관련되어 있음을 알 수 있습니다. 그러나 이들을 모두 최적화하기 전에, 쿼리에 매우 간단한 변화로도 2개의 연산자를 모두 없앨 수 있다는 점을 지적하고자 합니다.

 
그림 3 쿼리의 총 예상 실행 비용

 Sales.Customer 테이블로부터 반환된 유일한 열이 CustomerID이고 이 열은 Sales.SalesOrderHeaderTable에도 외래 키로 포함되어 있으므로, 논리적 의미 또는 쿼리에 의해 만들어진 결과를 변경하지 않고 아래와 같은 코드를 사용하여 쿼리에서 Customer 테이블을 완전히 제거할 수 있습니다.  

SELECT oh.CustomerID, SUM(LineTotal) FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID GROUP BY oh.CustomerID

 그 결과, 그림 4 와 같이 다른 실행 계획이 도출되었습니다. 

그림 4 쿼리에서 Customer 테이블을 제거한 이후의 실행 계획

 두 개의 작업(Customer 테이블의 Clustered Index Scan과 Customer와 SalesOrderHeader 사이의 Merge Join)이 완전히 제거되었으며 Hash Match 조인은 훨씬 더 효율적인 Merge Join으로 교체되었습니다. 그러나 SalesOrderHeader와 SalesOrderDetail 테이블 사이의 Merge Join을 사용하기 위해 두 테이블의 행들은 조인 열 SalesOrderID에 의해 정렬, 반환되어야 했습니다. 이를 위해 쿼리 최적화 프로그램은 Non-Clustered Index Scan을 사용하지 않고 I/O의 관점에서 더 저렴한 방법으로, SalesOrderHeader 테이블에서 Clustered Index Scan을 수행하기로 했습니다. 이것은 쿼리 최적화 프로그램이 실제 어떻게 작동하는지 보여 주는 좋은 사례입니다. 조인 작업을 수행하는 물리적 방법을 변경함으로써 발생하는 비용 절감의 크기가 Clustered Index Scan에 의해 생성되는 추가 I/O 비용보다 크므로 총 예상 실행 비용이 가장 낮기 때문에 쿼리 최적화 프로그램이 그와 같은 연산자 조합을 선택하는 것입니다. 필자의 노트북에서는 논리적 읽기 수가 증가(1,941까지)하더라도, 사용되는 CPU 시간은 실제로 더 줄었으며 이 쿼리의 예상 실행 비용은 약 13%(2,89548) 감소했습니다.  

그러면 이 쿼리의 성능을 더 높이려 한다고 가정하겠습니다. 이제 이 실행 계획에서 가장 비용이 높은 연산자가 된 SalesOrderHeader 테이블의 Clustered Index Scan을 살펴봅니다. 쿼리 실행을 위해선 이 테이블로부터 두 개의 열만 필요하므로, 그 두 개의 열만 담고 있는 비클러스터형 인덱스를 만들고, 전체 테이블의 스캔을 훨씬 적은 비클러스터형 인덱스의 스캔으로 교체합니다. 인덱스 정의의 형태는 다음과 같을 것입니다. 

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

 필자가 만든 인덱스에 계산된 열이 포함되어 있음에 유의하십시오. 이것은 항상 가능한 것이 아니고, 계산된 열의 정의에 따라 다릅니다. 이 인덱스를 만들고 동일한 쿼리를 실행한 후에, 그림 5 와 같은 새로운 실행 계획을 얻습니다. 

그림 5 최적화된 실행 계획  

SalesOrderDetail 테이블에 대한 Clustered Index Scan은 I/O 비용이 매우 낮은 비클러스터형 인덱스 스캔으로 교체되었습니다. 또한 필자의 인덱스에는 이미 계산된 LineTotal 열의 값이 포함되어 있으므로, Compute Scalar 연산자 중 하나를 제거했습니다. 이제 예상 실행 계획 비용은 2,28112이며 이 쿼리는 실행되면 1,125회의 논리적 읽기를 수행합니다.  

고객 주문 쿼리 연습   Q: 다음은 고객 주문 쿼리 연습입니다. 인덱스 정의를 생각해 보십시오. 이 쿼리의 커버 인덱스가 되려면 어느 열을 포함해야 하는지, 그리고 인덱스 정의 안의 열 순서에 따라 성능 차이가 발생할지를 생각해 보십시오.   A: 필자는 예로 든 쿼리에 대해 Sales.SalesOrderHeader 테이블에 만들 수 있는 가장 최적의 커버 인덱스를 알아내라는 과제를 냈습니다. 이를 위해 가장 유의해야 할 것은 그 쿼리가 그 테이블에서 오직 두 개의 열 CustomerID와 SalesOrderID만 사용한다는 점입니다. 필자의 설명을 주의 깊게 읽었다면 SalesOrderHeader 테이블의 경우, 언제나 이 쿼리를 커버하는 기존 인덱스가 있다는 것을 알 것입니다. 그것은 CustomerID의 인덱스이며 그것은 암시적으로, 테이블의 클러스터링 키인 SalesOrderID 열을 포함합니다. 필자는 쿼리 최적화 프로그램에서 이 인덱스를 사용하지 않기로 결정한 이유도 설명했습니다. 물론, 쿼리 최적화 프로그램에서 이 인덱스를 사용하도록 강제할 수 있지만 그 방법은 Clustered Index Scan과 Merge Join 연산자를 사용하는 기존 계획보다 효율성이 떨어질 것입니다. 그것은 여러분이 쿼리 최적화 프로그램으로 하여금, 여전히 Merge Join을 사용할 수 있도록 추가 Sort 연산을 수행하는 방법 또는 덜 효율적인 Hash Join을 사용하는 것으로 후퇴하는 방법 중 하나를 선택하도록 강제할 것이기 때문입니다. 두 옵션 모두 기존 계획보다 예상 실행 비용이 높습니다(Sort 연산을 사용하는 옵션이 특히 성능이 나쁨). 따라서 쿼리 최적화 프로그램은 강제가 없는 한 이러한 옵션을 사용하지 않을 것입니다. 그러므로 이 상황에서 Clustered Index Scan보다 성능이 좋은 유일한 인덱스는 SalesOrderID의 비클러스터형 인덱스, 즉 CustomerID입니다. 그러나 그 열의 순서가 정확해야 한다는 점에 유의해야 합니다. CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)   이 인덱스를 만든다면, 그 실행 계획에는 Clustered Index Scan 연산자보다는 Index Scan 연산자가 들어갈 것입니다. 이것은 상당한 차이가 있습니다. 이 경우, 단 두 개의 열만 포함하고 있는 비클러스터형 인덱스는 클러스터 인덱스 형태의 전체 테이블보다 훨씬 작습니다. 따라서 필수 데이터를 읽는 데 필요한 I/O가 훨씬 적습니다. 또한 이 예는 인덱스의 열 순서가 쿼리 최적화 프로그램에서 인덱스의 유용성에 큰 영향을 미치는 원리도 보여 줍니다. 열이 다수인 인덱스를 설계할 때는 이 점을 반드시 기억하시기 바랍니다.

 

커버 인덱스

SalesOrderDetail에 필자가 만든 인덱스는 소위 "커버 인덱스(covering index)"의 한 예입니다. 이것은 쿼리 실행에 필요한 모든 열을 포함하고 있는 비클러스터형 인덱스로서, Table Scan 또는 Clustered Index Scan 연산자를 사용하여 전체 테이블을 스캔할 필요가 없습니다. 이 인덱스는 테이블의 열 일부를 담고 있는, 본질적으로 테이블의 소규모 사본입니다. 쿼리에 답하는 데 필요한 열만 인덱스에 포함됩니다. 달리 말해, 쿼리를 '커버'하는 데 필요한 것만 포함됩니다.

 가장 자주 실행되는 쿼리에 대해 커버 인덱스를 만드는 것은 쿼리 조정에 사용되는 가장 쉽고 가장 흔한 기법입니다. 이 기법은 테이블에 다수의 열이 들어 있지만 그 중 일부만 쿼리에 의해 자주 참조되는 상황에 특히 효과적입니다. 하나 또는 여러 개의 커버 인덱스를 만들면, 액세스하는 데이터의 양이 훨씬 적어지므로 수반되는 I/O의 양도 줄어, 영향받는 쿼리의 성능을 대폭 높일 수 있습니다. 그러나 데이터 수정 작업(INSERT, UPDATE, DELETE) 중에는 추가 인덱스 유지라는 숨은 비용이 있습니다. 여러분의 환경, 그리고 SELECT 쿼리와 데이터 수정 사이의 비율에 따라, 이러한 인덱스 유지 관리 부담의 증가가 쿼리 성능 향상으로 충분히 정당화될 것인지 면밀히 판단해야 합니다.

 단일 열 인덱스와는 반대로 다수 열 인덱스를 만드는 것을 두려워하지 마십시오. 다수 열 인덱스는 단일 열 인덱스보다 훨씬 유용성이 높으며 쿼리 최적화 프로그램에서는 쿼리 커버를 위해 다수 열 인덱스를 더 많이 사용할 것입니다. 대부분의 커버 인덱스는 다수 열 인덱스입니다.

 이 글에서 사용한 샘플 쿼리의 경우, 여전히 향상의 여지가 있으며 SalesOrderHeader 테이블에 커버 인덱스를 배치하여 더 최적화할 수 있습니다. 이 방법은 Clustered Index Scan을 제거하고 비클러스터형 Index Scan을 사용합니다. 이것은 여러분에게 연습으로 남겨 두겠습니다. 인덱스 정의를 생각해 보십시오. 이 쿼리의 커버 인덱스가 되려면 어느 열을 포함해야 하는지, 그리고 인덱스 정의 안의 열 순서가 성능에 어떤 영향을 줄지 생각하십시오. 해답은 "고객 주문 쿼리 연습" 추가 기사에서 확인하십시오.

 인덱싱된 뷰 

예로 든 쿼리의 성능이 매우 중요하다면, 한 단계 더 나아가 쿼리의 구체화된 결과를 물리적으로 저장하는 인덱싱된 뷰를 만들 수 있습니다. 인덱싱된 뷰에는 일정한 전제 조건과 한계가 있지만, 일단 사용할 수 있다면 성능을 대폭 향상시킬 수 있습니다. 인덱싱된 뷰는 표준 인덱스보다 유지 관리 비용이 높다는 점을 기억하십시오. 따라서 사용할 상황을 신중히 판단해야 합니다. 이 경우, 인덱스 정의는 다음과 같습니다.  

CREATE VIEW vTotalCustomerOrders WITH SCHEMABINDING AS SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID GROUP BY oh.CustomerID

 그와 같은 뷰에 대한 인덱스 생성의 전제 조건인 WITH SCHEMABINDING 옵션과, 인덱스 정의에 집계 함수(이 예에서는, SUM)가 포함되어 있다면 필수적인 COUNT_BIG(*) 함수에 유의하십시오. 이 뷰를 만든 후에는 다음과 같이 그에 대한 인덱스를 만들 수 있습니다.  

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID ON vTotalCustomerOrders(CustomerID)

 이 인덱스를 생성할 때, 뷰 정의에 포함된 쿼리의 결과가 구체화되고 인덱스의 디스크에 물리적으로 저장됩니다. 그러면 기본 테이블의 모든 데이터 수정 연산이 뷰의 값을 그 정의에 따라 자동으로 업데이트한다는 점에 유의하십시오. 쿼리를 지금 다시 실행했을 때 어떤 일이 발생할지는 실행 중인 SQL Server의 버전에 따라 다릅니다. Enterprise 또는 Developer Edition의 경우, 쿼리 최적화 프로그램은 이 쿼리를 인덱싱된 뷰 정의와 자동으로 일치시키고 관련된 기본 테이블을 쿼리하는 대신 인덱싱된 뷰를 사용합니다. 그림 6 에서는 이 경우에 생성된 실행 계획을 보여 줍니다. 이 계획은 단 하나의 연산, 즉 필자가 뷰에 만든 인덱스의 Clustered Index Scan만으로 구성됩니다. 예상 실행 비용은 불과 0,09023이며 단 92회의 논리적 읽기만 수행됩니다. 

그림 6 인덱싱된 뷰를 사용할 때의 실행 계획

 다른 SQL Server 버전에서도 이러한 인덱싱된 뷰를 만들 수 있지만, 동일한 효과를 얻으려면 다음과 같이 NOEXPAND 힌트를 사용하여 직접 그 뷰를 참조하도록 쿼리를 변경해야 합니다. 

SELECT CustomerID, OrdersTotalAmt FROM vTotalCustomerOrders WITH (NOEXPAND)

 여기서 알 수 있듯이 인덱싱된 뷰는 적절히 사용될 경우 매우 강력한 기능이 될 수 있습니다. 인덱싱된 뷰는 대량의 데이터를 대상으로 집계를 수행하는 쿼리를 최적화하는 데 가장 유용합니다. Enterprise Edition에서 사용하는 경우 코드를 변경하지 않고도 많은 쿼리에 유익한 효과를 줄 수 있습니다.  

튜닝할 쿼리 식별  

튜닝할 가치가 있는 쿼리를 어떻게 알아볼 수 있을까요? 자주 실행되는 쿼리를 찾아 봅니다. 단일 실행 비용은 그리 높지 않을 수도 있지만 실행의 집계 비용은 실행 빈도가 매우 낮은 큰 쿼리의 실행 집계 비용보다 훨씬 높을 수 있습니다. 큰 쿼리는 튜닝할 필요가 없다는 말이 아니라 실행 빈도가 높은 쿼리에 먼저 집중해야 한다는 뜻입니다. 그러면 이것들을 어떻게 알아볼 수 있을까요?  

아쉽게도 가장 신뢰할 수 있는 방법은 다소 복잡하며 서버에 대해 실행된 모든 쿼리를 추적하고 용법에 따라 분류하는 작업이 포함됩니다. 즉, 다른 매개 변수 값으로 실행되었더라도 동일한 쿼리 유형을 식별할 수 있도록 자리 표시자로 교체된 실제 매개 변수 값으로 텍스트를 쿼리합니다. 쿼리 용법은 생성하기 까다롭기 때문에 이것은 복잡한 프로세스입니다. Itzik Ben-Gan은 그의 저서 Inside Microsoft SQL Server 2005: T-SQL Querying에서 CLR 사용자 정의 함수와 정규식을 이용하는 해결책을 설명했습니다.  

그리고 다소 신뢰성은 떨어지지만 훨씬 간단한 방법이 있습니다. 실행 계획 캐시에 모든 쿼리에 대해 보관된 통계치를 활용하여 동적 관리 뷰를 통해 쿼리할 수 있습니다. 그림 7 에는 가장 큰 논리적 읽기 누적 수와 함께 캐시의 20개 쿼리의 실행 계획과 텍스트를 모두 보여 주는 샘플 쿼리가 들어 있습니다. 이 쿼리는 가장 큰 논리적 읽기 수를 생성하는 쿼리를 신속히 식별하는 데 매우 편리하지만 여러 가지 제약이 있습니다. 즉, 쿼리 실행 시점에 계획이 캐시에 저장된 쿼리만 보여 줍니다. 캐시에 저장되지 않은 것이 있다면 놓치게 됩니다.

 

Figure 7 읽기 I/O의 측면에서 비용이 가장 높은 쿼리 20개 찾기

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 ORDER BY qs.total_logical_reads DESC

 이러한 성능 저하 요소를 파악하게 되면 그 쿼리 계획을 살펴보고 이 글에 설명된 인덱싱 기법 중 일부를 활용하여 그 성능을 높일 수 있는 방법을 찾아보십시오. 성공한다면, 시간을 들인 만큼 효과는 있을 것입니다. 튜닝의 재미를 느껴보세요!  

필자소개

Maciej Pilecki 는 교육, 지도, 컨설팅을 전문으로 하는 전 세계적인 기관인 Solid Quality Mentors의 Associate Mentor입니다. 그는 MCT(Microsoft Certified Trainer)이자 MVP(SQL Server Most Valuable Professional)로 SQL Server와 응용 프로그램 개발에 대한 다양한 주제로 활발하게 강의 및 강연 활동을 하고 있습니다.  

제공 : DB포탈사이트 DBguide.net