sql-server – 从索引列上的一个非常大的表中选择TOP 1非常慢,但

经过大量的故障排除后,我们将其缩小到一个非常简单的查询,但我们找不到解决方案.查询非常简单：

select top 1 EventID from EventLog with (nolock) order by EventID

它总是需要大约1.5秒！但是,使用“desc”的类似查询总是需要大约0毫秒：

select top 1 EventID from EventLog with (nolock) order by EventID desc

PTable有大约5亿行; EventID是主要聚簇索引列(有序ASC),数据类型为bigint(标识列).有多个线程将数据插入到顶部的表中(较大的EventID),并且有1个线程从底部删除数据(较小的EventID).

在SMSS中,我们验证了两个查询始终使用相同的执行计划：

>聚簇索引扫描;
>估计的和实际的行数都是1;
>估计和实际执行次数均为1;
>估计I / O成本是8500(似乎很高)
>如果连续运行,则两者的查询成本相同,为50％.

我用fullscan更新了索引统计信息,问题仍然存在;我再次重建了索引,问题似乎已经消失了半天,但又回来了.

我打开了IO统计信息：

set statistics io on

然后连续运行两个查询并找到以下信息：

(对于第一个查询,慢一个)

Table ‘PTable’. Scan count 1,logical reads 407670,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0.

(对于第二个查询,快速查询)

Table ‘PTable’. Scan count 1,logical reads 4,lob read-ahead reads 0.

请注意逻辑读取的巨大差异.该指数用于两种情况.

索引碎片可以解释一下,但我相信影响非常小;而这个问题从未发生过.另一个证据是,如果我运行如下查询：

select * from EventLog with (nolock) where EventID=xxxx

即使我将xxxx设置为表中最小的EventID,查询也总是闪电般快速.

我们检查过,没有锁定/阻塞问题.

注意：我只是试图简化上面的问题. “PTable”实际上是“EventLog”; PID是EventID.

没有NOLOCK提示我得到相同的结果测试.

有人可以帮忙吗？

XML中更详细的查询执行计划如下：

https://www.brentozar.com/pastetheplan/?id=SJ3eiVnob

https://www.brentozar.com/pastetheplan/?id=r1rOjVhoZ

我认为提供create table语句并不重要.它是一个旧的数据库,并且在维护之前已经运行了很长时间.我们自己做了很多研究,并将其缩小到我的问题中提供的信息.

该表通常以EventID列作为主键创建,该键是bigint类型的标识列.这时,我猜问题是索引碎片问题.在索引重建之后,问题似乎已经消失了半天;但为什么它回来这么快……？

解决方法

在索引顺序中找到第一行似乎相当多.

很可能你的幽灵清理任务已经落后很长时间,或者已经停止了.您应该在sys.dm_db_index_physical_stats中检查聚集索引的ghost_record_count,并监视一段时间内的更改.

从索引末尾看到的持续删除活动的有序扫描必须扫描大量的幻影记录,然后才能找到要返回的第一个“活动”行.这解释了额外的逻辑读取.向下搜索b树到索引的最低值将会遇到更少的幻影记录.

另一个影响性能的因素是扫描本身负责删除由Paul Randal在Inside the Storage Engine: Ghost cleanup in depth中提到的重影记录.

您应该检查跟踪标志661(禁用重影清理)是否处于活动状态.

解决方案

>您可能会发现运行sp_clean_db_free_space可以减轻负担.
>更改从索引末尾删除行的进程以使用PAGLOCK提示would enable ghost cleanup on the spot,这也可以很好地解决问题.

如果ghost清理进程已完全停止,则最有效的解决方案通常是重新启动SQL Server实例.您还应确保SQL Server正在运行最新的累积更新之一.多年来,有许多鬼清理漏洞.

在您的具体情况：

It turned out the problem was caused by another test database on the same server. That test database was restored with “data loss”,and is corrupt. Surprisingly,the ghost cleanup process apparently was stuck in that database. Once we deleted that corrupted database from SMSS,the problem resolved by itself (took a long time and might have caused DB to lockup for a short while).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!