Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2013-03-13 : 18:12:27
|
| 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.GlowCurveDataFROM-- 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
15732 Posts |
Posted - 2013-03-13 : 18:21:20
|
| 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. |
 |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-03-18 : 11:36:19
|
| 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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-03-18 : 14:00:57
|
| 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. |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-03-18 : 16:58:18
|
| 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.. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-03-18 : 17:57:54
|
| 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. |
|
|
|
|
|
|
|
|
select query performance issue