ASP.NET中连接池和sql server中的最大连接数

连接池是可以自动回收无效链接的；
我的机器上的sql server的max_connection 是32767；
所以我写了个测试程序来测试连接池的最大连接数就是max_connection，但是由于连接池自己的回收机制，总是不能如愿，但是发现连接池的Max Size不管设置多大，不同的机器到最后总是一定的，比方说说是250或336等；每建立一个和连接池的链接，会自动生成一个sqlserver.exe和之对应通信；
只要是连接池的连接字串一样，不管你在哪里链接，都会使用同一个连接池来响应的；
所以说一个连接池的最大连接数目是很小的；sql server的理论最大连接数是32767，但是能不能达到我不知道；

 

参考：

http://www.sqljunkies.com/WebLog/sqldude/archive/2004/06/14/3146.aspx

http://msdn.microsoft.com/zh-cn/library/8xx3tyca(en-us,vs.71).aspx

http://msdn.microsoft.com/zh-cn/library/ms254503.aspx

 

源码：

using System; using System.Data.SqlClient; using System.Diagnostics; using System.Runtime.InteropServices; namespace ConsoleApplication1 { class Program { PerformanceCounter[] PerfCounters = new PerformanceCounter[10]; SqlConnection connection = new SqlConnection(); static void Main() { Program prog = new Program(); // Open a connection and create the performance counters. prog.connection.ConnectionString = GetIntegratedSecurityConnectionString(); prog.SetUpPerformanceCounters(); Console.WriteLine("Available Performance Counters:"); // Create the connections and display the results. prog.CreateConnections(); Console.WriteLine("Press Enter to finish."); Console.ReadLine(); } private void CreateConnections() { // List the Performance counters. WritePerformanceCounters(); CreateManyConnetcion(3276800); // Create 4 connections and display counter information. SqlConnection connection1 = new SqlConnection( GetIntegratedSecurityConnectionString()); connection1.Open(); Console.WriteLine("Opened the 1st Connection:"); WritePerformanceCounters(); SqlConnection connection2 = new SqlConnection( GetIntegratedSecurityConnectionString()); connection2.Open(); Console.WriteLine("Opened the 2nd Connection:"); WritePerformanceCounters(); SqlConnection connection3 = new SqlConnection( GetIntegratedSecurityConnectionString()); connection3.Open(); Console.WriteLine("Opened the 3rd Connection:"); WritePerformanceCounters(); SqlConnection connection4 = new SqlConnection( GetIntegratedSecurityConnectionString()); connection4.Open(); Console.WriteLine("Opened the 4th Connection:"); WritePerformanceCounters(); SqlConnection connection5 = new SqlConnection( GetIntegratedSecurityConnectionString()); try { connection5.Open(); } catch (System.Exception ex) { Console.WriteLine(ex.ToString()); } Console.WriteLine("Opened the 5th Connection:"); WritePerformanceCounters(); connection1.Close(); Console.WriteLine("Closed the 1st Connection:"); WritePerformanceCounters(); connection2.Close(); Console.WriteLine("Closed the 2nd Connection:"); WritePerformanceCounters(); connection3.Close(); Console.WriteLine("Closed the 3rd Connection:"); WritePerformanceCounters(); connection4.Close(); Console.WriteLine("Closed the 4th Connection:"); WritePerformanceCounters(); } private void CreateManyConnetcion(int number) { for (int i = 0; i <= number; i ++) { SqlConnection connection1 = new SqlConnection( GetIntegratedSecurityConnectionString()); try { connection1.Open(); } catch (System.Exception ex) { Console.WriteLine(ex.ToString()); } Console.WriteLine("Opened the " + i +"st Connection:"); WritePerformanceCounters(); if(i > 32765) { Console.Read(); } } } private enum ADO_Net_Performance_Counters { NumberOfActiveConnectionPools, NumberOfReclaimedConnections, HardConnectsPerSecond, HardDisconnectsPerSecond, NumberOfActiveConnectionPoolGroups, NumberOfInactiveConnectionPoolGroups, NumberOfInactiveConnectionPools, NumberOfNonPooledConnections, NumberOfPooledConnections, NumberOfStasisConnections // The following performance counters are more expensive to track. // Enable ConnectionPoolPerformanceCounterDetail in your config file. // SoftConnectsPerSecond // SoftDisconnectsPerSecond // NumberOfActiveConnections // NumberOfFreeConnections } private void SetUpPerformanceCounters() { connection.Close(); this.PerfCounters = new PerformanceCounter[10]; string instanceName = GetInstanceName(); Type apc = typeof(ADO_Net_Performance_Counters); int i = 0; foreach (string s in Enum.GetNames(apc)) { this.PerfCounters[i] = new PerformanceCounter(); this.PerfCounters[i].CategoryName = ".NET Data Provider for SqlServer"; this.PerfCounters[i].CounterName = s; this.PerfCounters[i].InstanceName = instanceName; i++; } } [DllImport("kernel32.dll", SetLastError = true)] static extern int GetCurrentProcessId(); private string GetInstanceName() { //This works for Winforms apps. string instanceName = System.Reflection.Assembly.GetEntryAssembly().GetName().Name; // Must replace special characters like (, ), #, /, // string instanceName2 = AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(', '[') .Replace(')', ']').Replace('#', '_').Replace('/', '_').Replace('//', '_'); // For ASP.NET applications your instanceName will be your CurrentDomain's // FriendlyName. Replace the line above that sets the instanceName with this: // instanceName = AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(','[') // .Replace(')',']').Replace('#','_').Replace('/','_').Replace('//','_'); string pid = GetCurrentProcessId().ToString(); instanceName = instanceName + "[" + pid + "]"; Console.WriteLine("Instance Name: {0}", instanceName); Console.WriteLine("---------------------------"); return instanceName; } private void WritePerformanceCounters() { Console.WriteLine("---------------------------"); foreach (PerformanceCounter p in this.PerfCounters) { Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue()); } Console.WriteLine("---------------------------"); } private static string GetIntegratedSecurityConnectionString() { // To avoid storing the connection string in your code, // you can retrive it from a configuration file. return @"Data Source=192.168.10.3/SQLEXPRESS;Connection Lifetime =1;Initial Catalog=test;User ID=sa;Password=1;Max Pool Size=32769;Min Pool Size=0;"; } private static string GetSqlConnectionString() { // To avoid storing the connection string in your code, // you can retrive it from a configuration file. return @"Data Source=192.168.10.2/SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=1;"; // "Initial Catalog=AdventureWorks"; } private static string GetSqlConnectionStringDifferent() { // To avoid storing the connection string in your code, // you can retrive it from a configuration file. return @"Data Source=192.168.10.1/SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=1;Max Pool Size=100;Min Pool Size=0;"; } } }