The SQL server 2000 process info monitor displays about 100 processes that use a mix of Named Pipes/TCP-IP and (integrated authenticated) users. I was wondering if:

  • disabling Named pipes - or - TCP-IP (so that all applications are forced to use a single protocol) would affect SQL server performance in anyway

  • using a single SQL server login instead of multiple windows login would affect SQL server performance in anyway (there is no real need for separate logins)

What I am saying is that if I consolidate all connections into few possible combinations as possible, do I get some performance benefit?


Yes, IMHO you should disable named pipes and use only TCP/IP for better performance. You can force this in the connection strings for your apps and change them one at a time, rather than just shutting off named pipes and then getting a bunch of complaints:

Data Source = tcp:IP-or-host-name[\instance-name]

Don't shut off named pipes until all your apps are using TCP/IP. You can check what your apps are using via sys.dm_exec_connections.net_transport - this will have values such as TCP or Shared Memory. You can check the program too via a query like this:

SELECT c.net_transport, s.program_name, c.client_net_address
  FROM sys.dm_exec_sessions AS s
  INNER JOIN sys.dm_exec_connections AS c
  ON s.session_id = c.session_id;

However, for your second question, using one login or multiple logins won't affect performance much unless you're at the upper end and exceeding your pooled connections limit. In most cases consolidating into a single login will just make it much harder to later audit etc.

  • What is pooled connections limit? – Salman A Jan 7 '13 at 19:30
  • If you are using connection pooling then your connections get re-shared (as long as all the connection string attributes are the same - including username). If all your usernames are different, then only the same user can re-use that connection. You can monitor usage with performance counters, some of which you'll have to manually enable. (Actual limits will vary depending on operating system, configuration of other web sites, available resources, etc.) – Aaron Bertrand Jan 7 '13 at 20:04
  • @AaronBertrand Do you know where the linked article has landed? I couldn't find and update the link in your answer. – John K. N. May 21 '19 at 13:59
  • @hot2use updated – Aaron Bertrand May 21 '19 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.