Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)
Mars session pooling,
Well, it has been a long time since my last blog and I am finding it very hard to get this one started. You start thinking that you need to have something really good to start up the train again or you might as well not bother. I am afraid that session pooling does not fall under the “really good” category. It is transparent to users and nothing bad happens if session pooling does not work. I could argue that it is important to know how this feature works so as to maximize MARS performance scenarios (this is true) but as I mentioned in a previous blog I am NOT a big fan of using MARS for performance, it is possible but too painful for the gains you will find IMO.
So… it doesn’t really affect you, you will probably not be able to tell it’s there, and I don’t recommend using this knowledge to your advantage, why bother reading the rest of this blog? Well I think that this is one of those features that you can use to out geek the competition. I would start the conversation with something like “so you _think_ you know how that works do you?” and go from there.
If you don’t know what MARS is I would scroll start with this excellent article to get an idea of how things work https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp and continue reading down this blog to my MARS faq https://weblogs.asp.net/angelsb/archive/2004/09/07/226597.aspx
Onwards to business:
<start of blog>
Q: what is ado.net 2.0 SqlClient Session Pooling?
A: When you are using SqlClient to connect to Sql Server 2005 we will pool up to ten sessions per connection.
</end of blog>
Rambling out. Standard disclaimer: All the information in this blog is posted “AS IS” and confers no rights.
Huh? What is that you say? Well I never! Of course it makes sense. Don’t let little things like the fact that we have effectively made up new definitions for “connection” and “session” confuse you. The word buzzes around Redmond, “the ado.net team has been taken unawares. They cannot cope with the situation” - one hates to draw attention to oneself after all. Right Ho then. Connect to a Sql Server 2005 with your favorite query analyzer tool and bring up the sys.dm_exec_connections table.
Q: What is a session?
A: As far as I am concerned it is a row in the Sql Server 2005 “sys.dm_exec_connections” table.
Q: What different types of sessions are there?
A: As far as I am concerned there are two types. Sessions with a “Parent_connection_id” =null and their offspring.
Q: What does it mean when a session has a “Parent_connection_id” that is null?
A: It means that this session is a “physical connection”. Every SqlConnection needs to be associated with a single “physical connection”
Q: How do I know which “physical connection” is associated with a SqlConnection?
A: Simple, sys.dm_exec_connection.session_id = @@spid (ah! Now we are getting somewhere)
Q: So what does it mean for a session to have a Parent_connection_id that is not null?
A: It means that it is a “logical connection”. Every SqlCommand _that is executing_ requires a “logical connection” that has a session_id equal to the session_id of the "physical connection". All of the "logical connections" will have the same GUID parent_connection_id. I have no idea how this can be related to the physical connection, but there it is.
Q: So if I create five SqlCommands associated to a single SqlConnection I will see five “logical connections” in the sys.dm_exec_connections table?
A: No, this is actually the tricky part. Sql Server 2005 does not care how many SqlCommands you have in the client, it only cares about how many are executing at the same time (MARS).
Q: Is there a cost associated with creating a “logical connection”?
A: Yes, definitely. “logical connections” are a valuable resource. That is why we pool them.
Q: So how does session pooling work?
A: It is very similar conceptually to connection pooling, but without all the bells and whistles. When you create a SqlConnection we will create a physical connection and a logical connection. The logical connection goes in a “pool” (it would be better to call it a cache). When you attempt to execute a SqlCommand we will look to see if we have a logical connection available, if we do we will hand it out to you and you can execute. If we don’t then we create a new logical connection and hand that out to you. When your SqlCommand completes executing we will return the logical connection to the pool _as long as_ we have less than ten logical connections.
Q: What does this mean from a practical standpoint?
A: A person after my own heart! This means that if I ExecuteReader on five SqlCommands associated with the same SqlConnection at the same time I will see one physical and five logical connections when I execute “select * from sys.dm_exec_connections where session_id=@@spid” and that
I will continue seeing the same results after closing all the datareaders.
Q: Why cache ten logical connections?
A: Well, ten logical plus one physical makes eleven, that sounded like a good round number to us I guess. Realistically this is a number that seems to us to be more than sufficient. The downside is that if you use more than ten SqlCommands at the same time using the same SqlConnection you will see a (possibly) substantial slowdown. (just don’t do it!)
Q: Do the logical connections get cleaned out when I close the connection?
A: As long as you are using pooling the connection is not really closed and the logical connections are not disposed.
Comments
- Anonymous
January 13, 2005
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 - Anonymous
January 13, 2005
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? - Anonymous
January 13, 2005
The comment has been removed - Anonymous
January 13, 2005
<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 - Anonymous
January 13, 2005
The comment has been removed - Anonymous
January 14, 2005
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. - Anonymous
January 14, 2005
The comment has been removed - Anonymous
January 14, 2005
Excellent post my friend - glad to see you blogging again ;-) - Anonymous
January 14, 2005
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. - Anonymous
January 15, 2005
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 - Anonymous
January 15, 2005
Sahil,
This is the guy you want to bug for more info on Notifications :
http://blogs.msdn.com/sushilc/ - Anonymous
January 15, 2005
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?? - Anonymous
January 16, 2005
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. - Anonymous
January 17, 2005
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 :) - Anonymous
January 17, 2005
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. - Anonymous
June 02, 2008
I was working with one of the user project to monitor the Performance Audit of their SQL Server estate. - Anonymous
June 17, 2009
PingBack from http://pooltoysite.info/story.php?id=2498