Effect of the remote end of the remote data fetch index modulation(Link Server)

相关数据已经脱敏处理

直接测试

--远程调取数据时远程端索引的影响
set statistics time on;
set statistics io on;

--REMOTE
sp_helpindex DataSync
--The object 'DataSync' does not have any indexes, or you do not have permissions.

select * FROM REMOTELINKSERVER.TESTDB.DBO.DataSync where INNERCODE between 1000298 and 1000300
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 220 毫秒。

(16181 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 78 毫秒，占用时间 = 3908 毫秒。
   */
------------REMOTE
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(16181 行受影响)
Table 'DataSync'. Scan count 5, logical reads 231209, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 3282 ms,  elapsed time = 1454 ms.*/


--REMOTE
create index I_DataSync on DataSync(INNERCODE)

select * FROM REMOTELINKSERVER.TESTDB.DBO.DataSync where INNERCODE between 1000298 and 1000300
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 191 毫秒。

(16181 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 32 毫秒，占用时间 = 1087 毫秒。
   */
------------REMOTE
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(16181 行受影响)
Table 'DataSync'. Scan count 1, logical reads 16221, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 284 ms.
*/

statistics看不到远程调用的具体信息，但是多次测试远端建立索引后效率的确提高了。

换个玩法

执行一个复杂查询，查看执行计划
Remote Query(SOURCE:(DEVDB), QUERY:(SELECT "Tbl1001"."TRADE_DATE" "Col1038","Tbl1001"."PRE_CLOSE_PRICE" "Col1039" FROM "NJTESTDB"."DBO"."DataSync" "Tbl1001" WHERE "Tbl1001"."TRADE_DATE"<convert(date, '2014-11-18') AND "Tbl1001"."innercode"=?)) 
--REMOTE  捕获SQL
select   request_session_id  spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from   sys.dm_tran_locks with(nolock) where resource_type='OBJECT'
and OBJECT_NAME(resource_associated_entity_id)='DataSync'
--65
select spid,text from sys.sysprocesses a with(nolock) 
cross apply sys.dm_exec_sql_text(sql_handle)
where spid in(65)
--TEXT
(@P1 int)SELECT "Tbl1004"."TRADE_DATE" "Col1026","Tbl1004"."PRE_CLOSE_PRICE" "Col1027" FROM "NJTESTDB"."DBO"."DataSync" "Tbl1004" WHERE "Tbl1004"."TRADE_DATE"<convert(date, '2014-11-18') AND "Tbl1004"."innercode"=@P1

结论：远程抓取SQL的语句是交给远程服务器来跑的（附加上参数），所以在远程建立索引会对LinkServer调取数据库数据的效率产生影响的。