SQLSERVER：Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

• 背景：

在最近开发中遇到一个问题，对一个数据库进行操作时，我采用64个并行的任务每个任务保证一个数据库连接对象；但是每个任务内部均包含有24个文件需要读取，在读取文件之后，我们需要快速将这24个文件批量入库到数据库中。

于是我这样开发我的程序：

主任务处理方式：最多允许64并行主任务；

主任务内部子任务采用串行方式：24个文件依次读取，和当前主任务均使用同一个数据库连接字符串。

每个主任务都需要24个文件入库到各自的物理分表中，采用的是串行读取文件资源，串行入库，没有能并行插入24个批处理文件到当前主任务的分表中，感觉到这可能是数据库IO入库速度不高是一个主要因素。

于是包主任务中的24个文件解析入库子任务改为并行方式，结果问题来了：

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.   This may have occurred because all pooled connections were in use and max pool size was reached.

• 排查问题：

1、查看当前数据库已经占用的连接数方法：

A、通过perfiler监控我当前的连接数多少？采用Standard(default)监控模式，stop-》start,每次开始时，就会快速罗列出当前已经建立的连接列表，从列表记录中可以查看到目前数据库实例被占用的数据库连接数。

B、从sys.dm_os_performance_counters中查找

declare @value int;
while 1=1 begin
    waitfor delay '00:00:01'
    select @value=cntr_value from sys.dm_os_performance_counters where counter_name ='User Connections'    
    
    print @value;
end    

从上边的监控中查询出，目前我们的数据库连接数确实就只有100个左右，怎么就出现连接池满的问题。前50个是系统连接占用，不计算，那么，我们可以使用的连接数只有将近50个就出现连接池满的异常了。

吃惊！

我们查看下数据库连接数设置是否有问题，通过界面查看：

貌似一切正常，没有限制，难道100个连接就达到最大连接数了?!!!

我们执行查询最大连接数查询sql命令：

select * from sys.configurations where name ='user connections'

 

sqlserver难道在欺骗我们，绝不可能，那么大个公司，如果不能处理这么多，就不会不负责人多高数用户最多允许32767个连接。

我们注意到，这里的value_in_use字段和从数据库界面上看到的一样，都是0，没有限制，那么就是说我们默认就是不受限制，可以最多使用32767个连接。

• 问题发现：

那么，是谁给我们制定了最大连接数限制为100呢？

记得在数据库连接字符串中是包含这个设置的Max Pool Size属性，赶快查查MSDN,确认下是不是这里不写的话有默认值。

搜索关键字“SqlConnection.ConnectionString 属性”，找打了MSDN:https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

名称	默认值	说明
Connection Lifetime	0	当连接被返回到池时，将其创建时间与当前时间作比较，如果时间长度（以秒为单位）超出了由 Connection Lifetime 指定的值，该连接就会被销毁。这在聚集配置中很有用（用于强制执行运行中的服务器和刚置于联机状态的服务器之间的负载平衡）。 零 (0) 值将使池连接具有最大的连接超时。
Connection Reset	'true'	确定从池中提取数据库连接时是否重置数据库连接。对于 SQL Server 7.0 版，设置为 false 可避免获取连接时再有一次额外的服务器往返行程，但须注意此时并未重置连接状态（如数据库上下文）。 只要不将 Connection Reset 设置为 false，连接池程序就不会受到 ChangeDatabase 方法的影响。连接在退出相应的连接池以后将被重置，并且服务器将移回登录时数据库。不会创建新的连接，也不会重新进行身份验证。如果将 Connection Reset 设置为 false，则池中可能会产生不同数据库的连接。
Enlist	'true'	当该值为 true 时，池程序在创建线程的当前事务上下文中自动登记连接。可识别的值为 true、false、yes 和no。
Load Balance Timeout	0	连接被销毁前在连接池中生存的最短时间（以秒为单位）。
Max Pool Size	100	池中允许的最大连接数。
Min Pool Size	0	池中允许的最小连接数。
Pooling	'true'	当该值为 true 时，系统将从适当的池中提取 SQLConnection 对象，或在需要时创建该对象并将其添加到适当的池中。可识别的值为 true、false、yes 和 no。

当设置需要布尔值的关键字或连接池值时，您可以使用“yes”代替“true”，用“no”代替“false”。整数值表示为字符串。

Max Pool Size 默认为100。

• 问题验证：

问题终于锁定了，那么是否真的是这样子呢？

我做了一下测试：

    class Program
    {
        static void Main(string[] args)
        {
            ThreadPool.SetMaxThreads(1000, 1000);

            for (var i = 0; i < 1000; i++)
            {
                ThreadPool.QueueUserWorkItem(Connection);
            }

            while (true)
            {
                Thread.Sleep(1000);
                Console.WriteLine("alive...");
            }

            Console.WriteLine("Complete!!!");
            Console.ReadKey();
        }

        private static void Connection(object state)
        {
            SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=i_Master;Persist Security Info=True;User ID=NUser;Password=N2;max pool size=500");

            connection.Open();

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandTimeout = 30 * 60;
                command.CommandText = @"
                        SELECT @@VERSION;
                        waitfor delay '00:00:01'
                        ";
                command.CommandType = System.Data.CommandType.Text;

                command.ExecuteNonQuery();
            }


            Thread.Sleep(10000);
        }
    }

我设定1000个线程，在.net线程池中运行，每个线程打开数据库连接字符串后不关闭，让他永久占用连接，直到对象被回收或者连接超时。

通过上边两种监控连接的方式，查看到的结果：

A方式监控结果：

B方式监控结果：

 

• 参考资料：

Configure the user connections Server Configuration Option：https://technet.microsoft.com/en-us/library/ms187030.aspx

Connection Pooling and the “Timeout expired” exception FAQ：https://blogs.msdn.microsoft.com/angelsb/2004/08/25/connection-pooling-and-the-timeout-expired-exception-faq/

Max Connection Pool capped at 100：http://dba.stackexchange.com/questions/51219/max-connection-pool-capped-at-100

SqlConnection.ConnectionString 属性：https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx