April 23, 2009 at 12:02 pm
This question is kind of a hybrid SQL / .NET question but I'm hoping some of you might have some insight.
If have a connection string that contains a min pool size and a max pool size, my understanding is it will create the pool of size (min pool size) at the time the first connection is made, and that if those connections are all in use when a new connection is requested, it will create new connections to add to the pool until the pool is at its max pool size.
I'm not seeing this.
What I'm seeing is, the pool is created of size min pool size as I expect - but as soon as I need more connections than the min pool size, I'm seeing a lack of response. In my activity monitor I never see more than min pool size connections to this database from the caller.
My connection string has the following settings:
Data Source=(myserver);Initial Catalog=(my database); User ID=(my uid);Password=(my pw);Pooling=true;Max Pool Size=100;Min Pool Size=5;Integrated Security=false;Persist Security Info=false;Connect Timeout=30
With this connection string, I have problems with two many simultaneous queries.
If I change the connection string only by making Min Pool Size=10, I don't see any problems. But since they have the same max pool size it seems to me that they should not have different behavior except in how big the pool size is to start?
Is it possible that creating a new connection outside the pool takes longer than the connect timeout?
Thanks in advance
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
April 23, 2009 at 12:25 pm
I don't have the exact answer but just information if one of your connection is a transaction from the application layer SQL Server 2005 and below makes one invalid per ANSI SQL definition. If there is no transaction then it maybe related to objects not disposed.
Kind regards,
Gift Peddie
April 23, 2009 at 12:34 pm
Nope, no transactions in these queries - they are all reads only
I don't think there would be time yet for objects not to be disposed -this happens as soon as I start up.
Some more detail:
Application kicks off about 8 different asynchronous requests to a WCF service. Each request creates a connection to the same connection string and runs a select query.
In the min pool size = 5 example, only some of the requests return a response.
In the min pool size = 10 case, all return a response.
I can't guarantee which of the queries are faster than the other, but I do know that they are all kicked off together so the earlier ones wouldn't be done (or ready for disposing) by the time the next one is requested.
It just seems to me that the correct behavior given my connection pool settings would be to create additional connections for the pool, since the max pool size is 100. The min pool size shouldn't cause any problems.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
April 23, 2009 at 12:41 pm
When you are running Asynch processes in .NET there are ADO.NET Asych classes that are defined if your WCF service is not running these with 8 stored procedures SQL Server will close the connection because it maybe considered abandoned.
I have not used WCF but you need to see how those classes are consumed with WCF service because it is different from Web Service.
Kind regards,
Gift Peddie
April 23, 2009 at 1:02 pm
I'm sorry, but I really don't understand what you are trying to say. What do stored procedures have to do with it?
Since the connection pool is maintained on the WCF side I really don't see what the fact that the calls are asynchronous has to do with anything. The code to create the connection is in a class library that just happens to be instantiated from a service method. If anything I would expect multiple pools, not one pool that doesn't grow.
I will post the question on a WCF forum as well just in case, but if anyone else has any ideas (or can clarify what GP is talking about) I'd be appreciative.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
April 23, 2009 at 2:01 pm
The codes in the links below says standard connection pooling may not be valid for what you are doing because when operation is long most standard definition may not apply. And yes it is a good idea to post at the WCF forum.
http://code.msdn.microsoft.com/WCFConnectionPooling#CommentsAnchor
Kind regards,
Gift Peddie
April 23, 2009 at 2:23 pm
Interesting. That first link is actually not about SQL connections in WCF but basically creating a pool of connections TO the WCF service, more like a "proxy pool" than a SQL Connection pool.
I'm trying to determine if the second link applies, I'm not explicitly using the CCR AFAIK since the asynchronicity is handled via Silverlight.
Thanks for the links.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
April 23, 2009 at 2:55 pm
If you are in Silverlight I am closer to the correct implementation than your current solution, I found about two alternatives that can be used. Read the long conversation in the second link.
http://msdn.microsoft.com/en-us/magazine/cc794279.aspx
http://www.west-wind.com/weblog/posts/546995.aspx
Kind regards,
Gift Peddie
April 23, 2009 at 7:05 pm
I actually figured out what was happening by the scientific method, it has nothing to do with Silverlight but does have to do with the service layer and the difference between DatabaseFactory.CreateDatabase() and using the SqlDatabase constructor in the Enterprise Library data access block.
With a static connection string you can use DatabaseFactory.CreateDatabase() which properly manages the pool even in the Silverlight + WCF scenario.
If you have a dynamic connection string you are forced to instantiate the database object using the SqlDatabase constructor because you can't instantiate it from a connection string name - and this method does not properly manage the pool when called from the context of a WCF service. Even using the exact same connection string, it will work if you use the factory and not work if you instantiate the database object directly.
I have some ideas on how to resolve this, will just take a little extra plumbing in the service.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
April 23, 2009 at 7:19 pm
With a static connection string you can use DatabaseFactory.CreateDatabase() which properly manages the pool even in the Silverlight + WCF scenario.
If you have a dynamic connection string you are forced to instantiate the database object using the SqlDatabase constructor, which does not properly manage the pool.
Actually it is related to creating a database at the point of connection and adding objects and connecting to an existing database the context is not the same.
Kind regards,
Gift Peddie
April 24, 2009 at 8:06 am
Yes, clearly - but I think you're still missing the significance of the issue.
Since the context is different, you would think it would *create its own connection pool*. But it doesn't, because it clearly *can* access connections in the other created pool, because when I have a sufficiently large min pool size it works - if it couldn't get connections from the other context AT ALL then the application would not work AT ALL regardless of the min pool size because only the FIRST service method call that ever used the connection string would ever function. But that's not what I am seeing.
What is probably happening is that it tries to get connections from the pool in the other context - which it normally can if they are not all in use.
But, if they are all in use, it cannot grow the pool because it probably has an access exception for that particular operation that it doesn't have for just using the connection.
The "additional plumbing in the service" that I referred to in my previous post was to create a common database object (or pool of database objects) for each dynamic connection string, that the service method can access instead of using the SqlDatabase constructor every time I get one. In that way the context would be the same each time.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
April 25, 2009 at 8:10 am
Since the context is different, you would think it would *create its own connection pool*. But it doesn't, because it clearly *can* access connections in the other created pool, because when I have a sufficiently large min pool size it works - if it couldn't get connections from the other context AT ALL then the application would not work AT ALL regardless of the min pool size because only the FIRST service method call that ever used the connection string would ever function. But that's not what I am seeing.
Pool Fragmentation Due to Integrated Security
There are many reasons for your problem pool fragmentation is one reason check below for other known issues.
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
Kind regards,
Gift Peddie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply