Sql table unresponsive when selecting or counting all rows

Question

We have a table in a database that has 35 rows, according to

exec sp_spaceused Department.

I am able to run

SELECT TOP 1 * FROM Department,

and get a result, but when I run

SELECT COUNT(*) FROM Department,

it runs longer than 2 minutes (I then cancelled it and did not wait for a result, since I expect this to be a simple and fast query).

What could the reason for this be? Do you have any suggestions?

Answer 1

Is there a lock open on the table that is stopping you from reading some of the rows?

Try:

sp_lock

Answer 2

What you expect to be a simple and fast query actually results in Table Scan, which is effectively reading all data pages from disk and iterating over each record. This is very expensive. You can use this method, but I cannot be sure whether it is precise or not.

Answer 3

If your table really has 35 rows, it shouldn't take two minutes. Even if your table has billions of rows, a decent DBMS will store the row count for efficiency (I don't know if Microsoft does this).

Keep in mind that "top 1" will just get the first row in (seemingly) random order so it will be fast.

My first thought would be database corruption - what happens when you execute the following?

select top 2 * from Department
select top 3 * from Department
select top 4 * from Department

and so on.

Answer 4

The best way to find out what's going on is to trace the execution of the query, but I don't know how to do this in SQL Server.

You could also try viewing the execution plan of the query, which may show you if something unexpected is going on under the cover (e.g. if Department is really a complicated view).

Imagining that the table has a primary key called Department_ID that is always a positive integer, you might try something like:

SELECT COUNT(*) FROM Department WHERE Department_ID > 0

This might force it to scan the index instead of the actual table.

Answer 5

Does this query come back quickly?

SELECT COUNT(*) FROM Department WITH (NOLOCK)

If so I would definitely say there's a lock of some sort on your table / index, as has been suggested.

Marc

Answer 6

Check for Triggers on the table. Also get a performance monitor up so you can see how the server is doing on resources.