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.