Sql-server – How does disabling a network protocol affect SQL server performance

Networkperformancesql serversql-server-2000

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?

Best Answer

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.

Related Question