Linked Server performance may depend on permission
According to Linchi Shea post you should check the
permissions you are using to access a Linked Server.
As you know SQL Server optimizer tries to choose the best
way to execute a query according to distribution statistics
of the tables involved in the query.
The dilema here is that the user you use to access the
Linked Server may have not permissions to get the statictics
on a table in Linked Server. In this case the Optimizer may
choose table scan instead of seek,for example. And this may
hurt the performance of the distributed query.
It turns out that the user needs the same permission as is
required to execute DBCC SHOW_STATISTICS command.
Meaning the user should be one of the following:
Table Owner or SysAdmin or DB_Owner or DB_ddladmin.
Information source:
http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx
permissions you are using to access a Linked Server.
As you know SQL Server optimizer tries to choose the best
way to execute a query according to distribution statistics
of the tables involved in the query.
The dilema here is that the user you use to access the
Linked Server may have not permissions to get the statictics
on a table in Linked Server. In this case the Optimizer may
choose table scan instead of seek,for example. And this may
hurt the performance of the distributed query.
It turns out that the user needs the same permission as is
required to execute DBCC SHOW_STATISTICS command.
Meaning the user should be one of the following:
Table Owner or SysAdmin or DB_Owner or DB_ddladmin.
Information source:
http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx