Configure Connection Pooling for SQL managed Data Provider.doc

Configure Connection Pooling for SQL managed Data Provider

Database connections are expensive resources, hence pooling database connections can enhance performance and scalability significantly of an application. The SQL managed data provider provides connection pooling for applications. There are various modifiers, which are provided which can be used to configure the connection pool.

The connection string property of the SQLConnection class contains various modifiers, which can be used to configure the connection pool.

Name

Default

Description

Connection Lifetime

0

This is the time in seconds for which the idle connections in the pool are maintained. If the idle time exceeds this time, the connection is destroyed.

A value of zero (0) will cause pooled connections to have the maximum time-out.

Enlist

'true'

When true, the pool automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists.

Max Pool Size

100

The maximum number of connections maintained in the pool.

Min Pool Size

0

The minimum number of connections maintained in the pool.

Pooling

'true'

When true, the connection pool is created and maintained.

Sample Connection String

server=abc;database=xyz;user id=x;pwd=xx;Connection Lifetime=600; Max Pool Size=200;Min Pool Size=10

Performance Counters for Connection Pooling

The SQL managed data provider adds several performance counters that can be enabled to fine-tune the connection pool. The following table lists the connection pooling counters that can be accessed in Performance Monitor under the ".NET CLR Data" performance object.

Counter

Description

SqlClient: Current # of pooled and non pooled connections

Current number of connections, pooled or not.

SqlClient: Current # pooled connections

Current number of connections in all pools associated with the process.

SqlClient: Current # connection pools

Current number of pools associated with the process.

SqlClient: Peak # pooled connections

The highest number of connections in all pools since the process started. Note: this counter is only available when associated with a specific process instance. The _Global instance will always return 0.

SqlClient: Total # failed connects

The total number of connection open attempts that have failed for any reason.

The connection pool for other managed Data Providers like OleDb and Oracle can be configured in a similar fashion.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish