Multiple Active Result Sets (MARS) in SQL Server

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. The MARS feature is disabled by default. It can be enabled by adding the "MultipleActiveResultSets=True" keyword pair to your connection string and disable MARS by adding the "MultipleActiveResultSets=False" keyword pair to your connection string.

So what is this MARS. This is one of the robost feature of SQL Server which a application can use to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. This was introduced from Microsoft SQL Server 2005 onwards. More information on MARS can be obtained on Microsoft Technet Site which is a very good resource provided by MS. One of the reason to Love Microsoft.

As stated by Microsoft in http://technet.microsoft.com/en-us/library/ms131686.aspx

MARS simplifies application design with the following new capabilities:

  • Applications can have multiple default result sets open and can interleave reading from them.

  • Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while default result sets are open.

Applications using MARS will find the following guidelines beneficial:

  • Default results sets should be used for short lived or short result sets generated by single SQL statements (SELECT, DML with OUTPUT, RECEIVE, READ TEXT, and so on).

  • Server cursors should be used for longer lived or large result sets generated by single SQL statements.

  • Always read to the end of results for procedural requests regardless of whether they return results or not, and for batches that return multiple results.

  • Wherever possible, use API calls to change connection properties and manage transactions in preference to Transact-SQL statements.

  • In MARS, session-scoped impersonation is prohibited while concurrent batches are running.