为什么我的Server删除查询计划中存在问题？

Question

我在Server 2012 (web )中有一个非常大的表(150m+行)，它没有聚集索引，也没有一个非聚集索引。

当我运行这个delete语句时：

DELETE TOP(500000) 
FROM pick 
WHERE tournament_id < 157

(列名在非聚集索引中)，由Server生成的执行计划如下所示：

​

​

排序步骤看上去有问题--它占用了成本的45%，并且导致了一个警告：“操作符在执行过程中使用tempdb来泄漏数据。”这个查询需要几分钟才能运行，我觉得应该更快一些。

两个问题：

1. 为什么计划中有某种步骤？
2. 有什么办法克服漏油事故吗？服务器有64 4gb的RAM，tempdb大小为8x4GB数据文件。

如果这可能有帮助的话，我肯定可以重新讨论这个表上的索引策略。

希望这一切都有意义

Answer 1

我同意这里似乎没有什么好的理由。

我不认为它是必要的万圣节保护，因为它没有出现在= 157版本的计划。

而且，排序操作是按照Key Asc, Bmk ASC的顺序进行排序(想必是为了按索引顺序顺序排序)，但这是相同索引上的前向索引查找的顺序，也就是返回行的顺序。

删除它的一种方法是混淆TOP以获得一个狭窄的(每行)计划，而不是一个宽的(每个索引)计划。

DECLARE @N INT = 500000

DELETE TOP(@N) 
FROM pick
WHERE  tournament_id < 157 
OPTION (OPTIMIZE FOR (@N=1))

​

​

您需要进行测试，以确定这是否确实改进了一些东西。

Answer 2

我将尝试更小的块和更具选择性的WHERE子句，以及一种强制SQL Server按照指定的顺序选择顶部行的方法：

;WITH x AS
(
  SELECT TOP (10000) tournament_id
  FROM dbo.pick
  WHERE tournament_id < 157 -- AND some other where clause perhaps?
  ORDER BY tournament_id -- , AND some other ordering column
)
DELETE x;

更多的选择性也意味着删除tournament_id < 20，然后tournament_id < 40等等，而不是从1-157中选择500000行。通常情况下，执行一系列小型事务而不是一个大型事务对系统整体来说更好(包括阻塞影响、锁升级等，以及对日志的影响)。我在博客上写了这个：http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes

在这些情况下，这类数据可能仍然存在(尤其是在万圣节保护或与垃圾有关的情况下)，但在较小的范围内问题可能要少得多(请不要仅仅根据估计的成本%数字，因为这些数字往往是垃圾)。因此，首先，我将真正考虑添加一个聚集索引。如果没有更多的要求，我不会为您提供明确的建议，但它可以像聚集索引一样简单，只在tournament_id上(取决于每个id有多少潜在行)，或者添加一个标识列，您可以使用它来帮助确定将来要删除的行。

Answer 3

我将采取以下步骤：

1. 在列tournament_id上创建一个集群索引
2. 更新数据库的统计数据
3. 再次运行查询

根据我的经验，这应该有几秒钟的时间。

此外，如果可能的话，我将对您的表进行更详细的查询。

第1版(日期格式为dd/mm/yyyy)：

;WITH To_Delete
(
SELECT tournament_id
FROM dbo.pick
WHERE tournmanet_id < 157
AND date like '01/%/2013' -- if available, Need to be customized
AND date like '03/%/2013' -- if available, Need to be customized
)
DELETE X;

Verion 2(使用month函数，不管您的日期采用哪种格式)：

;WITH To_Delete
(
SELECT tournament_id
FROM dbo.pick
WHERE tournmanet_id < 157
AND month(date) = 1
AND month(date) < 3
)
DELETE X;