의 연결을 두 개 이상의 데이터 세트가 가장 일반적으로 표현 T-SQL 사용하여UNION ALLclause. 는 SQL Server 최적화할 수 있는 종종 순서를 변경 같은 것들을 결합 및 골재 성능 향상을 위해,그것은 매우 합리적인 기대하는 SQL Server 도 고려해 재정렬 연결은 입력이 제공 할 것이 장점이다. 예를 들어,최적화할 수 있의 이점에 대해 알아보십시오 다시 쓰기A UNION ALL BB UNION ALL A.실제로 SQL Server 최적화 프로그램은이 작업을 수행하지 않습니다. 더 정확하게,거기에 몇 가지 제한에 대한 지원이 연결 입력 순서 SQL 서버 릴리스 최대 2008R2,하지만 이것이 제거되었에서 SQL Server2012 년,그리고는 다시 표시 되지 않습니다.

SQL Server2008R2

,직관적으로 순서의 연결 입력만 사항이 있는 경우 행 목표입니다. 기본적으로 SQL Server 는 모든 적격 행이 클라이언트로 반환되는 것을 기준으로 실행 계획을 최적화합니다. 행 목표가 적용되면 최적화 프로그램은 처음 몇 행을 빠르게 생성하는 실행 계획을 찾으려고 시도합니다.

행 목표를 설정할 수 있습의 숫자에서 예를 들어,여러 가지 방법으로 사용하는TOPFAST nEXISTS(의 성격을 찾을 필요가에서 가장 중 하나 행)이 있습니다. 이 없는 행 목표(즉,클라이언트에 필요한 모든 행),하지 않는 일반적으로 문제에서는 어떤 순서로 연결 입력 읽기:각각의 입력이 완전히 처리되는 결국 어떤 경우에도 빠지지 않았습니다.

sql Server2008R2 까지의 버전에서의 제한된 지원은 정확히 한 행의 목표가있는 곳에 적용됩니다. 이 특정 상황에서 SQL Server 는 예상 비용을 기준으로 연결 입력을 재정렬합니다.

이렇게 하지 않는 동안 비용 기반으로 최적화(하나는 기대할 수 있습니다)하지만,오히려 마지막으로 분 후 최적화를 다시 쓰기의 정상적인 최적화 프로그램 출력됩니다. 이 배열의 이점이 있는지 증가 비용 기반한 계획 검색 공간(재 대체에 대한 각각의 가능한 재정렬),하는 동안 여전히 생산 계획는 최적화되어 반환하는 첫 번째 행다.

예제

다음 예제는 내용이 동일한 두 개의 테이블을 사용합니다. 하나의 테이블은 비 클러스터 된 인덱스가없는 힙이고 다른 테이블은 고유 한 클러스터 된 인덱스를 가지고 있습니다:

CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);

행 목표

다음과 같은 쿼리를 위한 동일한 행에서 각각 테이블,그리고 반환합니다 연결하여 두 집합:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;

실행 계획에 의해 생산 쿼리 최적화 프로그램:

경고에 뿌리SELECT운영자는 우리에게 경고를 선택하실 지수에 힙 테이블. 테이블 스캔 연산자의 경고는 센트리 원 플랜 탐색기에 의해 추가됩니다. 스캔 내에 숨겨진 잔여 술어의 입출력 비용에 우리의주의를 환기시키고 있습니다.

우리가 행 목표를 설정하지 않았기 때문에 연결에 대한 입력의 순서는 여기서 중요하지 않습니다. 모든 결과 행을 반환하기 위해 두 입력을 완전히 읽습니다. 관심의 대상(이것이 보장되지는 않지만)입력의 순서가 원래 쿼리의 텍스트 순서를 따른다는 것을 알 수 있습니다. 관찰도의 순서는 최종 결과는 행을 지정하지 않은 하나 이후,우리는 사용하지 않고 최고 수준의ORDER BYclause. 우리는 고의적이며 최종 주문이 손에있는 작업에 중요하지 않다고 가정 할 것입니다.

다음과 같이 쿼리에서 테이블의 작성된 순서를 반대로하면:

SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;

실행 계획을 다음과 같이 변화에 액세스하는 클러스터 테이블(첫 또 다시,이것은 보장되지는 않음):

모두를 쿼리할 수 있을 것으로 예상되는 동일한 성능을 특징으로,그들은 같은 작업을 수행,그냥 다른 순서.

행 목표

명확하고,부족의 인덱싱에 힙 테이블이 정상적으로 만들이 찾는 특정 행 더 비싸고,비교와 동일한 작업에서 클러스터 테이블. 가 요청할 경우 최적화 프로그램에 대한 계획을 반환하는 첫 번째 행에 신속하게,우리가 기대하는 것 SQL 서버를 재정렬 연결을 입력 그래서 저렴한 클러스터 테이블은 먼저 상담을.

힙 테이블을 먼저 언급하는 쿼리를 사용하고 빠른 1 쿼리 힌트를 사용하여 행 목표를 지정합니다:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);

예상 생성된 실행 계획을 인스턴스에 대한 SQL Server2008R2 입니다:

통 연결 입력을 다시 정렬되었습을 줄 예상 비용을 돌아의 첫 번째 행이 있습니다. 또한 누락 된 색인 및 잔여 입출력 경고가 사라졌습니다. 목표가 가능한 한 빨리 단일 행을 반환 할 때 어느 문제도이 계획 모양에 결과가 없습니다.

동일한 쿼리를 실행에 SQL Server2016(중 하나를 사용하여 카디널리티 예측 모델)

SQL Server2016 지 않은 다시 정렬 연결하여 입력이 있습니다. 계획 탐색기 I/O 경고가 반환되었지만 슬프게도 옵티마이 저는 이번에 누락 된 인덱스 경고를 생성하지 않았습니다(관련성이 있지만).

일반 재정렬

언급 한 바와 같이 연결 입력을 재정렬하는 최적화 후 재 작성은 다음과 같은 경우에만 효과적입니다:

• SQL Server2008R2 이전 버전
• 행 목표를 정확히 하나의

경우 우리가 진정으로 하나만 반환되는 행보다는 계획을 최적화되어 반환하는 첫 번째 행을 신속하고(그러나 어떤 것이 궁극적으로는 여전히 모든 행 반환),우리가 사용할 수 있는TOP절 파생된 테이블 또는 일반적인 표현(CTE):

SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;

에 SQL Server2008R2 이하 버전이 생산 최적의 재정렬 입력 계획:

에 SQL Server2012,2014 년,및 2016 후 최적화 순서가 발생합니다.

경우 우리는 하나 이상의 행 돌아 예를 들어 사용하는TOP (2),원하는 재작성에는 적용되지 않습니다 SQL Server2008R2 는 경우에도FAST 1TOPOPTIMIZE FOR힌트:

DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint

쿼리 힌트를 설정하기에 충분하는 행의 목표는 하나는 동안 런타임에는 변수 값을 보장하고 원하는 행의 수(2)반환한다.

실제 실행을 계획에서 SQL Server2008R2

모두 행 돌아서 다시 정렬 입력을 추구하고,테이블 검색이 실행되지 않습니다. 계획 탐색기에 표시됩 행 계산에 빨간이기 때문에 예상한 행(로 인한 힌트)반면 두 개의 행이 발생했습니다.

UNION 없이 ALL

이 문제는UNION ALLEXISTSOR발생할 수 있습니다 최적화 프로그램 소개 연결하여 연산자를 겪을 수 있는 부족에서의 입력을 공개적으로 연결됩니다. 정확히이 문제로 데이터베이스 관리자 스택 교환에 대한 최근 질문이있었습니다. 변형 쿼리에서 그 질문을 사용하여 우리의 예 테이블:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;

실행 계획에 SQL Server2016 는 힙 테이블에 입력:

에 SQL Server2008R2 의 순서 입력을 최적화를 반영한 단 하나 줄의 목표는 반 가입:

에 더 많은 최적의 계획,힙 검사는 결코 실행됩니다.

해결 방법

어떤 경우에,그것은 것이 명백한 쿼리는 작가 중 하나의 연결을 입력 항상 저렴해 실행됩니다. 그것이 사실이라면,더 싼 연결 입력이 서면 순서대로 먼저 나타나도록 쿼리를 다시 작성하는 것이 매우 유효합니다. 물론 이것은 쿼리 작성자가이 옵티 마이저 제한을 인식하고 문서화되지 않은 동작에 의존 할 준비가되어 있어야 함을 의미합니다.

더 어려운 문제가 발생한 경우의 비용은 연결 입력에 따라 상황에서,아마도에 따라 매개 변수의 값입니다. OPTION (RECOMPILE)를 사용하면 SQL Server2012 이상에서 도움이되지 않습니다. 이 옵션은 SQL Server2008R2 이상에서 도움이 될 수 있지만 단일 행 목표 요구 사항도 충족되는 경우에만 가능합니다.

에 대한 우려가 있는 경우에 의존하는 관찰된 동(쿼리 계획을 연결 입력을 일치하는 쿼리를 텍스트 순서대로)을 계획을 사용할 수 있는 계획을 적용하려면 모양입니다. 다른 입력 주문에 대한 최적의 서로 다른 상황에서,여러 계획을 사용할 수 있는 조건 수 있는 정확하게 코딩된다. 이것은 거의 이상적이지 않습니다.

최종 생각

SQL 서버는 쿼리를 최적화제하지 않을 포함 비용을 기반으로 하는 탐사 규칙,UNIAReorderInputs는 생성할 수 있는 연결을 입력해 변화와 대안을 탐험하는 동안 비용 기반으로 최적화(지 않으로 단일 총 게시물 최적화를 다시 쓰).

이 규칙은 현재 일반용으로 활성화되어 있지 않습니다. 내가 알 수있는 한 계획 가이드 또는USE PLAN힌트가있는 경우에만 활성화됩니다. 이것은 엔진이 성공적으로 강제하는 계획에 대해 생성하는 쿼리를 자격을 갖춘 입력을 위해-재정렬 다시 쓰기 때라도,현재의 쿼리를 자격을 주지 않는다.

My 감각이 탐험은 규칙이 의도적으로 이에 제한되 사용하기 때문에,쿼리에서 혜택을 것 이라고 연결한 재정렬 입력의 일환으로 비용 기반으로 최적화되지 않는 것으로 간주됩 충분히 일반적인,또는 아마도 때문에 우려가 있는 여분의 노력하지 않습니다. 내 자신의 견해는 행 목표가 적용될 때 연결 연산자 입력 순서 바꾸기가 항상 탐색되어야한다는 것입니다.

또한 SQL Server2012 이상에서(더 제한된)사후 최적화 재 작성이 효과적이지 않은 것은 부끄러운 일입니다. 그리고 성경에 기록된 것들 때문이에 미묘한 버그,하지만 나는 아무것도 찾을 수 없습니다 이것에 대해 설명서에 기술 자료,또는 연결합니다. 여기에 새로운 연결 항목을 추가했습니다.

2017 년 8 월 9 일 업데이트:이제 sql Server2014 및 2016 의 추적 플래그 4199 에서 수정되었습니다.KB4023419:

수정: SQL Server2008R2

와 비교할 때 UNION ALL 및 row 목표가있는 쿼리는 sql Server2014 이상 버전에서 느리게 실행될 수 있습니다.