Disadvantages of MARS (Multiple Active Result Sets)?

Question

Does anyone know of any disadvantages of MARS (Multiple Active Result Sets)? Does anyone know of any reason why one should avoid using MARS, like cases where cursors are more useful then MARS.

Peloquin

Answer 1

There are apparently at least two known (potential) drawbacks (from this (1) Team blog):

1. Obviously this can cause potential problems for any legacy systems which weren't designed to run against a MARS enabled design - "existing code optimized to run in the non-MARS world may show a slight performance dip when run un-modified with MARS"

2. “With MARS you can send multiple multi-statement batches to the server. The server will interleave execution of such batches, which means that if the batches change server state via SET or USE statements, for example, or use TSQL transaction management statements (BEGIN TRAN, COMMIT, ROLLBACK), both you and the server can get confused about what your actual intent is.”

I've yet to try out a MARS enabled design, but I'm coming very close to doing so on my current project. We have a slight issue with competing (and sometimes dependent) query operations (like lazy loading configuration data out of the same database that an active recordset is executing).

There's more information on the MSDN site (2) here

[ (1) https://web.archive.org/web/20190911155929/https://blogs.msdn.microsoft.com/sqlnativeclient/2006/09/27/using-mars-with-sql-native-client/ ]
[ (2) http://msdn.microsoft.com/en-us/library/ms131686.aspx ]

Mai

Before applying @Mai great solution, I'd read this article first to assure you can't solve this issue with the following solution: devproconnections.com/development/… OR adding .ToList() to the end of your DB call, which solved my problem. Btw, Thanks RobS for the great advice, MARS will come in handy in the future. :) – Bridgettebridgewater

Answer 2

• It takes slightly more server resources than doing one connection at a time.
• You have to be running SQL Server 2005 or later. So that can be a problem in legacy (ack!) environments.

Humphrey

you cna use SqlTransaction just fine. – Celanese

How much is "slightly more server resources"? Can you put an estimate on it in terms of memory or CPU? I am interested in this mainly because I am in an Azure environment where resources are proportional with the cost of the server. – Melisma

@MathiasLykkegaardLorenzen Did you ever discover the answer to your question? – Tatouay

Answer 3

depending on what? there are no real disadvantages.

they don't support Transaction savepoints. but i don't think of this as a disadvantage.

Celanese

That's a major disadvantage! The whole point of transactions is rollbacks and save points, the lack of this makes MARS pretty useless in professional applications. Anything that can potentially cause data corruption or prevent data recovery should be avoided! – Caracul