主页 / dba / 问题 / 178151
Accepted
RomanG
Asked: 2017-07-07 03:39:15 +0800 CST

SQL Server 缓存了在某些情况下不是最佳的执行计划，并将其用于所有后续查询

  • 772

我们的应用程序使用 SQL Server 2014，我们遇到了与计划缓存相关的问题。

我们有一个参数化查询，它的执行计划取决于参数值。服务器缓存在某些情况下不是最佳的执行计划，然后将其用于所有后续查询。

细节：

我们有一个表由以下列组成：

(
 [Revision] [bigint] IDENTITY(1,1) NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 ...A WHOLE LOT OF OTHER COLUMNS...
)

这两列的意思很清楚，UserId是记录所属用户的Id，是记录Revision的自增索引。其他列并不重要，但它们存在并影响执行计划。

该表包含约 40.000.000 行和约 200.000 个不同的UserId值，因此每个用户平均有 200 条记录。行永远不会更新，我们只使用 INSERT 和 DELETE 来修改数据。

我们的应用程序对该表执行以下查询：

SELECT * FROM SampleTable WHERE Revision > {someRevision} AND UserId = {someId}

该表有两个索引：

  1. 聚集索引：Revision asc
  2. 非聚集索引：UserId asc, Revision asc

当我手动执行此查询时，我看到执行计划取决于someRevision.

  • 如果它比较接近 Revision 的当前最大值，则服务器使用Clustered Index SeekwithSeek Predicate: Revision > someRevision

  • 如果它不关闭，则服务器使用Index Seek (NonClustered)+Key Lookup (Clustered)和Seek Predicate: UserId = someId AND Revision > someRevision。

我们的应用程序使用 Linq-To-Sql 并生成参数化查询，它们看起来像这样：

exec sp_executesql N'SELECT * FROM [SampleTable] AS [t0]
WHERE ([t0].[Revision] > @p0) AND ([t0].[UserId] = @p1)',N'@p0 bigint,@p1 
uniqueidentifier',@p0=1234,@p1='bc38dd12-238c-41a2-9dea-bb12ce105e6d'

我使用dm_exec_cached_plans,并了解服务器将此查询的单个计划放入缓存中dm_exec_sql_text。dm_exec_query_plan因此，如果具有相应值的查询Revision首先出现，则使用的计划Clustered Index Seek将存储在计划缓存中，然后将用于所有后续查询。

它会导致过多的逻辑读取 (x10000) 和不可接受的查询执行时间，这些查询应该使用第二个计划 ( Index Seek (NonClustered)+ Key Lookup (Clustered)) 执行。

我还注意到服务器在计划之间切换的阈值（临界点）取决于统计信息，如果它是陈旧的，即使缓存不考虑，计划也可能是次优的，因为服务器错误地估计了Revision大于的行数给了一个。

此外，我们有大量具有相似用例的相似表，它们都有相同的问题。

我能做些什么来解决这个问题？

我可以尝试使用OPTION (RECOMPILE)，这对于 Linq-To-Sql 来说并不容易，但它在性能方面看起来也不是最优的。

我也可以更多地使用sp_create_plan_guide或破解 Linq-To-Sql 并尝试WITH (INDEX(...))使用第二个计划来强制使用，但正如我所说，有很多具有相同核心结构的表，所以这种方式看起来像很多手工工作.

一般来说，我的问题：

SQL Server 能否理解存储在缓存中的计划对于给定参数不是最佳的并且不使用它？

如果参数化查询的最佳执行计划取决于参数，是否有一些处理参数化查询的最佳实践？

sql-server index

1 个回答

  1. Best Answer

    这被称为参数嗅探，它在 Erland Sommarskog 的史诗文章“应用程序中的慢，SSMS 中的快”中得到了广泛的介绍。

    我什至无法在这里公正地对待它，但示例解决方案包括：

    • OPTION (RECOMPILE) - 这会导致计划编译的 CPU 使用增加，并且会丢失查询执行的历史指标，但可以为每组参数构建一个唯一的计划（尽管在基数估计问题的情况下它仍然可能是次优计划)
    • 针对特定值进行优化 - 如果您非常了解您的数据，则可以使用 OPTIMIZE FOR 提示，以便始终为特定参数值构建计划，无论用户传入什么。这就像制造技术债务 - 如果您的数据倾斜更改，您可能必须重新访问您的代码以获得更好的计划。
    • 使用索引提示 - 这通常比针对特定值进行优化更糟糕，因为您不仅要控制查询优化器，而且如果该索引消失，您的查询就会失败。SQL Server 不会尝试为您的查询使用备用索引。
    • 计划指南 - 但如果您的查询有任何变化，即使是一个字母，那么计划指南将不再匹配。
    • 查询和索引调优的结合——让开发人员避免选择 *（所有字段），而只获取他们真正需要的字段。然后，构建一个覆盖索引进行匹配，您将获得一个适用于所有参数的单一查询计划。

    继续阅读 Erland 的出色文章- 它不仅会在今天带来红利，而且随着您一次又一次地解决这个问题，它将继续为您的职业生涯带来回报。今天适用于您的查询的解决方案可能与您明天用于另一个查询的解决方案大不相同。

    • 7

相关问题