何时重用非参数化、非平凡、即席查询计划

我目前正在研究一个应用程序，该应用程序似乎针对它正在查询的数据库生成 99% 的即席查询计划。我可以通过运行以下语句来检索查询计划缓存中的对象摘要来验证这一点：

_{抱歉无法在 SE 编辑器中输入代码，因此截图}

^{参考：为临时工作负载规划缓存和优化(SQLSkills.com / K. Tripp)，稍作修改}

上述查询结果如下：

CacheType            Total Plans          Total MBs                               Avg Use Count Total MBs - USE Count 1                 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc                158997               5749.520042                             2             2936.355979                             126087
Prepared             1028                 97.875000                               695           46.187500                               576
Proc                 90                   69.523437                               39659         21.187500                               21
View                 522                  75.921875                               99            0.453125                                3
Rule                 4                    0.093750                                22            0.000000                                0
Trigger              1                    0.070312                                12            0.000000                                0    

在计划缓存中的 158'997 个临时查询中，有 126'087 个查询只执行了一次。

在对即席查询的进一步检查中，我发现有些查询甚至会生成多次。我使用以下查询检查了计划缓存，以检索相同的执行计划：

SELECT SUM(cplan.usecounts) AS [Unique Same Single Plans],
       qtext.text
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
             CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'   -- <-- only Adhoc plans
       AND sdb.name = 'DATABASENAME' -- <-- for a certain database
       AND cplan.usecounts = 1       -- <-- with a usecounts of 1
GROUP BY
       qtext.text having sum(cplan.usecounts) > 1
ORDER BY
       1 DESC --,cplan.objtype, cplan.usecounts   

^{参考：不记得了。让我知道它是否最初是你的，我会归于它。}

这给了我一个即席查询列表，这些查询的查询计划与现有的相同查询计划相同，并且计划缓存中唯一相同的查询计划的总和。

正如您从编辑的 GUID 中看到的那样，有许多独特的即席查询计划已经被创建了多次。

为了证明我正在朝着正确的方向前进，我从上面获取了一个唯一计数为 3 的语句，并将该语句用作我的计划缓存摘要语句中的过滤器来检索语句和查询计划：

SELECT cplan.usecounts,
       qtext.text,
       qplan.query_plan
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'
       AND sdb.name = 'DATABASENAME'
       AND qtext.text = 
           'SELECT description,id,name,osguid,profil FROM omitted WHERE osguid IN (SELECT osgroupguid FROM omitted WHERE osuserguid=''81C4B8_REMOVED_SOME_9DD2'')'
ORDER BY
       1 DESC

^{参考：不记得了。让我知道它是否最初是你的，我会归于它。}

这给了我一个独特的即席查询列表，这些查询已经创建并存储在计划缓存中：

现在上面屏幕截图中的数字表明一个查询已经被再次重用，因为它的计数为 3。但是，所有查询都是相同的。

现在从我到目前为止所读到的，我假设：

• 即席查询是在其（可能很短的）生命周期中第一次传递给 SQL Server 查询优化器的查询
• 没有参数的语句被认为是唯一的，并将导致在Adhoc计划缓存中创建一个条目
• 即席查询可能很简单，这会导致为每个语句创建单独的查询计划，即使它们是相同的

我同样意识到：

• 打开optimize for ad hoc workloads将导致缓存中查询计划的大小略有减少，用于仅使用一次的临时计划
• 就我而言，跑步ALTER DATABASE [DATABASENAME] SET PARAMETERIZATION FORCED可能是一个好主意，但是...
  • 有限制（见 BrentOzar 的文章）
  • 程序中的参数化会更好

问题

在阅读了所有文章和输入此问题时弹出的一些相关问题后，我有以下两个问题：

1. 在哪些情况下会重用非参数化、非平凡、即席查询计划？
2. 为什么相同语句有多个缓存查询计划？

我意识到我的问题是矛盾的，因为非参数化查询计划被认为是唯一的，但为什么一些非参数化即席查询计划仍然被再次重用？

回应@DenisRubashikin 的评论：

以 XML 格式保存“相同”查询的计划并比较文件，我认为可能存在一些差异（例如，在设置选项中）– Denis Rubashkin 29 分钟前

SET 选项是相同的。整个计划的唯一区别是在和<StatementSetOptions>部分之后的第二行。我在下面粘贴了两个相关部分：CompileTimeCompileCPU

查询计划1.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="472">

查询计划2.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="472">

未发现其他差异。

用于解决此问题的参考资料：

• 针对临时工作负载规划缓存和优化(SQLSkills.com)
• 查询处理架构指南(Microsoft SQL Docs)
• ALTER DATABASE SET 选项 (Transact-SQL) (Microsoft SQL Docs)
• 为什么一个查询的多个计划不好（BrentOzar.com）
• 强制参数化会出错吗？(BrentOzar.com)
• 闪电战结果：强制参数化(Brentoar.com)