OPTION(재컴파일)은 항상 고속입니다.그 이유는 무엇입니까?

OPTION(재컴파일)은 항상 고속입니다.그 이유는 무엇입니까?

이상한 .OPTION (RECOMPILE)쿼리로 인해 쿼리는 0.5초 만에 실행되지만 생략하면 쿼리는 5분 이상 걸립니다.

Query Analyzer를 통해# 입니다.SqlCommand.ExecuteReader()하지 않음DBCC FREEPROCCACHE ★★★★★★★★★★★★★★★★★」DBCC dropcleanbuffers는 항상 즉시 됩니다. 이치OPTION (RECOMPILE) 의 경우 쿼리는 항상 이위해서).쿼리는 항상 같은 파라미터로 호출됩니다(이 테스트를 위해).

SQL Server 2008을 사용하고 있습니다.

을 쓰는 꽤 한 적은 .OPTION이 포럼의 투고를 스캔하기 전까지는 플랜 캐시 개념 전체에 익숙하지 않았습니다.으로 알 수 로는 ★★★★★★★★★★★★★★★★★★★★★★★★★.OPTION (RECOMPILE)비용이 많이 드는 수술입니다.쿼리에 대한 새로운 검색 전략을 만드는 것 같습니다.그 은 왜 요?OPTION (RECOMPILE)힌트를 해야 하는 것 요?후속 쿼리는 재컴파일 힌트를 포함한 이전 콜에서 계산된 룩업 전략을 사용해야 하지 않습니까?

콜마다 재컴파일 힌트를 필요로 하는 쿼리가 있는 것은 매우 드문 일입니까?

초보적인 질문이라 죄송합니다만, 저는 정말 이해할 수 없습니다.

업데이트: 쿼리를 게시하도록 요청받았습니다...

select acctNo,min(date) earliestDate 
from( 
    select acctNo,tradeDate as date 
    from datafeed_trans 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_money 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_jnl 
    where feedid=@feedID and feedDate=@feedDate 
)t1 
group by t1.acctNo
OPTION(RECOMPILE)

Query Analyzer에서 테스트를 실행할 때 다음 행을 추가합니다.

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

할 때 는 C#을 됩니다.SqlCommand.Parameters★★★★★★★★★★★★★★★★★★.

이 설명에서는 파라미터가 변경되지 않기 때문에 최적의 파라미터 냄새는 원인에서 제외할 수 있습니다.

사용하기도 합니다.OPTION(RECOMPILE)말이 되네.지금까지의 경험으로는 다이내믹 SQL을 사용하는 경우에만 이 옵션을 사용할 수 있습니다.이것이 당신의 상황에서 말이 되는지 알아보기 전에 나는 당신의 통계를 재구축할 것을 추천한다.이를 수행하려면 다음 작업을 수행합니다.

EXEC sp_updatestats

그리고 실행 계획을 다시 만듭니다.이렇게 하면 실행 계획이 작성될 때 최신 정보를 사용할 수 있습니다.

" " " OPTION(RECOMPILE)는 조회가 실행될 때마다 실행 계획을 재구축합니다. 적이 요.creates a new lookup strategy같은 것을 다른 용어로 사용하고 있는 것일지도 모릅니다.

스토어드 프로시저가 작성되었을 때(에서 애드혹SQL을 호출하고 있을 것으로 생각됩니다).NET 단, 파라미터화된 쿼리를 사용하는 경우 이는 저장된 proc 콜이 됩니다). SQL Server는 데이터베이스 내의 데이터와 전달된 파라미터(파라미터 스니핑)를 기반으로 이 쿼리에 대한 가장 효과적인 실행 계획을 결정하고 이 계획을 캐시합니다.즉, 데이터베이스에 10개의 레코드가 있는 곳에 쿼리를 작성한 후 100,000,000개의 레코드가 있을 때 쿼리를 실행하면 캐시된 실행 계획이 가장 효과적이지 않을 수 있습니다.

저는 그렇게 할 OPTION(RECOMPILE)을 사용하다상황에 이 될 수 .고객의 상황에 따라서는, 통계의 재구축이 DBA 업무의 중요한 부분이 될 수 있습니다.통계 갱신 후에도 문제가 해결되지 않으면 두 가지 실행 계획을 모두 게시하는 것이 좋습니다.

그리고 질문에 대답하자면, 쿼리를 실행할 때마다 실행 계획을 재컴파일하는 것은 매우 이례적인 일입니다.

쿼리의 실행마다 큰 차이가 있는 경우, 5가지 문제 중 하나가 되는 경우가 많습니다.

1. 통계 - 통계가 최신이 아닙니다.데이터베이스는 테이블 및 인덱스의 다양한 열에 값 유형의 범위 및 분포에 대한 통계를 저장합니다.이를 통해 쿼리 엔진은 해시를 사용하여 테이블 간에 키를 대조하거나 세트 전체를 조사하기 위해 사용하는 방식 등 쿼리 수행 방법에 대한 "계획" 공격을 개발할 수 있습니다.전체 데이터베이스 또는 특정 테이블 또는 인덱스에서만 통계 업데이트를 호출할 수 있습니다.이렇게 하면 한 실행에서 다른 실행으로 쿼리가 느려집니다. 통계가 최신이 아닐 경우 쿼리 계획이 동일한 쿼리에 대해 새로 삽입되거나 변경된 데이터에 대해 최적화되지 않을 수 있기 때문입니다(나중에 자세히 설명).샘플화할 데이터의 양에 따라 오버헤드, 속도 저하 및 지연이 발생하기 때문에 프로덕션 데이터베이스에서 통계를 즉시 업데이트하는 것은 적절하지 않을 수 있습니다.전체 스캔 또는 샘플링을 사용하여 통계를 업데이트할 수도 있습니다.쿼리 계획을 보면 DBCC SHOW_STATISTICS(tablename, indexname) 명령을 사용하여 사용 중인 인덱스에 대한 통계도 볼 수 있습니다.쿼리 계획이 접근 방식에 기반하는 키의 분포와 범위가 표시됩니다.

2. PARAMETER SNIPING - 쿼리 자체는 변경되지 않았지만 캐시된 쿼리 계획은 전달 중인 특정 파라미터에 적합하지 않습니다.예를 들어 1,000,000 행 중 10 행만 가져오는 매개 변수를 전달하면 작성된 쿼리 계획에서 해시 조인(Hash Join)을 사용할 수 있지만 전달된 매개 변수가 1,000,000 행 중 750,000 행을 사용하는 경우 작성된 계획은 인덱스 스캔 또는 테이블 스캔일 수 있습니다.이러한 상황에서는 OPTION(RECOMFILE) 또는 SP를 사용하여 RECOMFILE과 함께 사용하도록 SQL 문에 지시할 수 있습니다.엔진에 "일회용 계획"이며 적용되지 않을 가능성이 높은 캐시된 계획을 사용하지 않도록 지시합니다.이 결정 방법에 대한 규칙은 없습니다. 사용자가 쿼리를 사용하는 방법을 알고 있어야 합니다.

3. INDEX - 쿼리가 변경되지 않았을 수 있지만 매우 유용한 인덱스 제거와 같은 다른 변경으로 인해 쿼리가 느려졌습니다.

4. ROWS CHANGED - 문의하는 행이 콜마다 대폭 변경됩니다.통상은, 통계가 자동적으로 갱신됩니다.단, 동적 SQL을 구축하거나 엄격한 루프 내에서 SQL을 호출하는 경우 잘못된 극단적 수의 행 또는 통계를 기반으로 한 오래된 쿼리 계획을 사용하고 있을 수 있습니다.이 경우에도 OPTION(RECOMPILE)이 유용합니다.

5. 논리입니다. 쿼리는 더 이상 효율적이지 않습니다. 소수의 행은 괜찮았지만 더 이상 확장되지 않습니다.여기에는 보통 쿼리 계획에 대한 보다 심층적인 분석이 포함됩니다.예를 들어, 더 이상 대량으로 작업을 수행할 수 없고, 청크와 더 작은 커밋을 수행해야 합니다.또는 크로스 프로덕트가 작은 세트에서는 괜찮았지만, 현재는 CPU와 메모리를 크게 사용하고 있습니다.이것은 DISTINT를 사용하는 경우에도 마찬가지입니다.행마다 함수를 호출합니다.CASTING 유형 변환, NULLS 또는 함수 때문에 키 일치에 인덱스가 사용되지 않습니다.여기엔 가능성이 너무 많아요.

일반적으로 쿼리를 작성할 때는 특정 데이터가 테이블 내에서 어떻게 분포되어 있는지에 대한 대략적인 그림을 가지고 있어야 합니다.예를 들어, 열에 균등하게 분포된 서로 다른 값의 수가 있을 수도 있고, 80%의 시간에 따라 분포가 자주 변동하는지 아니면 상당히 정적인지에 관계없이 특정 값 집합이 있을 수도 있습니다.이를 통해 효율적인 쿼리를 작성하는 방법을 보다 잘 이해할 수 있습니다.그러나 디버깅을 할 때 쿼리 성능은 왜 느린지 또는 비효율적인지에 대한 가설을 세울 수 있는 기초가 됩니다.

OPTION(RECOMFILE)이 매우 도움이 될 수 있는 상황의 우수한 목록(@CodeCowboyOrg에서 제공)에 추가합니다.

6. 표 변수테이블 변수를 사용하는 경우 테이블 변수에 대한 사전 작성된 통계량이 없으므로 쿼리 계획에서 추정된 행과 실제 행 간에 큰 차이가 발생할 수 있습니다.테이블 변수가 있는 쿼리에 OPTION(RECOMPILE)을 사용하면 관련된 행 번호를 훨씬 더 잘 추정할 수 있는 쿼리 계획을 생성할 수 있습니다.OPTION(RECOMPILE)을 추가할 때까지 사용할 수 없는 테이블 변수를 특히 중요하게 사용했습니다.상영 시간이 몇 시간에서 몇 분으로 단축되었습니다.이것은 아마 드문 일이지만, 어떤 경우에도 테이블 변수를 사용하고 최적화에 임하고 있다면 OPTION(RECOMPILE)에 의해 차이가 나는지 확인해 볼 필요가 있습니다.

쿼리를 조정하기 전에 가장 먼저 수행하는 작업은 인덱스 및 통계를 조각 모음/재구축하는 것입니다. 그렇지 않으면 시간을 낭비하게 됩니다.

실행 계획이 안정적인지 확인해야 합니다(파라미터 변경 시 동일). 그렇지 않은 경우 각 테이블에 대해 커버 인덱스를 작성해야 할 수 있습니다(다른 쿼리에 대해서도 사용할 수 있는 시스템을 알 수 있습니다).

예: create index idx01_datafeed_trans On datafeed_trans(feedid, feedDate) INCLUDE(acctNo, tradeDate)

계획이 안정적이거나 안정화 할 수 있는 경우 sp_sql sql sql ' ' sql ' ' sp sp the the the the the the the the the the the the the the the the the the the the the the

계획이 불안정할 경우 매번 실행 계획을 평가하고 작성하기 위해 임시 문장 또는 EXEC('sql 문장')를 사용해야 합니다.(또는 저장 프로시저 "재컴파일").

도움이 됐으면 좋겠다.

이 질문을 다시 끄집어내지만 아무도 고려하지 않은 설명이 있다.

통계 - 통계를 사용할 수 없거나 잘못된 정보를 제공합니다.

다음 조건이 모두 충족될 경우:

1. 열 feedid와 feedDate는 높은 상관관계가 있을 수 있습니다(예를 들어 피드 ID는 피드 날짜보다 구체적이고 날짜 매개 변수는 중복 정보임).
2. 두 열을 순차 열로 하는 색인이 없습니다.
3. 이 두 열을 모두 포함하는 수동으로 작성된 통계는 없습니다.

그러면 SQL Server가 열이 상관 관계가 없다고 잘못 가정하여 제한 적용에 대한 카디널리티 추정치가 예상보다 낮아지고 실행 계획이 제대로 선택되지 않을 수 있습니다.이 경우 수정은 두 열을 연결하는 통계 개체를 만드는 것입니다. 이 작업은 비용이 많이 들지 않습니다.

OPTION (RECOMPILE)는 실제 단어 생성 시나리오에서 사용됩니다.파라미터의 냄새를 제거하고 큰 쿼리를 최적화하기 위해 사용하고 있습니다.이것이 문제에 대한 해답일 수도 있지만, 미지의 것(예: 로컬 변수)을 최적화하면 문제를 해결할 수 있다는 징후가 있습니다.

몇 년 전에 수정한 타임 버그가 있다고 해서 그 옵션을 피할 수는 없습니다.★★★★★★★★★★★★★★★의 주요 리스크OPTION (RECOMPILE)고주파 요구 등 부적절하게 사용되는 경우입니다.

언급URL : https://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why