December 27, 2011 at 6:24 am
Today we have two SQL 2000s that use linked servers to get data from another SQL Server and the transactions run quite quickly. We are in the process of moving the first of those SQL 2000 dbs to SQL 2008R2 SP1 CU2 cluster and I created a linked server back to the SQL2000 DB. However, queries that used to run in 11 seconds take 5 minutes and I have narrowed it down to all of the time is getting the data from the SQL2000 table. The explain looks quite different now but I am at a loss as to why it takes so long to retrieve the data from SQL2000. As a test I moved the SQL2000 linked server db to another SQL2008R2, rebuilt indexes and updated stats and created a linked server to that and it runs in 30 seconds. Much quicker but still a lot longer than the old SQL2000/SQL2000 linked server.
Does anyone have any ideas of what I should check to see where the bottleneck is?
December 27, 2011 at 7:23 am
I would say it depends on what operations you're doing on the data as you retrieve it. Also, what driver you're using for the connection. Quite possibly, you'll need to look into making sure the connections are defined correctly for what you're trying to do, and look into how the distributed transaction coordinator is being used.
Without more specifics about what you're doing, I can't be more specific than that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 6:07 am
Well I have narrowed it down to opening up Mgt Studio and running a select from the linked server table and in SQL 2000 it runs 30 seconds.... in SQL 2008 it runs over 3 minutes. I don't know where to start looking to see what the bottleneck is. It isn't stats and it isn't tuning indexes.
December 28, 2011 at 6:47 am
Can you check execution plans on each of those servers? Locally to each, I mean, not the linked plan.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 7:04 am
Clustered index scan 100% on both on the primary Key. For simplification I am now simply running a select * from xtable... nothing complicated just a simple select.
Both queries start to return data fairly quickly... the SQL2008 query seems to take a long time returning all of the data and the SQL2000 query returns the data faster. I am thinking it isn't the database engine as much as some throughput issue. That is why I don't have any idea where to begin. The SQL2008 server is much more powerful, not really being used yet and has 48 gig of memory.
As another test I moved the database that has the SQL2000 'linked' data to another SQL 2008 that isn't being used yet, rebuilt the indexes, updated stats and setup a linked server to it and it runs slow as well.
I just opened a case with Microsoft.
December 30, 2011 at 2:58 pm
Markus (12/28/2011)
Clustered index scan 100% on both on the primary Key. For simplification I am now simply running a select * from xtable... nothing complicated just a simple select.Both queries start to return data fairly quickly... the SQL2008 query seems to take a long time returning all of the data and the SQL2000 query returns the data faster. I am thinking it isn't the database engine as much as some throughput issue. That is why I don't have any idea where to begin. The SQL2008 server is much more powerful, not really being used yet and has 48 gig of memory.
As another test I moved the database that has the SQL2000 'linked' data to another SQL 2008 that isn't being used yet, rebuilt the indexes, updated stats and setup a linked server to it and it runs slow as well.
I just opened a case with Microsoft.
Make sure to test your networking between servers outside of SQL and make sure its as fast as you'd expect given your adapters. Just a shot in the dark but something I'd try, having been bit by networking wierdness before.
December 31, 2011 at 6:33 am
In doing some further testing we noticed that it was NETWORK IO... then it dawned on me... the two servers are in different data centers.... dumb me....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply