asked
Extremely horrible Linked server performance after upgrade to 2008
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) insert into [Server1].DB1.dbo.SCAN_COMPLETION_FRM( ID, USER_ID, CASE_ID, SCAN_TYPE_ID, SCANNER_ID, INTERPRETING_PHYSICIAN_ID, SUBMITTED, SCAN_COMPLETED, COMPLETION_DATE, FILLED_FIRST_NAME, FILLED_LAST_NAME, REGION_SCANNED_ID) select ID, USER_ID, CASE_ID, SCAN_TYPE_ID, SCANNER_ID, INTERPRETING_PHYSICIAN_ID, SUBMITTED, SCAN_COMPLETED, COMPLETION_DATE, FILLED_FIRST_NAME, FILLED_LAST_NAME, REGION_SCANNED_ID from "LinkedServer".DB3.dbo.SCAN_COMPLETION_FRM where case_id in (select id from [Server1].DB1.dbo.CASE_REGISTRATION_FRM) insert into "LinkedServer".DB2.dbo.UNIQUESSN(SSN) select distinct replace ( ltrim(rtrim(ssn)), '-', '') from "LinkedServer".DB3.dbo.CASE_REGISTRATION_FRM where replace(ltrim(rtrim(ssn)), '-', '') COLLATE DATABASE_DEFAULT not in (select distinct SSN from "LinkedServer".DB2.dbo.UNIQUESSN) insert into "LinkedServer".DB2.dbo.UNIQUESSN(SSN) select distinct replace(ltrim(rtrim(ssn)), '-', '') from "LinkedServer".DB2.dbo.CASE_REGISTRATION_FRM where replace(ltrim(rtrim(ssn)), '-', '') COLLATE DATABASE_DEFAULT not in (select distinct SSN from "LinkedServer".DB2.dbo.UNIQUESSN) update [Server1].DB1.dbo.CASE_REGISTRATION_FRM set SSN = convert(nvarchar(15),
UNIQUESSN.id) from "LinkedServer".DB2.dbo.UNIQUESSN UNIQUESSN where UNIQUESSN.SSN = replace(ltrim(rtrim(DB1.dbo.CASE_REGISTRATION_FRM.SSN)), '-', '') Any Ideas are appreciated. UPDATE: 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 another thing to look at is the provider options (SQLNCI10/SQLOLEDB or whatever you use) under the Linked Servers node. Try checking Allow Inprocess and Dynamic Parameter. This often solves poor performance with linked servers
@DaniSQL, this might be able to help on steps 2 and 3. EXECUTE (' insert into DB2.dbo.UNIQUESSN(SSN) select distinct replace ( ltrim(rtrim(ssn)), ''-'', '''') from DB3.dbo.CASE_REGISTRATION_FRM where replace(ltrim(rtrim(ssn)), ''-'', '''') NOT IN (select distinct SSN from DB2.dbo.UNIQUESSN) ') AT [LinkedServer]; EXECUTE (' insert into DB2.dbo.UNIQUESSN(SSN) select distinct replace(ltrim(rtrim(ssn)), ''-'', '''') from DB2.dbo.CASE_REGISTRATION_FRM where replace(ltrim(rtrim(ssn)), ''-'', '''') not in (select distinct SSN from DB2.dbo.UNIQUESSN) ') AT [LinkedServer]; =========== 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 INSERT INTO #temp (ColumnA, ColumnB, ..., ColumnN) EXECUTE ('SQL') AT [C}; Then used the #temp table to update on server A within the same instance. Good Luck 125
@DaniSQL, for query 1 and 4, if the DB collations are the same, see if you can use the REMOTE hint (
http://goo.gl/iWkx). The idea is to have the local table on the right side of the INNER JOIN.
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. ---------- EDIT I have a question... can these sections, since they appear totally remote, not be SPs on the linked server? insert into "LinkedServer".DB2.dbo.UNIQUESSN(SSN) select distinct replace ( ltrim(rtrim(ssn)), '-', '') from "LinkedServer".DB3.dbo.CASE_REGISTRATION_FRM where replace(ltrim(rtrim(ssn)), '-', '') COLLATE DATABASE_DEFAULT not in (select distinct SSN from "LinkedServer".DB2.dbo.UNIQUESSN) insert into "LinkedServer".DB2.dbo.UNIQUESSN(SSN) select distinct replace(ltrim(rtrim(ssn)), '-', '') from "LinkedServer".DB2.dbo.CASE_REGISTRATION_FRM where replace(ltrim(rtrim(ssn)), '-', '') COLLATE DATABASE_DEFAULT not in (select distinct SSN from "LinkedServer".DB2.dbo.UNIQUESSN)
Looks like 2008 decides to go RBAR with the replace(ltrim(rtrim(ssn)), '-', '') section. We need to re-work that logic. Maybe pass the value to a new SP on the remote side that returns a result set.
In the above queries the first one takes only seconds. But the bottom three takes hours. For these queries the cost is different between the old plan the new plan. For example the cost of one of the nested join for the third insert query is 222222 and I am not kidding. For some reason SQL server 2008 doesnt figure out how to efficiently get the data out from the other server and insert/update data here. I have still the old server set up and it takes only couple of min and uses a totally different plan.
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 `IN` sections of the code may be your reason for nested loops. I don't know how linked servers do on gathering statistics, but I am guessing the execution plan is thinking that the linked server is only delivering a few rows back, when in fact you are getting a lot more. The missing statistics can lead to the bad join decision. 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.
