While SQL Server
2005 can handle thousands of connections, it is not a good idea
to use two-tier architecture where the client directly connects
to the database server. Dead connections will cause timeout and
deadlock problems. You should use 3-tier architecture by placing
an application processor (IIS, Websphere, ColdFusion, ...) between
the client and the database server. For example, an IIS Webserver
needs only a few connections to serve hundreds of web clients.
If there are 10 webservers, the connections you need is 30 or
less. sp_monitor can be used to see statistics on the number of connections. In the following example, 44 seconds past between runs of sp_monitor and 22 connections logged during this period. 350K connection since the server was last started.
EXEC sp_monitor
/*
last_run current_run seconds
2010-03-14 05:59:25.530 2010-03-14 06:00:09.300 44
cpu_busy io_busy idle
759(0)-0% 230(0)-0% 2182777(84)-190%
packets_received packets_sent packet_errors
581622(56) 592672(56) 0(0)
total_read total_write total_errors connections
10155(0) 161565(14) 0(0) 350508(22)
*/
|