컴퓨토피아

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

SQL Server CPU 성능 최적화

 한눈에 보기:

• 데이터베이스 성능 문제 해결
• 하드웨어 원인 검토
• PerfMon을 사용하여 데이터베이스 병목 상태 추적
• 쿼리 성능 평가

데이터베이스 시스템의 성능 문제를 해결하는 작업은 매우 복잡할 수 있습니다. 문제가 어디에서 발생하는지 파악하는 것도 중요하지만 시스템이 특정 요청에 대해 현재와 같이 반응하는 이유를 알아내는 것이 더욱 중요합니다. SQL 문의 컴파일 및 재컴파일, 누락된 인덱스, 다중 스레드 작업, 디스크 병목 상태, 메모리 병목 상태, 일상적인 유지 관리 작업, ETL(추출, 변환, 로드) 작업을 비롯한 많은 요소가 데이터베이스 서버의 CPU 사용률에 영향을 줄 수 있습니다. CPU 사용률 자체가 나쁜 것은 아닙니다. 작업을 수행하는 것이야말로 CPU의 존재 이유니까요. 다시 말하지만 CPU 사용률을 적정 수준으로 유지하기 위해서는 CPU가 최적화되지 않은 코드나 느린 하드웨어에서 프로세서 주기를 낭비하는 대신 사용자가 원하는 작업을 처리하는 데 시간을 할애하도록 하는 것이 필수입니다.

 서로 다르지만 목적은 같은 두 가지 방법

 크게 봐서 CPU 성능 문제를 확인하는 방법은 두 가지입니다. 하나는 시스템 하드웨어의 성능을 검토하는 것입니다. 이 방법은 다음에 나오는 두 번째 방법에 따라 서버의 쿼리 효율성을 검토할 때 어느 부분을 조사해야 하는지 결정하는 데 도움이 됩니다. 일반적으로 두 번째 방법이 SQL Server™의 성능 문제를 파악하는 데 있어서 더 효과이지만, 쿼리 성능 문제의 원인이 어디에 있는지 정확히 알고 있는 경우가 아니면 시스템 성능부터 확인해 보는 것이 좋습니다. 대개는 두 가지 방법을 모두 사용하게 됩니다. 이제 이 두 가지 방법을 자세히 살펴보기 전에 기본적인 몇 가지 사항에 대해 알아보겠습니다.  

기본 개념 형성

하이퍼 스레딩   하이퍼 스레딩은 SQL Server에 꽤 많은 영향을 주므로 이에 대해 자세히 설명할 필요가 있습니다. 하이퍼 스레딩은 각각의 물리적 프로세서에 대해 두 개의 논리적 프로세서를 OS에 제공합니다. 하이퍼 스레딩은 기본적으로 물리적 프로세서의 시간을 빌려 씀으로써 각 프로세서의 사용률을 높입니다. 하이퍼 스레딩의 작동 방법에 대한 자세한 설명은 Intel 웹 사이트( intel.com/technology/platform-technology/hyper-threading/index.htm )를 참조하십시오.   SQL Server 시스템의 경우 DBMS에서 고유의 매우 효율적인 큐 및 스레딩을 OS에 제공하므로 CPU 사용률이 이미 높은 시스템의 경우 하이퍼 스레딩은 시스템의 물리적 CPU를 오버로드하는 기능만 하게 됩니다. SQL Server가 여러 스케줄러에서 작업을 수행하기 위해 여러 개의 요청을 큐에 놓을 때 OS는 두 개의 논리적 프로세서가 동일한 물리적 프로세서에 있는 경우에도 계속해서 만들어지는 요청에 만족하도록 물리적 프로세서에서 스레드 컨텍스트를 실제로 전환해야 합니다. 물리적 프로세서당 Context Switches/sec가 5000 이상이면 시스템의 하이퍼 스레딩을 해제하고 성능을 다시 테스트해야 합니다.   드물기는 하지만 SQL Server에서 CPU 사용률이 높은 응용 프로그램이 하이퍼 스레딩을 효과적으로 사용할 수 있는 경우도 있습니다. 프로덕션 시스템에서 변경을 구현하려면 항상 먼저 하이퍼 스레딩을 각각 설정 및 해제한 상태로 SQL Server에서 응용 프로그램을 테스트해야 합니다.

 

 최고급 듀얼 코어 프로세서의 경우 시스템의 RAM보다 성능이 뛰어나므로 결국 연결된 저장 장치보다 속도가 빨라지게 됩니다. 성능이 우수한 CPU의 처리량은 현재 최고급 DDR2 메모리보다 6배 가량 많고 최고급 DDR3 메모리보다는 2배 가량 많습니다. 일반적인 메모리 처리량은 가장 빠른 파이버 채널 드라이브의 10배 이상입니다. 다시 말해 하드 디스크는 IOPS(초당 입/출력 작업 수)만큼의 한정된 작업만 수행할 수 있으며 이 수치는 드라이브 한 개가 수행할 수 있는 초당 검색 수에 따라 결정됩니다. 공정하게 비교하자면, 엔터프라이즈 데이터베이스 시스템의 모든 저장소 요구 사항을 처리하기 위해 단 한 개의 저장소 드라이브만 사용되는 경우는 드뭅니다. 현재 사용되는 대부분의 설치 프로그램은 엔터프라이즈 데이터베이스 서버의 SAN(저장 영역 네트워크) 또는 디스크 I/O 프로세서 문제를 없애거나 최소화할 수 있는 대규모 RAID 그룹을 사용합니다. 가장 중요한 점은 설치 프로그램의 형태에 관계없이 디스크 및 메모리 병목 상태는 프로세서의 성능에 영향을 줄 수 있다는 것입니다.

 I/O 속도의 차이로 인해 디스크에서 데이터를 검색하는 경우 메모리에서 데이터를 검색할 때보다 비용이 훨씬 많이 듭니다. SQL Server의 데이터 페이지 하나는 8KB이고 익스텐트 하나는 8KB 페이지 8개로 구성되어 있으므로 64KB입니다. 이 값을 정확히 알고 있어야 하는데 그 이유는 SQL Server가 디스크에서 특정 데이터 페이지를 요청할 경우 해당 데이터 페이지뿐 아니라 데이터 페이지가 있는 전체 익스텐트가 검색되기 때문입니다. SQL Server의 경우 이렇게 비용이 많이 드는 데에는 이 외에도 많은 이유가 있지만 여기에서는 이에 대해 자세히 설명하지 않겠습니다. 이미 캐시된 데이터 페이지를 버퍼 풀에서 가져오는 데 걸리는 시간은 0.5밀리초 미만이고(성능이 최적인 경우), 디스크에서 단일 익스텐트를 검색하는 데 걸리는 시간은 2~4밀리초입니다(최적 환경의 경우). 일반적으로 성능 및 상태가 좋은 디스크 하위 시스템에서 읽는 경우 4~10밀리초가 걸릴 것으로 예상됩니다. 그리고 메모리에서 데이터 페이지를 검색하는 속도는 디스크에서 데이터 페이지를 가져오는 속도보다 4~20배가 빠릅니다.

 SQL Server는 데이터 페이지를 요청할 때 디스크 하위 시스템의 데이터 페이지를 검색하기 전에 먼저 메모리 내 버퍼 캐시를 확인합니다. 데이터 페이지가 버퍼 풀에 있으면 프로세서가 데이터를 검색한 다음 필요한 작업을 수행하는데 이를 소프트 페이지 폴트라고 합니다. 소프트 페이지 폴트는 SQL Server에 가장 이상적입니다. 이는 요청의 일부로 검색되는 데이터를 사용할 수 있으려면 데이터가 먼저 버퍼 캐시에 있어야 하기 때문입니다. 버퍼 캐시에 없는 데이터 페이지는 서버의 디스크 하위 시스템에서 검색해야 합니다. OS가 디스크에서 데이터 페이지를 검색해야 하는 경우 이를 하드 페이지 폴트라고 합니다.

 메모리 성능, 디스크 성능, CPU 성능을 연관 지어 검토할 때 처리량을 공통 분모로 정하면 전체적인 사항을 파악하는 데 도움이 됩니다. 그다지 과학적이지 않은 이 처리량이라는 용어는 유한한 파이프 안에 넣을 수 있는 데이터의 양에 대한 측정치입니다.  

