Sql-server – SQL Server: efficient query with (double) linked servers

linked-serversql serversql-server-2008-r2view

I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version).
I have no control on server C, and I cannot contact its DBA. I am not even sure that server C is Microsoft SQL Server, it might be Oracle.

On server B, I can contact the DBA and ask for a view on server C, example:

Create view [dbo].[View_Stuff] as 
select * from 
openquery (SERVER_C, 'SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x')

I would like to do things like:

SELECT * FROM View_Stuff WHERE mykey=27

This view gets a lot of data from server C, because the openquery gets all the content of the table, which is not acceptable. On the other hand, I know that this would extract only one row, and would be very efficient on server C:

SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27

I considered using indexed views, but they cannot work with linked servers.
Mirroring the data is possible, but I really want real time information.

What can I ask to define on server B to be able to get data efficiently and in real time from a query on server A?

Best Answer

Since you know exactly the T-SQL you'd like to run on server 'B', why not just execute that query remotely, at server 'B', like this:

DECLARE @cmd nvarchar(max);
SET @cmd = N'SELECT t.c1
    , t.c2
    , t.c3
FROM OPENQUERY (SERVER_C, N''SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27;'') t;
';
EXEC [SERVER_B].tempdb.sys.sp_executesql @cmd;

That will cause server 'B' to query server 'C' with all the parameters you want, and should be about as quick as you can get.

Related Solutions

Sql-server – Get statistics of open connection to linked servers

SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.

I would start by running my usual "find any connection that is waiting" query:

SELECT 
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command 
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' 
    END AS transaction_isolation_level 
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2, 
    (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2 
        ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text   
,ph.query_plan
FROM sys.dm_exec_connections ec 
  LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
  LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
  OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID 
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where  blocking_session_id != 0)
)
ORDER BY es.session_id

I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.

Sql-server – Connecting Linked Server

Dynamic SQL:

declare @ServIP varchar(50) = 'IP of server'
declare @dsql varchar(1024)
set @dsql = 'select * from ' + quotename(@ServIP) + '.DbName.dbo.TableName'
exec (@dsql)

Related Question