SQL Server connection pool and usage

In fact, we have been using the SqlServer connection pool. In the connection string, Pooling indicates whether to enable the connection pool. The default value is true, indicating that the connection pool is enabled.

Two important parameters related to the connection Pool 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. The default values are 0 and 100, respectively.

When we create a connection instance and call the Open () method, the connection pool administrator finds an available connection in the connection pool. when the Close () method is called, the connection pool manager returns the connection to the connection pool for the next call of the Open () method.

In addition, the Connection Lifetime in the Connection string sets the lifecycle for the Connection in the Connection pool. The default value is 0. When the Connection is returned to the pool, the creation time is compared with the current time. If the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection will be destroyed. This is useful in clustering configuration (used to force load balancing between running servers and servers that are just online ). If the value is zero, the connection pool has the maximum connection timeout.

 

From the above understanding, we can see that even the simplest connection string is using the connection pool.

Bytes ----------------------------------------------------------------------------------------------

Ps. Significance of the connection string Keyword:

Keywords	Default Value	Description
Application Name	Unavailable	Application name, or ". Net SqlClient Data Provider" (if no application name is provided ).
Async	'False'	If this parameter is set to true, asynchronous operations are supported. The identifiable values are true, false, yes, and no.
AttachDBFilename -Or- Extended properties -Or- Initial File Name	Unavailable	The name of the master database file, including the complete path name that can be connected to the database. Only primary data files with. mdf Extensions support AttachDBFilename. If the primary data file is read-only, the additional operation fails. This path can be an absolute or relative path, depending on whether to use DataDirectory to replace the string. If DataDirectory is used, the corresponding database file must exist in the subdirectory to which the replacement string points. DescriptionRemote Server, HTTP, and UNC path names are not supported. You must use the keyword "database" (or one of its aliases) to specify the database name as follows: "AttachDbFileName = | DataDirectory | \ data \ YourDB. mdf; integrated security = true; database = YourDatabase" If the log file exists in the directory where the data file is located and the "database" keyword is used when the primary data file is appended, an error is generated. In this case, remove the log file. After a database is attached, the system automatically generates a new log file based on the physical path.
Connect Timeout -Or- Connection Timeout	15	The duration (in seconds) of waiting for connection to the server before terminating the attempt and generating an error ).
Context Connection	'False'	True If SQL Server is to be connected in-process.
Current Language	Unavailable	The name of the SQL Server language record.
Data Source -Or- Server -Or- Address -Or- Addr -Or- Network Address	Unavailable	The name or network address of the SQL Server instance to connect. You can specify the port number after the server name: Server = tcp: servername, portnumber Always use (local) when specifying a local instance ). To force a protocol, add one of the following prefixes: Np :( local), tcp :( local), lpc :( local) DescriptionADO. NET 2.0 does not support asynchronous command execution on SQL Server 2000 or a lower version of shared memory. However, you can force TCP to replace the shared memory by adding the "tcp:" prefix to the server name in the connection string or using "local host ".
Encrypt	'False'	When this value is true, if the Server has a certificate installed, SQL Server uses SSL encryption for all data transmitted between the client and the Server. The identifiable values are true, false, yes, and no.
Enlist	'True'	True indicates that the SQL Server connection pool program automatically registers the connection in the context of the current transaction of the Creation thread.
Failover Partner	Unavailable	The name of the Failover partner server in which the database image is configured. . NET Framework 1.0 or 1.1 does not support the Failover Partner keyword.
Initial Catalog -Or- Database	Unavailable	The name of the database.
Integrated Security -Or- Trusted_Connection	'False'	If the value is false, the user ID and password are specified in the connection. If this parameter is set to true, the current Windows Account creden。 are used for authentication. The identifiable values are true, false, yes, no, and sspi equivalent to true (strongly recommended ). If the user ID and password are specified and the Integration security is set to true, the user ID and password are ignored and integrated security is used.
MultipleActiveResultSets	'False'	If it is true, the application can maintain the multi-activity result set (MARS ). If the value is false, the application must process or cancel multiple result sets in one batch before any other batch processing on the connection is executed. The identifiable values are true and false. . NET Framework 1.0 or 1.1 does not support this keyword.
Network Library -Or- Net	'Dbmssocn'	The network library used to establish a connection with the SQL Server instance. Supported values include: Dbnmpntw (Named Pipe) Dbmsrpcn (multi-protocol, Windows RPC) Dbmsadsn (Apple Talk) Dbmsgnet () Dbmslpcn (shared memory) Dbmsspxn (IPX/SPX) Dbmssocn (TCP/IP) Dbmsvinn (Banyan Vines) The corresponding network DLL must be installed on the system to be connected. If you do not specify a network and use a local server (for example, "." or "(local)"), use shared memory. In this example, the network library is Win32 Winsock TCP/IP (dbmssocn) and the port used is 1433. View and print copies to clipboard in a color-differentiated 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 of network data packets used to communicate with SQL Server instances, in bytes.
Password -Or- Pwd	Unavailable	The logon password of the SQL Server account. We recommend that you do not use it. To maintain a high Security level, we strongly recommend that you use the Integrated Security or Trusted_Connection keyword.
Persist Security Info	'False'	When this value is set to false or no (strongly recommended), if the connection is enabled or is always in the open state, then the security sensitive information (such as the password) will not be returned as part of the connection. Resetting the connection string resets all connection string values, including passwords. The identifiable values are true, false, yes, and no.
Plan	Unavailable	Specify the connection plan. If the following items are also specified in the connection string, ArgumentException is triggered: failover partner, AttachDbFileName, UserInstance = true, or contextConnection = true.
Copy	'False'	True if the connection is used to support replication.
Transaction Binding	Implicit Unbind	Controls the connections associated with the registered System. Transactions transaction. Possible values include: Transaction Binding = Implicit Unbind; Transaction Binding = Explicit Unbind; Implicit Unbind allows connections to be separated from transactions at the end of a transaction. After the splitting, other requests on the connection will be executed in the automatic submission mode. When a request is executed when the Transaction is active, the System. Transactions. Transaction. Current attribute is not checked. After the transaction ends, other requests are executed in the automatic commit mode. Explicit Unbind can keep the connection to the transaction until the connection is closed or Explicit SqlConnection. TransactionEnlist (null) is called ). If Transaction. Current is not a registered Transaction or the registered Transaction is not active, InvalidOperationException is thrown.
TrustServerCertificate	'False'	If it is set to true, SSL is used to encrypt the channel, but the certificate chain is not used to verify the reliability. If you set TrustServerCertificate to true and Encrypt to false, the channel is not encrypted. The identifiable values are true, false, yes, and no. For more information, see connection string syntax (ADO. NET ).
Type System Version	Unavailable	Specifies the string value of the type system that the application expects. Possible values include: Type System Version = SQL Server 2000; Type System Version = SQL Server 2005; Type System Version = SQL Server 2008; Type System Version = Latest; If it is set to SQL Server 2000, SQL Server 2000 is used. When you connect to an SQL Server 2005 instance, perform the following conversions: XML to NTEXT UDT to VARBINARY VARCHAR (MAX), NVARCHAR (MAX), and VARBINARY (MAX) are respectively associated with TEXT, NEXT, and IMAGE. If it is set to SQL Server 2005, SQL Server 2005 is used. The current version of ADO. NET is not converted. If it is set to Latest, the Latest version cannot be processed by this client-server. The latest version is automatically updated with the upgrade of client and server components.
User ID	Unavailable	SQL Server Logon account. We recommend that you do not use it. To maintain a high Security level, we strongly recommend that you use the Integrated Security or Trusted_Connection keyword.
User Instance	'False'	A value that indicates whether to redirect the connection from the default SQL Server Express instance to the running instance started under the caller account.
Workstation ID	Local Computer Name	The name of the workstation that connects to SQL Server.

The following table lists the valid names of connection pool values in ConnectionString. For more information, see SQL Server connection pool (ADO. NET ).

 

Keywords	Default Value	Description
Connection Lifetime	0	When the Connection is returned to the pool, the creation time is compared with the current time. If the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection will be destroyed. This is useful in clustering configuration (used to force load balancing between running servers and servers that are just online ). The zero (0) value will cause the maximum connection timeout for the pool connection.
Enlist	'True'	When this value is true, the pool program automatically registers connections in the current transaction context of the Creation thread. The identifiable values are true, false, yes, and no.
Load Balance Timeout	0	The shortest time (in seconds) before the connection pool is destroyed ).
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 and adds it to the appropriate pool as needed. The identifiable values are true, false, yes, and no.

When you set a keyword or connection pool value that requires a Boolean value, you can replace "yes" with "true" and "no" with "false ". An integer is a string.