SQL Server connection pooling and its usage

In fact, we've been using SQL Server's connection pool. In the connection string, pooling is enabled if connection pooling is enabled and the default value is true.

The two important parameters related to connection pooling are min pool size and max pool size, which are the minimum number of connections in the pool and the maximum number of connections in the pool, with default values of 0 and 100, respectively.

When we create an instance of a connection and call the open () method, the connection pool Manager finds an available connection in the connection pool, and when the close () method is called, the connection pool manager returns the connection to the connection pool for use by the next call to the open () method.

In addition, the Connection Lifetime in the connection string sets the life cycle for the connection in the connection pool. Its default value is 0. When a connection is returned to the pool, its creation time is compared to the current time, and if the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection is destroyed. This is useful in a clustered configuration (used to enforce load balancing between a running server and a server that has just been brought online). A value of 0 (0) causes the connection pool to have the maximum connection timeout.

With the above understanding, we can see that even the simplest connection strings are used in connection pooling.

----------------------------------------------------------------------------------------------

Ps. The meaning of the connection string keyword:

Key words	Default value	Description
Application Name	Not available	The name of the application, or ". Net SqlClient Data Provider" (if the application name is not provided).
Async	' False '	If set to true, asynchronous operation support is enabled. The values that are recognized are true, false, yes, and No.
AttachDbFileName Or Extended Properties Or Initial File Name	Not available	The name of the primary database file, including the full path name of the database that can be connected. AttachDbFileName is supported only for master data files that have an. mdf extension. If the primary data file is read-only, the attach operation will fail. The path can be either an absolute or a relative path, depending on whether the string is replaced with DataDirectory. If you use DataDirectory, the corresponding database file must exist in a subdirectory of the directory to which the replacement string is pointing. Description remote server, HTTP, and UNC path names are not supported. You must specify the database name by using the keyword "database" (or one of its aliases) as follows: "Attachdbfilename=| datadirectory|\data\yourdb.mdf;integrated Security=true;database=yourdatabase " If a log file exists in the same directory as the data file and the "database" keyword is used when attaching the master data file, an error is generated. If this is the case, remove the log file. When the database is attached, a new log file is automatically generated based on the physical path.
Connect Timeout Or Connection Timeout	15	The length of time, in seconds, to wait for a connection to the server before terminating the attempt and generating an error.
Context Connection	' False '	True if an in-process connection should be made to SQL Server.
Current Language	Not available	The SQL Server language record name.
Data source Or Server Or Address Or Addr Or Network Address	Not available	The name or network address of the instance of SQL Server to connect to. You can specify a port number after the server name: Server=tcp:servername, PortNumber Always use (local) when specifying a local instance. To enforce the use of a protocol, add one of the following prefixes: NP: (local), TCP: (local), LPC: (local) Description ADO 2.0 does not support the execution of asynchronous commands on a SQL Server 2000 or earlier version of shared memory. However, you can force TCP to replace shared memory by adding a "tcp:" prefix to the server name in the connection string, or by using localhost.
Encrypt	' False '	When this value is true, if a certificate is installed on the server side, SQL Server uses SSL encryption for all data that is transferred between the client and the server. The values that are recognized are true, false, yes, and No.
Enlist	' True '	True indicates that the SQL Server connection pooling program automatically enlists the connection in the context of the current transaction in which the thread was created.
Failover Partner	Not available	The name of the failover partner server in which to configure database mirroring. The. NET Framework version 1.0 or 1.1 does not support the Failover Partner keyword.
Initial Catalog Or Database	Not available	The name of the database.
Integrated Security Or Trusted_Connection	' False '	When false, a user ID and password are specified in the connection. When True, the current Windows account credentials are used for authentication. The recognized values are true, false, yes, no, and SSPI, which is equivalent to true (highly recommended). If you specify a user ID and password, and the integrated security setting is True, the user ID and password are ignored and integrated security is used.
Multipleactiveresultsets	' False '	If true, the application can maintain a multi-active result set (MARS). If False, the application must process or cancel multiple result sets in a batch before any other batches on the connection are executed. The values that are recognized are true and false. The. NET Framework version 1.0 or 1.1 does not support this keyword.
Network Library Or Net	' DBMSSOCN '	A network library that is used to establish a connection to an instance of SQL Server. The supported values are: DBNMPNTW (Named pipes) DBMSRPCN (Multi-Protocol, Windows RPC) Dbmsadsn (Apple talk) Dbmsgnet (VIA) DBMSLPCN (Shared memory) DBMSSPXN (ipx/spx) DBMSSOCN (TCP/IP) Dbmsvinn (Banyan Vines) The appropriate network DLLs must be installed on the system to which you are connecting. If you do not specify a network and use a local server (for example, "." or "(local)"), using shared memory. In this example, the network library is WIN32 Winsock TCP/IP (DBMSSOCN) and the port used is 1433. View Copy to Clipboard printing in a color-coded format Network Library=dbmssocn;data source=000.000.000.000,1433; Network Library=dbmssocn;data source=000.000.000.000,1433;
Packet Size	8192	The size, in bytes, of the network packet used to communicate with the instance of SQL Server.
Password Or Pwd	Not available	The password for the SQL Server account login. It is not recommended. To maintain a high level of security, we strongly recommend that you use the Integrated security or trusted_connection keywords.
Persist Security Info	' False '	When the value is set to False or no (strongly recommended), security-sensitive information (such as a password) will not be returned as part of the connection if the connection is open or has been open. Resetting the connection string resets all connection string values, including passwords. The values that are recognized are true, false, yes, and No.
Plan	Not available	Specifies the connection schedule. Argumentexception:failover partner, AttachDbFileName, Userinstance=true, or contextconnection= are raised if the following items are also specified in the connection string True
Copy	' False '	True if the connection is used to support replication.
Transaction Binding	Implicit Unbind	Controls the connection associated with the enlisted System.Transactions transaction. Possible values include the following: Transaction binding=implicit Unbind; Transaction binding=explicit Unbind; Implicit Unbind allows the connection to detach from the transaction at the end of the transaction. After separation, other requests on the connection are executed in autocommit mode. The System.Transactions.Transaction.Current property is not checked when a request is executed while the transaction is active. After the transaction ends, other requests are executed in autocommit mode. Explicit Unbind allows the connection to remain connected to a transaction until the connection is closed or an explicit sqlconnection.transactionenlist (null) is invoked. If Transaction.Current is not an enlisted transaction or the enlisted transaction is not active, InvalidOperationException is raised.
TrustServerCertificate	' False '	If set to True, the channel is encrypted with SSL, but the confidence is not verified through the certificate chain. If TrustServerCertificate is set to true and the Encrypt is set to False, the channel is not encrypted. The values that are recognized are true, false, yes, and No. For more information, see Connection string Syntax (ADO).
Type System Version	Not available	A string value that indicates the type system the application expects. Possible values include the following: Type System version=sql Server 2000; Type System version=sql Server 2005; Type System version=sql Server 2008; Type System version=latest; If set to SQL Server 2000, the SQL Server 2000 type system is used. When you connect to an instance of SQL Server 2005, the following conversions are performed: XML to NTEXT UDT to VARBINARY VARCHAR (max), NVARCHAR (max), and VARBINARY (max) are respectively associated with TEXT, NEXT, and IMAGE. If set to SQL Server 2005, the SQL Server 2005 type system is used. No conversions are made to the current version of ADO. If set to Latest, this client-server pair will be used for the latest version that cannot be processed. This latest version will be updated automatically as the client and server components are upgraded.
User ID	Not available	SQL Server logon account. It is not recommended. To maintain a high level of security, we strongly recommend that you use the Integrated security or trusted_connection keywords.
User Instance	' False '	A value that indicates whether the connection is redirected from the default SQL Server Express instance to the runtime-initiated instance running under the caller account.
Workstation ID	Local Computer name	The name of the workstation that is connected to SQL Server.

The following table lists the valid names for the connection pool values within ConnectionString. For more information, see SQL Server Connection Pooling (ADO).

Key words	Default value	Description
Connection Lifetime	0	When a connection is returned to the pool, its creation time is compared to the current time, and if the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection is destroyed. This is useful in a clustered configuration (used to enforce load balancing between a running server and a server that has just been brought online). A value of 0 (0) causes the pool connection to have the maximum connection timeout.
Enlist	' True '	When this value is true, the pool program automatically enlists the connection in the context of the current transaction in which the thread was created. The values that are recognized are true, false, yes, and No.
Load Balance Timeout	0	The minimum time, in seconds, that the connection will survive before it is destroyed in the connection pool.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Pooling	' True '	When this value is true, the system extracts the SQLConnection object from the appropriate pool, or creates the object when needed and adds it to the appropriate pool. The values that are recognized are true, false, yes, and No.

When you set a keyword or connection pool value that requires a Boolean value, you can use "yes" instead of "true" and "no" instead of "false". Integer values are represented as strings.

SQL Server connection pooling and its usage