Posted by Erland Sommarskog on 10/15/07 21:48

pramodbura (pramodbura@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, esquel@sommarskog.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

[Back to original message]