Los servidores vinculados ó linked servers en SQL Server son una característica bastante utilizada en la mayoría de entornos actualmente.
¿Qué es un servidor vinculado?
Un servidor vinculado es una configuración que permite ejecución de comandos contra orígenes OLEDB remotos. En este artículo nos vamos a centrar en servidores vinculados hacia orígenes de datos SQL Server.
¿Qué beneficios tiene utilizar un servidor vinculado?
El utilizar un servidor vinculado nos da la ventaja de:
- Acceso a servidores remotos desde SQL Server sin necesidad de abrir explícitamente conexión en nuestras aplicaciones
- Lanzar consultas distribuidas, actualizaciones, comandos y transacciones entre uno o varios de dichos servidores vinculados y el servidor local propiamente dicho
- La habilidad de acceder a varios orígenes de datos simultáneamente
Mitos y leyendas de los servidores vinculados
Existe un mito que le leido por ahí (no pondré fuentes para no ser el dedo acusador) que dice que usar servidores vinculados da mal rendimiento siempre porque blah blah blah….
La realidad es que, aunque no van mal encaminados, todo tiene un por qué y aquí es donde lo vamos a analizar, demostrar y ver como evitarlo.
El siguiente texto forma parte de la documentación oficial hablando de linked servers :
“To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server.Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server”
Y aquí por tanto nos está diciendo que debemos cumplir una premisa relativa a la seguridad del servidor vinculado. En el siguiente punto veremos qué implicaciones tiene el enunciado anterior.
¿Qué debemos tener en cuenta al utilizar un servidor vinculado?
Lo que debemos tener muy presente a la hora de utilizar un servidor vinculado es que si no cumplimos el requisito anterior, los valores de las estadísticas que se utilizan para generar el plan de ejecución que SQL Server genera son incorrectos y terminan produciendo un grave problema de rendimiento “desapercibido”.
La razón es ni mas ni menos, que se necesitan permisos sysadmin, db_owner o db_ddadmin del login remoto que se usa para logearse en el servidor remoto. De lo contrario, no se tienen permisos suficientes para poder acceder a las estadísticas de los objetos remotos y por tanto el plan de ejecución será generado con información incorrecta y potencialmente se generará ineficientemente.
El siguiente ejemplo ilustra bastante bien el problema y su solución
- —
- :connect 192.168.9.9sql2008r2 -Usa -Ppassword
- USE [master]
- GO
- CREATE LOGIN [userLimited] WITH PASSWORD=N’password’,
- DEFAULT_DATABASE=[AdventureWorks],
- CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
- GO
- USE [AdventureWorks]
- GO
- CREATE USER [userLimited] FOR LOGIN [userLimited]
- GO
- USE [AdventureWorks]
- GO
- EXEC sp_addrolemember N’db_datareader’, N’userLimited’
- GO
- :connect (local)sql2008r2
- USE [master]
- GO
- EXEC master.dbo.sp_addlinkedserver @server = N’192.168.9.9SQL2008R2′, @srvproduct=N’SQL Server’
- GO
- USE [master]
- GO
- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’192.168.9.9SQL2008R2′,
- @locallogin = NULL , @useself = N’False’, @rmtuser = N’userLimited’, @rmtpassword = N’password’
- GO
Ahora lanzamos la consulta y vemos su plan de ejecución
- :connect (local)sql2008r2
- — 2362
- SELECT l.*
- FROM AdventureWorks.Sales.SalesOrderHeader l
- JOIN [192.168.9.9SQL2008R2].AdventureWorks.Sales.SalesOrderHeader r ON l.SalesOrderID = r.SalesOrderID
- WHERE r.CustomerID = 666
- go
Su plan de ejecución
y más concretamente, si vemos los detalles del operador “Remote Query”
Es importante darse cuenta de la gran disparidad entre “Actual Number of Rows” y “Estimated Number of Rows”.
Esa disparidad viene dada porque el plan de ejecución remoto no ha podido acceder al histograma de distribución de datos y ha devuelto el nº de filas estimado.
No es mi intención ahora explicar como leer y acceder las estadísticas de SQL Server (eso será fruto de un futuro post), pero creo conveniente que se vea que si se añade por ejemplo el rol db_owner al login remoto, el plan de ejecución cambia (para mejor) puesto que se puede acceder al histograma de distribución:
- :connect 192.168.9.9sql2008r2 -Usa -Ppassword
- USE [AdventureWorks]
- GO
- EXEC sp_addrolemember N’db_owner’, N’userLimited’
- GO
Volviendo a lanzar la misma query de antes, ahora obtenemos el siguiente plan de ejecución:
- :connect (local)sql2008r2
- DBCC DROPCLEANBUFFERS
- go
- SELECT l.*
- FROM AdventureWorks.Sales.SalesOrderHeader l
- JOIN [192.168.9.9SQL2008R2].AdventureWorks.Sales.SalesOrderHeader r
- ON l.SalesOrderID = r.SalesOrderID
- WHERE r.CustomerID = 666
De entrada vemos que el operador principal utilizado ahora es un Nested Loops…y lo es simplemente porque sabe que el nº de filas que van a venir de la query remota es extremadamente bajo:
Como vemos, ahora ya el nº de filas estimadas es bastante acercado a la realidad, simple y llanamente porque se ha podido acceder al histograma:
- :connect 192.168.9.9sql2008r2 -Usa -Ppassword
- DBCC SHOW_STATISTICS(N'”AdventureWorks”.”Sales”.”SalesOrderHeader”‘, “IX_SalesOrderHeader_CustomerID”) WITH HISTOGRAM_STEPS
- go
[box type=”info”] NOTA: En futuros posts, se darán nociones para comprender la lectura de las estadísticas en SQL Server.[/box]
Conclusión
Si estas utilizando servidores vinculados, ten muy presente los requerimientos de seguridad mencionados en este post, si no quieres llevarte sorpresas desagradables en el rendimiento de tu sistema.
9 comments
Hola:
Tengo una duda después de leer su post, y es sobre la ejecución remota de un scan (REMOTE SCAN) que he visto varias veces.
¿Se hace un scan en el server remoto y se envían al local solo las filas que cumplan una posible condición WHERE? ¿O se envían todas las filas (de remoto a local) y es en el local donde se aplica el WHERE?
Gracias,
Ignacio
Hola Ignacio, efectivamente lo ideal es que se haga el scan en el server remote y se envien las filas que cumplen la condición. El problema como comento es que si no se tienen los permisos necesarios, al no poder ir a las estadísticas para estimar qué filas devolver…el scan se lee todo y lo envia todo, teniendose que filtrar en local, no en remote con el consiguiente problema de Rendimiento que eso ocasiona (bloqueos, lentitud, consumo de RAM, ancho de banda de red,…)
hola tengo una consulta más bien una pregunta
tengo dos servidores linkedados server1 y server2
voy a hacer una select al server 1 y el resultado lo quiero guardar en una tabla con WITH y quiero hacer un update en el server dos la consulta esta corriendo en el server dos pero son 26mil registros y se tarda mas de una 1
y creando la tabla local sin el WITH se tarda 4 segundos
es conveniente el uso de WITH en servidores linkeados?
La cláusula WITH en principio no es el problema. El problema habría que ver si es porque tienes latencias de red, microcortes, falta de ram, saturación de discos,…o simplemente está mal indexado. Si una opción tarda 1h y la otra 4s, algo le pasa al servider2 🙂
Gracias
Hola.
Y como el doy seguridad a ese linked server ?
Me explico, el usuario userLimited solo quiero que tenga permisos de lectura a una tabla especifica, al darle permisos OWNER la seguridad como la manejo desde el servidor que accede al Linked Server ?
Saludos.
la seguridad se la das luego al login que uses para crear ese linked server. Si el login para el linked server se llama “cristian”, podrias darsela a ese login en el servidor destino, como le darias a un usuario cualquiera.
un saludo.
Hola,
Tengo una duda, ejecuto una consulta con un linkserver,
Por ejemplo
SELECT l.*
FROM AdventureWorks.Sales.SalesOrderHeader l
JOIN [192.168.9.9SQL2008R2].AdventureWorks.Sales.SalesOrderHeader r
ON l.SalesOrderID = r.SalesOrderID
WHERE r.CustomerID = 666
Y esa misma consulta la ejecuto, pero en el servidor vinculado directamente, que causa que en el servidor vinculado me indique que hace falta un indice y ejecutándola con el linkserver no.
Es posible que tengas falta de permisos. ¿validaste que tenias los permisos necesario como comento en el post?
un saludo