Experts,

I was able to connect a database(SQL Server) directly and also through Linked server(same database).

When I run a query, on direct data base and linked server , The amount of time taken for the query running is always long( more than double)for linked servers. How can I increase the linked server performance?

Thanks & regards

Praveen

This is totally accepted behavior how can you compare time when running database on Server itself and linked server.

Linked server would fetch data from network and ofcourse it would take time during the request going and coming back while on server this is avoided

Any use of the database is done using connection. For example the SSMS connect the database using connection string as any other application. Now lets' examine the situation

Option 1: You connect to the database using SSMS

Option 2: You connect to the database using SSMS, and you work to a table on linked server, which mean SQL Server connected the database in the linked server. 

what will be faster?!? As Shanky wrote network has big influence in remote connection and this is Normal behavior that using Linked server will be slower even if the Linked server connect to the same server. 

Moreover! You need to check all the connection parameters if you want to compare perfomance. It is a very common issue in the C# forum to get questions like: "It is fast in SSMS, but slow in the my application" and usually the only reasone is that SSMS use different parameters then the default .NET connection dose.

>> "How can I increase the linked server performance?"

We need more information, lot more :-)
First we need to see the linked server configuration (connection string), next we need to see the queries, and we need to understand the used (maybe we have another solution which is better then linked server like OPENROWSET in some cases), and not less important we need to understand the architecture (is the linked server connect to remote server or local

Experts,

I was able to connect a database(SQL Server) directly and also through Linked server(same database).

When I run a query, on direct data base and linked server , The amount of time taken for the query running is always long( more than double)for linked servers. How can I increase the linked server performance?

Thanks & regards

Praveen

Hi Praveen,

I would make sure that the linked server connection has "DDL_ADMIN" permissions. Since the connection may not have permissions to run dbcc showstats it may be getting a sub-optimal plan.

Please review the following: http://blogs.msdn.com/b/psssql/archive/2009/09/02/distributed-queries-remote-login-permissions-and-execution-plans.aspx

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

How can I increase the linked server performance?

Please take a look at this article: How to Achieve High Performance with Linked Servers

Can you show the query? I have seen that using OPENQUERY command somehow speeds up  the linked query, but sure, it depends on how many rows you return and networking

select * 
from OPENQUERY(servername,'exec dbname.dbo.proc1')
where ....
go