March 13, 2013 at 4:10 pm
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
March 14, 2013 at 2:04 am
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 [/url]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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply