SQL Server 2008 - Query execute => timeout on my webservice - web application

Question

When i execute a query on my SQL Server (same database as my web application) and my query gets a timeout, all my web users on my web application also receive a timeout.

Is it possible to avoid this problem?

Answer 1

I guess your two options are

1) Make the query faster using all the usual methods i.e. indexes, materialised views etc

2) Increase the timeout value

Without any more information its hard to offer better advice

Answer 2

try to extract only one smallest record, if it works then your querry is very resource consuming, increase time out limit in your sql server

if the above simple query fails also then check connections.

Answer 3

What is the query doing?

Are you updating the same records that the web application is trying to read? If so the issue may well be a locking one and breaking updates down into smaller batches will help or possibly considering snapshot isolation.

If not then I guess it may be a hardware resource issue. A couple of suggestions

• Make sure you have all the indexes you need
• Use the Dynamic Management Views to find out the reason for the waits

We need more details really to save giving you a load of irrelevant possibilities.