Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

ql server does not allow remote connections

hi,

sql server 2005 express is installed on comp1. my client application is installed on comp2 and comp3. all comps have win xp. when i run my application, it can't connect to sql server express. i got the following message:

an error has occured while establishing a connection to the server. when connecting to sql server 2005, the failure may be caused by the fact that under the default settings sql server does not allow remote connections. (provider: sql network interfaces, error 26 - error locating server/instance specified)

so, how can i solve this problem, to connect to the server?

hi,

SQLExpress installs by default disabling network protocols so that only local connections are available..

you have to run the SQL Server Surface Area Configuration (for services and connections) and enable remote connections..

then run SQL Server Configuration Manager and, in the SQL Server 2005 Network Configuration, enable the desired/required protocol(s) for the instance you are interested with...

additionally, you have to configure firewall exceptions on the pc hosting SQLExpress..

regards

|||

These articles will help walk you through Andrea's suggestions:

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Configuration - Guideline for Connectivity Question Posting
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||thanx a lot both !