SQL Server Connection Pool基础
连接到数据库一般情况下是比较耗费时间的, 因为这个过程是由一些耗时较长的步骤组成的.
1. 建立一个物理连接, 要么是socket, 要么是named pipe.
2. 与数据库服务器之间的握手是必须的.
3. 连接字符串必须要被解析.
4. 连接需要经过服务器认证(authenticated).
5. 还需要经过一些权限等方面的校验.
6. 等等.
在实际应用中, 绝大多数的应用程序仅需使用一个或几个对数据库连接的配置. 这意味着, 在应用程序执行的过程中, 许多一模一样的连接会被重复地开启和关闭. 为了最小化开启数据库连接的损耗, ADO.NET中使用了一种叫做connection pooling的优化技术.
Connection Pooling会减少打开新建连接的次数. Pooler拥有物理连接的所有权. 对每一种数据库连接配置它都让几个处于活动状态的连接持续地处于活动状态, Pooler通过这种方式来管理数据库连接. 每当一个用户对一个connection调用Open方法, Pooler都会在连接池中寻找一个可用的active的连接. 如果有这样的一个connection可用, 它就返回给调用者这个connection, 而不是去打开一个新连接. 当应用程序对connection调用Close方法的时候, pooler会把这个connection返回给pooler, 放入活动状态的可用的connection的池中, 而不是将之关闭. 一旦连接返回了pool中, 它就准备好了下一次再Open调用的时候被返回给调用者.
只有当connection是同一种configuration的时候, 他们才可以被放入同一个pool中. ADO.NET会同时维护多个pool, 每个pool对应一种configuration. Connection通过connection string(连接字符串)来分类到不同的pool中, 另一个标准就是当ingetrated security被使用的时候的Windows identity. Connection还按是否被列入transaction而被分类放入池中.
Connection被按池的方式管理可以显著地增强你的应用程序的性能和可扩展性. 默认地, connection pool是由ADO.NET来开启的. 除非你显式地关掉它, 只要你的应用程序开启和关闭connection, pooler都会对connection的操作进行优化. 你还可以使用一些对数据库连接字符串的修饰符来控制pooler的行为.
Pool的创建和分配
===========
当一个connection第一次被开启的时候, 一个connection pool就会按照连接字符串的精确匹配算法被创建出来. 每个connection pool都与一个独一无二的连接字符串相关联. 当一个新的连接建立起来, 如果连接字符串与已经存在的pool的连接字符串都不匹配, 那么一个新的pool就会被创建出来. Connection会分进程, 分application domain, 分connection string来进入不同的pool. 还有, 如果开启了integrated security, 还要按照windows identity来分配.
为pool添加connection
============
当一个pool被创建出来的时候, 就会有很多connection对象也被创建出来并添加到pool里, 这样的话minimum pool size的条件会被满足. 之后connection会按照需求不断地被添加进来, 直到到达maximum pool size的值(默认该值为100).
当connection被closed或disposed之后, 会被放回到pool中.
当一个SqlConnection对象被请求, 如果pool中有可用的connection, 那么这个connection就会被返回. 为了满足"可用"这个条件, 这个connection必须是当前未被使用的, 有匹配的transacton context的, 或没被与任何transaction context关联的, 并且有对服务器的一个合法的链接.
Connection pooler会返回之前别人释放的connection给新的请求. 如果pool的最大值已经到了, 并且仍然没有可用的connection的话, 那么该请求就会进入队列(默认的进入队列后的timeout的值是15秒). Pooler然后会尝试回收任何connection.如果pooler不能在timeout之前满足队列里的请求, 那么就会抛出一个异常.
该异常应该是这样的:
Exception type: System.InvalidOperationException
Message: 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.
InnerException: <none>
StackTrace (generated):
SP IP Function
0636F4B8 653CF486 System_Data_ni!System.Data.ProviderBase.DbConnectionFactory.GetConnection(System.Data.Common.DbConnection)+0x133f46
0636F4C4 652D69BA System_Data_ni!System.Data.ProviderBase.DbConnectionClosed.OpenConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory)+0x6a
0636F4F8 652F5440 System_Data_ni!System.Data.SqlClient.SqlConnection.Open()+0x70
移除connection
===========
Connection pooler会一处闲置时间过久的connection.
另外, 如果connection没有在跟目的服务器有连接的话, 这个connection就会被标记为invalid, Invalid的connection仅会在closed, 或reclaimed的时候被从pool中移除出去.
注意:
如果一个Windows Server基于某种原因不关掉死去的connection, 那么SQL Server有全假设这个connection还是处于活动状态的, 所以就不会把它清除掉.
补充:
SQL connection是种很便宜的资源, 一台SQL Server里有两三千个也不是问题.
并不是一个connection就会关联一个SQL的线程.
提起了SQL的线程, 为了给大家一个直观的感受, 大家可以运行一下下面的语句. 它返回的是SQL的工作者线程以及他们的状态.
select * from sys.dm_os_workers
这些工作者线程是按照线程池的方式来管理的. 用白话说就是有活儿就干, 没活儿就把cpu放掉去歇着.
可以通过下面的语句来查看全部的connection
select * from sys.dm_exec_sessions where session_id > 50
通过下面的语句来查看所有非SQL系统的正在运行中的session
select db_name(database_id), * from sys.dm_exec_requests where session_id > 50
通过下面的语句来确定处于闲置状态中的connection:
select * from sys.dm_exec_sessions where status = 'sleeping' and session_id > 50
通过下面的语句来查看哪些主机上的哪些进程连到了哪些数据库, 同样的connection有多少.
select db_name(dbid), hostname, hostprocess, COUNT(*) as counts from sysprocesses where spid > 50 group by hostname, dbid, hostprocess order by counts desc
拿到了进程号, 就可以继续抓dump或者找研发人员来排查该进程上的可能的sql connection泄露的问题了.
如果connection很多, 然而闲置的也很多, 那么说明SQL的状态还正常.
Connection数量多本身并不说明问题.
参考资料
===================
SQL Server Connection Pooling (ADO.NET)
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
How to troubleshoot orphaned connections in SQL Server
