Query execution time problem

We are executing following query from query analyzer,
SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
[Table Strcuture => id int, data varchar(500)]
This table contains 45000 records.
When this query is executed from Query Analyzer locally on the database
server, output is returned in 30 seconds.
However, when the query is executed from Query Analyzer installed on
application server it takes 110 seconds.
Execution plan is same both times. CPU, reads are identical for both
executions. However there is big difference between the execution times.
Also, both database server and application server in same VLAN and direct
network connectivity is available without any interim hops.
Any kind of help or suggestion giving insight into this aspect is welcomed.
Thanks in advance for all the inputs.pvv30 (pvv30@.discussions.microsoft.com) writes:
> We are executing following query from query analyzer,
> SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
> [Table Strcuture => id int, data varchar(500)]
> This table contains 45000 records.
> When this query is executed from Query Analyzer locally on the database
> server, output is returned in 30 seconds.
> However, when the query is executed from Query Analyzer installed on
> application server it takes 110 seconds.
> Execution plan is same both times. CPU, reads are identical for both
> executions. However there is big difference between the execution times.
> Also, both database server and application server in same VLAN and direct
> network connectivity is available without any interim hops.
The most obvious guess is that network latency matters. It may be a good
VLAN, but on the database server the connection is over shared memory
so it's not unreasonable that it takes more time to get it over the
wire.
Another thing to consider is the CPU power on the two machines.
Particularly if you run the output in text mode in QA, this could
matter.
Then again, I don't really see the point in measuring how long time
takes to receive 45000 rows in Query Analyzer. The tool is not really
intended for that.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Query Analyzer was used just to indicate a client application.
We are also monitoring the SQL calls using Profiler which is also giving us
the same output.
Also, the two boxes are of same configuration in terms of RAM & CPU.
You mentioned about connection being created in shared memory. Can anyone
please explain it in more detail as to how will it impact the query
performance? It might help us in understanding the problem better and work
towards a solution.
This time we enabled client statistics option in Query Analyzer. The
difference between two queries is for following attributes,
1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
(app server)] v/s 81 [for 30 sec execution (locally from database server)]
2. "Number of bytes received" recieved is slightly more for query executed
from app server compared to database server
What does "Cumulative wait time on server replies" stats represents and how
to interpret it?
Thanks in advance for the responses.
"Erland Sommarskog" wrote:

> pvv30 (pvv30@.discussions.microsoft.com) writes:
> The most obvious guess is that network latency matters. It may be a good
> VLAN, but on the database server the connection is over shared memory
> so it's not unreasonable that it takes more time to get it over the
> wire.
> Another thing to consider is the CPU power on the two machines.
> Particularly if you run the output in text mode in QA, this could
> matter.
> Then again, I don't really see the point in measuring how long time
> takes to receive 45000 rows in Query Analyzer. The tool is not really
> intended for that.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||pvv30 (pvv30@.discussions.microsoft.com) writes:
> Query Analyzer was used just to indicate a client application.
I don't think it's a good benchmark for that purpose, since QA does a lot
of things a client app wouldn't do. You are better of writing a client
application that just receives the dataset. Particularly, you should use
the client API you plan to use in your client application.

> You mentioned about connection being created in shared memory. Can anyone
> please explain it in more detail as to how will it impact the query
> performance? It might help us in understanding the problem better and work
> towards a solution.
Normally clients communicate with SQL Server over network connection, most
commonly these days is TCP/IP, but there is also support for named pipes
and a few more protocols.
But when the client and server are on the same physical machine, using
the network is bit of overkill and therefore there is the possibiliy to
use shared memory, and this is also the default when connecting locally.
You can use the Client Network Utility to turn off shared memory, but
even with TCP/IP locally, you would see much better execution times
locally than over the network.

> This time we enabled client statistics option in Query Analyzer. The
> difference between two queries is for following attributes,
> 1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
> (app server)] v/s 81 [for 30 sec execution (locally from database server)]
> 2. "Number of bytes received" recieved is slightly more for query executed
> from app server compared to database server
> What does "Cumulative wait time on server replies" stats represents and
> how to interpret it?
Books Online says "Cumulative amount of time the driver spent waiting for
replies from the server."
I would account the difference in numbers to the network connection. I
did a quick test on our site. I ran the same SELECT * against a 35000
row table on two servers. One which is hear at our main office in
Stockholm, and one which is at our branch office in Gvle. For the
server here in Stockholm the number was 60, and the server in Gvle
it was 12075. The difference in execution time was 2 seconds in
Stockholm and 28 seconds for the server in Gvle.
I ran both tests from my own machine, which hosts none of the servers in
question.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx