I really need your help on this guys:
I have upgraded side by side one of our servers to SQL Server 2008 on Windows 2008 R2 Starndard and I am getting horrible linked server performance on one of the SP's. On the old server which was a sql server 2005 it took less than 3 min to excute the sp now its taking from 5-10 HOURS. I made sure statistics are updated and Linked server security is elevated to make sure it has access to the statics of the target database, but still its taking hours. Since the new server is a virtual machine I have checked whether the host is having underlying resource issues but thats not the case. So i started evaluating the plans generated by the two versions which I found very different. The 2005 one uses hash join and the 2008(the slow one) uses Nested loops. Since the queries doesnt have join in them i am not able to use query hint INNER HASH JOIN. Alos I have tried OPTION(HASH JOIN) and its failing with error Msg 8622, Level 16, State 1.
Here are sample queries from the sp:( N.B. I have renamed the server and databases but not the table and columns and also Queries are running on Server1)
Any Ideas are appreciated.
I have finished modifying the queries: I changed all totally remote queries to run as Execute(' ') AS 'LinkedServer' and re-wrote the update queries with OPENQUERY and the whole sp is taking less than 3 min now. Thank you all for the help
@DaniSQL, this might be able to help on steps 2 and 3.
=========== First post ======================
@DaniSQL, my guess is that you cannot refactor this in a timely fashion, but just as fact dealing with linked servers for such a large operation is not really recommended.
What I can infer here is that you have 3 instances (A,B,C). Instance B is pulling data from A and C to compare.
My recommendation will be to try using the REMOTE hint and let SQL Server force all the join operations at the remote site.
If you have a chance to refactor, then collect all the data that needs to be from server C using
-- On Server A
Then used the #temp table to update on server A within the same instance.
Good Luck 125
In my experience Linked Servers are always crappily slow - even if they are both new SQL Servers with identical setups and connected on the same network switch.
I would like to suggest that you think about maybe replicating the tables that you are working with (or a subset of them) into a central location so that you have a local read-only version of the data. That way SQL Server can fully untilise the realtional engine to process your queries (You can also change the collation to match up in flight - also a performance booster!)
To add to this, the
The suggestion from @ozamora is valid if you don't want to go the replication route. Grab the data into a local temp table and process that instead. You will see a performance difference then.
Before Grant asks - what are your WAIT STATES?
---------- Minutes to hours is a very large increase. How much data are we talking at various stages in the SP? And does the 2005 version return the same as 2008 at all steps?
Are the costs in the execution plans for the remote work similar? ASYNC_NETWORK_IO is time waiting for a result set basically.
I have a question... can these sections, since they appear totally remote, not be SPs on the linked server?