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).
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:
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).
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.
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.
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).
Can anyone explain why SQL Server 2008 would do this?