[SQL] 왜 그렇게 높은 윈도우 집계 함수에 대한 읽고 논리적인가?

왜 그렇게 높은 윈도우 집계 함수에 대한 읽고 논리적인가?

나는 논리적보고 한 공통 부분 식 스풀을 사용하여 실행 계획에서 큰 테이블에 매우 높게 읽는 것으로 나타났습니다.

약간의 시행 착오 후에 나는 아래의 테스트 스크립트 및 실행 계획에 대한 유지 보인다 수식을 발견했습니다. 논리 작업 테이블은 = 1 + NumberOfRows * 2 + 4 * NumberOfGroups 읽

나는이 공식 그래도 보유 이유를 이해하지 않습니다. 내가 생각이 계획을보고 필요했던 것보다 더 많은입니다. 사람이에 대한 그 계정에 무슨 일이 일어나고 있는지의 타격 계정에 의한 타격을 줄 수 있습니까?

또는 실패하는 것은 나 자신을 위해 그것을 해결할 수 있도록 각 논리 읽기에 읽은 어떤 페이지 추적의 방법은 무엇입니까?

SET STATISTICS IO OFF; SET NOCOUNT ON;

IF Object_id('tempdb..#Orders') IS NOT NULL
  DROP TABLE #Orders;

CREATE TABLE #Orders
  (
     OrderID    INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
     CustomerID NCHAR(5) NULL,
     Freight    MONEY NULL,
  );

CREATE NONCLUSTERED INDEX ix
  ON #Orders (CustomerID)
  INCLUDE (Freight);

INSERT INTO #Orders
VALUES (N'ALFKI', 29.46), 
       (N'ALFKI', 61.02), 
       (N'ALFKI', 23.94), 
       (N'ANATR', 39.92), 
       (N'ANTON', 22.00);

SELECT PredictedWorktableLogicalReads = 
        1 + 2 * Count(*) + 4 * Count(DISTINCT CustomerID)
FROM   #Orders;

SET STATISTICS IO ON;

SELECT OrderID,
       Freight,
       Avg(Freight) OVER (PARTITION BY CustomerID) AS Avg_Freight
FROM   #Orders; 

산출

PredictedWorktableLogicalReads
------------------------------
23

Table 'Worktable'. Scan count 3, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Orders___________000000000002'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

추가 정보:

쿼리 튜닝 및 최적화 책의 제 3 장에서 이러한 스풀의 좋은 설명과 폴 화이트에 의해이 블로그 게시물이 있습니다.

요약 계획의 상단에있는 세그먼트 반복자는 새 파티션의 시작이 때 나타내는 보내는 행 플래그를 추가합니다. 차 세그먼트 (segment)의 스풀 세그먼트 반복자에서 한 번에 행을 얻고 tempdb의에서 작업 테이블에 삽입합니다. 그것은 새로운 그룹이 중첩 루프 연산자의 최고 입력에 행을 반환하기 시작했다고 말하는 플래그를 얻으면. 이는 평균 후 계산 작업 테이블이 새 그룹에 대한 준비가 잘 리기 전에이 값은 작업 테이블의 행과 다시 결합되고, 스트림 집계 작업 테이블의 행을 통해 호출됩니다. 세그먼트 스풀 처리 최종 그룹을 얻기 위해 더미 열을 방출한다.

지금까지 내가 이해 작업 테이블은 힙 (또는이 인덱스 스풀과 계획에 표시 될 것이다)입니다. 그러나 나는 시도하고 동일한 프로세스를 복제 할 때 그것은 단지 논리적 (11) 읽기가 필요합니다.

CREATE TABLE #WorkTable
  (
     OrderID    INT,
     CustomerID NCHAR(5) NULL,
     Freight    MONEY NULL,
  )

DECLARE @Average MONEY

PRINT 'Insert 3 Rows'

INSERT INTO #WorkTable
VALUES      (1, N'ALFKI', 29.46) /*Scan count 0, logical reads 1*/

