Solved

SQL Server linked server performance issue

Posted on 2013-11-21
9
3,129 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
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Assisted Solution

by:BradySQL
BradySQL earned 250 total points
ID: 39668247
I don't know if you are going to love the answer with the size of you query but here you go.

When running a query like that over a linked server your performance is going to be slow due to the nature of a linked server. If you want to get better performance I would advise putting it in a dynamic query and running it directly on the remote machine. Or you can use a stored procedure that you can call on the remote database.

Examples

DECLARE @sql
SELECT @sql = 'SELECT * FROM [Database].dbo.Person WHERE Name LIKE ''A%'''
EXEC (@SQL) AT [LinkedServer]

OR

EXECUTE ('SELECT * FROM [Database].dbo.Person WHERE Name LIKE ''A%''') AT [LinkedServer]

OR - If you need to work with it later.

INERT INTO #temp EXECUTE ('SELECT * FROM [Database].dbo.Person WHERE Name LIKE ''A%''') AT [LinkedServer]

When you run a query like SELECT * FROM [LinkedServer].[Database].dbo.Person SQL will actually access the remote table like it would a local table, this is what takes it so long to run, you obviously don't have the same bandwidth to the remote database as you do to a local database, so this approach rarely works. However when you run a query like EXEC ('SELECT * FROM [Database].dbo.Person) AT [LinkedServer] you are instructing sql to run the script locally on the remote database and only return you the results. This speeds things up considerably.

If you want to use these results later the best thing to do is use a temp table or global temp table to hold them depending on your situation.

Hopefully this helps. A liked server is never an easy solution to run queries like this.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39670410
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.
0
 

Author Comment

by:sqldba2013
ID: 39670972
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.
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 39685829
Hi,

My advice is to develop against a linked server. Linked server queries are difficult for SQL to optimise as well as queries contained within the server. The core problem is that the query has to run on a server. That is, it can run here or there, and if it runs here then it transfers the entire table from there and filters it here.

To do the linked server, you can create a linked server to itself, but I'm not sure if this shows the usual linked server problems.

The other way around some of this, is to not write queries with the four part naming convention (server.database.schema.table) but to use the openquery function. This allows part of the query to run against the linked server, and part to run against the local server.

ie
select blah from openquery( linkedservername, 'select blah from table etc' ) as ll inner join somelocal table t on t.column = ll.column

The key issue is making the openquery dynamic and passing parameters to the where clause, and this is often handled using dynamic SQL.

Other technics:
Create procedures on the linked server and execute them via openquery, to perform filtering and calculations on the distant server.
Bring the results back via openquery (or four part query) to a temp table, and then continue with the local processing.

HTH
  David
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 39686483
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
0
 
LVL 34

Expert Comment

by:ste5an
ID: 39686826
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 .
0
 

Author Closing Comment

by:sqldba2013
ID: 39812028
--
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 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