Server哈希联接和嵌套循环

Question

我开发关于现有数据库(SQL2008 - SQL2012)的报告，在那里我需要以许多不同的方式收集数据。

一个典型的查询看起来像

WITH ThisYearData AS (
    SELECT ...
)
,LastYearData AS (
    SELECT ...
)
,BudgetData AS (
    SELECT ...
)
SELECT ...
FROM SomeDateTable
     LEFT JOIN ThisYearData
         ON ...
     LEFT JOIN BudgetData
     ...

有时CTE是相互依赖的，一个典型的查询涉及其中的5-10个。

问题是，类似的查询可以从一秒钟到五分钟内在一小部分数据上运行。

性能缓慢的主要原因是Server使用嵌套循环而不是散列联接。在某些情况下，我可以将HASH JOIN放在正确的位置，使查询速度提高100倍。在其他情况下，我将一个或多个CTE转换为一个表变量。

我知道在散列联接上选择嵌套循环的原因是基于基于统计的估计行数。在我的示例中，我无法访问数据库模式，因此无法添加索引或统计信息，但我可以确保更新现有统计信息(并且启用了自动创建/更新统计信息)。

要查找导致减速的嵌套循环，我喜欢这样做：

1. 查找行数和执行次数较多的节点
2. 沿着这条路径向上走，直到找到正确的嵌套循环。
3. 找出导致嵌套循环的连接，并将其强制到hash join

(SQL Sentry explorer使这件事变得容易多了)

我的问题是：

1. 我是否做了一些根本错误，导致Server几乎总是低估行数(以千为单位的个位数估计和实际数据是常见的)？
2. 执行计划通常会显示一些不相关的表扫描，并且会有很高的百分比，这并不是很有帮助。执行计划中的百分比是多少？我能把它更改为在我的场景中更有帮助吗？

Answer 1

在我的经验中，至少在Server上使用WITH是一个肯定的性能损失。在没有WITH结构的情况下编写您的查询，并查看您是否获得了性能改进(我的钱是:是的)。

即使这样也不起作用，那么在临时表中构造您本来可以从CTE或派生表中选择的内容。只有当行数确实很小(即10行或更少)时，表变量才是最好的。如果您注意到对CTE/派生表的查询太慢，至少从我的经验来看，临时表提供了最好的性能。

Answer 2

执行计划通常会显示一些不相关的表扫描，并且会有很高的百分比，这并不是很有帮助。执行计划中的百分比是多少？我能把它更改为在我的场景中更有帮助吗？

添加提示‘散列连接’的问题是，它也会导致所有其他联接的隐式‘强制顺序’。

出发地：https://msdn.microsoft.com/en-us/library/ms173815(v=sql.105).aspx：

备注 联接提示在查询的FROM子句中指定。联接提示在两个表之间强制执行连接策略。如果为任意两个表指定了连接提示，则查询优化器将根据on关键字的位置自动强制执行查询中所有已连接表的连接顺序。

我认为您应该复制数据库并添加统计信息，以尝试提高测试环境中的性能，并查看会发生什么。如果它有帮助，那么您可以与决策者交谈，或者创建一个数据仓库。