We help IT Professionals succeed at work.

SQL Server linked server performance issue

5,932 Views
Last Modified: 2014-01-27
We are fetching prod data from two different servers using attached query with linked server setup.

In development server (without linked server setup, two DB's on same server), the attached query will take around 3 minutes to return 10000 records.

But in prod linked server, it was taking around 2 hour 40 minutes of time to return 10000 records.

Please review the attached sql query, indexes and execution plan and advise me how to speed-up the attached query using linked server setup in prod environment.
SQLQuery.sql
indexes.sql
execution-plan-file.sqlplan
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
I agree with previous post that a SQL Stored proc on the remote server is the best and execute that one to return you the exact record set you need.

Also may I ask what is the purpose for "fetching prod data from two different servers using attached query with linked server setup."?

Same thing can be achieved in many different ways and much faster if data is on the same server so you could bring one set of data into a staging table or temp table from the remote server and use it/aggregate it if needed on one server only.
Try create a SQL Stored proc (or even a view if there are just simple selects) that gives you exactly the record set you need from linked server and run it on that liked server to see how long it takes. Should be quick and if it's like that the use data returned by that sproc to populate a staging table or temp table then use that one rather than distributed query against two servers and databases - those will be the slowest no matter what.

Author

Commented:
We have to fetch data from two DB's and these two DB's are located in different servers. Because of this we are planning to implement linked server.

Is there any other solution instead of linked server setup? Our main goal is to reduce/speed-up the execution of query.
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
You are running what is known as "Distributed Queries" and they are guaranteed to be slow if you are doing JOIN's the mix tables from multiple servers.  To make matters worse, you are doing a lot of collation casting which will slow things down even more.

This is an excellent article on the topic:
http://www.sqlbadpractices.com/linked-servers-and-distributed-queries/

I'd recommend using the OPENQUERY object that dtodd mentioned.  Done well it can trim the data transmitted from the remote server to local to minimize latency
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
The most important problem arises when running distributed queries from the fact that you may not use the statistics from the remote tables. See Linked servers: permissions and distributed query performance .

Author

Commented:
--

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.