Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rkbh
Starting Member
2 Posts |
 Posted - 2005-05-17 : 09:06:24
|
| I have a generel question regarding the amount of processes on the sql-server.I'm a developer on a IVR-system. The ivr-system is designed to establish and hold a connection to the sql-server as long as the ivr-system is up and running. But that is one for each ivr-line(120) on the ivr-system. Actually sp_who tells me that is holds 2 connections(240) for each line. I have 3 ivr-servers which is 720 connections. Is that a problem for the sql-server(2000)?. I'm thinking about making a dll extension to the ivr-system instead. By using the dll i can remove the 720 permanent-connections, because I would open and close the connection i the dll. So my question is : would i waste my time by making this dll.The sql-server has a performance problem when all lines is connected with a caller. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-05-17 : 12:16:33
|
| From BOL"user connections OptionUse the user connections option to specify the maximum number of simultaneous user connections allowed on Microsoft® SQL Server™. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application(s) and hardware. SQL Server allows a maximum of 32,767 user connections.Because user connections is dynamic (self-configuring option), SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you should not need to change the value for this option.You can use SQL Query Analyzer and the following Transact-SQL statement to determine the maximum number of user connections that your system allows:SELECT @@MAX_CONNECTIONSuser connections helps avoid overloading the server with too many concurrent connections. You can estimate the number of connections based on system and user requirements. For example, on a system with many users, each user would not usually require a unique connection. Connections can be shared among users. Users who are running OLE DB applications need a connection for each open connection object, users who are running ODBC applications need a connection for each active connection handle in the application, and users who are running DB-Library applications need one connection for each process started that calls the DB-Library dbopen function.Important If you must use this option, do not set the value too high because each connection takes approximately 40 kilobytes (KB) of overhead regardless of whether the connection is being used. If you exceed the maximum number of user connections, you receive an error message and are not able to connect until another connection becomes available.user connections is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change user connections only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.*need more coffee* |
 |
|
|
rkbh
Starting Member
2 Posts |
Posted - 2005-05-17 : 17:12:50
|
| Thanks, so I should not worry about 720 concurrent connections? Even if they are hold for weeks or months? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-05-17 : 20:23:05
|
| What language is your IVR written in? Generally if using ADO or ADO.net you want to open a connection, do work, close connection.This will allow you to use connection pooling, so you don't have lots and lots of these expensive connections open.For example, I've got a telephony app that's running well over 1500 lines, all reporting their status to a SQL database constantly. I never get above 120 - 130 connections, and that's with other applications using the SQL server too.Connection pooling is your friend. It's fast, it's effecient, and a reccomended practice by Microsoft.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Number of processes.