sqlserver连接池及查看连接数相关

本是古典何须时尚 2022-06-16 07:20 532阅读 0赞

# sqlserver连接池及查看连接数相关       #

## asp.net 连接池 ##

数据库链接是一种危险的、昂贵的、有限的资源，特别是在多层Web应用程序中。你必须正确管理你的链接，因为你的方法将极大的影响应用程序的整体升级性。高性能应用程序与使用中的数据源保持最短时间的连接，并且利用性能增强技术，例如连接池。连接池用于ODBC 的 SQL Server、OLE DB 和 .NET 框架数据提供程序隐式缓冲连接。通过在连接字符串中指定不同的属性值，可以控制连接池的行为。  
连接池概述  
  
数据库链接池使应用程序能够重用池中的现有链接，而不是重复地建立对数据库的链接。这种技术将极大地增加应用程序的可扩展性，因为有限的数据库链接可以为很多的客户提供服务。此技术也将提高性能，因为能够避免用于建立新链接的巨大时间。  
具体来说，大多数 ADO.NET 数据提供程序使用连接池，以提高围绕 Microsoft 断开连接的 .NET 结构构建的应用程序的性能。应用程序首先打开一个连接（或从连接池获得一个连接句柄），接着运行一个或多个查询，然后处理行集，最后将连接释放回连接池。如果没有连接池，这些应用程序将花费许多额外时间来打开和关闭连接。  
下面将以SQL Server .NET Framework 数据提供程序连接池为例来说明连接池的一些性能和用法。（另外还有ODBC .NET Framework 数据提供程序连接池、OLE DB .NET Framework 数据提供程序连接池。）  
池的创建和分配  
  
当连接打开时，将根据一种精确的匹配算法来创建连接池，该算法会使连接池与连接中的字符串相关联。每个连接池都与一个不同的连接字符串相关联。当新连接打开时，如果连接字符串不精确匹配现有池，则将创建一个新池。  
在以下示例中，将创建三个新的 SqlConnection 对象，但只需要使用两个连接池来管理这些对象。请注意，第一个和第二个连接字符串的差异在于为 Initial Catalog 分配的值。

SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
    conn.Open();      
    // Pool A is created.
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
    conn.Open();      
    // Pool B is created because the connection strings differ.
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
    conn.Open();      
    // The connection string matches pool A.

连接池一旦创建，直到活动进程终止时才会被毁坏。非活动或空池的维护只需要最少的系统开销。  
在使用SQL Server .NET数据供应器链接池时，必须清楚：  
链接是通过对链接字符串精确匹配的法则被池化的。池化机制对名称-值对间的空格敏感。例如，下面的两个链接字符串将生成单独的池，因为第二个字符串包含了一个额外的空字符。  
SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Database=Northwind");  
conn.Open(); // Pool A is created  
SqlConmection conn = new SqlConnection("Integrated Security=SSPI ; Database=Northwind");  
conn.Open(); // Pool B is created (extra spaces in string)  
连接的添加  
  
连接池是为每个唯一的连接字符串创建的。当创建一个池后，将创建多个连接对象并将其添加到该池中，以满足最小池大小的要求。连接将根据需要添加到池中，直至达到最大池大小。  
当请求 SqlConnection 对象时，如果存在可用的连接，则将从池中获取该对象。若要成为可用连接，该连接当前必须未被使用，具有匹配的事务上下文或者不与任何事务上下文相关联，并且具有与服务器的有效链接。  
如果已达到最大池大小且不存在可用的连接，则该请求将会排队。当连接被释放回池中时，连接池管理程序通过重新分配连接来满足这些请求。对 Connection 调用 Close 或 Dispose 时，连接被释放回池中。  
连接的移除  
  
如果连接生存期已过期，或者连接池管理程序检测到与服务器的连接已断开，连接池管理程序将从池中移除该连接。请注意，只有在尝试与服务器进行通信后，才可以检测到这种情况。如果发现某连接不再连接到服务器，则会将其标记为无效。连接池管理程序会定期扫描连接池，查找已释放到池中并标记为无效的对象。找到后，这些连接将被永久移除。  
如果存在与已消失的服务器的连接，那么即使连接池管理程序未检测到已断开的连接并将其标记为无效，仍有可能将此连接从池中取出。当发生这种情况时，将生成异常。但是，为了将该连接释放回池中，仍必须将其关闭。  
选择池容量  
  
能建立最大极限对于管理几千用户同时发出请求的大型系统来说是非常重要的。你需要监视链接池及应用程序的性能，以确定系统的最优池容量。最优容量还要依赖于运行SQL Server的硬件。  
在开发期间，也许需要减小默认的最大池容量（目前是100）以帮助查找链接泄漏。  
如果设立了最小池容量，那么当池最初被填充以达到该值时，会导致一些性能损失，尽管最初链接的几个客户会从中受益。注意，创建新链接的过程被序列化了，这就意味着当池最初被填充时，服务器无法处理同时发生的请求。  
监视链接池化  
  
要监视应用程序对链接池化的应用情况，可以使用随SQL Server发行的Profiler工具，或随微软Windows 2000发行的性能监视器。  
要利用SQL Server Profiler 监视链接池化，操作如下：  
1.         单击开始，指向程序，指向Microsoft SQL Server，然后单击Profiler运行Profiler。  
2.         在文件菜单中，指向新建，然后单击跟踪。  
3.         提供链接内容，然后单击确定。  
4.         在跟踪属性对话框中，单击事件标签。  
5.         在已选事件类别列表中，确保审核登录和审核登出事件显示在安全审核下面。  
6.         单击运行开始跟踪。在链接建立时，将会看到审核登录事件；在链接关闭时看到审核登出事件。  
要通过性能监视器监视链接池化，操作如下：  
1.         单击开始，指向程序，指向管理工具，然后单击性能运行性能监视器。  
2.         在图表背景中右击，然后单击增加计数器。  
3.         在性能对象下拉列表框中，单击SQL Server:通用统计。  
4.         在出现的列表中，单击用户链接。  
5.         单击增加，然后单击关闭。  
在config中加pooling=false，说明程序中不用连接池。  
现在，问题解决，在config文件的连接字符串中增加max pool size ="1000" 扩大连接池，同时，调整程序，尽量避免多开连接。

## 查看连接数相关 ##

### 1、获取SQL Server允许同时用户连接的最大数 ###

SELECT @@MAX\_CONNECTIONS

### 2、获取当前指定数据库的连接信息 ###

SELECT * FROM master.dbo.sysprocesses WHERE dbid IN
    (
    SELECT dbid FROM master.dbo.sysdatabases
    WHERE NAME='YourDataBaseName'
    )
    --根据需要更改YourDataBaseName
    SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'YourDataBaseName'

### 3、获取当前SQL服务器所有的连接详细信息 ###

SELECT \* FROM sysprocesses  
　　以上查询结果包含了：系统进程和用户进程。  
　　如果只是想查用户进程的话则需采用下面的方法

### 4、获取自上次启动 SQL Server服务 以来连接或试图连接的次数 ###

SELECT @@CONNECTIONS

### 5 ---查看当前数据库系统所有请求情况. ###

只列出了我认为比较重要有助于我解决问题的字段。

SELECT ds.session_id,
           ds.status,
           Db_name(dr.database_id) AS database_name,
           ds.login_name,
           ds.login_time,
           ds.host_name,
           dc.client_net_address,
           dc.client_tcp_port,
           ds.program_name,
           dr.cpu_time,
           dr.reads,
           dr.writes,
           dc.num_reads,
           dc.num_writes,
           ds.client_interface_name,
           ds.last_request_start_time,
           ds.last_request_end_time,
           dc.connect_time,
           dc.net_transport,
           dc.net_packet_size,
           dr.start_time,
           dr.status,
           dr.command,
           dr.blocking_session_id,
           dr.wait_type,
           dr.wait_time,
           dr.last_wait_type,
           dr.wait_resource,
           dr.open_transaction_count,
           dr.percent_complete,
           dr.granted_query_memory
    FROM   Sys.dm_exec_requests dr WITH(nolock)
           RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
                         ON dr.session_id = ds.session_id
           RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
                         ON ds.session_id = dc.session_id
    WHERE  ds.session_id > 50
    ORDER  BY ds.program_name

### 6 用户连接数 ###

SELECT login_name,
           Count(0) user_count
    FROM   Sys.dm_exec_requests dr WITH(nolock)
           RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
                         ON dr.session_id = ds.session_id
           RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
                         ON ds.session_id = dc.session_id
    WHERE  ds.session_id > 50
    GROUP  BY login_name
    ORDER  BY user_count DESC

## 附：连接超时时间已到。在尝试使用预登录握手确认时超过了此超时时间。这可能是因为预登录握手失败或服务器未能及时响应。  尝试连接到 Principle 服务器时发生了此故障问题 ##

