Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query data using linked server Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 4:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
HI,

Linked servers in test environment working differently than prod. Not sure what is causing slowness in test environment? is this issue with SQL version? Please advise.

Test ServerA – SQL 2000 dev edition 8.00.2055 (X86), Windows NT 5.2 (Build 3790 SP2)
TestServerB -- SQL 2000 std edition 8.00.2055 (X86), Windows NT 5.2 (Build 3790 SP2)

Linked server exists between TestServerA and Test ServerB (also vice versa)


ProdServerA-- SQL 2000 EE edition 8.00.2187 (X86), Windows NT 5.2 (Build 3790 SP2)
ProdServerB-- SQL 2000 EE edition 8.00.2055 (X86), Windows NT 5.2 (Build 3790 SP2)

Linked server exists between ProdServerA and ProdServerB (also vice versa)


Pulling data from ProdServerA to ProdServerB:
• Estimated Execution Plan shows an Inner Join being used.
• Runs quickly.

Pulling data Test ServerA to TestServerB :
• Estimated Execution Plan shows a Hash Join being used.
• Very slow due to Hash. Trying to bring entire source table of rows across.

TestServerB -Is setup with linked server to TestServerA.
TestServerA on this server has system tables named SysRemote_...

TestServerA Is setup with linked server to TestServerB
TestServerB on this server does NOT have system tables named SysRemote_...


Here’s the query:
SELECT
rp.ReadID,
rp.ReadPositionIndex,
rp.ConvertedValue,
rp.LaserPower,
rp.LaserDuration,
rp.Counts,
rp.CalibrationID,
rp.GlowCurveData
FROM
-- To run on ProdServerB (Production)
TableB arp
JOIN ProdServerA.DatabaseA.dbo.TableA rp ON arp.ReadID = rp.ReadID AND
arp.ReadPositionIndex = rp.ReadPositionIndex

-- To run on TestServerB (Test)
-- JOIN TestServerA.DatabaseA.dbo.TableA rp ON arp.ReadID = rp.ReadID
-- AND arp.ReadPositionIndex = rp.ReadPositionIndex
Post #1430663
Posted Thursday, March 14, 2013 2:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 3,085, Visits: 3,281
Hi - it might be worth checking what other software you have installed on your Dev server - is the hardware the same spec and so on. Otherwise you won't be doing a fair comparison.

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1430808
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse