OPTION (RECOMPILE)은 항상 빠릅니다.

OPTION (RECOMPILE)은 항상 빠릅니다. 왜?

---

OPTION (RECOMPILE)쿼리에 추가 하면 0.5 초 안에 실행되는 반면, 생략하면 쿼리는 5 분 이상 걸리는 이상한 상황이 발생했습니다.

쿼리를 통해 쿼리 분석기 또는 내 C # 프로그램에서 쿼리를 실행하는 경우 SqlCommand.ExecuteReader()입니다. 전화 (또는 전화하지 않음) DBCC FREEPROCCACHE또는 DBCC dropcleanbuffers차이가 없습니다. 쿼리 결과는 항상 OPTION (RECOMPILE)5 분을 초과하지 않고 항상 즉시 반환 됩니다. 이 테스트를 위해 쿼리는 항상 동일한 매개 변수로 호출됩니다.

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

SQL 작성에 상당히 익숙하지만 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없지만 아마도 같은 용어에 대해 다른 용어를 사용하고있을 것입니다.

저장 프로 시저가 만들어 질 때 (.NET에서 ad-hoc sql을 호출한다고 생각하지만 매개 변수가있는 쿼리를 사용하는 경우 저장 프로 시저 호출이됩니다 ) SQL Server는이 쿼리에 대한 가장 효과적인 실행 계획을 결정하려고합니다. 데이터베이스의 데이터 및 전달 된 매개 변수 ( parameter sniffing )를 기반으로이 계획을 캐시합니다. 즉, 데이터베이스에 10 개의 레코드가있는 쿼리를 생성 한 다음 100,000,000 개의 레코드가있는 경우 실행하면 캐시 된 실행 계획이 더 이상 가장 효과적이지 않을 수 있습니다.

요약하면- OPTION(RECOMPILE)여기에 도움이 될 이유가 없습니다 . 통계와 실행 계획을 업데이트해야한다고 생각합니다. 통계 재 구축은 상황에 따라 DBA 작업의 필수 부분이 될 수 있습니다. 통계를 업데이트 한 후에도 여전히 문제가 발생하면 두 실행 계획을 모두 게시하는 것이 좋습니다.

그리고 귀하의 질문에 대답하기 위해-예, 최선의 선택이 쿼리를 실행할 때마다 실행 계획을 다시 컴파일하는 것은 매우 드문 일입니다.

---

쿼리 실행마다 급격한 차이가있을 때 종종 5 가지 문제 중 하나라는 것을 알았습니다.

1. 통계 - Statistics are out of date. A database stores statistics on the range and distribution of the types of values in various column on tables and indexes. This helps the query engine to develop a "Plan" of attack for how it will do the query, for example the type of method it will use to match keys between tables using a hash or looking through the entire set. You can call Update Statistics on the entire database or just certain tables or indexes. This slows down the query from one run to another because when statistics are out of date, its likely the query plan is not optimal for the newly inserted or changed data for the same query (explained more later below). It may not be proper to Update Statistics immediately on a Production database as there will be some overhead, slow down and lag depending on the amount of data to sample. You can also choose to use a Full Scan or Sampling to update Statistics. If you look at the Query Plan, you can then also view the statistics on the Indexes in use such using the command DBCC SHOW_STATISTICS (tablename, indexname). This will show you the distribution and ranges of the keys that the query plan is using to base its approach on.

2. PARAMETER SNIFFING - The query plan that is cached is not optimal for the particular parameters you are passing in, even though the query itself has not changed. For example, if you pass in a parameter which only retrieves 10 out of 1,000,000 rows, then the query plan created may use a Hash Join, however if the parameter you pass in will use 750,000 of the 1,000,000 rows, the plan created may be an index scan or table scan. In such a situation you can tell the SQL statement to use the option OPTION (RECOMPILE) or an SP to use WITH RECOMPILE. To tell the Engine this is a "Single Use Plan" and not to use a Cached Plan which likely does not apply. There is no rule on how to make this decision, it depends on knowing the way the query will be used by users.

3. INDEXES - Its possible that the query haven't changed, but a change elsewhere such as the removal of a very useful index has slowed down the query.

4. ROWS CHANGED - The rows you are querying drastically changes from call to call. Usually statistics are automatically updated in these cases. However if you are building dynamic SQL or calling SQL within a tight loop, there is a possibility you are using an outdated Query Plan based on the wrong drastic number of rows or statistics. Again in this case OPTION (RECOMPILE) is useful.

5. THE LOGIC Its the Logic, your query is no longer efficient, it was fine for a small number of rows, but no longer scales. This usually involves more indepth analysis of the Query Plan. For example, you can no longer do things in bulk, but have to Chunk things and do smaller Commits, or your Cross Product was fine for a smaller set but now takes up CPU and Memory as it scales larger, this may also be true for using DISTINCT, you are calling a function for every row, your key matches don't use an index because of CASTING type conversion or NULLS or functions... Too many possibilities here.

In general when you write a query, you should have some mental picture of roughly how certain data is distributed within your table. A column for example, can have an evenly distributed number of different values, or it can be skewed, 80% of the time have a specific set of values, whether the distribution will varying frequently over time or be fairly static. This will give you a better idea of how to build an efficient query. But also when debugging query performance have a basis for building a hypothesis as to why it is slow or inefficient.

---

To add to the excellent list (given by @CodeCowboyOrg) of situations where OPTION(RECOMPILE) can be very helpful,

6. Table Variables. When you are using table variables, there will not be any pre-built statistics for the table variable, often leading to large differences between estimated and actual rows in the query plan. Using OPTION(RECOMPILE) on queries with table variables allows generation of a query plan that has a much better estimate of the row numbers involved. I had a particularly critical use of a table variable that was unusable, and which I was going to abandon, until I added OPTION(RECOMPILE). The run time went from hours to just a few minutes. That is probably unusual, but in any case, if you are using table variables and working on optimizing, it's well worth seeing whether OPTION(RECOMPILE) makes a difference.

---

The very first actions before tunning queries is to defrag/rebuild the indexes and statistics, otherway you're wasting your time.

You must check the execution plan to see if it's stable (is the same when you change the parameters), if not, you might have to create a cover index (in this case for each table) (knowing th system you can create one that is usefull for other queries too).

as an example : create index idx01_datafeed_trans On datafeed_trans ( feedid, feedDate) INCLUDE( acctNo, tradeDate)

if the plan is stable or you can stabilize it you can execute the sentence with sp_executesql('sql sentence') to save and use a fixed execution plan.

if the plan is unstable you have to use an ad-hoc statement or EXEC('sql sentence') to evaluate and create an execution plan each time. (or a stored procedure "with recompile").

Hope it helps.

---

Necroing this question but there's an explanation that no-one seems to have considered.

STATISTICS - Statistics are not available or misleading

If all of the following are true:

1. The columns feedid and feedDate are likely to be highly correlated (e.g. a feed id is more specific than a feed date and the date parameter is redundant information).
2. There is no index with both columns as sequential columns.
3. There are no manually created statistics covering both these columns.

Then sql server may be incorrectly assuming that the columns are uncorrelated, leading to lower than expected cardinality estimates for applying both restrictions and a poor execution plan being selected. The fix in this case would be to create a statistics object linking the two columns, which is not an expensive operation.

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