sql-server - Multi-Tenancy SQL Server 数据库和参数嗅探

Question

我在 SQL Server 2012 中有一个 Multi-Tenancy 数据库，其中每个租户的行均由 tenant_id 列标识(也称为 Shared Database, Shared Schema 方法)。一些租户，尤其是新租户，只有很少的行，而其他租户则有很多。

SQL Server 的查询优化器通常会根据首次执行期间提供的参数构建一个查询计划，然后在以后的所有查询中重复使用该计划，即使提供了不同的参数也是如此。这称为 parameter sniffing .

我们的数据库存在的问题是，SQL Server 有时会根据指向较小租户的参数构建这些计划，这对于该租户来说效果很好，但是当它将缓存的计划重新应用到较大租户时，它会发生灾难性的失败(事实上​​，通常会超时)。通常，只有当我们的一个较大的租户联系我们询问超时错误时，我们才会发现这种情况，然后我们必须进入系统并手动刷新所有查询计划来纠正它。

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

所以问题是—— Multi-Tenancy 数据库在参数嗅探方面的最佳实践是什么？有没有办法在数据库范围内禁用参数嗅探，而不必在每个查询上指定它？如果是这样，那是最好的方法吗？我应该以其他方式对数据进行分区吗？还有其他我没有想到的方法吗？

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 可以为我做的一些优化，但最重要的是，我错过了参数嗅探启动时所获得的真正可怕的性能。

在你的情况下，也许你可以尝试只对 Multi-Tenancy ID 执行此操作(我假设它是所有 SP 的参数？)，并让 SQL Server 优化其余参数(如果可以的话)。