x

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
more ▼

asked Oct 19, 2010 at 06:31 AM in Default

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

@DaniSQL - can you provide the execution plans for the queries? Maybe we can do something for you in the short-term to at least reduce the 5 hours to something approaching acceptable
Oct 19, 2010 at 07:33 AM WilliamD
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
Oct 19, 2010 at 04:08 PM Scot Hauder
@Scot I have tried that after I read some related thread after googling but it didnt help much. SQL 2008 kept generating horrible plans. But OPENQUERY helped me solve most of the problem now.
Oct 20, 2010 at 06:52 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

@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
more ▼

answered Oct 19, 2010 at 06:43 AM

ozamora gravatar image

ozamora
1.4k 2 3 5

@ozamora, Only two servers are involved 'Server1' and 'Linkedserver'
Oct 19, 2010 at 06:53 AM DaniSQL
The REMOTE hint will only work where collations match and on inner joins, so I don't think that will work here.
Oct 19, 2010 at 06:58 AM WilliamD
I see @DaniSQL. Check my updated answer. @WilliamD, you got BOL in your brain. Good catch.
Oct 19, 2010 at 07:16 AM ozamora
Wonderful!! earlier I had moved the 2nd and 3rd query and other similar queiries out placed them as a separate sp on 'LinkedServer' but this one is better.
Oct 19, 2010 at 07:43 AM DaniSQL
@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.
Oct 19, 2010 at 08:08 AM ozamora
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 19, 2010 at 06:54 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1 and congrats on the 4K William :)
Oct 19, 2010 at 07:18 AM Fatherjack ♦♦
Thanks muchly :) - still got a ways to go to if I want to catch up to the top 4 though
Oct 19, 2010 at 07:23 AM WilliamD
Congrats @William on the 4K!
Oct 19, 2010 at 07:44 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Oct 19, 2010 at 06:43 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

It depends on which part of the query I am running but mostly ASYNC_NETWORK_IO, OLEDB, SOS_SCHEDULER_YIELD and lots of CXPACKET before I forced it to use few cores to execute the query
Oct 19, 2010 at 06:52 AM DaniSQL
OLEDB is the linked server, ASYNC_NETWORK_IO most probably too.
Oct 19, 2010 at 06:55 AM WilliamD
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.
Oct 19, 2010 at 07:16 AM DaniSQL
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.
Oct 19, 2010 at 07:22 AM Blackhawk-17
How about doing the replace gubbins as an indexed computed column too so that it is the same on both sides. That would allow you to remove the functions in the WHERE clause and improve SARGability
Oct 19, 2010 at 07:43 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x1834
x106
x68

asked: Oct 19, 2010 at 06:31 AM

Seen: 7037 times

Last Updated: Oct 20, 2010 at 06:54 AM