Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Fri, 14 Jan 2005 06:57:00 GMT

Excellent post Angel - and good to see you back.

I have a few questions -

a) Would it make sense to keep the session pooling parameters configurable like the connection pooling parameters? Or are they configurable?

b) What would then be the best practices for making sure sessions are pooled effectively? Short executing commands? No more than 10? What could one do in the data layer to prevent anymore than 10 concurrent executing commands on the same connection - does it make sense to expose a SqlCommand.CurrentCommandCount property or sump'n like that?

c) Is it 10 logical connections per physical connection or 10 logical connections in all?

d) I am assuming that if we took a physical connection, and really tired it with too many concurrent SqlCommands, then the physical connection goes back to the connection pool. UserB comes along merrily and tries to fill a dataset - he won't notice any difference because all those commands are done and over with? Or will he see a performance hit too?

e) I understand why physical connections are expensive, but why are logical connections such a valuable resource?

f) The session pool broker - does that run on another thread? Would sleeping my thread for 0 between too many fast executing commands help?

g) Why do you have to blog at 2 AM EST everyday LOL !!

- Sahil

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Fri, 14 Jan 2005 07:04:00 GMT

Okay this is yet another funky question - This question might make zero sense whatsoever - but I have to ask it LOL.

Can I do two DataAdapters.Fill using the same open SqlConnection, two different threads, two different commands, but at the same time - if MARS is enabled?

Or Should I? (This is a test hard to simulate, but I know with the introduction of MARS someone will run into it).

How would ADO.NET 2.0 handle such a situation?

.::Szőkelizer 120::.

RIO - Randektív Informatikai Oldal — Fri, 14 Jan 2005 12:40:00 GMT

<p><ul><li><a href="http://weblogs.asp.net/angelsb/archive/2005/01/13/352718.aspx" target="_blank">MARS és session pooling</a>, avagy mi a különbség a session pool és a connection pool között, avagy mi is

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel — Fri, 14 Jan 2005 10:54:00 GMT

Sahil,
Great questions, I had some code that I was going to post with this to make things a little clearer, unfortunatelly this post was becoming too long for code.

a)make session pooling configurable.
I _dislike_ knobs. Of course they make my job a lot harder so I am very biased, but in general the more knobs we add to a feature the more things we are going to get wrong. This feature is designed specifically to ensure that asp.net applications that use MARS _responsably_ are not slowed down by constant logical connection creation. It does this job well. The 10 logical connection limit should be 5x more connections as a normal application will use, using more than 10 is going to be problematic. Remember that this only applies to SqlCommands that are executing at the same time.

b)what are best practices for this feature?
IMO most applications don't need MARS, best practice would then be to not use it when you don't need it. For my opinion on when MARS makes sense take a look at the MARS FAQ blog post I mention in this post. It does not make sense to expose this information as a property, you can always get this information from the server with "select * from sys.dm_exec_connections where session_id=@@spid".

c)Is it 10 logical connections per physical connection?
Yes, as shown in the sys.dm_exec_connections table the logical connections belong to an individual physical connection. We will cache up to ten logical connections per physical.

d)what happens when the physical connection goes back to the connection pool?
This is the beauty of this feature, when you close the connection and it goes back to the connection pool it will retain the logical connections. the next time you use this connection it will behave faster than the first time arround.

e)why are logical connections such a valuable resource?
They are not nearly as expensive as physical connections, but as soon as you use two SqlCommands at the same time we get the cost of creating a new logical connection. As soon as we did our first perf pass on MARS without session pooling we realized that throwing these logical connections away was not an option.

f)The session pool broker ?
fortunatelly no such thing, one of the benefits of keeping things simple.

Can I do two DataAdapters.Fill using the same open SqlConnection, two different threads.
Man this is the question I always dread. The easy answer of course is "yes", this is in fact one of the scenarios that we originally envisioned for this feature. The thing that makes this a hard question is that _under certain circumstances_ it actually makes sense to do so. A physical connection costs about 40k (I am going with sql server 2000 numbers here, may be way off for 2005) logical connections are much cheaper so by using MARS you can save memory resources. If instead you use the tried and true current model, open a connection in each thread, you will almost certainly execute faster but consume more memory.

I am a huge fan of the "open a connection in each thread" model and I have real concerns that trying to use MARS in this way is going to create a lot of ugly hard to debug code that runs into threading and transaction issues. I would not use a connection in multiple threads at all. Take a look at my MARS FAQ to see more of my concerns.

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Fri, 14 Jan 2005 14:18:00 GMT

Angel,

I completely agree with your feelings about MARS. In my eyes, there is nothing that MARS gives you that you cannot acheive with two open connections.

