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]

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" <pr********@gmail.comwrote in message
news:11**********************@e34g2000pro.googlegr oups.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]

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]

pramodbura (pr********@gmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx