Suspended status

[woyler]

Hi All,

Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?



regards

Bill

 

[ptheriault]

What account are you using for RS? To get all rows from sysprocesses you must use a sysadmin account.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[woyler]

Thanks for the response Paul. Unfortunatly , I dont follow your question. The issue occurs regardless of the credentials I am using with RS.

 

[ptheriault]

Not the credentials you use to login to RS but the credentials that RS use to connect to the SQL instance. What are the credentials that the Data source is using?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[woyler]

It happens regardless of the credentials. I initially was using a readonly SQL account. I have tested the scenario with the sa account and have the same results.

 

[woyler]

The solution is to disable parallel query execution.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22850924.html