INSERT INTO #WorkTable
VALUES      (2, N'ALFKI', 61.02) /*Scan count 0, logical reads 1*/

INSERT INTO #WorkTable
VALUES      (3, N'ALFKI', 23.94) /*Scan count 0, logical reads 1*/
PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 1*/
PRINT 'Return Rows - With the average column included'

/*This convoluted query is just to force a nested loops plan*/
SELECT *
FROM   (SELECT @Average AS Avg_Freight) T /*Scan count 1, logical reads 1*/
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

PRINT 'Clear out work table'

TRUNCATE TABLE #WorkTable

PRINT 'Insert 1 Row'

INSERT INTO #WorkTable
VALUES      (4, N'ANATR', 39.92) /*Scan count 0, logical reads 1*/
PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 1*/
PRINT 'Return Rows - With the average column included'

SELECT *
FROM   (SELECT @Average AS Avg_Freight) T /*Scan count 1, logical reads 1*/
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

PRINT 'Clear out work table'

TRUNCATE TABLE #WorkTable

PRINT 'Insert 1 Row'

INSERT INTO #WorkTable
VALUES      (5, N'ANTON', 22.00) /*Scan count 0, logical reads 1*/
PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 1*/
PRINT 'Return Rows - With the average column included'

SELECT *
FROM   (SELECT @Average AS Avg_Freight) T /*Scan count 1, logical reads 1*/
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

PRINT 'Clear out work table'

TRUNCATE TABLE #WorkTable

PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 0*/
PRINT 'Return Rows - With the average column included'

SELECT *
FROM   (SELECT @Average AS Avg_Freight) T
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

DROP TABLE #WorkTable 

해결법

1. ==============================

   1.논리 작업 테이블에 대해 다르게 계산됩니다 읽 행 읽기 당 하나의 '논리적 읽기'가있다. 이것은 그 작업 테이블은 어떻게 든 '진짜'스풀 테이블 (매우 반대)보다 효율적 의미하지 않는다; 논리는 다른 단위에 읽습니다.

   논리 작업 테이블에 대해 다르게 계산됩니다 읽 행 읽기 당 하나의 '논리적 읽기'가있다. 이것은 그 작업 테이블은 어떻게 든 '진짜'스풀 테이블 (매우 반대)보다 효율적 의미하지 않는다; 논리는 다른 단위에 읽습니다.

   나는 생각이 작업대의 논리에 대한 그 계산 해시 페이지이었다 생각이 구조는 서버 내부에 있기 때문에 매우 유용하지 않을 것입니다 읽습니다. 논리적으로 스풀 행을보고하는 것은 카운터 읽고 분석을 위해 수 많은 의미가 있습니다.

   이 통찰력은 수식 분명 작동하는 이유를 확인해야합니다. 두 개의 보조 스풀 완전히 차 스풀 방출주고 내 블로그 항목에 설명 된대로 행 (그룹 값 + 1의 수) (COUNT을 (DISTINCT의 CustomerID) + 1) 구성 요소를 두 번 (2 * COUNT (*)를) 읽고 있습니다 . 더하기 하나는 최종 그룹이 종료 나타낼 차 스풀 방출 여분 행이다.

   폴

2. ==============================

   2.공식에서 당신은 NumberOfRows 2 때문에 당신의 실행도 처리를 완료에 둘 필요가 모든 행에 정렬 기능 및 스트림 집계 쇼의 성립 것 * 제공합니다. 당신은 "여기서"절에 대한 추가 될 때 논리의 감소 읽기 comfirm 수 :

   공식에서 당신은 NumberOfRows 2 때문에 당신의 실행도 처리를 완료에 둘 필요가 모든 행에 정렬 기능 및 스트림 집계 쇼의 성립 것 * 제공합니다. 당신은 "여기서"절에 대한 추가 될 때 논리의 감소 읽기 comfirm 수 :

from https://stackoverflow.com/questions/4230838/why-are-logical-reads-for-windowed-aggregate-functions-so-high by cc-by-sa and MIT license