September 23, 2010 at 3:08 pm
I had a sql statement which ran on a hefty sql 2005 box at a remote location that looks at some data and then did a NOT IN against some data on a hefty sql 2008 server (local at our data center). The statement was taking almost 9 minutes. All indexes looked correct on both sides. I moved the statement to the (local 2008 box) and ran the same statement, it took 20 seconds for same results. ANyone seen this behavior before. I remember somthing like this when I was doing linked server queries between SQL 2000 and 2005.
September 24, 2010 at 8:02 am
Yep. A linked server query will move all the data across the wire and then perform filtering. You're better off using OPENQUERY to pass the processing to the remote machine and only move the results over.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2010 at 8:56 am
But why when the query is run on the 2008 server the query back to the data on the sql 2005 server should still be slow. It isn't, no OPENQUERY on either end. They are both linked servers in either direction
September 24, 2010 at 9:04 am
You mean that the exact same amount of data is moved regardless of which side you run the query from? Are you sure that one side or the other isn't doing different processing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2010 at 9:06 am
There is actually more data on the sql 2005 side than on the 2008
September 24, 2010 at 9:21 am
So you're saying that the 2008 instance moves more data locally and then processes faster than the 2005 instance...
Are there other differences? More memory, better/more cpu's? Are the connection settings on both servers the same? Is one using a different connection mechanism, different driver (other than 2008)?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2010 at 9:33 am
new cpu on 2008, more memory on 2005
September 24, 2010 at 11:23 am
Well, we're already not comparing apples to apples then. That just makes it harder. No differences in the connection settings, both using the same driver (albeit, different versions)?
I haven't seen anything that, in and of itself, would make for such a radical difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2010 at 12:04 pm
No but still pretty close, several minutes verus seconds is large
September 27, 2010 at 1:10 pm
Just as any FYI, tested going from a sql 2008 box with one cpu, and 6 gig of RAM, still ran fast as compared to the other way, I beleive this qualifies as an undocumented feature
September 27, 2010 at 5:39 pm
I would look at the execution plans from both queries and compare them. 2008 may be creating a more efficient plan.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply