何时使用 OPTIMIZE FOR UNKNOWN 的经验 [英] Experience with when to use OPTIMIZE FOR UNKNOWN

问题描述

我已阅读 SQL Server 2008 的OPTIMIZE FOR UNKNOWN"查询计划选项背后的理论和观点.我非常了解它的功能.

I have read the theory and views behind SQL Server 2008's "OPTIMIZE FOR UNKNOWN" query plan option. I understand what it does well enough.

我做了一些有限的实验，发现使用热缓存，它只对 > 100k 行有益.然而，这是在一个简单的表和查询上，没有连接、过滤等.在冷缓存上，图片无疑会更有利.

I did some limited experiments and found that with a warm cache, it only was of benefit on > 100k rows. However, this was on a simple table and query with no joins, filtering, etc. On a cold cache, the picture would undoubtedly be much more in its favor.

我目前没有一个生产系统来测试之前/之后.因此，我很好奇是否有人在测试之前/之后进行了测试，并就何时使用该选项以及何时不使用该选项做出了任何有用的发现.

I don't currently have a production system to bench the before/after. So I am curious if anyone has done before/after testing and made any useful discoveries as to exactly when to use this option and when not to.

更新:

我创建了一个包含 3 个列的表、一个 UID 上的 PK 以及一个 Col2 (int) 上的索引.所有处理都针对 Col2.表示的是行数和时间(DATEDIFF * 1000000):

I created a table with 3 cols, a PK on the UID, and an index on Col2 (int). All processing was against Col2. Indicated are number of rows and time (DATEDIFF * 1000000):

Type        1,000   5,000   20,000  100,000
Normal      0.3086  6.327   26.427  144.83, 141.126
Recompile           117.59  584.837 
For Unknown 0.8101  6.52    26.89   143.788, 143.248

推荐答案

当您的数据有足够的偏差以致使用一个参数值生成的计划完全不适合该参数的另一个潜在值时，您将使用它.即解决参数嗅探问题.

You would use it when your data is sufficiently skewed that a plan generated with one parameter value would be completely unsuitable for another potential value of the parameter. i.e. to resolve a parameter sniffing issue.

您的问题的其余部分似乎与提示的目的或可回答的 IMO 无关.

The remainder of your question doesn't seem particularly relevant to the purpose of the hint or answerable IMO.

这篇关于何时使用 OPTIMIZE FOR UNKNOWN 的经验的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！