SQL execution slow over the network

[pramodbura]

We have a quick query regarding SQL performance.
We have SQL Server 2000 (32 Bit) and SQL Server 2005 (64 Bit) as two
separate instances on a DB Server.

We were analysing the execution times for the same stored procedure on
both instances:
1. Through Remote Desktop of the actual DB server
2. Through Query Analyser of my local machine.

The results were as follows:

1. Through Remote Desktop of the actual DB server

Iteration SP Execution Time (in secs)
SQL 2000 SQL 2005
1 28 5
2 27 3
3 27 3
4 40 3
5 38 3
Average 32 3


2. Through Query Analyser of my local machine.
Iteration SP Execution Time (in secs)
SQL 2000 SQL 2005)
1 37 96
2 32 77
3 35 84
4 27 79
5 43 91
Average 35 85


Could you please provide some light on why case 2 is slow and any
suggestions to improve the same?

Thanks in Advance!

[Jack Vamvas]

Re: SQL execution slow over the network

Are you testing with the same basis: i.e cache cleared , clean buffers.Also
, are the statistics updated on both?

--

Jack Vamvas
_______________ _______________ _____
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"pramodbura " <pramodbura@gma il.comwrote in message
news:1192422342 .795840.169000@ e34g2000pro.goo glegroups.com.. .

We have a quick query regarding SQL performance.
We have SQL Server 2000 (32 Bit) and SQL Server 2005 (64 Bit) as two
separate instances on a DB Server.
>
We were analysing the execution times for the same stored procedure on
both instances:
1. Through Remote Desktop of the actual DB server
2. Through Query Analyser of my local machine.
>
The results were as follows:
>
1. Through Remote Desktop of the actual DB server
>
Iteration SP Execution Time (in secs)
SQL 2000 SQL 2005
1 28 5
2 27 3
3 27 3
4 40 3
5 38 3
Average 32 3
>
>
2. Through Query Analyser of my local machine.
Iteration SP Execution Time (in secs)
SQL 2000 SQL 2005)
1 37 96
2 32 77
3 35 84
4 27 79
5 43 91
Average 35 85
>
>
Could you please provide some light on why case 2 is slow and any
suggestions to improve the same?
>
Thanks in Advance!
>

[pramodbura]

Re: SQL execution slow over the network

Hi,
We have cleared the cache. And updated the stats too..
Notice the significant change in the execution time of the query in
SQL Server 2005 instance ie.an average of 3 sec over 5 iterations
using Remote desktop. And the same using client connection takes 85
secs,

[Erland Sommarskog]

Re: SQL execution slow over the network

pramodbura (pramodbura@gma il.com) writes:

We have a quick query regarding SQL performance.
We have SQL Server 2000 (32 Bit) and SQL Server 2005 (64 Bit) as two
separate instances on a DB Server.
>...
1. Through Remote Desktop of the actual DB server
>
Iteration SP Execution Time (in secs)
SQL 2000 SQL 2005
1 28 5
2 27 3
3 27 3
4 40 3
5 38 3
Average 32 3
>
>
2. Through Query Analyser of my local machine.
Iteration SP Execution Time (in secs)
SQL 2000 SQL 2005)
1 37 96
2 32 77
3 35 84
4 27 79
5 43 91
Average 35 85
>
>
Could you please provide some light on why case 2 is slow and any
suggestions to improve the same?

Does the stored procedure return any result sets? Do you run with
SET NOCOUNT ON?

Verify with DBCC USEROPTIONS that your settings are exactly the same in
all cases. Also examine the query plan to see if you have the same
plan when you run locally and remotely.

Which query tool do you use when you run on the DB server through
Remote Desktop?


Sorry, no answers, only questions. I think I would like to more before
I try to say what is going on.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx