IIS concurrent connection number and performance optimization

The simplest and easiest way to view the number of IIS connections is through site statistics, where the current number of online Web site statistics can be considered the current number of IIS connections. However, the current online number of "website statistics" is longer, typically 10 minutes or 15 minutes, plus statistical techniques and statistical mechanisms, resulting in more or less statistical errors.

If you want to know the exact number of IIS connections for the current Web site, the most efficient way to do this is through the System Monitor that Windows comes with. This is the approach that this article is going to introduce. New IIS Concurrent Connection number monitor

1. Run--> enter "Perfmon.msc"

2. Right-click in the System Monitor chart area and click "Add Counter"

Figure I

3. In the Add Counters window, the performance object selects the Web Service, "Select counters from list" To select Current Connection, "Select instances from List" To select the site you want to count, and then click the "Add" button

Figure II , set up

This way, you can see a curve in the System Monitor chart area (you can set the parameters such as color and width), which is the number of IIS connections to the site, as shown in the yellow curve.

What you need to know is that Windows System Monitor displays the number of instant IIS concurrent connections, not the number of visitors within 15 minutes of "site statistics", so you'll find that there are not too many IIS concurrent connections

Workaround:

The timeout period has expired. The timeout has expired, but the connection has not been fetched from the pool. This may occur because all pool connections are in use and the maximum pool size is reached.
Note: An unhandled exception occurred during the execution of the current WEB request. Check the stack trace for more information about the error and where the error occurred in the code.

Exception Details: System.InvalidOperationException: Timeout has expired. The timeout has expired, but the connection has not been fetched from the pool. This may occur because all pool connections are in use and the maximum pool size is reached.

This is an old problem. You just check two points:
First, see if all open connections are close.
Second, if the traffic is very large, plus max Pool size=512 This sentence, of course, this is to lose system performance at the expense.
This will certainly solve your problem later.

Solution One

I think the reason may be a concurrency operation. DataReader is an exclusive connection, which means that your program may have problems with the design. For example, the maximum connection is 100, assuming that 100 people use DataReader is reading the contents of the database, then when the 101th person read, the connection in the pool is gone, there will be the above error. DataReader is an exclusive connection, and each DataReader occupies one connection. Of course, this happens occasionally, so it can occur a long time, because only concurrent operations that exceed the maximum number of connection pools will occur. And you increase the number of concurrent can only temporarily alleviate the problem, if you increase to 200 concurrent connection, if there are 201 people to operate at the same time how to do. You said that you use the close () method of the Connection object, which is not possible because the close () method simply closes the connection, but the connection is not released or is occupied by this object, to release the Dispose () that must use connection. method to release the connection, otherwise the connection that this object occupies can only be released when garbage collection is in progress. There is definitely a "time-out" error in this situation.

Workaround:
1 Modify several key pages or access more frequent database access operations, use DataAdapter and dataset to get database data, do not use DataReader.
2 Use the data cache on the page accessing the database, if the page's data is not constantly updated (a few minutes update), the cache object can be used without access to the database to use the contents of the cached, you can greatly reduce the number of connections.
3 Modify the code, put the use of connection objects in the place after close () plus Dispose () call.
4 It is suggested that the database operation should be modified greatly, the database operation proxy class should be established, the System.IDisposable interface should be inherited, and the resources will be forced to be freed, so that the problem of insufficient number of connections can not occur.

Solution Two

Workaround (*): Web.config: In the database connection add Max Pool Size = 512;server=local;uid=;p wd=;d atabase=2004; Max Pool Size = 512; " > Once and for all.

Solution Three

It is estimated that the connection (Connection) object does not have close. It is not necessary to dispose, and DataReader should be closed after use, but do not close also no problem, just do not close the Connection object has been unable to use, as long as you finally closed the connection object will not be a problem.

The operation of the connection object after open is placed in a try block followed by a finally block: Conn. Close ();

How to query the number of sql2005 connections

The error of "Too Many database connection errors" occurs frequently, but it is not clear how many connections the current number is, and summarizes several methods roughly.
1. Through the system's "performance" to view:
Start-> Management Tools-> performance (or run inside MMC) and then pass

[Attach]52716[/attach]

Add counters to add common statistics for SQL then in the items listed below Select the user connection can always query the number of connections to the database.
This method, however, requires access to that computer, which is to be logged in through a Windows account to add this counter.
2. Through the system table to query:
[Code] SELECT * FROM
[Master]. [dbo]. [sysprocesses] WHERE [DBID]
In
(
SELECT
[DBID]
From
[Master]. [dbo]. [sysdatabases]
WHERE
Name= ' DatabaseName '
)
[/code]

DatabaseName is the database that needs to be viewed, and then the number of rows that are queried is the current number of connections. But there are some other states that can be used for reference.

From the above statement you can see that the system table gets some connection and activity information, mainly introduces the following two system tables:

(1) sprocesses

The sysprocesses table holds information about the processes running on the Microsoft®sql Server™. These processes can be either client processes or system processes. sysprocesses is stored only in the master database.

(2) Sysperfinfo

Includes an internal performance counter for Microsoft®sql Server™ notation, which can be displayed through Windows NT Performance Monitor.

It has been suggested that in order to get the current number of connections for SQL Server: Use SQL as follows:

SELECT COUNT (*) as connections from master. sysprocesses

Personally think this is wrong, look at. sysprocesses's login_time column can be seen.

Another aspect is that processes cannot be compared to connections, are they one-to-one relationships, that is, a process is a connection? A connection should have multiple processes, so the relationship between the connection and the process should be 1:n.

Because the processes listed in sysprocesses contain system processes and user processes, you can use the following SQL to get user connections:

SELECT Cntr_value as user_connections from master. Sysperfinfo as P
WHERE p.object_name = ' sqlserver:general Statistics ' and p.counter_name = ' User connections '

Personally still think wrong, because it is a counter, may be cumulative.

Another option would be to take advantage of the following SQL:

Select Connectnum=count (Distinct net_address)-1 from master. sysprocesses

The reason is that net_address is the visitor machine card value, this should always be the only one. But what it looks like is the number of connections in all the time.

I hope you can give your own solution. This problem has been solved and I believe it will be of great use.

3. Through the system process to query:
[url=http://search.microsoft.com/default.asp?so=reccnt&siteid=us%2fdev&p=1&nq=new&qu=sp_who &intlsearch=&boolean=phrase&ig=01&i=09&i=99]sp_who[/url] ' loginName '
LoginName is of course login SQL username, the general program inside will use a username to login SQL so through this user name can be viewed to the user name after the connection occupied.
If you don't write loginname, all the connections are returned.

sp_who provides information on Icrosoft®sql Server™ users and processes. You can filter the information returned so that only those processes that are not idle are returned.

List all active users: sp_who ' active '

List information for a particular user: sp_who ' sa '

4. System Variables

@ @CONNECTIONS Returns the number of connections or attempts to connect since Microsoft®sql Server™ was last started.

@ @MAX_CONNECTIONS Returns the maximum number of simultaneous user connections allowed on the Microsoft®sql Server™. The number returned does not have to be the currently configured numeric value

*************** Connection pool and number of connections **********

Connecting to a database server usually consists of several steps that take a long time. A physical channel (such as a socket or named pipe) must be established, the initial handshake must be made to the server, the connection string information must be parsed, the connection must be authenticated by the server, the check must be run to enlist in the current transaction, and so on.

In fact, most applications use only one or several different connection configurations. This means that many of the same connections will be turned on and off repeatedly during the execution of the application. To minimize the cost of open connections, Ado.net uses an optimization method called connection pooling.

Connection pooling reduces the number of times a new connection needs to be opened. The pool process retains ownership of the physical connection. Manage connections by keeping a set of active connections for each given connection configuration. As long as the user calls Open on the connection, the pool process checks to see if there is a connection available in the pool. If a pool connection is available, the connection is returned to the caller instead of opening a new connection. When the application calls close on the connection, the pool process returns the connection to the active connection pool set instead of actually shutting down the connection. Once the connection is returned to the pool, it can be reused in the next Open call.

A pool connection can be established only if the same connection is configured. Ado.net preserves multiple pools at the same time, each configured a pool. Connections are divided into pools by connection strings and by Windows identities (when using integrated security). Pool connections are also established based on whether the connection is enlisted in a transaction.

Pool connectivity can significantly improve the performance and scalability of your application. By default, connection pooling is enabled in Ado.net. Unless explicitly disabled, the pool process optimizes the connection when it is opened and closed in an application. You can also provide several connection string modifiers to control the behavior of the connection pool. For more information, see "Controlling connection pooling using connection string keywords" later in this topic.

Creation and allocation of pools
When the connection is first opened, a connection pool is created based on the exact matching algorithm, which associates the pool with the connection string in the connection. Each connection pool is associated with a different connection string. When you open a new connection, a new pool is created if the connection string does not exactly match the existing pool. Set up a pool connection by process, by application domain, by connection string, and by Windows identity when using integrated security. The connection string must also be fully matched, and the keywords provided in different order for the same connection are divided into separate pools.

Three new SqlConnection objects were created in the following C # example, but only two connection pools are required for management. Note that the first and second connection strings are different depending on the value assigned to the Initial Catalog.

Copy Code
using (SqlConnection connection = new SqlConnection (
"Integrated security=sspi;initial Catalog=northwind"))
{
Connection. Open ();
Pool A is created.
}

using (SqlConnection connection = new SqlConnection (
"Integrated security=sspi;initial Catalog=pubs"))
{
Connection. Open ();
Pool B is created because the connection strings differ.
}

using (SqlConnection connection = new SqlConnection (
"Integrated security=sspi;initial Catalog=northwind"))
{
Connection. Open ();
The connection string matches pool A.
}


If minpoolsize is not specified or specified as zero in the connection string, the connection in the pool is closed after a period of inactivity. However, if the specified minpoolsize is greater than 0, the connection pool will not be corrupted until the AppDomain is unloaded and the process ends. The maintenance of inactive or empty pools requires minimal overhead.

Attention:
The pool is cleared automatically when errors such as failover occur.


Add a connection
The connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirements. The connection is added to the pool as needed, but cannot exceed the specified maximum pool size (the default is 100). The connection is released back into the pool when it is turned off or disconnected.

When a SqlConnection object is requested, the object is fetched from the pool if there is a connection available. The connection is available, must not be used, has a matching transaction context or is not associated with any transaction context, and has a valid link to the server.

The connection pool process satisfies these connection requests by reallocating the connection when the connection is released back into the pool. If the maximum pool size has been reached and no available connections exist, the request will be queued. The pool process then attempts to re-establish any connection until the timeout is reached (the default is 15 seconds). Throws an exception if the pool process cannot satisfy the request before the connection times out.

Warning:
We strongly recommend that you always turn off the connection after you have finished using it so that the connection is returned to the pool. To turn off a connection, you can use the close or Dispose method of the Connection object, or you can open all connections in a using statement in C # or in a using statement in Visual Basic. Connections that are not explicitly closed may not be added or returned to the pool. For more information, see Using Statement for Visual Basic (C # Programmer ' s Reference) or how to:dispose of a System Resource.

Attention:
Do not call Close or Dispose on Connection, DataReader, or any other managed object in the Finalize method of the class. In finalizers, only unmanaged resources that are directly owned by the class are freed. If the class does not own any unmanaged resources, do not include the Finalize method in the class definition. For more information, see Garbage Collection.


To remove a connection
If the connection is idle for a long time, or if the pool process detects that a connection to the server has been disconnected, the connection pool process removes the connection from the pool. Note that a disconnected connection cannot be detected until you attempt to communicate with the server. If a connection is found to be no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only after they are closed or re established.

If there is a connection to a server that has disappeared, it is possible to remove the connection from the pool even if the connection pool manager does not detect the disconnected connection and marks it as invalid. This is because the system overhead of checking whether the connection is still valid will result in another round-trip to the server, thereby offsetting the advantage of the pool process. When this occurs, the first attempt to use the connection detects whether the connection was disconnected and throws an exception.

Clear Pool
Ado.net 2.0 introduces two new methods for clearing the pool: Clearallpools and Clearpool. Clearallpools clears the connection pool for a given provider, Clearpool clears the connection pool associated with a particular connection. If the connection is in use at the time of the call, the corresponding token is made. When the connection is closed, it is discarded instead of being returned to the pool.

Transaction support
The connection is taken out of the pool and allocated according to the transaction context. Unless Enlist=false is specified in the connection string, the connection pool ensures that the connection is enlisted in the current context. When a connection is closed through a registered System.Transactions transaction and returned to the pool, the connection is retained so that the next time the connection pool is requested with the same System.Transactions transaction, the same connection can be returned (if the connection is available). If the connection is not available, a new connection opens. If the transaction does not have an available connection, the connection is automatically registered when the connection is opened.

When the connection is closed, it is released back into the pool and placed in the appropriate sub section according to its transaction context. Therefore, even if a distributed transaction is still suspended, you can still close the connection without generating an error. This allows you to commit or abort a distributed transaction later.

Controlling connection pooling using connection string keywords
The SqlConnection object's ConnectionString property supports connection string key/value pairs, which can be used to adjust the behavior of connection pooling logic. For more information, see ConnectionString.

Pool Fragmentation
Pool fragmentation is a common problem in many WEB applications, and applications may create a large number of pools that will not be freed until the process exits. This will open a large number of connections, consuming a lot of memory, which can affect performance.

Because of the pool fragmentation generated by integrated security
The connection establishes a pool connection based on the connection string and the user identity. Therefore, if you use Basic authentication or Windows authentication and integrated security logins on the Web site, each user will get a pool. Although this can improve the performance of subsequent database requests by individual users, the user cannot take advantage of connections established by other users. This also causes each user to produce at least one connection to the database server. This can have a side effect on a particular WEB application structure, because developers need to measure security and audit requirements.

Because many database-generated pool fragments
Many Internet service providers host multiple Web sites on a single server. They may use a single database to confirm form authentication logins, and then open a connection to a specific database for that user or group of users. The connection to the authentication database establishes a pool connection for use by each user. However, there is a separate pool for each database connection, thus increasing the number of connections to the server.

This can also have side effects on application design. However, this side effect can be avoided in a relatively simple way without affecting security when connecting to SQL Server. Instead of connecting a separate database for each user or group, connect to the same database on the server, and then execute the Transact-SQL USE statement to switch to the desired database. The following code snippet shows how to create an initial connection to the master database, and then switch to the desired database specified in the DatabaseName string variable.

Visual Basic Copy Code
' assumes that command are a valid SqlCommand object and that
' ConnectionString connects to master.
Command. Text = "Use DatabaseName"
Using Connection as New SqlConnection (connectionString)
Connection. Open ()
Command. ExecuteNonQuery ()
End Using

C # Copy Code
Assumes that command are a SqlCommand object and that
ConnectionString connects to master.
Command. Text = "Use DatabaseName";
using (SqlConnection connection = new SqlConnection (
connectionString))
{
Connection. Open ();
Command. ExecuteNonQuery ();
}


Application roles and connection pooling
After the SQL Server application role has been activated by invoking the sp_setapprole system stored procedure, the security context for the connection cannot be reset. However, if the pool is enabled, the connection returns to the pool, and there is an error when reusing the pool connection. For more information, see Knowledge Base article "SQL Application role errors with OLE DB resource pooling" (OLE DB resource pool has an error with SQL application roles).

Application role Substitution
If you are using SQL Server 2005, we recommend that you take advantage of new security mechanisms that can be used in place of application roles. For more information, see Creating an application Role (ado.net) in SQL Server.