방법 1:시스템 성능 

서버에 CPU 병목 상태가 있는지 확인하는 방법은 실제로는 몇 가지에 불과합니다. 또한 CPU 사용률을 높일 만한 원인도 그다지 많지 않습니다. 이러한 문제 중 몇 가지는 PerfMon이나 이와 비슷한 시스템 모니터링 도구를 사용하여 추적할 수 있고, 그 외 다른 문제는 SQL 프로파일러나 이와 비슷한 도구를 사용하여 추적할 수 있습니다. 쿼리 분석기나 SQL Server Management Studio(SSMS)를 통해 SQL 명령을 사용하는 방법도 있습니다.  

시스템 성능을 평가할 때 필자는 광범위하게 시작해서 세부적으로 들어가는 방식을 취합니다. 분명한 건 문제가 있는 영역을 파악하기 전까지는 문제를 세부적으로 조사할 수 없다는 것입니다. 먼저 PerfMon 같은 도구를 사용하여 전반적인 CPU 사용률을 평가한 후 PerfMon에서 간단하고 이해하기 쉬운 몇 가지 성능 카운터를 살펴보는 것이 좋습니다.  

가장 친숙한 성능 카운터 중 하나가 바로 % Processor Time입니다. 이 성능 카운터는 PerfMon에서 카운터 추가 창을 여는 경우 처음에 강조 표시되는 카운터입니다. % Processor Time은 프로세서가 작업을 실행하는 데 사용한 시간입니다. 최대 작동 시간 중 대부분의 시간 동안 이 값이 80% 이상일 경우 프로세서 사용률이 높은 것으로 간주됩니다. 서버가 사용률 80%에서 실행되고 있지 않을 때에도 수치가 100%에 이르는 때가 있는데 이는 일반적인 현상입니다.

 PerfMon에서 System 성능 개체에 있는 Processor Queue Length 카운터도 검토해야 합니다. Processor Queue Length는 CPU에서 작업을 대기 중인 스레드 수를 보여 줍니다. SQL Server는 데이터베이스 엔진의 스케줄러를 통해 작업을 관리합니다. 즉, SQL Server는 데이터베이스 엔진을 통해 요청을 큐에 놓고 처리합니다. SQL Server는 작업을 자체적으로 관리하므로 각 논리적 프로세서에 대해 CPU 스레드를 하나만 사용합니다. 이는 SQL Server 전용 시스템의 경우 작업을 수행하기 위해 프로세서 큐에서 대기 중인 스레드 수가 최소한으로 유지되어야 함을 의미합니다. 일반적으로 SQL Server 전용 시스템에 있는 물리적 프로세서 수의 5배를 넘지 않아야 하지만 2배 이상만 되더라도 문제가 있는 것으로 생각할 수 있습니다. DBMS가 다른 응용 프로그램과 시스템을 공유하는 서버의 경우 이 카운터를 % Processor Time 및 Context Switches/sec 성능 카운터와 함께 검토하여 다른 응용 프로그램이나 DBMS를 다른 서버로 이동할 필요가 있는지 확인해야 합니다. 컨텍스트 전환에 대해서는 나중에 간단히 설명하겠습니다.

 

높은 CPU 사용률과 함께 프로세서가 큐에 대기 중인 것을 발견할 경우 필자는 SQL Server:SQL Statistics 성능 개체에 있는 Compilations/sec와 Re-Compilations/sec 카운터를 검토합니다( 그림 1 참조). 쿼리 계획 컴파일 및 재컴파일은 시스템의 CPU 사용률을 높입니다. Re-Compilations 값은 0에 가까워야 하지만, 시스템의 추세를 주의 깊게 검토하여 서버의 일반적인 동작과 정상적인 컴파일 수를 판단하는 것이 좋습니다. 재컴파일을 피할 수 없는 경우도 있습니다. 하지만 재컴파일 횟수를 최소화하고 쿼리 계획을 다시 사용하도록 쿼리 및 저장 프로시저를 최적화하는 것은 가능합니다. SQL Server:SQL Statistics 성능 개체에 있는 Batch Requests/sec를 검토하여 이들 값과 시스템으로 들어오는 실제 SQL 문의 수를 비교해 보십시오. 초당 컴파일 및 재컴파일 횟수가 시스템으로 들어오는 일괄 요청에서 높은 비율을 차지하는 경우 이 부분을 자세히 검토해야 합니다. SQL 개발자는 자신의 코드가 이러한 유형의 시스템 리소스 문제와 왜, 그리고 어떤 식으로 관련되는지 쉽게 파악할 수 없는 경우도 있습니다. 이 기사의 뒷부분에 나오는 참조 자료를 보면 이러한 유형의 작업을 최소화하는 데 도움이 될 것입니다. 

그림 1 모니터링할 카운터 선택

 PerfMon에서 Context Switches/sec라는 성능 카운터를 확인하십시오( 그림 2 참조). 이 카운터는 대기 중인 다른 스레드 대신 작업을 수행하기 위해 SQL 스케줄러가 아닌 OS 스케줄러에서 스레드가 전환된 횟수를 보여 줍니다. 컨텍스트 전환은 대개 IIS 또는 타 공급업체 응용 프로그램 서버 구성 요소와 같은 다른 응용 프로그램과 공유되는 데이터베이스 시스템에서 더 자주 발생합니다. 필자가 Context Switches/sec에 사용하는 임계값은 서버에 있는 프로세서 수의 5,000배 정도입니다. 하이퍼 스레딩이 설정되어 있으며 CPU 사용률이 적정 수준이거나 높은 시스템의 경우 이 값 또한 높을 수 있습니다. CPU 사용률과 컨텍트스 전환 횟수 모두 정기적으로 해당 임계값을 초과할 경우 CPU 병목 상태가 있음을 나타냅니다. 이러한 현상이 정기적으로 발생할 뿐 아니라 시스템이 노후되었다면 추가 CPU나 속도가 더 빠른 CPU 구입을 고려해야 합니다. 자세한 내용은 "하이퍼 스레딩" 추가 기사를 참조하십시오.

 Figure 2 검토할 성능 카운터

성능 카운터	카운터 개체	임계값	설명
% Processor Time	Processor	> 80%	가능한 원인으로는 메모리 부족, 낮은 쿼리 계획 재사용, 최적화되지 않은 쿼리 등이 있습니다.
Context Switches/sec	System	> 5000 x 프로세서 수	가능한 원인으로는 서버의 다른 응용 프로그램, 동일한 서버에서 실행되는 둘 이상의 SQL Server 인스턴스, 하이퍼 스레딩 설정 등이 있습니다.
Processor Queue Length	System	> 5 x 프로세서 수	가능한 원인으로는 서버의 다른 응용 프로그램, 높은 컴파일 또는 재컴파일 횟수, 동일한 서버에서 실행되는 둘 이상의 SQL Server 인스턴스 등이 있습니다.
Compilations/sec	SQLServer:SQL Statistics	추세에 따라 결정	Batch Requests/sec와 비교합니다.
Re-Compilations/sec	SQLServer:SQL Statistics	추세에 따라결정	Batch Requests/sec와 비교합니다.
Batch Request/sec	SQLServer:SQL Statistics	추세에 따라결정	초당 컴파일 및 재컴파일 횟수와 비교합니다.
Page Life Expectancy	SQLServer:Buffer Manager	< 300	가능한 원인으로는 메모리 부족이 있습니다.
Lazy Writes/sec	SQLServer:Buffer Manager	추세에 따라결정	가능한 원인으로는 대량 데이터 캐시 플러시 또는 메모리 부족이 있습니다.
Checkpoints/sec	SQLServer:Buffer Manager	추세에 따라 결정	PLE 및 Lazy Writes/sec를 기준으로 검사점을 검토합니다.
Cache Hit Ratio: SQL Plans	SQLServer:Plan Cache	< 70%	낮은 계획 재사용을 나타냅니다.
Buffer Cache Hit Ratio	SQLServer:Buffer Manager	< 97%	가능한 원인으로는 메모리 부족이 있습니다.

 CPU 사용률이 높을 경우 SQL Server 지연 기록기(SQL Server 2000) 또는 리소스 모니터(SQL Server 2005)라고 하는 영역도 모니터링해야 합니다. 버퍼 및 프로시저 캐시를 플러시하는 경우 리소스 모니터라고 하는 리소스 스레드를 통해 CPU 시간이 늘어날 수 있습니다. 리소스 모니터는 유지할 페이지와 버퍼 풀에서 디스크로 플러시해야 할 페이지를 결정하는 SQL Server 프로세스입니다. 버퍼 및 프로시저 캐시의 각 페이지에는 원래 해당 페이지가 캐시에 저장될 때 사용된 리소스를 나타내는 비용이 할당됩니다. 이 비용 값은 리소스 모니터가 값을 검사할 때마다 감소합니다. 요청에서 캐시 공간을 필요로 하는 경우 각 페이지와 관련된 비용을 기준으로 페이지가 메모리에서 플러시되며, 값이 가장 낮은 페이지가 가장 먼저 플러시됩니다. 리소스 모니터 작업은 PerfMon에서 SQL Server: Buffer Manager 개체에 있는 Lazy Writes/sec 성능 카운터를 통해 추적할 수 있습니다. 시스템의 일반적인 임계값을 확인하려면 이 값이 어떻게 변하는지 추적해야 합니다. 이 카운터는 메모리가 부족한지 여부를 확인하기 위해 일반적으로 Page Life Expectancy 및 Checkpoints/sec 카운터와 함께 검토합니다.

 PLE(Page Life Expectancy) 카운터는 메모리가 부족한지 확인하는 데 유용합니다. PLE 카운터는 데이터 페이지가 버퍼 캐시에 머무르는 기간을 보여 줍니다. 이 카운터의 업계 허용 임계값은 300초입니다. 이 값이 일정 기간 동안 평균 300초 미만이면 데이터 페이지가 메모리에서 너무 자주 플러시되고 있다는 것을 나타냅니다. 이 경우 리소스 모니터의 작업량이 많아지고 이에 따라 프로세서가 감당해야 할 작업량도 늘어납니다. PLE 카운터는 Checkpoints Pages/sec 카운터와 함께 검토해야 합니다. 시스템에서 검사점이 발생하면 버퍼 캐시의 더티 데이터 페이지가 디스크로 플러시되고 이로 인해 PLE 값이 감소합니다. 리소스 모니터 프로세스는 실제로는 이러한 페이지를 디스크에 플러시하는 메커니즘입니다. 따라서 이러한 검사점 작업 중에는 Lazy Writes/sec 값이 증가하는 현상도 나타납니다. 검사점이 완료된 즉시 PLE 값이 증가하는 경우 일시적인 현상이므로 무시해도 됩니다. 하지만 PLE 값이 해당 임계값보다 낮은 현상이 정기적으로 발생할 경우에는 메모리를 추가하여 문제를 해결하는 동시에 CPU에서 사용할 수 있도록 일부 리소스를 해제하는 것이 좋습니다. 이들 카운터는 모두 SQL Server: Buffer Manager 성능 개체에 있습니다.

 방법 2: 쿼리 성능

SP 추적 SQL Server 응용 프로그램을 추적하는 경우 추적에 사용되는 저장 프로시저에 친숙해지면 매우 유용합니다. 추적을 위해 GUI 인터페이스(SQL Server 프로파일러)를 사용할 경우 시스템 로드가 15~25% 정도 늘어날 수 있습니다. 추적에 저장 프로시저를 사용하면 이러한 시스템 로드를 절반으로 줄일 수 있습니다. 시스템의 특정 부분에서 병목 상태가 발생하고 있음을 알고 있고 서버에서 현재 이러한 문제를 일으키는 SQL 문이 어떤 것인지 확인하려는 경우 아래와 같은 쿼리를 실행해 보십시오. 이 쿼리를 실행하면 개별 문과 이러한 문이 현재 사용 중인 리소스는 물론 성능 향상을 위해 검토할 필요가 있는 문을 모두 함께 볼 수 있습니다. SQL Trace에 대한 자세한 내용은 msdn2.microsoft.com/ms191006.aspx 를 참조하십시오.   SELECT substring(text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,qs.plan_generation_num as recompiles ,qs.execution_count as execution_count ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time ,qs.total_worker_time as cpu_time ,qs.total_logical_reads as reads ,qs.total_logical_writes as writes FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st LEFT JOIN sys.dm_exec_requests r ON qs.sql_handle = r.sql_handle ORDER BY 3 DESC

 
SQL Server로 새 쿼리가 제출될 때 쿼리 계획이 평가, 최적화 및 컴파일되어 프로시저 캐시에 저장됩니다. 서버로 쿼리가 제출될 때마다 요청과 일치하는 쿼리 계획이 있는지 확인하기 위해 프로시저 캐시가 검토됩니다. 일치하는 쿼리 계획이 없으면 SQL Server에서 새 쿼리 계획을 만들게 되는데 이 경우 비용이 들 수 있습니다.  

다음은 T-SQL CPU 최적화를 위해 고려해야 할 몇 가지 사항입니다.

• 쿼리 계획 재사용
• 컴파일 및 재컴파일 횟수 줄이기
• 정렬 작업
• 잘못된 조인
• 누락된 인덱스
• 테이블/인덱스 검사
• SELECT 및 WHERE 절의 함수 사용
• 다중 스레드 작업

이제 이러한 사항에 대해 간단히 살펴보겠습니다. SQL Server는 일반적으로 메모리와 디스크 모두에서 데이터를 가져옵니다. 또한 사용자가 단일 데이터 페이지로만 작업하는 경우는 거의 없습니다. 오히려 관련 데이터를 전체적으로 볼 수 있도록 여러 개의 작은 쿼리를 실행하거나 테이블을 조인하는 등 한 레코드에 대해 응용 프로그램의 여러 부분이 작동하는 경우가 많습니다. OLAP 환경에서는 지역별 판매 보고서를 작성하기 위해 데이터를 통합, 취합 및 요약할 수 있도록 응용 프로그램이 한두 개의 테이블에서 수백만 개의 행을 가져올 수도 있습니다. 이 경우 데이터가 메모리에 있으면 데이터 반환 속도를 밀리초 단위로 측정할 수 있지만, 동일한 데이터를 RAM이 아닌 디스크에서 검색하는 경우에는 밀리초가 분으로 바뀔 수 있습니다. 

첫 번째 예는 트랜잭션 양이 많은 경우로 응용 프로그램에 따라 계획 재사용 여부가 달라집니다. 이 경우 낮은 계획 재사용으로 인해 SQL 문의 컴파일 횟수가 대폭 늘어나 결국 CPU 처리량이 엄청나게 많아질 수 있습니다. 두 번째 예의 경우 대량의 새 데이터 페이지를 위한 공간을 만들기 위해 기존 데이터가 버퍼 캐시에서 지속적으로 플러시되어야 하므로 시스템 리소스 사용률이 높아지고 이로 인해 시스템 CPU의 처리량이 많아질 수 있습니다.  

배송 상자 정보를 검색하기 위해 아래와 같은 SQL 문이 15분 동안 2000번 실행되는, 트랜잭션 양이 매우 많은 시스템이 있다고 가정해 보십시오. 쿼리 계획을 다시 사용하지 않을 경우 문당 개별 실행 시간이 450밀리초 정도일 것입니다. 쿼리를 처음 실행한 후 다음부터는 동일한 쿼리 계획을 사용한다면 이후의 쿼리는 실행되는 데 2밀리초 정도가 소요되어 총 실행 시간이 5초 정도로 줄어듭니다. 

USE SHIPPING_DIST01; SELECT Container_ID ,Carton_ID ,Product_ID ,ProductCount ,ModifiedDate FROM Container.Carton WHERE Carton_ID = 982350144;

  쿼리 계획 재사용은 트랜잭션 양이 많은 시스템에서 성능을 최적화하는 데 매우 중요한 요소이며 대부분의 경우 쿼리나 저장 프로시저를 매개 변수화함으로써 가능합니다. 다음은 쿼리 계획 재사용과 관련된 유용한 정보를 제공하는 리소스입니다.

• SQL Server 2005의 일괄 컴파일, 재컴파일 및 캐싱 계획 문제(microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
• SQL Server 저장 프로시저를 최적화하여 재컴파일 방지(sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
• SQL Server 2000의 쿼리 재컴파일(msdn2.microsoft.com/aa902682.aspx)

SQL Server 2005 DMV(동적 관리 뷰)도 많은 유용한 정보를 제공합니다. CPU 사용률이 높을 경우 CPU가 적절하게 사용되고 있는지 여부를 확인하는 데 사용할 수 있는 두 가지 DMV가 있습니다.

 그 중 하나가 바로 데이터베이스 관리자가 SQL Server가 사용하는 각 리소스 유형 또는 함수를 확인하는 데 사용하는 sys.dm_os_wait_stats로, 이 DMV는 시스템이 해당 리소스로 인해 대기해야 하는 시간을 측정합니다. 이 DMV의 카운터는 누적됩니다. 따라서 미해결된 문제에 대한 데이터를 검토한 후에는 DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) 명령을 실행하여 모든 카운터를 다시 설정해야 시스템의 여러 영역에 영향을 주는 리소스가 무엇인지 정확하게 검토할 수 있습니다. sys.dm_os_wait_stats DMV는 SQL Server 2000의 데이터베이스 일관성 검사를 위한 DBCC SQLPERF(WAITSTATS) 명령과 기능이 같습니다. 다른 대기 유형에 대한 자세한 내용은 SQL Server 온라인 설명서(msdn2.microsoft.com/ ms179984.aspx)를 참조하십시오. 

모든 것이 최적으로 실행될 때조차도 시스템에서 대기 상태가 발생하는 것은 일반적입니다. 하지만 이러한 대기 상태가 CPU 병목 상태로 인해 발생하는지 여부는 확인해야 합니다. 신호 대기 시간은 전반적인 대기 시간에 비해 가능한 한 낮아야 합니다. 특정 리소스가 프로세서 리소스를 대기하는 시간은 총 대기 시간에서 신호 대기 시간을 빼면 알 수 있으며 이 값은 총 대기 시간의 약 20%를 넘으면 안 됩니다.  

sys.dm_exec_sessions DMV는 SQL Server에서 열려 있는 모든 세션을 보여 줍니다. 이 DMV는 각 세션의 성능과 각 세션이 시작된 이후 세션에서 수행된 모든 작업을 자세히 보여 줍니다. 여기에는 세션이 대기한 총 대기 시간, 총 CPU 사용량, 메모리 사용량, 읽기 및 쓰기 수가 포함됩니다. 이 DMV는 로그인, 로그인 시간, 호스트 컴퓨터, 세션에서 마지막으로 SQL Server 요청이 이루어진 시간도 보여 줍니다. CPU 사용률이 높을 경우 가장 먼저 검토해야 할 항목 중 하나가 바로 sys.dm_exec_sessions인데 이는 이 DMV가 활성 세션에 대한 정보만 제공하기 때문입니다.  

CPU 사용률이 높은 세션부터 먼저 검토하십시오. 작업을 수행 중이었던 응용 프로그램과 사용자를 확인한 후 자세한 분석을 시작하는 것이 좋습니다. sys.dm_exec_sessions와 sys.dm_exec_requests DMV를 함께 사용하면 sp_who 및 sp_who2 저장 프로시저를 통해 얻을 수 있는 정보의 대부분을 볼 수 있습니다. sql_handle 열을 통해 이 데이터를 sys.exec_sql_text DMF(동적 관리 함수)와 조인하면 세션에서 현재 실행 중인 쿼리를 확인할 수 있습니다. 그림 3 의 코드 조각은 이러한 데이터를 모두 가져와 서버의 현재 상황을 확인하는 방법을 보여 줍니다. 

Figure 3 서버 작업 확인

SELECT es.session_id ,es.program_name ,es.login_name ,es.nt_user_name ,es.login_time ,es.host_name ,es.cpu_time ,es.total_scheduled_time ,es.total_elapsed_time ,es.memory_usage ,es.logical_reads ,es.reads ,es.writes ,st.text FROM sys.dm_exec_sessions es LEFT JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st WHERE es.session_id > 50 -- < 50 system sessions ORDER BY es.cpu_time DESC

 이 문은 집중적으로 조사해야 할 응용 프로그램이 어떤 것인지 파악하는 데 도움이 됩니다. 필자의 경우 한 응용 프로그램 내의 모든 세션에 대한 CPU, 메모리, 읽기 수, 쓰기 수, 논리적 읽기 수를 비교한 결과 CPU 리소스가 다른 리소스보다 사용률이 훨씬 높다는 것을 알게 되었고 이때부터 해당 SQL 문을 집중적으로 분석하기 시작했습니다.  

응용 프로그램에 대한 SQL 문을 과거부터 현재까지 시간별로 모두 추적하기 위해 필자는 SQL Server 추적을 사용했습니다. SQL Server 프로파일러 도구나 추적 시스템 저장 프로시저를 통해 SQL Server 추적에 액세스하면 시스템의 현 상황을 쉽게 평가할 수 있습니다. 이 항목에 대한 자세한 내용은 "SP 추적" 추가 기사를 참조하십시오. 프로파일러에서 CPU 사용률이 높고 해시 및 정렬 경고, 캐시 누락 및 기타 빨간색 플래그가 있는 문은 반드시 검토해야 합니다. 이런 식으로 특정 SQL 문이나 리소스 사용률이 높은 특정 시간대로 범위를 좁힐 수 있습니다. 프로파일러를 사용하면 SQL 문 텍스트, 실행 계획, CPU 사용량, 메모리 사용량, 논리적 읽기, 쓰기, 쿼리 계획 캐시, 재컴파일, 캐시에서의 쿼리 계획 제거, 캐시 누락, 테이블 및 인덱스 검사, 누락된 통계를 비롯한 다양한 이벤트를 추적할 수 있습니다.  

필자의 경우 sp_trace 저장 프로시저나 SQL Server 프로파일러를 사용하여 데이터를 수집한 후에는 문제 발생 후의 추적 데이터로 채워지거나 데이터베이스에 쓰도록 추적을 설정하여 추적 데이터로 채워진 데이터베이스를 사용합니다. 문제 발생 후 데이터베이스를 채우는 작업은 SQL Server 시스템 함수 fn_trace_getinfo를 사용하여 수행할 수 있습니다. 이 방법을 사용할 경우 다양한 방법으로 데이터를 쿼리하고 정렬하여 CPU를 가장 많이 사용하거나 읽기 수가 가장 많은 SQL 문을 파악할 수 있을 뿐만 아니라 발생한 재컴파일 횟수를 계산하는 등의 많은 작업을 수행할 수 있습니다. 다음 예제에서는 이 함수를 사용하여 프로파일러 추적 파일이 있는 테이블을 로드하는 방법을 보여 줍니다. 기본적으로 해당 추적에 대한 모든 추적 파일이 생성된 순서대로 로드됩니다.  

SELECT * INTO trc_20070401 FROM fn_trace_gettable('S:\Mountpoints\TRC_20070401_1.trc', default); GO

 결론

 지금까지 살펴보았듯이 CPU 사용률이 높다고 해서 반드시 CPU 병목 상태가 있는 것은 아닙니다. 높은 CPU 사용률에만 치중하다 보면 다른 응용 프로그램이나 하드웨어에서 발생하는 수많은 병목 상태를 놓칠 수 있습니다. 다른 카운터가 정상인데 CPU 사용률만 높을 경우 먼저 시스템 내에서 원인을 알아본 다음 CPU를 추가로 구입하거나 SQL 코드를 최적화하는 등의 해결 방법을 찾는 것이 좋습니다. 어떤 방법을 취하든 포기하지 마십시오. 약간의 실천과 탐구심을 갖고 이 기사에서 제시하는 방법을 적절히 활용한다면 'SQL Server에서의 CPU 사용률 최적화'라는 실행 계획을 실현할 수 있을 것입니다.

 필자소개

Zach Nichter 는 10년 넘게 SQL Server 관련 분야에 종사해 온 SQL Server 전문가로, DBA, 팀장, 관리자 및 컨설턴트를 비롯한 다양한 SQL Server 지원 업무를 수행해 왔습니다. 현재 Levi Strauss & Co.에서 DBA 설계자로 근무하고 있는 Zach는 주로 SQL Server 성능, 모니터링, 아키텍처 및 기타 전략적 이니셔티브에 관한 업무를 담당하고 있으며 www.sqlcatch.com 에 게시된 동영상 블로그의 저자이기도 합니다.

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

확장 저장 프로시저는 SQL Server가 동적으로 로드하고 실행할 수 있는 동적 연결 라이브러리(DLL)이다. 확장 저장 프로시저는 SQL Server 주소 공간에서 실행되며, SQL Server 개방형 데이터 서비스(Open Data Services) API를 사용하여 만들 수 있다. 쿼리 분석기에서 일반 저장 프로시저와 같이 확장 저장 프로시저를 실행할 수 있다. 확장 저장 프로시저를 사용하면 SQL Server의 기능을 확장할 수 있다. SQL Server와 함께 제공되거나 C 나 C++과 같은 프로그래밍 언어로 직접 작성한 많은 저장 프로시저를 통해 많은 이점을 취할 수 있다.

이 글에서는 몇몇 문서화 되지 않은 확장 저장 프로시저에 대해 이야기를 할 것이다. 이 확장 프로시저들은 SQL Server 7.0이나 SQL Server 2000에서 동작한다.

■ sp_MSgetversion
이 확장 저장 프로시저는 SQL Server의 현재 버전을 반환한다. SQL Server 버전을 확인하기 위해서는 아래와 같이 실행할 수 있다.

EXEC master..sp_MSgetversion
 -- 결과 Character_Value
-------------------- ----------- -----------
                  8.00.760             1           2 
-- 참고: SQL Server의 현재 버전을 구하는 보다 보편적인 방법은
-- 다음 SELECT 구문을 이용하는 것이다.
SELECT @@version

■ xp_dirtree
이 확장 저장 프로시저는 특정 폴더 하위에 존재하는 모든 폴더의 목록을 반환한다. C:\MSSQL7 폴더 하위에 존재하는 모든 폴더의 목록을 구하기 위해서는 아래와 같이 실행하면 된다.

EXEC master..xp_dirtree 'C:\MSSQL7'

■ xp_enum_oledb_providers
이 확장 저장 프로시저는 사용가능한 모든 OLE DB 공급자 리스트를 반환한다. 제공자 이름과 Parse Name, 설명을 함께 보여준다. SQL Server에서 OLE DB 공급자 리스트를 구하기 위해서는 아래와 같이 실행하면 된다.

EXEC master..xp_enum_oledb_providers
GO
-- 결과
Provider Name              Parse Name           Provider Description
-------------------------- -------------------- ----------------------------------------------------
SQLOLEDB                   {0C7FF16C-38E3-...}  Microsoft OLE DB Provider for SQL Server
DTSPackageDSO              {10010031-EB1C-...}  Microsoft OLE DB Provider for DTS Packages
SQLReplication.OLEDB       {10010100-D8C9-...}  SQL Server Replication
OLE DB Provider for DTS Microsoft.ISAM.OLEDB.1.1   {1cf650fc-2ff0-...}  Microsoft ISAM 1.1 OLE DB Provider
MSDMine                    {2CB6C2D3-DD7C-...}  Microsoft OLE DB Provider For Data Mining Services EMPOLEDB
VS71.1             {53544C4E-1DD6-...}  VSEE Versioning Enlistment Manager Proxy Data Source
ADsDSOObject               {549365d0-ec26-...}  OLE DB Provider for Microsoft Directory Services
MSOLAP                     {a07ccd00-8148-...}  Microsoft OLE DB Provider for OLAP Services
MSOLAP                     {a07ccd0c-8148-...}  Microsoft OLE DB Provider for Olap Services 8.0
MSDAIPP.DSO                {AF320921-9381-...}  Microsoft OLE DB Provider for Internet Publishing . . .

■ xp_enumcodepages
이 확장 저장 프로시저는 SQL Server의 모든 코드 페이지, 문자셋, 문자 집합과 그것들의 설명을 반환한다. 모든 코드 페이지, 문자셋의 목록을 구하기 위해서는 아래와 같이 실행하면 된다.

EXEC master..xp_enumcodepages
GO
-- 결과
ode Page   Character Set   Description
----------- -------------- -----------------------
708         ASMO-708       아랍어(ASMO 708)
720         DOS-720        아랍어(DOS)
28596       iso-8859-6     아랍어(ISO)
1256        windows-1256   아랍어(Windows)
1257        windows-1257   발트어(Windows)
852         ibm852         중앙 유럽어(DOS)
28592       iso-8859-2     중앙 유럽어(ISO)
1250        windows-1250   중앙 유럽어(Windows)
936         gb2312         중국어 간체(GB2312)
52936       hz-gb-2312     중국어 간체(HZ) . . .

■ xp_enumdsn
이 확장 저장 프로시저는 모든 시스템 DSN과 설명을 반환한다. 모든 시스템 DSN의 목록을 구하기 위해서는 아래와 같이 실행하면 된다.

EXEC master..xp_enumdsn

■ xp_enumerrorlogs
이 확장 저장 프로시저는 마지막 수정일을 포함하는 오류 로그의 목록을 반환한다. 오류 로그의 목록을 구하기 위해서는 아래와 같이 실행하면 된다.

EXEC master..xp_enumerrorlogs
GO  -- 결과 보관 #        날짜로그         파일 크기(바이트)  ----------- ------------------ -------------  0           09/30/2003  16:46  5406 1           09/25/2003  10:52  4437 2           09/25/2003  10:14  4521 3           09/21/2003  22:00  5220 4           09/18/2003  18:57  4570 5           09/18/2003  16:55  4493 6           09/17/2003  22:44  5514

■ xp_enumgroups
이 확장 저장 프로시저는 모든 Windows NT 그룹명과 설명을 반환한다.

EXEC master..xp_enumgroups GO  -- 결과 group                comment -------------------- ---------------------------------------------------------------------- Administrators       컴퓨터/도메인에 모든 액세스 권한을 가진 관리자 Backup Operators     파일을 백업하거나 복원하기 위해 보안 제한을 변경할 수 있는 백업 관리자 Guests               기본적으로 사용자 그룹의 구성원과 동일한 권한을 가진 게스트(별도의 ... Power Users          일부 권한을 제외한 관리자 권한을 가진 고급 사용자. 인증된 응용... Replicator           도메인에서 파일 복제 지원 Users                시스템 관련 사항을 변경할 수 없는 일반 사용자. 따라서 인증된 응용 ... . .

■ xp_fileexist
xp_fileexist 확장 저장 프로시저를 이용하여 디스크 상에 저장된 특정 파일이 실제로 존재 하는지 안 하는지 알아볼 수 있다.

문법 EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]  -- 다음은 boot.ini 파일이 C:\에  존재하는지 알아보는 예제. 
EXEC master..xp_fileexist 'c:\boot.ini' GO  -- 결과 파일이 있 파일이 디렉 상위 디렉  --------- ----------- --------- 1         0           1  * 주: 반환되는 결과의 컬럼명이 짤려 보인다. "파일이 있"는 "파일이 있는가?" 정도로 보면 될것이다. 인수로 지정한 파일이 존재하면 1, 존재하지 않으면 0을 출력한다. "파일이 디렉" 칼럼은 "파일이 디렉토리 인가?" 정도로 보면 된다. 인수로 파일 뿐 아니라 디렉토리 명을 줄 수 있다.(예:xp_fileexist 'c:\inetpub') 이것도 마찬가지로 디렉토리이면 1, 디렉토리가 아니면을 0을 출력한다.

■ xp_fixeddrives
xp_fixeddrives 확장 저장 프로시저는 매우 유용한데, 시스템 상의 모든 하드 디스크 드라이브에 대해 각각의 남은 용량을 메가바이트(Mb) 단위로 출력해 준다.

EXEC master..xp_fixeddrives
GO 
-- 결과 drive MB 비어 있음    

----- ----------- 
C     11023 D     14050

■ xp_getnetname
xp_getnetname 확장 저장 프로시저는 접속중인 SQL Server의 이름을 반환한다.

EXEC master..xp_getnetname

■ xp_readerrorlog
xp_readerrorlog 확장 저장 프로시저는 오류 로그 파일의 내용을 보여준다. SQL Server 7.0 에서는 오류 로그 파일이 디폴트로 C:\MSSQL7\Log 디렉토리에 저장된다. (주:SQL Server 2000은 C:\Program Files\Microsoft SQL Server\MSSQL\log)

EXEC master..xp_readerrorlog
 GO 
-- 결과
 ERRORLOG                                                                               ContinuationRow 
-------------------------------------------------------------------------------------- --------------- 
2003-09-26 15:18:29.45 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)     0  
Dec 17 2002 14:22:05                                                        0  
Copyright (c) 1988-2003 Microsoft Corporation                               0  
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)             0
2003-09-26 15:18:29.53 server    Copyright (C) 1988-2002 Microsoft Corporation.        0
2003-09-26 15:18:29.53 server    All rights reserved.                                  0
2003-09-26 15:18:29.53 server    서버 프로세스 ID는 724입니다.                         0
2003-09-26 15:18:29.53 server    'C:\Program Files\Microsoft SQL Server\MSSQL\log...   0
2003-09-26 15:18:29.83 server    SQL Server가 우선 순위 클래스 ''normal''(1 CPU ...    0
 2003-09-26 15:18:31.16 server    SQL Server가 thread 모드 처리를 위해 구성되었습니다.  0
2003-09-26 15:18:31.18 server    dynamic 잠금 할당을 사용하는 중입니다. [2500]...      0
2003-09-26 15:18:31.27 server    분산 트랜잭션 코디네이터를 초기화하는 중입니다.       0
2003-09-26 15:18:33.26 server    Failed to obtain TransactionDispenserInterface:.....  0
2003-09-26 15:18:34.35 spid3     'master' 데이터베이스를 시작하는 중입니다.            0
2003-09-26 15:18:37.44 spid3     0개의 트랜잭션이 'master'(1) 데이터베이스에서 롤백... 0 . .

■ xp_regdeletekey
xp_regdeletekey 확장 저장 프로시저는 시스템 레지스트리에서 모든 키를 삭제한다. 따라서 각별한 주의가 요망된다.

문법: EXECUTE xp_regdeletekey [@rootkey=]'rootkey',                         [@key=]'key'   -- 'HKEY_LOCAL_MACHINE' 에서 'SOFTWARE\Test' 키를 삭제하는 예제 EXEC master..xp_regdeletekey      @rootkey='HKEY_LOCAL_MACHINE',        @key='SOFTWARE\Test'

■ xp_regdeletevalue
xp_regdeletevalue 확장 저장 프로시저는 시스템 레지스트리에서 키의 특정 값을 삭제한다. 따라서 사용히 각별한 주의가 요망된다.

문법: EXECUTE xp_regdeletevalue

 [@rootkey=]'rootkey',

                           [@key=]'key',
                           [@value_name=]'value_name' 
-- 'HKEY_LOCAL_MACHINE'에서 'SOFTWARE\Test' 키의 값인 'TestValue' 를 삭제하는 예제 EXEC master..xp_regdeletevalue
      @rootkey='HKEY_LOCAL_MACHINE',
      @key='SOFTWARE\Test',
      @value_name='TestValue'

■ xp_regread
xp_regread 확장 저장 프로시저는 시스템 레지스트리를 읽어온다.

문법: EXECUTE xp_regread [@rootkey=]'rootkey',
                    [@key=]'key'
                    [, [@value_name=]'value_name']
                    [, [@value=]@value OUTPUT]
  --  'HKEY_LOCAL_MACHINE'의 'SOFTWARE\Test' 키에서
-- 'TestValue'의 값을 @test 변수에 저장하는 예제

DECLARE @test varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
   @key='SOFTWARE\Test',
   @value_name='TestValue',
   @value=@test OUTPUT
 
SELECT @test

■ xp_regwrite
xp_regwrite 확장 저장 프로시저는 레지스트리에 값을 쓸 때 사용한다.

문법: EXECUTE xp_regwrite [@rootkey=]'rootkey',
                     [@key=]'key',
                     [@value_name=]'value_name',
                     [@type=]'type',
                     [@value=]'value'
 --  'HKEY_LOCAL_MACHINE'의 'SOFTWARE\Test' 키에서
 -- 'TestValue'의 값을 Test로 설정하는 예제
EXEC master..xp_regwrite
      @rootkey='HKEY_LOCAL_MACHINE',
      @key='SOFTWARE\Test',
      @value_name='TestValue',
      @type='REG_SZ',
      @value='Test'

■ xp_subdirs
xp_subdirs 확장 저장 프로시저는 특정 폴더 하위에 존재하는 모든 폴더의 목록을 반환한다. xp_dirtree와 와는 달리, xp_subdirs는 지정한 디렉토리의 바로 하위에 존재하는 디렉토리(depth = 1)만 보여준다.

EXEC master..xp_subdirs 'C:\MSSQL7'

문서화되지 않은 이 확장 저장 프로시저들은 Microsoft에 의해 공식적으로 지원되지 않으며 SQL Server의 다음 버젼에서 존재하지 않을 수도 있음을 유념해야 한다.