Link to home
Create AccountLog in
Avatar of JElster
JElsterFlag for United States of America

asked on

SQL Server - Task State = SUSPENDED

Hi..
I have a simple stored procedure that is called by a web app.

The SP looks like this  "SELECT * FROM CUSTOMERS ORDER BY LASTNAME, FIRSTNAME"

Sometimes the app just hangs.. then I notice in SQL Activity Monitor that task is SUSPENDED.

What does this mean?  The query returns 20K rows.  No one is updating the table , it's readonly.
Any ideas?

thx
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

Just a couple of quick suggestions:
Always specify the schema, most likely dbo
Always alias the tables
Always use a table name or table alias to qualify the table names
Always (or almost always) specify the columns in a select and not use the wildcard(*)

Back to your question:
20k rows - this could be a sizeable results set if the rows are large. I don't imagine the problem is with SQL. I'd suggest that the issues is more likely to be the webserver trying to consume 20k rows. And how many users are doing this all at once? So it is 53 thousand users times 20k rows times 8k pages (big rows) - that's a lot of data/memory/etc.

Is the web running on the same box as SQL? Do you need to a) run these on separate boxes, scale out the web serving to a farm instead of individual server, etc?

Regards
   David
Avatar of JElster

ASKER

thanks..
SQL server is on Amazon Cloud
Web app is on ISP

Maybe 3 users max

What's wrong with using a wildcard?
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account