Connection max pool size in SQL Server 2008

connection-poolingsql-server-2008

We are using SQL Server 2008 express. At the moment in the connection string we have the default pool size of 100. At peak times we are receiving time-out error when processing transactions into the DB.

The situation has improved since we have set the pool-size to 200. My question is what is the maximum pool-size you can have in express edition? This edition is run with a GB of RAM.

Also we have the full-blown version of SQL. Can we specify a larger pool-size with the full blown version and if we do increase the pool size, would we have to increase such things as RAM etc to see the benefit?

Thanks in advance,

Matt.

Best Answer

Whenever you have such questions you should go to Maximum Capacity Specifications for SQL Server. Strictly speaking Connection pool size is a client setting but the engine needs to keep open a connection so you can hit server side limits. The Connections per client values is Maximum value of configured connections which implies is configurable through a Server Configuration Option and ideed it is, see user connections:

The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable.

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Sql – Connection Pooling Not working in .Net SQL Server 2008

This is expected behavior. From the documentation for the Audit Login Event Class:

The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.

You should look at the appropriate connection performance counters to see how many connections you really have (on the client and server).

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

Sql – Connection Pooling Not working in .Net SQL Server 2008

Related Topic