SQL Server connection pooling (ADO.NET)

  • Article
  • 10 minutes to read

APPLIES TO: .Internet Framework .Net Cadre .NET Standard

Download ADO.Internet

Connecting to a database server typically consists of several time-consuming steps. A physical aqueduct such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connexion string information must be parsed, the connection must exist authenticated by the server, checks must be run for enlisting in the current transaction, then on.

In practise, most applications use only one or a few different configurations for connections. This means that during awarding execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, Microsoft SqlClient Data Provider for SQL Server uses an optimization technique chosen connectedness pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set up of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, information technology returns it to the caller instead of opening a new connexion. When the application calls Shut on the connection, the pooler returns it to the pooled gear up of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open up call.

Only connections with the same configuration can be pooled. Microsoft SqlClient Data Provider for SQL Server keeps several pools at the aforementioned time, 1 for each configuration. Connections are separated into pools by connexion string, and by Windows identity when integrated security is used. Connections are likewise pooled based on whether they are enlisted in a transaction. When using ChangePassword, the SqlCredential instance affects the connection puddle. Unlike instances of SqlCredential volition utilise different connection pools, even if the user ID and password are the aforementioned.

Pooling connections can significantly enhance the performance and scalability of your application. By default, connection pooling is enabled in the Microsoft SqlClient Data Provider for SQL Server. Unless you explicitly disable it, the pooler optimizes the connections as they are opened and airtight in your application. You can besides supply several connection string modifiers to control connection pooling behavior. For more information, see "Controlling Connection Pooling with Connection String Keywords" later in this topic.

Important

When connectedness pooling is enabled, and if a timeout fault or other login error occurs, an exception volition be thrown and subsequent connection attempts will fail for the adjacent 5 seconds, the "blocking catamenia". If the application attempts to connect within the blocking period, the showtime exception will be thrown again. Subsequent failures after a blocking menstruum ends will event in a new blocking periods that is twice as long as the previous blocking catamenia, up to a maximum of 1 infinitesimal.

Notation

The "blocking period" machinery doesn't use to Azure SQL Server past default. This behavior can be inverse by modifying the PoolBlockingPeriod property in ConnectionString except for .Net Standard.

Pool creation and consignment

When a connection is first opened, a connexion puddle is created based on an verbal matching algorithm that associates the pool with the connection cord in the connectedness. Each connection puddle is associated with a singled-out connection string. When a new connectedness is opened, if the connexion cord is non an exact match to an existing puddle, a new pool is created.

Note

Connections are pooled per process, per application domain, per connection cord and when integrated security is used, per Windows identity. Connection strings must likewise be an exact match; keywords supplied in a different club for the aforementioned connection volition exist pooled separately.

Note

If MinPoolSize is either not specified in the connection string or is specified equally zero, the connections in the pool will exist closed later a menses of inactivity. All the same, if the specified MinPoolSize is greater than zero, the connection puddle is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.

Note

The pool is automatically cleared when a fatal error occurs, such as a failover.

In the following C# case, three new SqlConnection objects are created, only only two connectedness pools are required to manage them. Notation that the first and second connection strings differ by the value assigned for Initial Catalog.

              using (SqlConnection connexion = new SqlConnection(     "Integrated Security=SSPI;Initial Catalog=Northwind"))         {             connection.Open up();             // Pool A is created.         }      using (SqlConnection connectedness = new SqlConnection(     "Integrated Security=SSPI;Initial Catalog=pubs"))         {             connection.Open up();             // Puddle B is created because the connexion strings differ.           }      using (SqlConnection connexion = new SqlConnection(     "Integrated Security=SSPI;Initial Catalog=Northwind"))         {               connection.Open();             // The connexion cord matches pool A.           }

Add connections

A connection pool is created for each unique connection cord. When a pool is created, multiple connectedness objects are created and added to the pool and so that the minimum pool size requirement is satisfied. Connections are added to the pool equally needed, upwards to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or tending.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

The connexion pooler satisfies requests for connections by reallocating connections equally they are released dorsum into the pool. If the maximum pool size has been reached and no usable connectedness is available, the request is queued. The pooler then tries to repossess any connections until the time-out is reached (the default is xv seconds). If the pooler cannot satisfy the request earlier the connection times out, an exception is thrown.

Caution

Nosotros strongly recommend that you ever close the connection when you are finished using it and so that the connectedness volition be returned to the puddle. You tin do this using either the Close or Dispose methods of the Connectedness object, or past opening all connections within a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Argument or How to: Dispose of a Organisation Resource for Visual Basic.

Note

Do not call Shut or Dispose on a Connectedness, a DataReader, or whatever other managed object in the Finalize method of your course. In a finalizer, only release unmanaged resources that your class owns directly. If your grade does not ain any unmanaged resources, practise non include a Finalize method in your course definition. For more information, see Garbage Collection.

For more info most the events associated with opening and endmost connections, see Audit Login Event Course and Audit Logout Event Course in the SQL Server documentation.

Remove connections

The connection pooler removes a connectedness from the pool subsequently it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed.

Note

A severed connectedness can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connexion puddle merely when they are closed or reclaimed.

If a connection exists to a server that has disappeared, this connection can be drawn from the pool fifty-fifty if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connexion is even so valid would eliminate the benefits of having a pooler by causing another circular trip to the server to occur. When this occurs, the first effort to utilise the connection will detect that the connexion has been severed, and an exception is thrown.

Clear the puddle

Microsoft SqlClient Data Provider for SQL Server introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connexion pools for a given provider, and ClearPool clears the connectedness pool that is associated with a specific connection.

Note

If in that location are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Transaction support

Connections are drawn from the pool and assigned based on transaction context. Unless Enlist=false is specified in the connexion string, the connection pool makes sure that the connection is enlisted in the Current context. When a connection is airtight and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next asking for that connection puddle with the aforementioned System.Transactions transaction will return the same connection if it is available. If such a request is issued, and there are no pooled connections available, a connectedness is drawn from the non-transacted part of the puddle and enlisted. If no connections are available in either expanse of the pool, a new connection is created and enlisted.

When a connectedness is closed, information technology is released back into the puddle and into the appropriate subdivision based on its transaction context. Therefore, you can shut the connection without generating an error, fifty-fifty though a distributed transaction is nevertheless pending. This allows you to commit or arrest the distributed transaction later.

Control connection pooling with connection cord keywords

The ConnectionString property of the SqlConnection object supports connectedness string key/value pairs that can be used to suit the behavior of the connectedness pooling logic. For more information, encounter ConnectionString.

Pool fragmentation

Puddle fragmentation is a mutual problem in many Spider web applications where the application tin can create a large number of pools that are non freed until the process exits. This leaves a large number of connections open and consuming retentivity, which results in poor performance.

Pool fragmentation due to integrated security

Connections are pooled according to the connection string plus the user identity. Therefore, if yous use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot have advantage of connections fabricated by other users. It also results in at to the lowest degree one connection per user to the database server. This is a side effect of a detail Spider web application architecture that developers must weigh against security and auditing requirements.

Puddle fragmentation due to many databases

Many Net service providers host several Spider web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the hallmark database is pooled and used by everyone. However, at that place is a separate pool of connections to each database, which increase the number of connections to the server.

This is also a side-result of the application design. There is a relatively elementary way to avoid this side event without compromising security when you connect to SQL Server. Instead of connecting to 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 change to the desired database.

The following code fragment demonstrates creating an initial connection to the main database then switching to the desired database specified in the databaseName string variable.

              // Assume that connectionString connects to master.   using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand()) {     connection.Open();     command.Connection = connexion;     command.Text = "USE DatabaseName";     command.ExecuteNonQuery(); }

Application roles and connection pooling

After a SQL Server application office has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the puddle, and an fault occurs when the pooled connection is reused.

Awarding role alternatives

We recommend that you accept reward of security mechanisms that you lot can apply instead of application roles.

Meet as well

  • Connection pooling
  • SQL Server and ADO.NET - Diagnostic counters in SqlClient
  • Microsoft ADO.NET for SQL Server