SQL Server 连接池

连接到数据源可能需要很长时间。为了最大程度地降低打开连接的成本，ADO.NET 使用一种称为 连接池的优化技术，这会最大程度地降低重复打开和关闭连接的成本。.NET Framework 数据提供程序处理连接池的方式有所不同。

连接到数据库服务器通常由几个需要很长时间的步骤组成。必须建立物理通道（例如套接字或命名管道），必须与服务器进行初次握手，必须分析连接字符串信息，必须由服务器对连接进行身份验证，必须运行检查以便在当前事务中登记，等等。

实际上，大多数应用程序仅使用一个或几个不同的连接配置。这意味着在执行应用程序期间，许多相同的连接将反复地打开和关闭。为了最大程度地降低打开连接的成本，ADO.NET 一种称为连接 池 的优化技术。

连接池使新连接必须打开的次数得以减少。池程序维持物理连接的所有权。通过为每个给定的连接配置保留一组活动连接来管理连接。每当用户在连接上调用 Open 时，池进程就会查找池中可用的连接。如果某个池连接可用，会将该连接返回给调用者，而不是打开新连接。应用程序在该连接上调用 Close 时，池进程会将连接返回到活动连接池集中，而不是关闭连接。连接返回到池中之后，即可在下一个 Open 调用中重复使用。

在 SQL Server 中，user connections 选项可配置实例建立的最大用户连接数，默认值为 0，表示最大连接数为 32767。实际上允许的用户连接数还取决于正使用的 SQL Server 版本以及应用程序和硬件的限制。

那么，数据库的连接数，与客户端连接池有什么样的关系呢？

带着这个疑问，我设计了一个方案，想了解它们之间有什么相互影响。

测试代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;


namespace dbConnectionTest
{
    class Program
    {
        static void Main(string[] args)
        {
            int maxCount = 40000;
            string connstring = "Server=1card1-hzc;Database=mytest;User ID=sa;Password=sa;pooling=true;connection lifetime=0;min pool size = 1;max pool size=40000";
            List<SqlConnection> collection = new List<SqlConnection>();
            for (int i = 0; i < maxCount; i++)
            {
                Console.WriteLine(string.Format("已创建连接对象{0}", i));
                try
                {
                    var sqlConn = new SqlConnection(connstring);
                    sqlConn.Open();
                    collection.Add(sqlConn);//保存连接对象
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
        }
    }
}

我们可以查看 SQL Server 设置的最大连接数，[userconnections]  run_value=0（默认值为0即最大值32767）

测试一：

设置max poolsize=40000，即连接池40000，MSSQL 连接限制32767

以上监控可以看到，MSSL 连接峰值为32717，加上数据库系统自己默认占有的50个连接，总数为32767，即为数据库的最大连接数。在连接过程中，CPU和内存开销很大！

在出错的情况下还继续创建对象，但是数据库连接数是没有增加的，连接对象都保存到连接池中，还在等待连接数据库。

当连接池连接数达到最大值时，如下图到达 39999 时，程序将自动关闭并退出！本人电脑几乎卡死！数据库连接数回到正常，数据库内存仍然很大，还是很卡！最后重启数据库服务释放内存！

测试二： 

设置max poolsize=3000，即连接池3000，MSSQL 连接限制32767

客户端连接池最大连接为3000，再新建连接时，会因超时不能添加到连接池中，结果失败！

数据库系统当前最多可连接32767，现在只连接3000+系统session，为3019。此时数据库系统还可以继续创建连接，只是此客户端连接池限制而已。正常业务情况下，连接池会尽快处理没有用的连接，让客户端建立新的连接。

在数据库中，我删除1000个 sleeping 的 session：

declare @i int
set @i = 1000
while @i<2000
begin
  exec('kill '+ @i)
  SET @i = @i + 1
end

数据库连接是降下来了，但是连接池还是没有降。因为新的连接还是无法通过连接池。所以也不要删除数据库中 sleeping 的 session，因为连接池如果再重用连接的话，找不到数据库中的对应连接就断开了！

测试三： 

设置max poolsize=40000，即连接池40000，MSSQL 连接限制3000

MSSQL 中执行以下代码，然后重启数据库服务：

EXEC sp_configure 'user connections',3000
Reconfigure

当通过连接池连接数据达到2982时，发生了错误。加上数据库系统中的18个session，连接总数为3000.

这个错误网上很常见，连接池虽然可以继续创建新的连接，但是无法与数据库端连接。出现此错误可以判断是数据库端没法连接到。如果不是最大连接数出错，可能是tcp/ip协议未开启、端口未打开或被隔离等。

成功与服务器建立连接，但是在登录前的握手期间发生错误。(provider：tcp provider error：0 –指定的网络名不可再用。)

接下来在数据库中，删除1000个sleeping的session：

declare @i int
set @i = 1000
while @i<2000
begin
  exec('kill '+ @i)
  SET @i = @i + 1
end

连接又可以正常进行了。

以上测试总结如下：

现在，我们再做多几个测试，验证物理连接和连接池重用连接的耗时。

测试四： 

连接池重用：

设置max poolsize=40000，即连接池40000，MSSQL 连接限制0(32767)。循环次数：40000

物理连接重建：

设置max poolsize=4000，即连接池4000，MSSQL 连接限制0(32767)。循环次数：4000

本打算物理连接都设置40000，不过时间较久，这里测试4000，届时可多十倍时间来对比重用连接池的时间。

//重用：
 var sqlConn = new SqlConnection(connstring);
 sqlConn.Open();
 sqlConn.Close();
//重建：
var sqlConn = new SqlConnection(connstring);
sqlConn.Open();
collection.Add(sqlConn);

如重建次数增加10倍为40000,则平均时间比为 13:421,即重建/重用=32。重建连接比重用连接池所花费时间多32倍！所以现在的各种系统连接数据库，基本使用连接池。但是连接池也不能过多，否则一直占用系统资源，又用不上。

测试五：关闭连接池后数据库会话状态

现在把连接池关闭 pooling=false，每次打开或者关闭连接时，console暂停。

    for (int i = 0; i < maxCount; i++)
            {
                Console.WriteLine(string.Format("已创建连接对象{0}", i));
                try
                {
                    sqlConn.Open();
                    Console.WriteLine("已打开");
                    Console.ReadKey(true);
                    sqlConn.Close();
                    Console.WriteLine("已关闭");
                    Console.ReadKey(true);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }

此时我们查看MSSQL中的连接session。（在c#连接我是sa登录的）

SELECT * FROM sys.dm_exec_sessions where session_id>50 AND login_name='sa'
 
SELECT * FROM sys.dm_exec_connections
WHERE session_id IN(SELECT session_id FROM sys.dm_exec_sessions where session_id>50 AND login_name='sa')

关闭连接时再查看。Session 已经关闭了。下次再连接，就是新的一个物理连接了。

总结：

连接池与MSSQL连接关系：

连接池连接与物理连接耗时对比：

关闭连接池：

每次连接数据库都是一次物理连接，关闭连接时，数据库session也关闭