Another developer here was testing the latest revision of our software and he had 5 different threads that were all trying to open connections to the database. There was no error, but the 5 threads were all waiting for the Connection.Open() call to return. Which lead us to wonder if we've run out of connections in our connection pool.

The code uses Entity Framework 4 and I believe that uses its own internal connection pooling. Does anybody know how to control what the number of connections in the EF connection pool?

asked 06 Nov '13, 10:45

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 07 Nov '13, 09:38


Connection pooling to the database is controlled by the ADO.NET Provider itself, not by EF.

To control the availability/size of the connection pool in the ADO.NET provider, see the 'Pooling'/'Max Pool Size'/'Min Pool Size' options on the connection string:

http://dcx.sybase.com/index.html#sa160/en/dbprogramming/connection-pooling-dotnet-dev.html

The default maximum size of the pool is '100'.


I think we'll need some more details about your test in order to comment on why the connection threads might be 'hung'. Can you post a sample of the code you're using?

permanent link

answered 06 Nov '13, 12:07

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

Thanks, Jeff. We don't know for sure that the threads are hung, only that the other developer thinks that they might be. We will have to do some testing. I've never seen the threads in question hang since we've switched to SA, but anything is possible.

(06 Nov '13, 12:13) TonyV

We've done more testing and the threads are not hanging. We never changed the default, so I'm pretty sure there are a maximum of 100 connections in the pool. I know that I've seen as many as 10 connections on the database during testing I've done in the past without issue.

In all likelihood, the other developer's system was probably waiting for some background operation in the database to complete. I'm not going to worry about this.

(07 Nov '13, 09:42) TonyV

The multiprogramming level (-gn) in SQLA can become a limitation if set to low, if no worker thread is available no new connection will be served. Anyway I don't think that this is your problem here, check if the threads are blocking each other then using a common resource e.g. the connection object in .net.

permanent link

answered 07 Nov '13, 02:48

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

2

A sample of the connection string would also be useful.

Each unique connection string creates a new pool. So "UID=DBA;PWD=sql", "PWD=sql;UID=DBA", and "UserID=DBA;pwd=sql" would each create a separate pool (in this example, there would be 3 pools). In order to reuse a connection that has been pooled, you must use the exact same connection string as was used to create the pool. .NET connection pooling is a bit different from SQLA connection pooling. For example, the ConnectionName parameter is not ignored in .NET pooling.

I have run tests on the robustness of .NET connection pooling. In 11 minutes, I can do 250 iterations of 100 threads "simultaneously" connecting using shared memory to a server using 10 different pools (10 threads per poo), where each connection runs 5 SQL queries. That's 25,000 connections.

You can use sa_conn_info() from DBISQL to monitor your test connections.

(07 Nov '13, 09:41) JBSchueler
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×69
×14

question asked: 06 Nov '13, 10:45

question was seen: 12,791 times

last updated: 07 Nov '13, 09:42