Record count in suspended status immediately after beginning to execute?

Question

I'm trying to run a simple SELECT count(*) from TABLE, where TABLE has 127MM+ rows.

As soon as I hit F5 to execute I open another window to check the status with a sqlauthority query I found.It returns that the status of this particular query is suspended even though it seems it's still running. There's nothing else running at the moment.

On the other hand, if I run a TOP select, it returns data, so the table's not locked. Don't know if this has anything to do with it (it shouldn't), but this database is in an external hard drive.

Any help is appreciated.

Here's some more info on that particular query. I am the only one that uses and queries this DB, and it has only one table:

status: suspended
wait_type: CXPACKET
total_elapsed_time: 377695 (keeps increasing)
last_wait_type: CXPACKET
blocking_session_id: 0

Answer 1

>to check the status with a sqlauthority query

Can you post the query?

What is an "external" hard drive?

Thanks.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

SUSPENDED typically indicates a wait due to a non sql server activity like IO. Fro that perspective, it makes sense that query is in suspended status as there may be significantly increased IO activities for an external drive.

Answer 3

The query I'm running is: select count(*) from TABLE

The query I use to check the status is:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

An external hard drive is just that: a 2TB USB external hard drive that stores a database with just one table. Both MDF and LDF are in this external drive.

Answer 4

CXPACKET wait type indicates the query is waiting on a thread to complete.  Your SELECT query will be using parallelism to try and speed up the data retrieval, and it's always a case of waiting on the slowest thread to finish.

It's a huge table, so you've no choice but to wait on it to complete.  The 'suspended' state is a misnomer in this case.

If you want a faster way to get a record count (but don't rely on this method) you can look in the metadata:

select row_count
from sys.dm_db_partition_stats
where object_id = object_id('yourtable')
  and index_id = 1 -- change this to 0 if it is a heap

Run this query in the context of the containing database, and it will give you the row count.

Answer 5

Thanks again.

I ended up using sp_spaceused which is also pretty fast

EXEC sp_spaceused 'TABLE'

Answer 6

You may try adding OPTION (MAXDOP 1) to the query although it is not recommanded. I guess this is not a frequent query probabaly for one time or twice you may execute.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 7

You may try adding OPTION (MAXDOP 1) to the query although it is not recommanded. I guess this is not a frequent query probabaly for one time or twice you may execute.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

This option forces the use of a single thread, but parallelism isn't a bad thing.  It exists because it is often far quicker to run multiple threads and combine the results.  In particular, large SELECTs such as in this example and in OLAP systems can see large performance gains - so no, I wouldn't recommend using this option.

If you're running a transactional system with lots of lightweight queries, and you're seeing a lot of CXPACKET wait types, then by all means try this setting at the server level as you're unlikely to benefit from parallelism and it may just speed things up.