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 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 http://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 (19)

  1. Peter says:

    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.

  2. Angel says:

    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

  3. 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?

  4. Angel says:

    Andres,

    Pre-whidbey creating a new SqlCommand is practically free, I am going to have to assume that if you saw the need to cache them then it must have something to do with your exisiting architecture so I cannot know if Session Pooling will provide the same benefits.

    The reason this feature was implemented is because there is a not insignifcant cost associated with using a SqlCommand in whidbey. On the client we must associate it with a session on the server, on the network layer they need to multiplex the TDS buffer for it and on the server create a new session. We found that the only way to keep performance was to pool these expensive commands (up to ten of them only, no knobs). Now when you open a connection and create a few commands for the first time it will be much slower than in v1.1, but as long as you keep the connection arround (either yourself or by relying on pooling) it will continue to reuse these cached commands. Of course this means that if you use more than 9 commands per connection you are going to notice a performance drop.

    Angel

  5. 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.

  6. Angel says:

    Andres,

    I see, yes completelly different. this caching has nothing to do with text and parameters and it will not affect your current architecture. We are caching the underlying resource that has become the MARS enabled SqlCommand and it is as transparent to the implementation as connection pooling.

    I will get a blog on this going in the next few days, but the basic idea goes something like this:

    open connection

    create command and execute it //command creation is very expensive now.

    close connection //connection goes back to pool, command goes to session pool.

    open connection //same connection as above, retrieved from pool

    create command and execute it //we use the cached command instead of creating a new one. No performance penalty.

    In your scenario you can associate your cached command to the second connection just as you do today and get the benefit of session pooling transparently.

  7. Bill Vaughn says:

    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).

  8. Angel says:

    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.

  9. shafi says:

    what is sqlservercursor? & Ado.net cursors?

  10. geekSpeak says:

    Thanks for attending the webcast. We intended to also have Shawn Wildermuth (ADO Guy) on the webcast…

Skip to main content