时间戳

首页 > 解决方案 > T-SQL 聚集索引寻求性能

sql-server - T-SQL 聚集索引寻求性能

问题描述

通常的废话……查询运行时间太长……废话。长问题。废话。显然，我正在研究重写查询的不同方法；但这不是这篇文章的内容。

为了解决查询中的“溢出到 tempdb”警告，我已经

  1. 重建数据库中的所有索引
  2. 更新了表和索引的所有统计信息

这修复了“溢出到 tempdb”警告并提高了查询性能。

由于重建索引和统计信息为一个查询带来了巨大的性能提升（无需重写），这让我开始思考如何在不重写它们的情况下提高其他查询的性能。

我有一个很好的大查询，它连接了大约 20 个表，做了很多我没有在这里发布的花哨的东西，但运行大约需要 6900 毫秒。

查看实际执行计划，我看到 4 个步骤，总成本为 79%；所以“啊哈”就是性能问题所在。3 个步骤是 PK_Job 上的“聚集索引搜索”，第 4 个步骤是“索引惰性假脱机”。

执行计划慢查询

因此，我将这些元素分解为一个独立的查询以进一步调查......我在执行计划中得到了“相同”的 4 个步骤，成本为 97%，只有查询时间快到 34 毫秒。... 怎么回事？性能问题消失在哪里？

执行计划快速查询

我希望额外的表会增加查询时间；但我不希望查询这个 Job 表的执行时间从 30 毫秒到 4500 毫秒。

-- this takes 34ms
select * 
from equip e 
left join job jf on (jf.jobid = e.jobidf) 
left join job jd on (jd.jobid = e.jobidd)
left join job jr on (jr.jobid = e.jobidd)


-- this takes 6900ms
select * 
from equip e 
left join job jf on (jf.jobid = e.jobidf) 
left join job jd on (jd.jobid = e.jobidd)
left join job jr on (jr.jobid = e.jobidd)
-- add another 20 tables in here..

问题 1：我应该在两个执行计划中查看什么来确定为什么该表上的执行时间（聚集索引查找）从 30 毫秒变为 4500 毫秒？

因此，考虑到这可能与统计信息有关，我查看了PK_Job = JobID（这是一Int列）直方图范围看起来没用的索引统计信息......所有“当前”记录都集中在一个范围内（第 21 行）图片）。增加 PK 的标准问题，新数据始终在最后一个范围内；也就是说，99.999% 的JobID引用值都在一个直方图范围内。我尝试添加过滤统计信息，但这对实际执行计划没有影响。

PK_Job 的 DBCC SHOW_STAT 输出

问题 2：上述PK_Job统计数据是否是导致复杂查询变慢的一个因素？也就是说，“修复”统计信息是否有助于复杂的查询？如果是这样，该修复会是什么样子？

再说一遍：我知道，重写查询。发布更多代码（它的全部 1500 行没有人会发现任何用处）。呸呸呸。

我想要的是关于如何回答 Q1 和 Q2 的提示。提前致谢！

问题 3：为什么简单的 IIF 会为查询添加 100 毫秒？“计算标量”节点都显示成本为 0%，但 IIF 使查询的执行时间加倍。

添加此选项以将执行时间从 90 毫秒增加到 180 毫秒；案例陈述也同样糟糕。

IFF(X.Okay = 1, '', 'N') As OkayDesc

下一个观察：实际执行计划显示查询成本相对于批次为 98%；但 STATISTICS TIME 显示 CPU 时间为 141 毫秒；但是批处理 cpu 时间是 3640 毫秒。

问题4：为什么查询成本%（相对于batch）与语句cpu时间不匹配？

标签： sql-servertsqlstatisticssql-execution-plan

解决方案

在大多数情况下，SQL 引擎在优化写得不好的查询方面非常聪明。但是，当查询过于复杂时，有时它无法使用这些优化，甚至表现不佳。

所以，你在问：

我将这些元素分解为一个独立的查询以进一步调查......我在执行计划中得到了“相同”的 4 个步骤，成本为 97%，只有查询时间快到 34 毫秒？性能问题消失在哪里？

答案很简单。打破查询并在@table 或#table 中具体化数据有助于引擎更好地了解它正在工作的数量并制定更好的计划。

Brent Ozar 昨天写了一篇关于这个的文章，举了一个例子，一个大查询有多糟糕。

如果您想了解有关如何通过重写优化查询的更多详细信息，则需要提供更多详细信息，但在我的实践中，在大多数情况下，简化查询并在 #temp 表中具体化数据（因为我们可以使用并行操作使用他们）正在产生良好的效果。

推荐阅读