Solución:
Puede activar el indicador de seguimiento 7300 que podría darle un mensaje de error más detallado
¿Cuántas filas devuelve una consulta representativa? ¿Qué tan rápida / confiable es la conexión de red entre los dos servidores?
Es posible que un conjunto de datos grande esté tardando demasiado en transferirse (además del tiempo real de consulta). Podría aumentar el valor del tiempo de espera.
Puede intentar reconfigurar la configuración del tiempo de espera de la siguiente manera:
Establezca el tiempo de espera de inicio de sesión remoto en 300 segundos:
sp_configure 'remote login timeout', 300
go
reconfigure with override
go
Establezca el tiempo de espera de la consulta remota en 0 (espera infinita):
sp_configure 'remote query timeout', 0
go
reconfigure with override
go
Actualizar: SQL Server 2012 SP1 en adelante: usuarios con SELECT permiso podrá acceder DBCC SHOW_STATISTICS lo que mejorará el rendimiento de solo lectura en servidores vinculados. Ref: https://msdn.microsoft.com/en-us/library/ms174384(v=sql.110).aspx
Actualizar: Tiene razón al decir que no es el tamaño de los datos o la velocidad de conexión. Sonó una campana en mi borrosa memoria y recordé dónde lo había visto: ¿Lento en la aplicación, rápido en SSMS? (¿Un problema con los servidores vinculados). No es un rastreo de parámetros, son las estadísticas mismas las que faltan (debido a los permisos), lo que hace que se utilice un plan de consulta incorrecto:
Puede ver que las estimaciones son diferentes. Cuando ejecuté como administrador de sistemas, la estimación era 1 fila, que es un número correcto, ya que no hay pedidos en Northwind donde el ID de pedido supere los 20000. Pero cuando ejecuté como usuario simple, la estimación fue de 249 filas. Reconocemos este número en particular como el 30% de 830 pedidos, o la estimación de una operación de desigualdad cuando el optimizador no tiene información. Anteriormente, esto se debía a un valor de variable desconocido, pero en este caso no hay ninguna variable que pueda ser desconocida. No, son las estadísticas mismas las que faltan.
Siempre que una consulta acceda solo a las tablas del servidor local, el optimizador siempre puede acceder a las estadísticas de todas las tablas de la consulta; no hay comprobaciones de permisos adicionales. Pero esto es diferente con las tablas en un servidor vinculado. Cuando SQL Server accede a un servidor vinculado, no existe un protocolo secreto que solo se utiliza para la comunicación entre servidores. No, en su lugar, SQL Server utiliza la interfaz OLE DB estándar para servidores vinculados, ya sean otras instancias de SQL Server, Oracle, archivos de texto o su fuente de datos casera, y se conecta como cualquier otro usuario. Exactamente cómo se recuperan las estadísticas depende de la fuente de datos y del proveedor OLE DB en cuestión. En este caso, el proveedor es SQL Server Native Client, que recupera las estadísticas en dos pasos. (Puede ver esto ejecutando Profiler en el servidor remoto). Primero, el proveedor ejecuta el procedimiento sp_table_statistics2_rowset que devuelve información sobre qué estadísticas de columna hay, así como su cardinalidad y su información de densidad. En el segundo paso, el proveedor ejecuta DBCC SHOW_STATISTICS, un comando que devuelve las estadísticas de distribución completas. (Veremos más de cerca este comando más adelante en este artículo). Aquí está el truco: para ejecutar DBCC SHOW_STATISTICS, debe ser miembro del rol de servidor sysadmin o cualquiera de los roles de base de datos db_owner o db_ddladmin.
Y es por eso que obtuve resultados diferentes. Al ejecutar como administrador de sistemas, obtuve las estadísticas de distribución completas que indicaron que no hay filas con ID de pedido> 20000, y la estimación fue una fila. (Recuerde que el optimizador nunca asume cero filas a partir de las estadísticas). Pero cuando se ejecuta como usuario simple, DBCC SHOW_STATISTICS falló con un error de permiso. Este error no se propagó, sino que el optimizador aceptó que no había estadísticas y utilizó supuestos predeterminados. Como obtuvo información de cardinalidad, supo que la tabla remota tiene 830 filas, de ahí la estimación de 249 filas.
Siempre que encuentre un problema de rendimiento en el que una consulta que incluye acceso a un servidor vinculado sea lenta en la aplicación, pero se ejecute rápido cuando lo pruebe desde SSMS, siempre debe investigar si la causa podría ser la falta de permisos en la base de datos remota. (Tenga en cuenta que el acceso al servidor vinculado puede no ser evidente en la consulta, pero podría estar oculto en una vista). Si determina que los permisos en la base de datos remota es el problema, ¿qué acciones podría tomar?
Puede agregar los usuarios al rol db_ddladmin, pero dado que esto les da derecho a agregar y eliminar tablas, esto no es recomendable.
De forma predeterminada, cuando un usuario se conecta a un servidor remoto, se conecta como él mismo, pero puede configurar una asignación de inicio de sesión con sp_addlinkedsrvlogin, de modo que los usuarios se asignen a una cuenta proxy que tenga membresía en db_ddladmin. Tenga en cuenta que esta cuenta de proxy debe ser un inicio de sesión SQL, por lo que esta no es una opción si el servidor remoto no tiene habilitada la autenticación SQL. Esta solución también es algo dudosa desde una perspectiva de seguridad, aunque es mejor la sugerencia anterior.
En algunos casos, puede reescribir la consulta con OPENQUERY para forzar la evaluación en el servidor remoto. Esto puede resultar especialmente útil si la consulta incluye varias tablas remotas. (Pero también puede ser contraproducente, porque el optimizador ahora obtiene incluso menos información estadística del servidor remoto).
Por supuesto, puede utilizar la batería completa de sugerencias y guías de planes para obtener el plan que desea.
Finalmente, debe preguntarse si se necesita ese acceso al servidor vinculado. ¿Quizás las bases de datos podrían estar en el mismo servidor? ¿Se pueden replicar los datos? ¿Alguna otra solución?
¿Qué sucede cuando intenta esto (es decir, indique explícitamente qué se debe ejecutar en el servidor remoto) ?:
select [fields]
into dbo.current_accounts
from OPENQUERY(linkedserver, 'SELECT [fields] FROM database.dbo.accounts where date=""1/20/2012''');
Sospecho que en su caso anterior, SQL Server simplemente está extrayendo toda la tabla del servidor remoto y luego ejecutando la consulta localmente (he visto que esto sucedió muchas veces en el pasado). Prefiero ser explícito (ya sea usando OPENQUERY o creando un SP en el servidor remoto) para que no haya posibilidad de confusión.