CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-mars
Samiksha
Samiksha

Reputation: 6182

Disadvantages of MARS (Multiple Active Result Sets)?

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.

Upvotes: 95

Views: 51395

Answers (4)

Scott Langley
Scott Langley

Reputation: 61

The current Entity Framework Core documentation on transactions mentions savepoints being incompatible with MARS:

https://learn.microsoft.com/en-us/ef/core/saving/transactions#savepoints

Warning

Savepoints are incompatible with SQL Server's Multiple Active Result Sets (MARS). Savepoints will not be created by EF when MARS is enabled on the connection, even if MARS is not actively in use. If an error occurs during SaveChanges, the transaction may be left in an unknown state.

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97791

Upvotes: 7

RobS
RobS

Reputation: 9422

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 ]

Upvotes: 64

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

depending on what? there are no real disadvantages.

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

Upvotes: 1

Related Questions