Anyway, tell me a reason why should I use MARS. Under what circumstance (other than 40k memory saved - big deal), or specific situation is MARS so kick-ass that I should use it .. over two Sql connections.

The situation that you dread, about two concurrent DataAdapters.Fill - is the hard to debug situation that we will see in newsgroups - just wait and see :).

BTW, the article you mentioned is awesome. I am coming up with a "Best ways to use MARS" blog post sometime later in the day and will trackback to this post over here. I think such a thing would be ultra valuable :).

- SM

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel — Fri, 14 Jan 2005 18:31:00 GMT

Sahil,
In my mind there are two main scenarios that have been enabled:

1)You would need to use MARS in the following scenario:

Executing under the same isolation level:
Open connection and lock data in the database (with a transaction for example), try to access the locked data (datareader) and update it at the same time. Without true MARS the locks that are held by the first connection would stop the second connection from updating.

2)A lot of customers have requested that we enable the following scenario:

Update database while procesing SqlDataReader.
Create reader and start procesing the results, use a second command to update/insert/delete as you go.

It is not necesary to use MARS on the second scenario, but it was one of the biggest feature requests from v1.0. Quite frankly it results in clean easy to manage code and it may be worth it for that reason.

Two scenarios that are newly enabled but that I would not recomend would include using SqlCommands in different threads as we discussed and using MARS with Async commands. There is nothing wrong with mixing Async and MARS but in my experience you won't get the performance gains that you would expect.

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Fri, 14 Jan 2005 21:45:00 GMT

#1 - Yes in certain isolation levels .
#2 - Yes that will result in cleaner code.

That helps .. :) .. your answers as usual are very enlightening.

ADO.NET 2.0 should ship with two nipple clips that should give the programmer a 110V jolt anytime he tried doing MARS on two threads on the same connection.

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Bill — Fri, 14 Jan 2005 23:13:00 GMT

Excellent post my friend - glad to see you blogging again ;-)

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel — Fri, 14 Jan 2005 23:22:00 GMT

Bill,
Great to hear from you, I tried sending you an email when I got back from vacation but it bounced right back-at-me, drop me a note some time so I can keep in touch.

As I mentioned in the title this is stuff you don't need to know, unfortunatelly in my experience it is always this type of under the covers work that ends up having the most issues. By explaining exactly how we have implemented this I am hoping that whatever issues arrise come as no surprise to the experts.

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Sat, 15 Jan 2005 20:43:00 GMT

Hey Angel - unrelated question-

Can you point me to some good documentation around the SqlCommand.NotificationAutoEnlist property? It seems hella interesting and somehow connected to Sql Server 2005 Service Broker (I could be completely wrong here) - but some guidance on that would be ultra awesome :).

Regards,

- Sahil

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel — Sun, 16 Jan 2005 08:13:00 GMT

Sahil,

This is the guy you want to bug for more info on Notifications :
http://blogs.msdn.com/sushilc/

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Sun, 16 Jan 2005 09:17:00 GMT

Sorry yet another question - Is SqlDataReader.Close in any way connected with the number of session pools you can have active at a given time??

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel — Sun, 16 Jan 2005 19:39:00 GMT

Sahil,
Sorry not sure I understand the question. there is one pool per physical connection so the number of session pools does not change.

If you are asking whether the number of sessions in a pool change on SqlDataReader.Close then ... it is possible. If you have more than ten cached logical connections and you are using them all, opening a SqlDataReader will create the eleventh session. When you close the datareader the logical connection will be freed and since we already have 10 cached sessions it will be discarded.

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Sahil Malik — Mon, 17 Jan 2005 18:59:00 GMT

Yeah Angel you understood my question without me typing it right.

I was trying to establish a simili between

Connection.Close <---> Physical Connections
SqlDataReader.Close <----> # of sessions in a pool.

Thanks :)

re: Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel — Mon, 17 Jan 2005 19:02:00 GMT

Ah, I see.

Yes, the SqlDataReader.Close does free up a logical connection. If we were not caching them you would be able to see it disapear with the server query. Since we are caching up to 10 logical connections you will not see this until you go over our cache.

SQL Server High Number of User Connections found on PERFMON, how to resolve it?

SQL Server Storage Engine & Tools (SSQA.net) — Tue, 03 Jun 2008 11:39:42 GMT

I was working with one of the user project to monitor the Performance Audit of their SQL Server estate.

Angel Saenz Badillos Ado net 2 0 Mars Session Pooling aka stuff you | pool toys

Angel Saenz Badillos Ado net 2 0 Mars Session Pooling aka stuff you | pool toys — Thu, 18 Jun 2009 12:06:43 GMT

PingBack from http://pooltoysite.info/story.php?id=2498