导致SQL Server 2008挂起的前100名？

I have inherited a VERY poorly designed and maintained database and have been using my knowledge of SQL Server and a little luck keeping this HIGH availability server up and not completing coming down in flames (the previous developer, who quit basically just kept the system up for 4 years).

我继承了一个非常糟糕的设计和维护数据库，并一直在使用我的SQL Server知识和一点点运气保持这个高可用性服务器，而不是完成火灾（以前的开发人员，谁退出基本上只是保持系统的4年）。

I have come across a very strange problem today. I hope someone can explain this to me so if this happens again there is a way to fix it.

我今天遇到了一个非常奇怪的问题。我希望有人可以向我解释这一点，如果再次发生这种情况，有办法解决它。

Anyway, there is a stored proc that is pretty simple. It joins two tables together between a SHORT date/time range (5 mins range) and passes back the results (this query runs every 5 mins via a windows service). The largest table has 100k rows, the smallest table has 10k rows. The stored proc is very simple and does:

无论如何，存储过程非常简单。它在SHORT日期/时间范围（5分钟范围）之间连接两个表并传回结果（此查询通过Windows服务每5分钟运行一次）。最大的表有100k行，最小的表有10k行。存储过程非常简单，并且：

NOTE:The table and columns names have been changed to protect the innocent.

注意：已更改表和列名称以保护无辜者。

SELECT TOP 100 m.*
FROM dbo.mytable1 m WITH (nolock)
INNER JOIN dbo.mytable2 s WITH (nolock) ON m.Table2ID = s.Table2ID
WHERE m.RowActive = 1
      AND s.DateStarted <= DATEADD(minute, -5, getdate())
ORDER BY m.DateStarted

Now, if I keep "TOP 100" in the query, the query hangs until I stop it (running in SMS or in the stored proc). If I remove the TOP 100, the query works as planned and returns 50-ish rows, like it should (we don't want it to return more than 100 rows if we can help it).

现在，如果我在查询中保持“TOP 100”，查询将一直挂起，直到我停止它（在SMS或存储过程中运行）。如果我删除TOP 100，查询按计划工作并返回50-ash行，就像它应该的那样（如果我们可以帮助它，我们不希望它返回超过100行）。

So, I did some investigating, using sp_who, sp_who2, and looked at the master..sysprocesses and used DBCC INPUTBUFFER to look for any SPIDs that might be locking or blocking. No blocks and no locking.

所以，我做了一些调查，使用sp_who，sp_who2，查看master..sysprocesses并使用DBCC INPUTBUFFER查找可能锁定或阻塞的任何SPID。没有块也没有锁定。

This JUST STARTED today with no changes to these these two tables designs and from what I gather the last time this query/tables have been touched was 3 years ago and has been running without error since.

今天刚刚开始，没有对这两个表设计进行任何更改，也不是我上次触摸这个查询/表时收集的内容是3年前，并且自那以后一直运行没有错误。

Now, a side note, and I don't know if this would have anything to do with this. But I reindexed both these tables about 24 hours before because they were 99% fragmented (remember, I said this was poorly designed and poorly maintained server).

现在，附注，我不知道这是否与此有关。但是我在24小时之前对这两个表进行了重新索引，因为它们是99％的碎片（记住，我说这是设计不佳和维护不良的服务器）。

Can anyone explain why SQL Server 2008 would do this?

任何人都可以解释为什么SQL Server 2008会这样做？

3 个解决方案

#1

The absolute first thing I would do would do a side by side comparison of the query plans of the full and the top 100 queries and see if the top 100 is not performant. You might need to update stats or even have missing indexes.

我要做的绝对第一件事就是对完整和前100个查询的查询计划进行并排比较，看看前100名是不是高性能。您可能需要更新统计信息，甚至缺少索引。

#2

THE ORDER BY is the killer. it has to read all rows, sort by that order by column, and then give you the first 100 rows.

ORDER BY是杀手。它必须读取所有行，按列按顺序排序，然后给出前100行。

#3

I'd presume there's no index on mytable1.DateStarted. I think something might be deciding to perform the sorting earlier on in the query process when you did SELECT TOP 100.

我认为mytable1.DateStarted上没有索引。我认为当您执行SELECT TOP 100时，某些事情可能决定在查询过程中更早地执行排序。

#1