how to speed up sp_testlinkedserver
-
sp_testlinkedserver this is taking 30 seconds on each server it there a way to speed it up
-
It's not sp_testlinkedserver that's slow. It's the connection that is slow. That could be anything between the server where the linked server has been created to the target of the linked server, which includes every cable, cable connection, switch, router, NIC, and all the settings for those.
Some of the things that I've found that can really slow things down are poor cable connections, cables that are too long or have too tight a bend in them, and having hardware that is either setup as half-duplex or auto-negotiate or both. It could also be too narrow a pipe (bandwidth) or a crap long haul connection if it's across a VPN.
It can also be due to other settings including packet size, etc, ad infinitum.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
it is actually taking 40 seconds and the servers are not even on
i did change the remote login from 30 seconds to 15 seconds
and now the sp_testlinkserver will fail 20 seconds
i tried changing remote login to 5 still 20 seconds for the job to fail
-
dschlobohm (12/9/2015)
it is actually taking 40 seconds and the servers are not even oni did change the remote login from 30 seconds to 15 seconds
and now the sp_testlinkserver will fail 20 seconds
i tried changing remote login to 5 still 20 seconds for the job to fail
That is because it takes it a little bit when it can't find the remote server before the attempt times out. I have to admit I nearly spit out my drink when you asked if you could speed up the process and then stated the remote server is turned off. You could greatly increase the performance if the remote server was fired up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
that is why am doing the sp_testinkedservers
to find out what servers on or off
before i query them
in the wonderful vm world that i live in servers turn on or off without notice and the management jobs all fail
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply