SQL SERVER - Linked Server and query performance

Question

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =).

• I have a database K with 3 linked servers L1,L2,L3 to 3 databases X,Y,Z.
• In X,Y,Z i have, respectively, 3 views called V1,V2,V3.
• I want to query the union of V1,V2,V3 by database K with linked server L1,L2,L3.

After some testing, this is the situation:

1. In SSMS, if I run this pseudo query SELECT * FROM (L1.V1 u L2.V2 u L3.V3) WHERE some filters the performance is really great
2. If I create a view VK in database K, which contains the union of the three views and then i run the query SELECT * FROM VK WHERE some filters the performance is worse than case 1

Questions

• Why is the performance so different?
• How can I improve performance in case 2?

I'm interested in improving performance in case 2 because I need a view to map with nHbinernate in our software...

Thanks in advance, regards

UPDATE AFTER JOHN ALAN's POST

Ok, I try but with no results. I'm not a DBA and my skills on DB configuration is really limited. Can we proceed step by step?

1. On remote server (called Y) I created a new account (called linkedserver) by security->logins->new login. I select login name then sql authentication and I choose a password. for deafault database I select master. In server roles tab I select public. in User mapping tab I select the databases involved in remote queries and, for each of them, I select db_ddladmin and public role. Then, for each database involved in remote query, I checked the effective permission for linkedserver user and there are a lot of ALTER and a lot of CREATE permissions but not SHOW PLAN (then I selected this one too).

2. On the database server (called X) where the linked server to Y exists, I created a linked server (called L1). In security TAB, I selected local user sa and remote user linkedserver with its password.

When i run the query across L1 without a VIEW I have good result, If I put the query in a VIEW low performace, nothing have changed...

I think I did some step wrong, but I don't know where...

FOR BETTER CLARITY

This is the query I run without a view using linked server:

select * from
(
 select * from dolph.agendasdn.dbo.vistaaccettazionegrp
 union
 select * from dolph.acampanet.dbo.vistaaccettazionegrp
 union
 select * from municipio.dbnet.dbo.vistaaccettazionegrp
) a 
where cognome = 'test' and nome = 'test'

into the view I put only this code

 select * from dolph.agendasdn.dbo.vistaaccettazionegrp
 union
 select * from dolph.acampanet.dbo.vistaaccettazionegrp
 union
 select * from municipio.dbnet.dbo.vistaaccettazionegrp

then I called select * from VIEW where cognome = 'test' and nome = 'test'

So..

• 1st case 0-1 seconds.
• 2nd case >15 seconds...

I think this is absurd!

EXECUTION PLAN

The execution plan with plain query, without the use of a view: enter image description here

the execution plan using the view: execution plan with view

• Roi Gavish: Please post your execution plan for each of the cases

Answer 1

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
 SELECT * 
 FROM dolph2.agendasdn.dbo.vistaanagraficagrp
 UNION
 SELECT * 
 FROM dolph2.acampanet.dbo.vistaanagraficagrp
 UNION
 SELECT * 
 FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

• ced: First of all, thanks! I try with N'sometext' in the filter on VIEW query and work as you say and it's fantastic! As I say in the question, however, i can't use directly a function beacause i have to use a ORM framework (hibernate) programmatically side which can map directly only Tables and Views from database... So, how can i put this useful information (i mean unicode string) inside a view? I have to convert all string column inside the view on remote server (vistaangraficagrp is a view on remote server).
• Roi Gavish: @ced This is getting awkward, but what if you create the function and then create a local view with SELECT * FROM fn_anagrafiche2()? Does a select from that view perform better?
• Roi Gavish: And one may want to read this as well.
• ced: if i create a local view with the select * from fn_anagrafiche2() (which contains the union) the performance are the same as the case where the union is directly into the view... so no better performance in this case unfortunately... =(
• Roi Gavish: @ced Forget about the function then, just add AND 1 = 1 to your filter. Can your app do that?

Answer 2

There is a fundamental restriction when it comes to linked servers and statistics: you can only see them if you have one of the following permissions on the remote location:

• a member of the sysadmin server role
• a member of the db_owner or db_ddladmin database role
• object owner

Usually you are just data reader, who can see no statistics (not even histograms). Execution plans then tend to suffer.

This has been fixed in SQL Server 2012 SP1, but it is of no comfort to legacy code running on older versions.

The trick I do is as follows:

1. Grant the linked_server account permissions as db_ddladmin on the remote server (so it can see stats).

2. Deny the account everything db_ddladmin actually grants:

   CREATE TABLE

   CREATE VIEW

   CREATE PROCEDURE

   CREATE FUNCTION

   CREATE RULE

etc.

It works like a charm without compromising security!

• Roi Gavish: The complete list of permissions granted to the db_ddladmin role is listed HERE
• ced: I don't understand very well. Consider only one remote server and only one linked server. On remote server i need an account with db_ddladmin user role enabled mapped to database? In the database with linked server i need to specify the user created on remote server in the permission tab of the linked server?
• John Alan: Yes - the account used to read on the remote server needs to have ddl_admin on the remote server as well. Otherwise it will not see the statistics (dbcc show_statistics runs behind the scenes, you can see it on profiler)
• ced: question updated with some test i've done, withous any results...
• Roi Gavish: @JohnAlan If he can't see statistics, why would there be a performance difference between the two cases(with and without a view)?