Incorrect row estimates when using views over linked servers

Incorrect row estimates when using views over linked servers by Paul McLoughlin

Resolved

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	773286
Opened:	12/3/2012 7:24:57 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When querying a view over a linked server, the row estimates are incorrect and set to be 10,000 rows. The credentials used for the linked server make no difference. This can lead to inefficient execution plans.

This is using SQL Server 2012, SP1.

A reproduction script is included in the details below.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows Server 2008 R2 Standard (SP1)

English

Create a table, dbo.MyTable and populate with some rows. Create a view, dbo.MyView, onto that table which simply selects all the rows from the table.

Over a linked server, query both select * from dbo.MyTable and select * from dbo.MyView. Look at the row estimates for each query.

Notice that the select from the table will give correct row estimates, and the select from the view will give estimate of 10,000 rows.

The credentials used for the linked server make no difference here. In my test I ensure that the credentials have sysadmin rights on the remote instance.

This test is with SQL Server 2012, SP1.

A script to reproduce this is given below:

-- Create a table on the target side, and populate with a large number of rows
create table dbo.MyTable
(
    id int identity(1,1) not null primary key,
    val varchar(200) not null
);

with l0 as (select 1 as a union all select 1 as a),
    l1 as (select 1 as a from l0 as a cross join l0 as b),
    l2 as (select 1 as a from l1 as a cross join l1 as b),
    l3 as (select 1 as a from l2 as a cross join l2 as b),
    l4 as (select 1 as a from l3 as a cross join l3 as b),
    myCte as (select ROW_NUMBER() over (order by (select null)) as rn from l4)

insert into dbo.MyTable(val)
select 'value ' + cast(m.rn as varchar(200)) as r
from myCte as m;

go

-- Create a view on the target side that simply does a select from the table
create view dbo.MyView with schemabinding
as
    select id, val
    from dbo.MyTable;
go

-- On the source side, run the following queries and look at the row estimates:
-- The view will give estimates (incorrectly) of 10,000 rows
-- The table will give estimates (correctly) of 65,536 rows
-- The credentials used on the linked server are irrelevant
select * from [linkedServerName].[databaseName].[dbo].[myTable];
select * from [linkedServerName].[databaseName].[dbo].[myView];

row estimates of 10,000 rows when using the view

row estimates to be the same when querying the view and the table

X64

0 attachments

I, too, would appreciate an explanation.

We are FORCED into querying a View into our Customer's data over a Linked Server. We have absolutely no say in it, so to be hamstrung, yet again, by the seemingly more deficient as the days go by "optimizer", begs the question, "Why?"

Having lowered the permissions threshold in SQL2012 for seeing statistics over a Linked Server, one would have thought that now "seeing clearly" the optimizer would have no issues at all computing accurate estimates, or did I miss something...?