실행 계획을 사용하여 T-SQL 쿼리를 최적화하는 방법 실행 계획에서 테이블 및

시행 착오 및 실행 계획을 사용하여 최적화하려고 지난 이틀 동안 보낸 SQL 쿼리가 있지만 아무 소용이 없습니다. 이 일을 용서해주십시오. 그러나 전체 실행 계획을 여기에 게시하겠습니다. 쿼리 및 실행 계획에서 테이블 및 열 이름을 간략하게 만들고 회사의 IP를 보호하기 위해 노력했습니다. 실행 계획은 SQL Sentry Plan Explorer 로 열 수 있습니다 .

나는 상당한 양의 T-SQL을 수행했지만 쿼리를 최적화하기 위해 실행 계획을 사용하는 것이 새로운 영역이며 실제로 수행 방법을 이해하려고 노력했습니다. 따라서 누군가 나를 도와 주고이 실행 계획을 해독하여 쿼리에서 최적화하는 방법을 찾는 방법을 설명 할 수 있다면 영원히 감사 할 것입니다. 최적화 할 쿼리가 더 많이 있습니다. 첫 번째 질문에 도움이되는 스프링 보드 만 있으면됩니다.

이것은 쿼리입니다.

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50)  = 'Text3';

SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
    B_Var1_PK int,
    Job_Var1 varchar(512),
    Job_Var2 varchar(50)
)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2)
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

CREATE TABLE #TempTable
(
    TTVar1_PK INT PRIMARY KEY,
    TTVar2_LK VARCHAR(100),
    TTVar3_LK VARCHAR(50),
    TTVar4_LK INT,
    TTVar5 VARCHAR(20)
);

INSERT INTO #TempTable
SELECT DISTINCT
    T.T1_PK,
    T.T1_Var1_LK,
    T.T1_Var2_LK,
    MAX(T.T1_Var3_LK),
    T.T1_Var4_LK
FROM
    MyTable1 T
    INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
    INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
SELECT
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK,
    SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
    INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK  = T.TTVar1_PK
    INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
    INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
    INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
    INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK
    LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK


IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END

내가 찾은 것은 세 번째 진술 (느린 것으로 언급 된)이 가장 많은 시간을 소비하는 부분이라는 것입니다. 이 두 문장은 거의 즉시 돌아온다.

실행 계획은 이 링크 에서 XML로 제공됩니다 .

마우스 오른쪽 버튼을 클릭하고 저장 한 다음 브라우저에서 열지 않고 SQL Sentry Plan Explorer 또는 다른보기 소프트웨어에서 여는 것이 좋습니다.

테이블이나 데이터에 대한 정보가 더 필요하면 언제든지 문의하십시오.



답변

주요 답변을 얻기 전에 업데이트해야 할 두 가지 소프트웨어가 있습니다.

필요한 소프트웨어 업데이트

첫 번째는 SQL Server입니다. SQL Server 2008 서비스 팩 1 (빌드 2531)을 실행 중입니다. 최소한 현재 서비스 팩 (SQL Server 2008 서비스 팩 3-빌드 5500)으로 패치해야합니다. 작성 시점의 최신 SQL Server 2008 빌드는 서비스 팩 3, 누적 업데이트 12 (빌드 5844)입니다.

두 번째 소프트웨어는 SQL Sentry Plan Explorer입니다. 입니다. 최신 버전에는 전문가 분석을 위해 쿼리 계획을 직접 업로드하는 기능 (어디서나 XML을 붙여 넣을 필요가 없음)을 포함하여 중요한 새로운 기능과 수정 사항이 있습니다.

쿼리 계획 분석

명령문 레벨 재 컴파일 덕분에 테이블 변수에 대한 카디널리티 추정이 정확히 맞습니다.

불행히도 테이블 변수는 분산 통계를 유지하지 않으므로 모든 최적화 프로그램은 6 개의 행이 있다는 것을 알고 있습니다. 이 6 개의 행에있을 수있는 값을 전혀 모릅니다. 이 정보는 다음 작업이 다른 테이블에 대한 조인이라는 점에서 중요합니다. 해당 조인의 카디널리티 추정치는 옵티마이 저의 거친 추측을 기반으로합니다.

이때부터 옵티마이 저가 선택한 계획은 잘못된 정보를 기반으로하므로 성능이 너무 나쁘다는 것은 놀라운 일이 아닙니다. 특히 정렬과 해시 조인에 대한 해시 테이블을 제외하고 메모리 세트는 너무 작습니다. 실행시 오버플로 정렬 및 해싱 작업이 물리적 으로 유출됩니다. tempdb 디스크 .

SQL Server 2008은이를 실행 계획에서 강조하지 않습니다. 확장 이벤트 또는 프로파일 러 정렬 경고해시 경고를 사용하여 유출을 모니터링 할 수 있습니다 . 메모리는 실행이 시작되기 전의 카디널리티 추정을 기반으로 정렬 및 해시를 위해 예약되어 있으며 SQL Server의 예비 메모리 용량에 관계없이 실행 중에 증가시킬 수 없습니다. 따라서 정확한 행 수 추정은 작업 공간 메모리 소비 작업과 관련된 모든 실행 계획에 중요합니다.

쿼리도 매개 변수화됩니다. OPTION (RECOMPILE)다른 매개 변수 값이 쿼리 계획에 영향을주는 경우 쿼리에 추가 하는 것을 고려해야 합니다. 어쨌든 그것을 사용하는 것을 고려해야하므로 최적화 프로그램은@Param1 컴파일 타임 . 다른 것이 없다면, 이는 테이블이 매우 크고 분할되어 있기 때문에 옵티마이 저가 위에 표시된 인덱스 탐색에 대해보다 합리적인 추정치를 생성하는 데 도움이 될 수 있습니다. 정적 파티션 제거를 활성화 할 수도 있습니다.

테이블 변수 대신 임시 테이블을 사용하여 쿼리를 다시 시도하십시오 OPTION (RECOMPILE). 또한 다른 임시 테이블에 대한 첫 번째 조인 결과를 구체화하고 나머지 쿼리를 이에 대해 실행해야합니다. 행의 수가 크지 않은 것은 아니므로 (3,285,620) 이것은 상당히 빠릅니다. 그런 다음 옵티마이 저는 조인 결과에 대한 정확한 카디널리티 추정 및 분배 통계를 갖습니다. 운이 좋으면 계획의 나머지 부분이 훌륭하게 적용됩니다.

계획에 표시된 특성에서 작업하는 구체화 쿼리는 다음과 같습니다.

SELECT
    A.A_Var7_FK_LK,
    A.A_Var4_FK_LK,
    A.A_Var6_FK_LK,
    A.A_Var5_FK_LK,
    A.A_Var1,
    A.A_Var2,
    A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
    ON A.Job = B.B_Var1_PK
WHERE
    A_Var8_FK_LK = @Param1;

당신은 또한 수있는 INSERT미리 정의 된 임시 테이블에 (내가 그 부분을 할 수 있도록 올바른 데이터 유형은, 계획에 표시되지 않습니다). 새로운 임시 테이블은 클러스터 및 비 클러스터형 인덱스의 이점을 얻거나 얻지 못할 수 있습니다.


답변

@MyTableVar에 PK가 있어야하며 #MyTableVar가 더 나은 성능을 발휘한다는 데 동의합니다 (특히 행 수가 많을수록).

where 절의 조건

   WHERE A.A_Var8_FK_LK = @Param1

내부 조인 A로 이동해야합니다. 옵티마이 저는 내 경험상이 작업을 수행하기에 영리하지 못하며 (죄송합니다. 계획을 보지 못했습니다) 큰 차이를 만들 수 있습니다.

이러한 변경 사항이 개선되지 않으면 다음으로 A의 다른 임시 테이블과 A.A_Var8_FK_LK = @ Param1에 의해 제한 된 A에 대한 모든 임시 테이블을 만듭니다.

그런 다음 다음 조인 조건에 대해 해당 임시 테이블 (만들기 전 또는 후에)에 클러스터형 인덱스를 만듭니다.

그런 다음 그 결과를 남아있는 몇 개의 테이블 (F 및 T)에 조인하십시오.

Bam (행 추정치가 꺼져 있고 어쨌든 쉽게 개선 할 수 없는 경우에는 유능한 쿼리 계획이 필요함 ). 나는 당신이 적절한 계획을 가지고 있다고 가정하고 있는데, 그것은 내가 계획 내에서 먼저 확인할 것입니다.

추적은 급격한 영향을 줄 수도 있고 그렇지 않을 수도있는 tempdb 유출을 표시 할 수 있습니다.

최소한 시도하는 것이 더 빠른 또 다른 대안은 가장 적은 수의 행 (A)에서 가장 높은 수로 테이블을 정렬 한 다음 조인에 병합, 해시 및 루프를 추가하는 것입니다. 힌트가 있으면 결합 순서가 지정된대로 고정됩니다. 상대 행 수가 급격히 변하면 장기적으로 상처를 입을 수 있기 때문에 다른 사용자는 현명하게이 방법을 사용하지 않습니다. 최소한의 힌트가 바람직합니다.

이 중 많은 작업을 수행하는 경우 상용 최적화 프로그램을 사용해 보거나 시험해 볼 가치가 있으며 여전히 좋은 학습 경험입니다.