解决办法  
1.在代码里面,把未关闭的连接关闭  
2.扩大共享池,方法如下:  
解决方法可以是修改连接池的连接生存期,因为默认值是60秒,即连接从应用程序被释放后可以在池中保存的时间。  
具体操作步骤如下：  
如果是ODBC的话，则可以在ODBC Data Source Administrator中手动更改，该程序位于“Start”菜单中的“Programs”->\\"Adminstrative Tools\\"（中文名：管理工具）下，或从\\"Control Panel\\"(中文名：控制面板)中打开“ODBC Data Source Administrator”（ODBC数据源管理器），再选择“Connection Pooling”（连接池）选项卡，双击驱动程序中的“SQL Server”，选择“Pool Connetions to this drive”(使用池连接该驱动程序)，把下面的时间60秒改的大一点，例如120。  
如果是SqlConnection的共享连接，则为ConnectionString属性Connection Lifetime数值名称指定一个值，该值默认情况下是0，表示连接永远不会从池中被自动移走。如果指定一个不同的值，那么一旦连接被返回到池中，该值将和连接的创建时间及当前时间进行比较。如果生存期超过了Connection Lifetime的值，那么连接将从池中被移走。  
  
问题的原因是与数据库的连接没有显示关闭,而等系统自动回收是要时间的.解决的方法是改变连接池的大小.在连接字符串的后面加上以下代码:Enlist=true;Pooling=true;Max Pool Size=300;Min Pool Size=0;Connection Lifetime=300;user id=sa;packet size=1000  
packet size参数取512到32767中的任一整数值  
  
DataReader是独占连接的，就是说你的程序可能设计上有问题。比如说最大连接设100，假设有100个人同时使用DataReader正在读取数据库内容，那么当第101人读取的时候，连接池中的连接已经没有了，就会出现上面的错误。DataReader是独占连接的，每个DataReader都要占用一个连接。当然这个情况是偶尔出现的，所以会很长时间出现一次，因为只有同时有超过连接池最大连接数量的并发操作才会发生。而且你加大并发数量只能暂时缓解问题，如果你加大到200个并发连接，如果有201人同时操作怎么办？你说了你使用Connection对象的Close()方法，这是不行的，因为Close()方法仅仅是关闭连接，但这个连接没有释放，还是被这个对象占用，要释放必须使用Connection的Dispose()方法显式释放连接才可以，否则这个对象占用的连接只能等到垃圾收集的情况下才能被释放。这种情况肯定会出现“超时时间已到”的错误。  
  
解决方法：  
1 修改几个关键页面或访问比较频繁的数据库访问操作，使用DataAdapter和DataSet来获取数据库数据，不要使用DataReader。  
2 在访问数据库的页面上使用数据缓存，如果页面的数据不是经常更新（几分钟更新一次）的话，使用Cache对象可以不用访问数据库而使用缓存中的内容，那么可以大大减少连接数量。  
3 修改代码，把使用Connection对象的地方都在Close()后面加上Dispose()调用。  
4 建议对数据库操作进行大的修改，建立自己的数据库操作代理类，继承System.IDisposable接口，强迫释放资源，这样就不会出现连接数量不够的问题了。  
  
解决方法(\*):WEB.config 里面:在数据库连接加 Max Pool Size = 512;server=local;uid=;pwd=;database=2004;Max Pool Size = 512;\\">一劳永逸。  
  
\-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  
超时时间已到。超时时间已到，但是尚未从池中获取连接。出现这种情况可能是因为所有池连接均在使用，并且达到了最大池大小。  
  
解决办法：首先要做的是在我们使用连接后立即关闭连接。如果没有关闭连接那么连接将保存到连接池中知道GC来销毁。这种情况下你以为连接池没有到达最大值但实际上连接池已经到达了最大值 其次我们可以通过连接字符串中的Max Pool Size = N;来动态扩大连接池中的连接最大数量。  
  
说明：   
  
也就是在connectionString中如果未指定max pool size的值，则max pool size=100，当访问人员同时连接数据库的数量为101人时，则等待SqlConnection.ConnectionTimeout设置的时间（默认是15 秒）后，还是没有可用的Connection则会出现上面的错误。  
  
当我们设置为：  
  
"Server=(local); Integrated Security=SSPI; Database=Northwind; Max Pool Size=512; Min Pool Size=5"  时。则访问人员同时连接数据库的数量为513时，则等待SqlConnection.ConnectionTimeout设置的时间（默认是15 秒）后，还是没有可用的Connection则 就会出现上面的错误。  
  
