DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to handle over five thousands connections?

 

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)

*/


Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE