SQL Server 최적화 프로세스의 이해

Nov 16 2020

SQL Server 최적화 프로그램에서 쿼리 최적화 중에 고려되는 모든 쿼리 계획 변형을보고 싶습니다. SQL Server는 querytraceon옵션을 사용하여 매우 상세한 통찰력을 제공합니다. 예를 들어 QUERYTRACEON 3604, QUERYTRACEON 8615MEMO 구조를 QUERYTRACEON 3604, QUERYTRACEON 8619인쇄하고 최적화 프로세스 중에 적용된 변환 규칙 목록을 인쇄 할 수 있습니다. 훌륭하지만 추적 출력에 몇 가지 문제가 있습니다.

MEMO 구조에는 쿼리 계획의 최종 변형 또는 나중에 최종 계획으로 다시 작성된 변형 만 포함되어있는 것 같습니다. "실패 / 무효"쿼리 계획을 찾는 방법이 있습니까?
MEMO의 연산자는 SQL 부분에 대한 참조를 포함하지 않습니다. 예를 들어 LogOp_Get 연산자는 특정 테이블에 대한 참조를 포함하지 않습니다.
변환 규칙에는 MEMO 연산자에 대한 정확한 참조가 포함되어 있지 않으므로 어떤 연산자가 변환 규칙에 의해 변환되었는지 확신 할 수 없습니다.

좀 더 정교한 예를 보여 드리겠습니다. 두 개의 인공 테이블 A과 B:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

지금은 간단한 쿼리 하나를 실행합니다.

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)

15 개의 그룹으로 구성된 MEMO 구조 (직접 시도 할 수 있음)를 설명하는 매우 복잡한 출력을 얻습니다. 다음은 트리를 이용하여 MEMO 구조를 시각화 한 사진입니다.

트리에서 옵티마이 저가 최종 쿼리 계획을 찾기 전에 적용된 특정 규칙이 있음을 알 수 있습니다. 예 : join commute( JoinCommute), join to hash join( JNtoHS) 또는 Enforce sort( EnforceSort). 언급했듯이 옵티 마이저에서 QUERYTRACEON 3604, QUERYTRACEON 8619옵션을 사용하여 적용한 전체 다시 쓰기 규칙 세트를 인쇄 할 수 있습니다. 문제 :

8619 목록에서 JNtoSM( Join to sort merge) 다시 쓰기 규칙을 찾을 수 있지만 정렬-병합 연산자는 MEMO 구조에 없습니다. 정렬 병합이 아마도 더 많은 비용이 든다는 것을 이해합니다. 그런데 왜 그것이 MEMO에 없습니까?
LogOp_GetMEMO의 연산자가 테이블 A 또는 테이블 B를 참조 하는지 확인하는 방법은 무엇 입니까?
GetToIdxScan - Get -> IdxScan8619 목록에 규칙이 있는 경우 이를 MEMO 연산자에 매핑하는 방법은 무엇입니까?

이에 대한 리소스의 수가 제한되어 있습니다. 변형 규칙 및 메모에 대한 많은 Paul White 블로그 게시물을 읽었지만 위의 질문에 대한 답변이 없습니다. 도움을 주셔서 감사합니다.

답변

FrancescoMantovani Nov 26 2020 at 03:38

귀하의 질문에 답장 해 드리겠습니다.

1. MEMO 구조에는 쿼리 계획의 최종 변형 또는 나중에 최종 계획으로 다시 작성된 변형 만 포함되어있는 것 같습니다. "실패 / 무효"쿼리 계획을 찾는 방법이 있습니까?

아니, 안타깝게도 그렇게 할 방법이 없습니다. @Ronaldo는 댓글에 멋진 링크를 붙여 넣었습니다. 내 제안은Include Live Query Statistics

다른 쿼리 계획이 있는지 알아보십시오. 사용 top 10, top 1000또는 *당신은 다른 쿼리 계획이 제안됩니다 것을 볼 수 있습니다. query hint쿼리 계획을 다른 패턴으로 사용 하고 강제 할 수도 있습니다 . 기본적으로 "자신의 폐기 된 쿼리 계획 수행"

2. MEMO의 연산자는 SQL 부분에 대한 참조를 포함하지 않습니다. 예를 들어 LogOp_Get 연산자는 특정 테이블에 대한 참조를 포함하지 않습니다.

를 사용 QUERYTRACEON 8605하면 테이블에 대한 참조를 볼 수 있습니다.

3. 변환 규칙에는 MEMO 연산자에 대한 정확한 참조가 포함되어 있지 않으므로 변환 규칙에 의해 어떤 연산자가 변환되었는지 확인할 수 없습니다.

GetToIdxScan - Get -> IdxScan제공하신 검색어에 아무것도 표시되지 않습니다 . 내 제안은 Use 사용 QUERYTRACEON 8605하거나 QUERYTRACEON 8606참조가 있어야합니다.

편집하다:

따라서 "... SQL Server에서 후보 계획에 대한 자세한 정보를 볼 수 있습니까?"

다른 후보 쿼리 계획이 없기 때문에 응답은 no 입니다. 사실, SQL Server가 최상의 쿼리 계획을 반환한다는 일반적인 오해입니다 . SQL Server는 가능한 모든 솔루션을 계산할 수 없습니다. 시간이 걸립니다 ... 모르겠습니다 ... 몇 분 ...? 시간...? 모든 단일 솔루션을 계산하는 것은 불가능합니다.