注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

hurt0759的个人主页

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

 
 
 

日志

 
 

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

  评论这张
 
阅读(2673)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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