Thank you all for responding. Below are the details all of you asked for:

1. Dataset returned is 1.75 million rows. The query is a simple SELECT query, but aggregating (SUM) on a large number of rows. So i guess 30 secs is acceptable for now.

2. Number of tables joined in the query are two. The join is a simple INNER JOIN. First table has around 5 million rows and second table has 30,000 rows. you might have already guessed, aggregation is happening on first table.

3. Same user for all connections.

4. Windows login used for connecting to remote server from my local SSMS client.

5. Windows login used when connecting to the box through RDP session and through SSMS client on the box.

6. I checked ANSI settings and both SSMS clients have similar settings. No difference.

I tried executing the query from other machines using other user logins and got same result. Query executes much faster when connecting through remote SSMS client and same executes much slower when connecting through SSMS client on server box. For the life of me, i am not able to figure this out. Any help is highly appreciated.

Thanks,

Vasu