网易 
新闻 LOFTER 邮箱 相册 阅读 图书 有道 摄影 企业邮箱 优惠券 云笔记 闪电邮 邮箱大师 印像派 考拉海购
 
更多
博客 
手机博客 博客搬家 博客VIP服务 发现 小组 风格
 
群博客 博客油菜地 博客话题 博客热点 博客圈子 找朋友
 
登录  
 加关注
   显示下一条  |  关闭
温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！立即重新绑定新浪微博》  |  关闭

hurt0759的个人主页

人生常态--跋涉.人生暂态--歇息.

导航

 
 
 
 
 

日志

 
 
关于我
michael

直率.果断.喜欢思考.

文章分类

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

2009-09-08 19:20:27|  分类： work |  标签： |举报 |字号大中小 订阅

  下载LOFTER 我的照片书  |

连接池是可以自动回收无效链接的；
我的机器上的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

源码：

view plaincopy to clipboardprint?
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;";  
        }  
    }  
} 
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;";
        }
    }
}
 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lantianjialiang/archive/2009/02/27/3943206.aspx

  评论这张
 
阅读(2771)| 评论(0)

历史上的今天

评论

<#--最新日志，群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇，下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

我的照片书 - 手机博客 - 下载LOFTER APP - 订阅此博客

网易公司版权所有 ©1997-2018