我遇到了一种奇怪的情况，向查询添加OPTION (RECOMPILE)会导致它在半秒内运行，而省略它会导致查询花费远远超过五分钟的时间。
当查询从查询分析器或通过sqlcommand . executerreader()从我的c#程序执行时，就是这种情况。调用(或不调用)DBCC FREEPROCCACHE或DBCC dropcleanbuffers没有区别;使用OPTION (RECOMPILE)查询结果总是立即返回，不使用则超过5分钟。查询总是使用相同的参数(为了测试)调用。
我使用的是SQL Server 2008。
I'm fairly comfortable with writing SQL but have never used an OPTION command in a query before and was unfamiliar with the whole concept of plan caches until scanning the posts on this forum. My understanding from the posts is that OPTION (RECOMPILE) is an expensive operation. It apparently creates a new lookup strategy for the query. So why is it then, that subsequent queries that omit the OPTION (RECOMPILE) are so slow? Shouldn't the subsequent queries be making use of the lookup strategy that was computed on the previous call which included the recompilation hint?
在每个调用上都需要重新编译提示的查询是否非常罕见?
抱歉问了这么初级的问题，但我真的搞不懂。
更新:我被要求张贴查询…
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传入的。参数属性。
为了讨论的目的，您可以假设参数永远不会改变，因此我们可以排除次优参数嗅觉的原因。
有时使用OPTION(RECOMPILE)是有意义的。根据我的经验，只有在使用动态SQL时才有这种可行的选择。在你探索这在你的情况下是否有意义之前，我建议重建你的统计数据。这可以通过运行以下命令来完成:
EXEC sp_updatestats
然后重新创建你的执行计划。这将确保在创建执行计划时使用最新的信息。
每次执行查询时，添加OPTION(RECOMPILE)都会重新构建执行计划。我从来没有听说过创建一个新的查找策略，但也许我们只是用不同的术语来描述同一件事。
When a stored procedure is created (I suspect you are calling ad-hoc sql from .NET but if you are using a parameterized query then this ends up being a stored proc call) SQL Server attempts to determine the most effective execution plan for this query based on the data in your database and the parameters passed in (parameter sniffing), and then caches this plan. This means that if you create the query where there are 10 records in your database and then execute it when there are 100,000,000 records the cached execution plan may no longer be the most effective.
总之，我不认为OPTION(RECOMPILE)有任何好处。我猜你只需要更新你的统计数据和执行计划。根据您的情况，重新构建统计信息可能是DBA工作的重要部分。如果你在更新数据后仍然有问题，我建议你发布两个执行计划。
为了回答您的问题——是的，我想说，对于您的最佳选择来说，每次执行查询时都重新编译执行计划是非常不寻常的。
通常，当一个查询的运行与运行之间存在巨大差异时，我发现它通常是5个问题之一。
STATISTICS - 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.
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.
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.
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.
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.
