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:

the execution plan using the view:

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 :)

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!