Simple select not returning all rows and sp_who returns no blocks

Question

I'm running a simple select on a table with a small amount of rows, and it's running for forever. I found that it returns fine if I run:

SELECT TOP 23 * FROM MyTable ORDER BY 1

However, it hangs forever if I run:

SELECT TOP 24 * FROM MyTable ORDER BY 1

If I run sp_who or sp_who2, there is no one blocking. Why does this happen, and how can I fix it?

The performance of the two queries should be essentially the same. — Gordon Linoff, Mar 19 '15 at 21:00
@GordonLinoff The problem was an open transaction inserting a 24th row. Running with `nolock` returned the row in question. — Millie Smith, Mar 19 '15 at 21:24

score 0 · Accepted Answer · answered Mar 19 '15 at 20:42

If you run with nolock on (ignoring uncommitted transactions), you should be able to select all rows:

SELECT * FROM MyTable WITH (NOLOCK)

There's a transaction somewhere that has not been committed or rolled back (I'm not sure why it doesn't show up in sp_who). You can run the following to force all transactions to rollback, and then selecting should work fine without nolock:

USE master
ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DbName SET MULTI_USER

Simple select not returning all rows and sp_who returns no blocks

1 Answers1