查询编译速度慢，编译阶段读取量大。

Question

我对以下表格有疑问：

exec sp_executesql N'
    UPDATE table
    SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' 
    WHERE (column5=@P1
       AND column6=@P2)',
    N'@P1 nvarchar(36),@P2 int',
    N'458986156148',87

我不能更改有关此查询本身的任何内容。它来自一个应用程序。

每次执行更新值时，都会更改更新值，从而生成新的查询计划，而不是重复使用以前的计划。

强制参数化是有效的，但似乎对此查询没有影响，可能是通过执行它的方式。

在执行查询本身时，我看到总共有4个读取以进行更新。在这种情况下，执行本身不会导致更新，因为where子句没有返回任何行。

使用Profiler，我看到进入查询和开始查询之间的时间几乎是查询的整个时间(1秒)。实际执行几乎是即时的。

分析器显示455.000次读取，查询执行本身没有显示这些数据。

我想知道：

1. 我可以在这里强制查询使用相同的计划吗？计划指南似乎只适用于同一查询或受强制参数化影响的查询。
2. 我们能提高这个查询的编译速度吗？455.000读物是从哪里来的？那张表上有很多统计数据(+- 100)，但是455.000的读取量似乎太大了。

这是在Server 2019上，还没有累积更新。我扫描了changelog，寻找任何可能与此相关的东西。

进一步的研究表明，在编译过程中，其他表上有很多锁。我有300个表，其中有一个外键引用我要插入的表中的主键。

所有外键关系都是可信的。在编译阶段，是否有办法防止这些检查？

依赖项不是外键约束，而是表中的视图。在执行过程中，使用该表的所有视图都有一个SCH-S锁，这是预期的。目前还不清楚这是否也是导致读取的原因。它们不是索引视图。它们只是SCH-S锁，但我在所有300个视图上都看到了它们。

显然，这455.000次读取来自对sys.sysmultiobjrefs系统表的扫描超过一百万次。这看上去不太合适。

我以前测试过异步状态更新，但没有成功。where子句有索引，生成的计划只是一个简单的查找和插入。插入本身只需4次读取。解析和编译时间超过一秒钟。

这个表上确实有大量索引(大约20个)。删除索引实际上不是一个选项，因为它们是由应用程序提供的。

Answer 1

我们有一个解决办法，简而言之，查询编译期间的绑定阶段在删除表上视图上的SCHEMABINDING属性时占用时间最长，并且消失了。

查询存储信息表明，查询编译时间主要用于绑定而不是优化或解析。

虽然目前还不清楚这是错误还是预期的行为，但读取是在sys.sysmultiobjrefs表上完成的。根据文档，此表“存在于每个数据库中。包含每个一般N到N引用的一行。”当查看值时，它还包含表上的(非索引的)视图。我们想要插入记录的表上有大约3.000个视图。

删除视图时，编译时间线性下降。当然，我们无法删除视图，也无法阻止绑定阶段检查我删除了表中视图的SCHEMABINDING属性的视图。查询编译和插入现在减少到毫秒，而不是1-2秒.

当然，我们需要考虑到的影响，在未来的升级应用程序。

Answer 2

强制参数化是有效的，但似乎对此查询没有影响，可能是通过执行它的方式。

这是正确的，强制参数化在这里不起作用，因为查询“已经在客户端应用程序上进行了参数化”(来源)。换句话说，问题是有两个实际的参数，所以不能强制嵌入日期和int参数。

关于读取/CPU是怎么回事..。

Auto创建/更新的统计数据

就分析器中显示的读取而言，这些数据确实包括统计数据更新和创建。如果只是偶尔发生，那就解释了这个问题。由于您提到每次执行时都会发生长时间的编译和高读取，所以似乎这并不是罪魁祸首(除非这个应用程序正在做一些非常不寻常的事情，比如积极地删除auto)。

版本存储库读取

还有一些在SET STATISTICS IO ON中没有报告的其他类型的读取，我谈到了在我的博客上。这包括版本存储读取，因此，如果在此数据库上启用了SNAPSHOT或READ COMMITTED SNAPSHOT，则可以解释某些CPU / read差异。特别是如果您有长期运行的事务。

附加故障排除

如果您可以在复制问题时共享有关查询的“实际执行计划”，可能会有所帮助。除了你已经提供的信息之外，还有一些线索。

计划强迫

据我所知，没有任何方法可以阻止每次出现这些查询时都会编译一个计划(正如您所提到的，计划指南将无法工作，查询存储计划也不会强制执行)。

Ad工作负载

另外，由于您有这些嵌入式参数，您的工作负载可能会从optimize for ad hoc workloads 服务器级配置选项中获益不少。它将防止您的计划缓存膨胀，这也可能会导致CPU问题(因为SQL Server由于每个查询的所有“新”计划，不得不在一段时间内从计划缓存中删除项)。