Menu
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.
436
Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.
A batch or stored procedure which starts a manual or implicit transaction when MARS is enabled must complete the transaction before the batch exits. If it does not, SQL Server rolls back all changes made by the transaction when the batch finishes.
Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.
You should enable MARS if you perform operations that require MARS, otherwise those will fail. Whether you or anyone else should write code that uses such operations is subjective.
There are apparently at least two known (potential) drawbacks (from this (1) Team blog):
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"
“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 ]
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
