Share via

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

  • Article

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 ado.net 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 https://www.microsoft.com/technet/prodtechnol/sql/2005/marssql05.mspx 

Q: What is MARS for an ado.net 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 ado.net 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.

Comments

  • Anonymous
    September 13, 2004
    Angel,

    Can you explain why you are not supporting MARS inside in SQLCLR?

    It will make porting code harder, and as there's no SqlResultset it won't be possible to have a clean way of having two open SQL statements in the same CLR stored proc. One option is to use SqlClient from SQLCLR (aargh) and the other is to use the cursor API with T-SQL statements (ugggh), which is the same as SqlResultsets but harder.



  • Anonymous
    September 14, 2004
    Peter,
    I don't think I can really explain it other than to reiterate that this feature has been much much harder to do than you would think. I would expect this feature to be considered for a later release of the SQLCLR provider.

    Tahnks for your feedback
    Angel
  • Anonymous
    September 14, 2004
    Angel,

    We are currently caching our SqlCommands in the TLS. Does this 'Session Pool' feature do the same thing? I mean, should we stop caching SqlCommands for Whidbey?
  • Anonymous
    September 15, 2004
    The comment has been removed
  • Anonymous
    September 15, 2004
    OK.

    We reused commands with the .Text and parameters set, to avoid creating the parameters each time, etc. Our key in the cache is the sql statement.

    We reuse the commands in multiple connections, but this new session cache seems to be related to caching commands in the same connection so it's probably different.

  • Anonymous
    September 15, 2004
    The comment has been removed
  • Anonymous
    September 15, 2004
    Angel, you say that the "qintessential" MARS configuration uses the same connection to post changes to the server while you're reading a DataReader. Yes, I can see how this would be appealing, but wouldn't it make more sense to do this processing on the server in TSQL (or CLR)? Moving data to the client to do logic-driven updates has always been frowned on. Now that one can code CLR-based logic the opportunity to do far more sophisticated logic-driven operations on the server makes more sense than ever.

    I'm also somewhat disturbed by the performance hit we're having to take (by default) to participate in MARS when not a single application that exists today uses it. I think the default behavior should disable MARS unless specifically called for.

    Performance in Whidbey is bad enough as it is without adding needless overhead for features we don't need except in special cases (which I have yet to see).
  • Anonymous
    September 15, 2004
    Bill,
    Great comment! I completely agree with you, I am not quite sure I understand the people who dislike stored procedures (I guess I have not met their DBAs...).

    The fact remains that this feature has been specifically requested by some of our most important partners to do exactly this type of logic driven updates.

    The performance hit is not “By Default” per se, since there is no way to avoid it. Setting MultipleActiveResultSets=false does not perform better, it just throws an exception if you attempt to use MARS. Yes this is definitely disturbing but keep in mind that this is only a one time set up cost per connection in pooled scenarios.
  • Anonymous
    June 12, 2006
    what is sqlservercursor? & Ado.net cursors?
  • Anonymous
    June 12, 2006
    nothing
  • Anonymous
    September 18, 2006
    Thanks for attending the webcast. We intended to also have Shawn Wildermuth (ADO Guy) on the webcast...
  • Anonymous
    September 24, 2006
    PingBack from http://dotnetfx3.wordpress.com/2006/09/24/commands-are-from-venus-queries-are-from-mars/
  • Anonymous
    February 12, 2008
    PingBack from http://willsql4food.wordpress.com/2008/02/13/the-multiple-record-set-problem/
  • Anonymous
    May 31, 2008
    PingBack from http://laci.stocknewsdigest.info/netadonetseeingconnectionstring.html
  • Anonymous
    November 24, 2008
    The comment has been removed
  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=75970