SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 select query performance issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

laddu
Constraint Violating Yak Guru

USA
327 Posts

Posted - 03/13/2013 :  18:12:27  Show Profile  Reply with Quote
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

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 03/13/2013 :  18:21:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
First things first, is the data the same in both systems? Next, are the statistics on those tables up to date on both systems? Do they have the same exact indexes?

If any of those answers are "no" then that's probably why the plans are different.
Go to Top of Page

laddu
Constraint Violating Yak Guru

USA
327 Posts

Posted - 03/18/2013 :  11:36:19  Show Profile  Reply with Quote
Hi,

In test environment Statistics are different than PROD for that particular table. In prod total 17 statistics, last udpated date 3/17/2013. In test total 5 statistics and last updated date 3/20/2011. How do i copy all the prod statistics to test in sql 2000 version? Indexes are same on both environments. Please let me know.

Edited by - laddu on 03/18/2013 11:39:57
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 03/18/2013 :  14:00:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
I can't remember if Enterprise Manager can script statistics, but I'd suggest looking for that option under "Script" or "Generate Scripts". If that doesn't work let me know. I'm also not sure if it will pick up auto-created stats.
Go to Top of Page

laddu
Constraint Violating Yak Guru

USA
327 Posts

Posted - 03/18/2013 :  16:58:18  Show Profile  Reply with Quote
I did't find any option to script statistics in Enterprise manager. I manually created the missing stats in test server and tried to execute the above statement. No luck.

I see that test server has additional schema and data changes, not sure that is the reason for slowness. I am also checking on linked server properties..
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 03/18/2013 :  17:57:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
You may have to flush the procedure cache and re-run the queries. This will cause performance to drop on both machines until the procedures are recompiled normally. If you can live with that, then run DBCC FREEPROCCACHE on both servers.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000