[MSSQL] 통계(Statistics) (2) - CREATE, UPDATE 시기 및 통계의 효율적 활용

본 포스팅에서는 지난 '[MSSQL] 통계(Statistics) (1)' 포스팅에 이어, MSSQL의 통계에 더 알아볼텐데,
Optimizer의 CREATE 및 UPDATE의 시기 설정과 통계를 효율적으로 사용하기 위해 쿼리를 어떤 식으로 작성해야 하는지에 대해 알아볼 것이다.

---

언제 CREATE STATISTICS 해야 하는가?

 

먼저 통계가 쿼리 옵티마이저에 의해 자동으로 생성되는 두 가지 경우를 살펴보자.

1. 인덱스가 생성되면, 쿼리 옵티마이저는 테이블 또는 뷰(View)의 인덱스에 대한 통계를 생성.
   이때 통계는 인덱스 키 Column에 대해 만들어지며, 필터링된 인덱스(특정 조건에 맞는 데이터에 대해서만 인덱스를 생성한 것)의 경우, 필터링된 인덱스로 지정된 Row의 동일한 Subset에 대해 필터링된 통계를 생성.
2. AUTO_CREATE_STATISTICS 옵션이 ON 이라면, 쿼리 옵티마이저는 쿼리 조건자의 단일 열에 대한 통계를 작성.

대부분의 쿼리에서 위 통계를 만드는 두 가지 방법을 통해 옵티마이저가 고품질의 쿼리 플랜(Plan)을 만들 수 있는데, 경우에 따라서 'CREATE STATISTICS' 문으로 추가 통계를 작성하여 쿼리 플랜을 향상시킬 수도 있다.

CREATE STATISTICS 문으로 통계를 만들 때에는 옵티마이저가 쿼리 조건자 열에 대한 단일 열 통계를 계속해서 정기적으로 생성할 수 있도록 AUTO_CREATE_STATISTICS 옵션을 ON으로 유지하는 것이 좋다. 

 

 

"그렇다면 어떤 경우에 CREATE STATISTICS 문을 별도로 사용하는게 좋을까?"

마이크로소프트에서는 다음 4가지 케이스의 경우, CREATE STATISTICS 문으로 통계를 별도로 생성하는 것을 권장한다.

1. DB 엔진 튜닝 관리자가 통계 작성을 제안하는 경우
2. 쿼리 조건자가 동일한 인덱스에는 없으면서 관련된 여러 열이 포함된 경우
3. 쿼리가 데이터 Subset(하위집합)에서 선택되는 경우
4. 쿼리에 통계가 누락된 경우

 

• 쿼리 조건자가 동일한 인덱스에는 없으면서 관련된 여러 열이 포함된 경우
  여러 열에 대한 통계에는 단일 열 통계에서는 사용할 수 없던 '밀도(열 간 상호 관계 통계)'를 활용하면서 카디널리티 예상치 정확도를 높일 수 있다.
  - 여러 열에 대한 인덱스를 별도 생성하는 것보다 여러 열에 대한 통계를 생성하는 것이 시스템 리소스를 덜 잡아먹기 때문에, 여러 열에 대한 인덱스가 반드시 필요한 경우가 아니라면 통계를 생성하여 시스템 리소스 절약 가능.
  - 여러 열에 대한 통계 생성 시에 통계 개체 정의(Statistics Object Definition)에서의 Column 순서는 카디널리티 예상치를 만들기 위한 밀도 효율성에 영향을 주는데, 이때 통계 개체는 주요 열의 각 Prefix(접두사)에 대한 밀도를 통계 개체 정의에 저장.
   => 따라서 효율적인 통계 생성을 위해서는, 쿼리 조건자의 열이 통계 개체 정의에 있는 열의 Prefix 중 하나와 일치해야 한다!
  ex) 
  

  USE AdventureWorks2012;  
  GO  
  IF EXISTS (SELECT name FROM sys.stats  
      WHERE name = 'LastFirst'  
      AND object_ID = OBJECT_ID ('Person.Person'))  
  DROP STATISTICS Person.Person.LastFirst;  # 단일 열에 대한 통계 삭제
  GO  
  CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName); 
  GO # 여러 열(LastNmae, MiddleName, FirstName)에 대한 통계 생성​

  위 쿼리를 실행하면 통계 개체 LastFirst가 여러 열에 대한 통계로 바뀌는데, Column Prefix (LastName), (LastName, MiddleName), (LastName, MiddleName, FirstName) 에 대한 밀도를 가진다. 이때 (LastName, FirstName)에 대한 밀도는 사용할 수 없다는 점을 명심하자! 
  LastName을 통계 개체에서 첫 번째 Column으로 지정하였기 때문에, 쿼리에서 조건자로 LastName이 아닌 FirstName과 MiddelName을 사용하는 경우 카디널리티 예상치에 대한 밀도 사용이 불가하므로, 효율적인 쿼리라고 볼 수 없는 것이다.
  
  
  
• 쿼리가 데이터 Subset에서 선택되는 경우
  쿼리 옵티마이저는 통계를 만들 때 모든 Row의 값에 대한 통계를 생성하는데, 만약 쿼리가 Row의 Subset에서 데이터를 선택하고 해당 Subset에서의 데이터 분포가 Unique한 경우, CREATE STATISTICS문을 WHERE 절과 함께 사용하여 별도의 필터링된 통계를 생성하면 쿼리 플랜의 성능을 향상시킬 수 있다.
  
  그냥 글로 된걸 읽을 때는 이해가 잘 안 갈테니, 예시를 통해 살펴보자!
  AdventureWorks2012 DB에서 Production.Product 테이블에 각 제품들에 대한 데이터가 있고, Production.ProductCategory 테이블에 4가지 범주로 자전거, 구성품, 의류, 악세서리 중 하나가 있다고 하자.
  이때 ProductCategory를 기준으로 한 각 범주의 가중치(Weight)가 자전거는 13.77~30.0, 구성품은 2.12~1050.00이면서 일부는 NULL 값을 가지고, 의류와 악세서리의 가중치는 모두 NULL 이라고 한다.
  이때 자전거의 경우 가중치 범위가 좁고 Unique하다고 볼 수 있으므로, 자전거에 대해서 조회를 할 때 통계를 활용하면 효율적인 쿼리 플랜을 활용할 수 있는 것이다.
  - 자전거처럼 가중치 범위가 좁을 때에 필터링된 인덱스를 활용하면 성능 향상은 있지만 그만큼 필터링된 인덱스를 DB에 추가하면서 추가 유지 관리 및 스토리지 비용이 부담될 수 있으므로 통계가 더 유리하다고 볼 수 있는 것이다.
  가중치에 대한 통계 생성 및 활용에 대한 쿼리는 아래와 같다.
  

  USE AdventureWorks2012;
  GO
  IF EXISTS ( SELECT name FROM sys.stats
      WHERE name = 'BikeWeights'
      AND object_ID = OBJECT_ID ('Production.Product'))
  DROP STATISTICS Production.Product.BikeWeights;
  GO
  CREATE STATISTICS BikeWeights
      ON Production.Product (Weight)	
  WHERE ProductSubcategoryID IN (1,2,3);	# Weight에 대한 ProductSubcategoryID로 통계 생성
  GO​

  SELECT P.Weight AS Weight, S.Name AS BikeName  
  FROM Production.Product AS P  
      JOIN Production.ProductSubcategory AS S   
      ON P.ProductSubcategoryID = S.ProductSubcategoryID  
  WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25  
  ORDER BY P.Weight;  
  GO​

• 쿼리에서 누락된 통계를 확인한 경우
  - Error 또는 기타 이벤트로 인하여 쿼리 옵티마이저가 통계를 생성하지 못하는 경우, 옵티마이저는 통계를 사용하지 않고 쿼리 플랜을 만드는데, 이때 쿼리 옵티마이저는 통계가 누락된 것으로 표시하며 다음 쿼리가 다시 실행될 때 통계 생성을 재시도한다.
  => SSMS에서는 위와 같은 상황이 발생하였을 때, 쿼리 플랜을 GUI로 확인하면 통계가 누락된 테이블 이름을 빨간색으로 표시한다.
  - AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS 옵션이 ON으로 설정되었는지 확인하여 통계 누락의 원인이 아닌지 확인한다.
  - DB가 읽기 전용이 아닌지 확인한다. 읽기 전용의 DB에서는 새로운 통계 오브젝트를 저장할 수 없기 때문이다.
  - 위를 모두 확인하였을 때 이상이 없다면, CREATE STATISTICS 문을 사용하여 누락된 통계를 작성한다. 

---

언제 UPDATE STATISTICS 해야 하는가?

 보통의 경우 쿼리 AUTO_UPDATE_STATISTICS 옵션을 ON으로 설정하여 옵티마이저가 자동으로 통계의 최신 여부를 판단하고 업데이트 하는데, 경우에 따라서는 이보다 더 자주 통계를 업데이트하여 쿼리의 성능을 향상시킬 수 있다. 
 단, 통계를 업데이트 하게 되면 쿼리가 최신 통계로 컴파일되는 반면, 쿼리 역시 다시 컴파일되므로, 쿼리 플랜 성능 향상과 쿼리 재컴파일 소요 시간 간의 균형을 맞춰야 하므로 자주 업데이트 한다고 좋은 것은 아닌 것을 반드시 명심하자!

 

통계가 마지막으로 업데이트된 시점을 확인하고 싶다면, sys.dm_db_stats_properties 또는 STATS_DATE 함수를 사용하면 된다.

 

"그렇다면 어떤 경우에 UPDATE STATISTICS를 별도로 사용하는게 좋을까?"

마이크로소프트에서는 다음 3가지의 경우, UPDATE STATISTICS 문으로 통계를 별도로 업데이트 하는 것을 권장한다.

1. 쿼리 실행 시간이 느린 경우
2. INSERT 작업이 오름차순 또는 내림차순 키 Column에 대해 발생하는 경우
3. 유지 관리 작업 이후

• 쿼리 실행 시간이 느린 경우
  쿼리 응답 시간이 느리거나 예측할 수 없는 경우 추가 문제 해결 단계를 수행하기 전에 쿼리에 최신 통계가 반영되었는지를 확인하자.
  
  
• INSERT 작업이 오름차순 또는 내림차순 키 Column에 대해 발생하는 경우
  INSERT 작업 시에 IDENTITY 또는 실시간 타임스탬프(TimeStamp) Column과 같은 오름차순 또는 내림차순 키 Column에 대한 통계의 경우, 추가되는 Row 수가 통계 업데이트를 Trigger하기에는 너무 적을 수 있는데, 이럴 경우 현재 통계는 이러한 새 값에 대한 카디널리티 예상치를 반영하지 않아 부정확해지고 쿼리 성능이 저하될 수 있다. 따라서 이와 같은 오름차순 또는 내림차순의 키 Column에 대해 INSERT 작업하는 경우에는 별도로 UPDATE STATISTICS 문으로 통계를 업데이트 해주는 것이 좋다. 
  
  
• 유지 관리 작업 이후
  - 테이블 삭제와 같은 데이터 분포를 변경하는 유지 관리 작업을 수행했거나 또는 많은 양의 Row에 대한 대량 INSERT를 수행한 후 통계를 업데이트 하는 것이 좋은데, 이렇게 함으로써 이후의 쿼리에서 자동 통계 업데이트 과정을 건너띄어 쿼리 처리 지연이 발생하는 것을 막을 수 있다. 
  - 단, ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, ALTER INDEX REORGANIZE 와 같은 인덱스 작업들은 데이터의 직접적인 분포를 변경하지는 않으므로, 이 경우 통계 업데이트를 할 필요가 없다. 참고로 ALTER INDEX REBUILD 및 DBCC DBREINDEX 문 같은 경우 인덱스를 다시 작성하기 때문에 쿼리 옵티마이저가 통계를 자동으로 업데이트 한다.

---

통계 활용을 위한 효과적인 쿼리 작성

쿼리 조건자에서 지역 변수 및 복잡한 식 등으로 인해 일부 쿼리 구현에서는 성능 저하를 유발할 수 있는 쿼리 플랜을 만들 수 있는데, 이를 방지하기 위해서는 효율적인 통계 활용을 위한 아래와 같은 쿼리 설계 지침을 따르는 것이 좋다.

• 식에 대한 카디널리티 예상치 정확도 향상
  - 가능하면 상수가 포함된 단순한 식을 사용.
     ex) ABS(-100) => 100으로 단순화.
  - 식에서 여러 변수를 사용하는 경우, 식에 대해 계산된 Column을 만든 다음 계산된 Column에 대해 통계 또는 인덱스를 생성.
     ex) WHERE PRICE + TAX > 100 과 같은 계산식이 있다면 TOTAL = PRICE + TAX 와 같은 Column을 별도로 생성하고, TOTAL 열에 대한 통계 또는 인덱스 생성하여 활용.
  
  
• 변수 및 함수에 대한 카디널리티 예상치 정확도 향상
  - 쿼리 조건자에서 지역 변수를 사용하는 것보다 매개 변수를 사용하는 것이 좋다. 쿼리 옵티마이저는 쿼리 플랜을 생성할 때 지역 변수 값을 알 수 없기 때문.
  - 다중 문 테이블 반환 함수(mstvf)의 결과를 저장하려면 표준 테이블 또는 임시 테이블을 사용한다. 옵티마이저는 mstvf에 대한 통계를 작성하지 않기 때문.
  - 테이블 변수 대신 표준 테이블 또는 임시 테이블을 사용한다. 옵티마이저는 테이블 변수에 대한 통계를 생성하지 않는다. 
    단, 저장 프로시저를 많이 사용하는 애플리케이션의 경우, 테이블 변수로 하여금 재컴파일 횟수를 줄임으로써 얻는 성능 향상이 더 큰 경우도 있다.
  - 이미 전달된 매개 변수를 사용하는 쿼리가 저장 프로시저에 포함되어 있는 경우, 쿼리에서 매개 변수 값을 사용하기 전에 저장 프로시저 내의 매개 변수 값을 변경하면 안된다. 쿼리에 대한 카디널리티 예상치는 업데이트된 값이 아닌 이미 전달된 매개 변수 값을 기준으로 하기 때문이다. 저장 프로시저 내의 매개 변수를 변경해야 한다면, 별도의 저장 프로시저를 새로 생성하는 것이 더 좋은 방법이 될 수 있다.
  
  
• 쿼리 힌트를 사용하여 카디널리티 예상치 정확도 향상
  - 쿼리 힌트 : 일종의 지시 구문으로, 옵티마이저에게 SQL 문 실행을 위한 데이터를 스캐닝하는 경로, 조인하는 방법 등을 알려주기 위해 SQL 사용자가 SQL 쿼리문에 작성하는 것. 힌트를 사용하면 옵티마이저는 우선적으로 SQL문의 힌트를 활용하고 그 다음에 OPTIMIZER_MODE에 설정된 값을 활용한다.
  - RECOMPILE과 함께 OPTIMIZE FOR <value> 또는 OPTIMIZE FOR UNKNOWN 쿼리 힌트 사용 가능.
  - 특정 날짜를 지정하는 힌트를 사용하는 아래 SQL 문 예시를 보자.
  

  USE AdventureWorks2012;  
  GO  
  IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL  
      DROP PROCEDURE Sales.GetRecentSales;  
  GO  
  CREATE PROCEDURE Sales.GetRecentSales (@date datetime)  
  AS BEGIN  
      IF @date is NULL  
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
      WHERE h.SalesOrderID = d.SalesOrderID  
      AND h.OrderDate > @date  
      OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))  
  END;  
  GO​

• 쿼리 플랜 지침(Plan Guides)을 사용하여 카디널리티 예상치 정확도 향상
  - 일부 애플리케이션의 경우 쿼리를 변경할 수 없거나 RECOMPILE 쿼리 힌트로 인해 너무 많이 재컴파일 되어 쿼리 설계 지침이 적용되지 않을 수 있다.
  - 애플리케이션 공급업체와 함께 애플리케이션 변경 내용을 조사하는 상황 등에서 쿼리 동작을 제어할 수 있도록 계획 지침을 활용하면 USE PLAN 과 같은 힌트를 지정할 수 있다.

 

 

참고

https://docs.microsoft.com/ko-kr/sql/relational-databases/statistics/statistics?view=sql-server-ver15 

통계 - SQL Server