1. Scaffolding
   a. New pool scaffolding #3352
   b. Add unit test project #3380
   c. Add ChannelDbConnectionPool stub and unit tests. #3396
2. Get/Return connections
   a. Get/Return pooled connections #3404
3. Clear
   a. Implement ChannelDbConnectionPool.Clear #4194
4. Context aware timeouts
   a. Remove unnecessary inheritance structure from pool key class. #4235
   b. Remove connection options inheritance #4237
   c. Remove extra connection options parameters #4261
   d. Connect timeout propagated through pool #4270
5. Pool pruning (with clear counter)
   a. Pruning timer not disposed even after disposing all SqlConnection instances #1881
   a. Add automatic pool size reduction (pruning) to ChannelDbConnectionPool #4304
6. Configurable idle timeout
   a. Add Pool Idle Timeout to control connection pool #343
   a. Add configurable idle connection timeout (ADO #39970) #4295
7. Shutdown
   a. Implement pool shutdown for ChannelDbConnectionPool and harden WaitHandleDbConnectionPool shutdown #4302
8. Rate limiting connection opens
   a. Extract pool blocking-period error state into BlockingPeriodErrorState #4395
   a. Add connection-creation rate limiting to ChannelDbConnectionPool #4396
9. Warmup
   a. Background pool warmup and replenishment for ChannelDbConnectionPool #4452
10. Connection resiliency (replace connection)
    a. Refactor ForceNewConnection #4415
    b. ChannelDbConnectionPool replace connection #4429
11. Transaction support
    a. Cleanup | SqlInternalConnection properties #3743
    b. Cleanup | Split out TransactedConnectionPool #3746
    d. Test | Connection pool transaction tests #3805
    e. ChannelDbConnectionPool transaction support #4487
12. Reclaim leaked connections
    a. Reclaim leaked connections in ChannelDbConnectionPool #4490 / Channel Pool: Reclaim leaked connections #4529
13. Tracing and metrics
    a. Emit pool metrics and traces, and fix Count semantics in ChannelDbConnectionPool #4504
    b. Add pool tracing/metrics parity and surface pooled-open timeout cause #4505
    i. Improve error reporting when an application fails to obtain a connection from the pool #3545
14. Benchmarking and performance testing
    a. [Tests] Setup needed for Connection Pool reliability testing #3667
    b. Expand connection pool benchmark coverage for pool implementation A/B testing #4459

Design Document: ChannelDbConnectionPool

Problem Statement

The current connection pool implementation is slow to open new connections and does not follow async best practices.

Connection opening is serialized, causing delays when multiple new connections are required simultaneously. This is done using a semaphore to rate limit connection creation. When multiple new connections are requested, they queue up waiting for the semaphore. Once acquired, the thread opens the connection and releases the semaphore, allowing the next thread to proceed. This approach was initially designed to prevent overwhelming the server but can lead to significant delays, especially in high-latency environments.

Async requests are also serialized through an additional queue. When an async request is made, it is added to a queue, and a reader thread processes these requests one by one. This method was chosen due to the lack of async APIs in native SNI, resulting in synchronous handling of async requests on a dedicated thread.

Design Goals

• Enable parallel connection opening.
• Minimize thread contention and synchronization overhead.
• Follow async best practices to reduce managed threadpool pressure and enable other components of the driver to modernize their async code.

Overview

The core of the design is the Channel data structure from the System.Threading.Channels library (available to .NET Framework as a nuget package) (Also see Stephen Toub's intro here). Channels are thread-safe, async-first queues that fit well for the connection pooling use case.

A single channel holds the idle connections managed by the pool. A channel reader reads idle connections out of the channel to vend them to SqlConnections. A channel writer writes connections back to the channel when they are returned to the pool.

Pool maintenance operations (warmup, pruning) are handled asynchronously as Tasks.

Transaction-enlisted connections are stored in a separate dictionary data structure, in the same manner as the WaitHandleDbConnectionPool implementation.

This design is based on the PoolingDataSource class from the npgsql driver. The npgsql implemenation is proven to be reliable and performant in real production workloads.

Why the Channel Data Structure is a Good Fit

1. Thread-Safety:
   Channels are designed to facilitate thread-safe communication between producers (e.g., threads returning connections to the pool) and consumers (e.g., threads requesting connections). This eliminates the need for complex locking mechanisms, reducing the risk of race conditions and deadlocks.

2. Built-In Request Queueing:
   Channels provide a succinct API to wait asynchronously if no connections are available at the time of the request.

3. Asynchronous Support:
   Channels provide a robust async API surface, simplifying the async paths for the connection pool.

4. Performant:
   Channels are fast and avoid extra allocations, making them suitable for high throughput applications: https://devblogs.microsoft.com/dotnet/an-introduction-to-system-threading-channels/#performance

Workflows

1. Warmup:
   New connections are written to the tail of the idle channel by an async task.

2. Acquire Connection:
   Idle connections are acquired from the head of the idle channel.

3. Release Connection:
   Connections that are released to the pool are added to the tail of the idle channel.

4. Pruning:
   Connections are pruned from the head of the idle channel.

Test Plan

The testing strategy is built on three pillars:

• Thorough unit and integration test coverage.
  • Test coverage follows a typical hierarchy, with targeted integration tests built on a large foundation of unit tests.
  • Tests run with the AppContextSwitch both on and off to prove functional parity between the new and old pool implementations.
• Performance testing
  • Covers both pool implementations, proves performance improvements in the new implementation and guards against performance regressions.
• Opt-in release approach and community feedback.
  • Customers and community members may choose to use the new pool implementation and provide feedback on its performance and reliability.

Performance Benchmarks

Note: All graphed results use managed SNI. See full results below for native SNI.

The channel based implementation shows significant performance improvements across frameworks and operating systems. In particular, interacting with a warm pool is much faster.

When interacting with a cold pool and connecting to an Azure database, performance is equivalent to the legacy implementation provided enough threads are made available in the managed threadpool. This requirement highlights a bottleneck present further down the stack when acquiring federated auth tokens.

Windows - .NET 8.0

Windows - .NET Framework 4.8.1

Linux - net8.0

Windows - net8.0 - AzureSQL - Default Azure Credential

Windows - .NET Framework 4.8.1 - AzureSQL - Default Azure Credential

Windows - NET 8.0 - Azure SQL - AccessToken