MARS - Multiple Active Result Sets support in SQL Server 2005 and above

SQL Server 2005 onwards supports MARS - Multiple Active Result Sets. MARS enables you to reuse an existing connection to perform operations on SQL Server. This makes MARS a viable alternative to server-side cursors with significant performance boosts. As powerful that may seem it has it's drawbacks so care needs to be taken when using it.


Here's an example from msdn:

SqlCommand cmd = conn.CreateCommand();
SqlCommand cmd2 = conn.CreateCommand();
cmd.CommandText = @"select operation_id, operation_code, product_id, quantity 
      from dbo.operations where processed=0";
cmd2.CommandText = @"update dbo.operations set processed=1 
      where operation_id=@operation_id";
SqlParameter opid=cmd2.Parameters.Add("@operation_id", SqlDbType.Int);
reader=cmd.ExecuteReader();
while (reader.Read())
{
   ProcessOperation();
   opid.Value=reader.GetInt32(0); // operation_id
   //notice how we are trying to execute an update query on the second command
   //this is going to fail miserably because
   //we did not set MutipleActiveResultSets=Yes 
   //on the connectionstring
   cmd2.ExecuteNonQuery();
}

By default MARS is not enabled. So reusing the same connection as the example above will throw an exception of type :

InvalidOperationException, There is already an open DataReader associated with this Connection which must be closed first.

Say hello to MARS :

Enabling usage of MARS is as simple as setting MultipleActiveResultSets=Yes on your connectionString, eg:

<connectionStrings>
    <clear />
      <add name="TyppsDB" 
         connectionString="Data Source=typps-pc;Initial Catalog=TyppsDB;
                 Integrated Security=True;MultipleActiveResultSets=Yes" />
 </connectionStrings>

you can now issue multiple commands on the same connectionstring which can result in a performance boost since opening and closing a connection can be expensive.

When retrieving recordsets, the client has to eager load, meaning consume the resultsets immediately as oppossed to executing the command and not reading the data. Not doing so will cause the server-side buffer(Where sql server is hosted) to hold on to the data in memory and tie up resources, locks, threads etc something you want to avoid.

Instead you want the data to stream to the client as fast as possible as the server returns the resultset without the server holding a large recordset in memory and tying up resources. This is not specific to MARS by the way, but when used correctly with eager loading meaning you consume the data as the command is executed  you can benefit from a significant performance boost as you have the ability to execute more commands and retrieve data seamlessly without the overhead of closing and reopening a connection to Sql Server.

myReader = myCommand.ExecuteReader(); while(myReader.Read())
{
 //consume immediately now, don't wait.
}

Any command that is a SELECT, FETCH, READTEXT, RECEIVE, BULK INSERT (or bcp interface) or Asynchronous cursor population can take full advantage of MARS.

What this means is that with MARS enabled, each of these commands listed above are defined in terms of interleaved executions, allowing them to be processed atomically within the same connection but with the ability to interleave, this means they can resume execution from the points where they were suspended if at all suspended.

For instance, INSERT and UPDATE cannot take advantage of MARS. Now, consider a long running INSERT or UPDATE operation followed by a SELECT statement, all executing within the same connection. Such an operation will suspend the SELECT command until the UPDATE or INSERT has completed and then resume the SELECT operation.

Consider reversing the above example where you had a SELECT followed by an UPDATE or INSERT operation within the same connection. Even in this case, if the UPDATE or INSERT command are issued while the SELECT is executing, then the SELECT command will interleave and as such become suspended until the INSERT or UPDATE complete and only after completion of the UPDATE or INSERT operation, will it resume execution.

This is because the SELECT command can take advantage of MARS and has the ability to resume from the point where it was suspended making it an interleaved execution.

Lastly, note that there are some intricacies when using transactions with MARS. You will need to workaround using recommendations provided eg: by using batch-scoped transactions.

References :
http://msdn.microsoft.com/en-us/library/ms345109(v=sql.90).aspx
http://msdn.microsoft.com/en-us/library/ms174377.aspx