ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Q: What is MARS?

A: MARS is a new feature in 2.0 and Sql Server 2005 that allows for multiple forward only read only result sets.

EDIT There is a great article on MARS up at the technet site. It is a must read for basic understanding of MARS. I am specially impressed with the "Transaction Semantics" section 

Q: What is MARS for an developer?

A: MARS allows you to avoid seeing the dreaded “There is already an open DataReader ..” exception when executing on separate SqlCommands associated with the same connection. You can have multiple SqlDataReaders open on a single connection (again, each reader must be started on a new SqlCommand) and you don’t have to worry about Transaction isolation level scope locks.

Q: What is MARS for Sql Server?

A: This has been a thorn in our side for too long. Other databases have supported this functionality for a long time and this has been one of the most important customer requested features to our model. I wish that I could explain how hard it has been to enable this behavior (completely anathema to our implementation) and how proud I am that we have driven this feature to market. That said I am concerned that this feature is going to be misused.

Q: So MARS allows for better performance right?

A: I would like to say no outright, it would make my life much easier. You are trading the expense of opening new connections (almost free with pooling) for the hidden expenses of using MARS (fairly high IMO) The truth is that in some scenarios you will see actual perf improvement by using MARS, mostly thanks to Session Pooling. These scenarios are somewhat contrived and I would highly recomend that you do not modfify existing code or write new ugly code just to attempt to improve performance with MARS.

Q: What is Session Pooling?

A: This question should ideally come after I have explained the hidden costs of MARS, for now let me just explain what it does and I will blog on this with more detail at a later time. If you understand how pooling works then you are familiar with the idea of not destroying a valuable resource just because the user decides to close or dispose it. In this case the SqlCommand has become a valuable resource because we have to associate it with a Sql Server 2005 batch to enable MARS functionality. Session Pooling keeps up to 9 of these valuable disposed SqlCommands in a pool and hands them out the next time you create a command associated with the same connection.

Q: What does Session Pooling mean to a SqlClient developer?

A: It means that you should _NOT_ use more than 9 SqlCommands per connection, if you do you will be forcing us to create/dispose very very expensive SqlCommands and you will definitelly notice the performance drop.

Q: Can I get better performance by combining MARS with ASYNC?

A: IMO, no. In ASP.NET applications you will not get better performance with MARs, and on Winform applications you should not use ASYNC with callbacks (See ASYNC FAQ) I would not mix these two features expecting to get better performance.

Q: So if MARS is not for performance what is it good for?

A: I like to use MARS in two core scenarios, 1) When using MARS results in cleaner looking code and 2) when I am using Transactions and need to execute in the same isolation level scope.

Q: When does using MARS result in cleaner looking code?

A: The quintessential MARS example involves getting a datareader from the server and issuing insert/delete/update statements to the database as you process the reader.

Q: What about the transaction isolation level scope?

A: Same example above but you have a transaction active and you have placed locks on the database. Please note that this behavior was _completelly_ broken before MARS! If you try to fake MARS by opening a second connection under the covers (like native SqlOledb does) you are outside of the transaction scope of the original connection. This is imo the biggest win with this feature.

Q: What are the costs of using MARS?

A: There are a lot of hidden costs associated with this feature, costs in the client, in the network layer and in the server. On the client we run into an issue where creating a new batch is not free, we kind of work around this issue by pooling mars commands but it is still expensive if you don’t used the pooled functionality. On the network layer there is a cost associated with multiplexing the TDS buffer, opening too many batches can be more expensive than opening another connection. On the server all MARS batches run in the same scope, worst case scenario your queries will end up running in the order received.

Q: Can I disable MARS?

A: Yes and No, you can disable MARS with the connection string keyword “MultipleActiveResultSets=false”. Under the covers we still use MARS headers so we will still have some of the overhead associated with MARS. You will not get better performance if you disable MARS, this option was only added to allow the developer to enable backward compatibility with applications that depend on SqlClient throwing an exception when more than one Result Set is used.

Q: What providers/backends support MARS?

A: The only provider/backend combination that supports the MARS feature that I am talking about in this FAQ is SqlClient talking to Sql Server 2005.

Q: Does this mean that none of the other providers support multiple DataReaders?

A: No it doesn’t. The OracleClient managed provider supports multiple DataReaders against all versions of Oracle. The OleDb managed provider is MUCH more problematic. It fully supports multiple DataReaders when talking to Sql Server 2005 when using MDAC 9. (IMPORTANT) In all other cases the OleDb managed provider will FAKE the ability of having multiple active result sets.

Q: So OleDb may fake MARS?

A: Unfortunately yes. In what has to be one of my least favorite “features” the SqlOledb native provider will fake the ability of having multiple active result sets by opening a separate non-pooled connection to the server when unable to provide real MARS behavior. What this really means is that your code may be opening and throwing away connections that can bring your server to its knees during heavy traffic.

Q: Wait a minute; in v1.1 I was not able to open multiple DataReaders with OleDb.

A: This was an artificial restriction on the client, we have removed this restriction going forward since OleDb with the latest version of MDAC will now support true MARS behavior.

Q: What does the removal of this client side restriction mean?

A: It means that you can now shoot yourself in the foot when using the managed OleDb provider. IMHO it means that you should ONLY use SqlClient to talk to Sql Server, the risk of running into this fake MARS behavior is too great. I have seen this “feature” (fake MARS) cost hundreds of thousands of dollars an hour in lost sales as the server was inundated with unnecessary non-pooled connection open requests.


Rambling out. Standard Disclaimer: This post is provided "AS IS" and confers no rights. The information in this post is just my opininon.