Slow query between linked servers

Posted on 2011-10-31
Last Modified: 2012-05-12
In my test environment I have a query that crosses two linked servers. They are both under that same instance of SQL 2005. When I run the query in the test environment, it returns results in about 10 seconds. When I deployed it into production, the exact same query takes 2 1/2 minutes. The biggest difference I see is that in production one database is SQL 2005 and the other is SQL 2000. There are separate instances for both on the same server. Could this be the reason for the slow response time and is there anything, short of dumping SQL 2000, to help speed this up?
Question by:rwheeler23
    LVL 5

    Assisted Solution

    Compare the properties of the linked servers in both servers; Test and Production. Then, try to apply those of Test on Production.
    LVL 22

    Accepted Solution

    For openers, if you haveen't already migrated off of SQL Server 2000 in your production environment, you seriously need to . . . especially since your test environment is already on SQL Server 2005!  

    Short Answwer: Short of migrating the SQL Server 2000 database to SQL Server 2005, you are probably stuck with the timing, unless you can improve the query's overall performance (but, from the test environment time, I would have to think that doesn't offer much hope).

    Longer answer:
    Yes, the fact that you are linking two separate instances, much less a SQL Server 2000 and a SQL Server 2005 instance, is conributing to the slow-down.  The fact that you have the two databases under one instance on your Test Server and it performs well only shows you that, unless the Test environment matches the Production environment, you aren't really performing a valid test. ;-)  Try bringing your Test Environment into alignment with your Production environment (either by adding a SQL Server 2000 instance to your Test Environment and putting the linked database on it or putting both of your Production databases in the same SQL Server 2005 instance) and you will be better able to compare the performances. ;-)


    Author Comment

    My problem is that I am being forced to pull data out of the SQL 2000 instance into SQL 2005 where 95% of the data resides. This issue gives me just one more reason to dump SQL 2000. I think the real reason is money. They have a 35 user SQL 2000 license but only a 5 user SQL 2005 license. Personally I would prefer to bring both of them up to SQL 2008 R2 but they have an old legacy application attached to the SQL 2000 database and they have not been willing to invest the time to upgrade that application to at least SQL 2005. My role is simply to advise. As long as they insist on the old SQL 2000 they will need to live with slow response times. One other thought I had was to create an SSIS job that runs at night that will pull the data out of SQL 2000 and into SQL 2005. I only need data in 4 tables. The rest of the tables are meaningless to me and the data does not change that often where this would be a problem. What do you think?
    LVL 22

    Assisted Solution

    I started to suggest extracvting the needed data into your SQL Server 2005 world rather than joining across the linkage.  Depending on the number of rows you would bneed to extract and the number of columns, you might be able to do the extraction a couple of times a day and satisfy the needs of whatever you are doing.  Using SSIS 2005 to do the extraction would take a little time but, depending on the frequency of use of the particular query in question, it could be a good trade-off . . . especially if it could be done in off-hours.

    Author Comment

    We are talking an insignificant amount of records. They are PO records with maybe 2,500 header readers and 10,000 detail records. A simple SSIS job running at 3am will have the records in place for the next day. I may even give them a button where if they the data immediately they just push the button. When I ran SQL Profiler trying to see if I could what was taking so long, you see absolutely nothing for over 3 minutes, then all of a sudden the rpc starts and completes in less than 5 seconds.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Add another "Having" clause? 9 39
    Sql query 12 56
    How to show the last employee that updated a record 5 35
    Sql Permission 6 23
    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now