\-         Connection Pool 是什么呢 ?  
每当程序需要读写数据库的时候。Connection.Open()会使用ConnectionString连接到数据库，数据库会为程序建立 一个连接，并且保持打开状态，此后程序就可以使用T-SQL语句来查询/更新数据库。当执行到Connection.Close()后，数据库就会关闭当 前的连接。很好，一切看上去都是如此有条不紊。  
   
但是如果我的程序需要不定时的打开和关闭连接，（比如说 ASP.Net 或是 Web Service ），例如当Http Request发送到服务器的时候、，我们需要打开Connection 然后使用Select\* from Table 返回一个DataTable/DataSet给客户端/浏览器，然后关闭当前的Connection。那每次都Open/Close Connection 如此的频繁操作对于整个系统无疑就成了一种浪费。  
   
ADO.Net Team就给出了一个比较好地解决方法。将先前的Connection保存起来，当下一次需要打开连接的时候就将先前的Connection 交给下一个连接。这就是Connection Pool。  
  
\-         Connection Pool 如何工作的？  
首先当一个程序执行Connection.open()时候，ADO.net就需要判断，此连接是否支持Connection Pool (Pooling 默认为True)，如果指定为False, ADO.net就与数据库之间创建一个连接（为了避免混淆，所有数据库中的连接，都使用”连接”描述），然后返回给程序。  
如果指定为 True，ADO.net就会根据ConnectString创建一个Connection Pool，然后向Connection Pool中填充Connection（所有.net程序中的连接，都使用”Connection”描述）。填充多少个Connection由Min Pool Size (默认为0)属性来决定。例如如果指定为5，则ADO.net会一次与SQL数据库之间打开5个连接，然后将4个Connection，保存在 Connection Pool中，1个Connection返回给程序。  
   
当程序执行到Connection.close() 的时候。如果Pooling 为True，ADO.net 就把当前的Connection放到Connection Pool并且保持与数据库之间的连接。  
同时还会判断Connection Lifetime(默认为0)属性，0代表无限大，如果Connection存在的时间超过了Connection LifeTime，ADO.net就会关闭的Connection同时断开与数据库的连接，而不是重新保存到Connection Pool中。  
  
（这个设置主要用于群集的SQL 数据库中，达到负载平衡的目的）。如果Pooling指定为False，则直接断开与数据库之间的连接。  
   
然后当下一次Connection.Open() 执行的时候，ADO.Net就会判断新的ConnectionString与之前保存在Connection Pool中的Connection的connectionString是否一致。  
（ADO.Net会将ConnectionString转成二进制流，所 以也就是说，新的ConnectionString与保存在Connection Pool中的Connection的ConnectionString必须完全一致，即使多加了一个空格，或是修改了Connection String中某些属性的次序都会让ADO.Net认为这是一个新的连接，而从新创建一个新的连接。所以如果您使用的UserID,Password的认 证方式，修改了Password也会导致一个Connection，如果使用的是SQL的集成认证，就需要保存两个连接使用的是同一个）。  
  
然后 ADO.net需要判断当前的Connection Pool中是否有可以使用的Connection(没有被其他程序所占用)，如果没有的话，ADO.net就需要判断ConnectionString设 置的Max Pool Size （默认为100），如果Connection Pool中的所有Connection没有达到Max Pool Size，ADO.net则会再次连接数据库，创建一个连接，然后将Connection返回给程序。  
  
如果已经达到了 MaxPoolSize，ADO.net就不会再次创建任何新的连接，而是等待Connection Pool中被其他程序所占用的Connection释放，这个等待时间受SqlConnection.ConnectionTimeout（默认是15 秒）限制，也就是说如果时间超过了15秒，SqlConnection就会抛出超时错误（所以有时候如果SqlConnection.open()方法抛 出超时错误，一个可能的原因就是没有及时将之前的Connnection关闭，同时Connection Pool数量达到了MaxPoolSize。）  
  
如果有可用的Connection，从Connection Pool 取出的Connection也不是直接就返回给程序，ADO.net还需要检查ConnectionString的ConnectionReset属性 (默认为True)是否需要对Connection 最一次reset。这是由于，之前从程序中返回的Connection可能已经被修改过，比如说使用 SqlConnection.ChangeDatabase method 修改当前的连接，此时返回的Connection可能就已经不是连接当前的Connection String指定的Initial Catalog数据库了。所以需要reset一次当前的连接。但是由于所有的额外检查都会增大ADO.net Connection Pool 对系统的开销。

转自:http://www.cnblogs.com/newsea/archive/2012/04/11/2442088.html

\--- end ---