主页 / dba / 问题 / 346346
Accepted
J. Mini
Asked: 2025-04-30 02:19:23 +0800 CST

OPTIMIZE FOR UNKNOWN 对表值变量/参数有什么作用吗？

  • 772

我有一个查询，它既容易受到参数敏感性的影响，又受表值参数的影响。我很懒，只想用查询提示来解决这个问题。当我懒惰的时候，我可以用 来解决参数敏感性问题OPTION (OPTIMIZE FOR UNKNOWN)。这给了我三个非常紧密相关的问题：

  1. 对表值变量OPTION (OPTIMIZE FOR UNKNOWN)有影响吗？
  2. 对表值参数OPTION (OPTIMIZE FOR UNKNOWN)有影响吗？
  3. 除了 之外，还有哪些查询提示OPTION(RECOMPILE)可用于影响表值参数的基数估计？

我自己的实验和文档都表明，这根本没用。表值参数很容易被参数嗅探，但嗅探到的只是基数，所以OPTION (OPTIMIZE FOR UNKNOWN)应该什么也不做。

至于使用其他表提示的问题，我知道我可以更改对表值参数的操作的估计（例如OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')），但我不认为提示可以影响参数本身的基数估计。

sql-server

1 个回答

  1. Best Answer

    哎呀

    看起来OPTIMIZE FOR UNKNOWN对表变量或参数没有任何影响。事实上，如果你尝试指定OPTIMIZE FOR (@data UNKNOWN)或OPTIMIZE FOR (@data = NULL)，你会得到

    Msg 137 Level 16 State 1 Line 7
Must declare the scalar variable "@data".

    这告诉我们这个提示仅适用于标量变量，完全没有记录。

    过去，表变量没有进行任何基数估计，估计值为 1 或 100 行。表变量延迟编译是使用跟踪标志 2453引入的，并且从 2019 年开始作为数据库引入，您可以使用提示禁用它。这通常会导致估计值正确，但在您的情况下，这似乎是不理想的。

    得到提示

    • 关闭 TF2453 或使用查询提示USE HINT (N'DISABLE_DEFERRED_COMPILATION_TV')仅适用于表变量，而不适用于参数。
    • ROBUST PLAN并且FAST 1不会影响这里的基数估计。

    坏的

    您可以将整个数据插入到表变量中。默认情况下，根据版本不同，估计会得到 1 行或 100 行。

    如果 TF2453 已启用，您还需要将其关闭。在 2019 及更高版本中，您需要禁用延迟表变量编译。您也可以使用QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100，但那太宽泛了。

    CREATE OR ALTER PROCEDURE p
  @data dbo.t READONLY
AS

DECLARE @data2 dbo.t;
INSERT @data2
SELECT *
FROM @data;

SELECT COUNT(*) AS c
FROM @data2
OPTION (USE HINT (N'DISABLE_DEFERRED_COMPILATION_TV'));

    这有一个明显的缺点，就是需要复制整个 TVP，而这可能非常大。

    丑陋

    您可以将sp_prepareandsp_execute与动态SQL一起使用，以强制编译而无需进行基数估计，如@PaulWhite的文章中所述。 在这种情况下没有复制。

    CREATE OR ALTER PROCEDURE p
  @data dbo.t READONLY
AS

DECLARE @handle int;
EXEC sp_prepare @handle OUT,
  N'@data dbo.t READONLY',
  N'
SELECT COUNT(*) AS c
FROM @data;
';
EXEC sp_execute @handle, @data = @data;

    这只适用于简单的语句，否则编译会被推迟并且参数会被嗅探。

    但它使程序的编写和调试变得有点混乱。

    好的

    只需整理一下你的查询。为什么OPTION (RECOMPILE)问题没有解决？或者优化器在估算正确的情况下，到底是什么导致了它无法正常工作？那就解决这个问题吧。

    或者只是得到一个好的计划并强制使用查询存储。

    您可以在此小提琴中看到选项。

    • 7

相关问题