多租户SQL Server数据库和参数嗅探

Question

我在SQL Server 2012中有一个多租户数据库，每个租户的行都由一个“tenant_id”列（也称为共享数据库，共享模式方法）进行标识。一些租户，特别是新租户，只有很少的行，而其他租户则有很多。

SQL Server的查询优化器通常根据第一次执行时提供的参数构建查询计划，然后将此计划重用于所有未来的查询，即使提供了不同的参数。这被称为参数嗅探。

我们的数据库问题在于，SQL Server有时会基于指向较小租户的参数构建这些计划，这对于该租户可以正常工作，但是当它将缓存的计划重新应用于较大的租户时，它会发生严重错误（通常是超时）。通常，我们只有在较大的租户联系我们报告超时错误时才会发现这种情况，然后我们必须进入系统并手动清除所有查询计划以进行更正。

有一个查询提示可以使用，防止SQL Server缓存查询计划(OPTIMIZE FOR UNKNOWN)，但这会导致一些额外的开销，因为每次调用查询时都会重新生成查询计划。另一个问题是，我们正在使用Entity Framework，它没有提供在查询中指定OPTIMIZE FOR UNKNOWN提示的能力。

所以问题是 - 关于参数嗅探，对于多租户数据库的最佳实践是什么？是否有一种方法可以在整个数据库范围内禁用参数嗅探而无需在每个查询中指定？如果有，那是否是最佳方法？我应该以其他方式分区数据吗？是否有其他我没有考虑的方法？

- Mike

---

据我所知，你需要使用存储过程来解决这个问题。除非有EF的某些功能可以绕过这个问题。 - RBarryYoung

OPTIMIZE FOR UNKNOWN并不具备您所描述的行为。您将其与OPTION (RECOMPILE)混淆了。如果您使用企业版，可以查看计划指南以提供提示。或者有一个跟踪标志（4136）可以完全禁用参数嗅探，但这适用于实例而非数据库。 - Martin Smith

Answer 1

我曾经遇到过类似的问题，成功解决了。我的解决方法是这样的：

将参数按照以下方式传递：

CREATE PROCEDURE [dbo].[InsertAPCheck]
@APBatchID  int = Null,
@BankAccountID  int = Null
AS
  /* copy parameters to temporary variables */
  SELECT @xAPBatchId = APBatchId, @xBankAccountID = @BankAccountID
 .
 .
 /* now run the meat of your logic using the temp variables */
 SELECT * FROM myTable where Id=@xAPBatchId.....etc.

换句话说，为每个传入的参数基于1-1的关系创建一个本地变量，然后只在SP的逻辑中引用这些新变量。我可能错过了SQL Server可以为我做的一些优化，但最重要的是我错过了当参数嗅探发生时所获得的可怕性能。

在您的情况下，也许您可以尝试仅针对多租户ID（我假设它是所有SP的参数？）执行此操作，并让SQL Server优化其余的参数（如果可以）。

- E.J. Brennan

---

这基本上就是 OPTIMIZE FOR UNKNOWN 查询提示所做的事情。不幸的是，我不能使用任何一种方法来处理 Entity Framework，因为我无法修改正在生成的查询。 - Mike

Answer 2

对于动态SQL，例如Entity Framework生成的SQL，将一个包含当前租户标识符的注释注入到命令文本中。这实际上通过租户将SQL的执行计划缓存分区，使执行计划保持隔离，但允许它们被同一租户重用。

要将注释注入到命令文本中，您可以子类化/实现DbConnection/IDbConnection和DbCommand/IDbCommand并应用装饰器模式。对DbCommand/IDbCommand.Execute*的调用可以在调用内部方法之前附加带注释的租户ID，然后在返回后删除注释。使用修饰的连接初始化Entity Framework或任何ORM。

如果您有大量租户，则按租户大小类别划分计划缓存可能是有意义的。否则，您将有效地执行与OPTION (RECOMPILE)相同的操作，因为计划将在重用之前从缓存中过期。

- user98258