Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
What is pooled connections limit? – Salman A Jan 7 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 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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