Performance of using localhost vs. IP

Question

Performance of using localhost vs. IP

gwgeller

Right there with Babe

Points: 762
More actions
August 3, 2009 at 3:07 pm

#135045

We have several local DTSs that used to be run from a remote computer. We are now going to run those DTSs locally. Because the local DTSs used to be run remotely we used an IP for all the connections. Now that we running the DTSs locally I'm wondering if there is any advantage to changing the IP in the connections to localhost. I don't know if localhost just resolves to an ip anyway.
thanks
GG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I can't remember for sure but I believe you can reference either (local) or . for the local server.. I used to do it this way so I didn't have to care what server it was running on..

CEWII

gwgeller Right there with Babe Points: 762 More actions · Answer 2

Thanks for the reply, but which server isn't my concern, it is if using localhost vs. the IP of the local server has any performance advantages. For instance in Mysql you can specify a socket which is faster than using an IP if the server is local.

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 3

At the simplistic level shared memory will give the fastest performance when everything is running on the same server. You can set this up by adding an alias for the server in the client configuration, then using that alias name in your packages.

Using shared memory means that everything is passed directly to the database engine rather than going through an intermediary stack. The potential downside is that it will use SQL Server's memory, so if you've got existing memory pressure that will be exacerbated, or if you're close to the memory pressure "ceiling" you may cross it.

I'd need to check, but I believe localhost by default uses named pipes. Again that will be quicker than TCP/IP when everything is running on the same server, but it won't be as quick as using shared memory. The upside is that it runs outside SQL Server's memory, and if you have to it can run between servers (although then it's slower than TCP/IP).

TCP/IP will be slower than the others when everything is running on the same server because the traffic has to pass up and back through more "layers". However it is significantly quicker when going between servers, and it's going to be faster than what you were getting before cross-server because there's no network transfer involved.

The big question is regarding the actual performance difference between the alternatives. Any difference is going to be incrememntal for each "packet" passed back and forth, so it probably won't matter if you're dealing with small data transfers. Depending on your hardware there may not be a significant difference until you start bouncing around multiple GB of data (and what is "significant" depends entirely on your specific cicumstances).

gwgeller Right there with Babe Points: 762 More actions · Answer 4

SSC thanks for the reply. Using the info you provided I was able to find that you can specify the protocol for either named pipes or tcp. I did read that if you use local it will use named pipes and localhost will use tcp. I also read you can use np: (local) or tcp: (local). The server is transferring a lot of data, some from others servers and some locally. The reason I bring this all up is lately a DTS has been erroring out because of "general network error" which I'm guessing means it is timing out because of so much traffic. The DTS only uses local data so why even use the network? So before posting here I changed the server in the connection to localhost. I'll probably change it to np: (local) once the DTS has finished. Thanks again for the reply.