MARS

I’m talking about SQL Server 2005 feature, not the planet :)

MARS (also known as “Multiple Active Result Sets”) is a nifty feature in SQL Server 2005, but it’s easy to misuse it. By default, MARS is disabled in SQL Server 2005, and you must enable it using the “MultipleActiveResultSets=True” parameter in your connection string.

This is for good reason – there are only two situations where I would recommend using MARS. The first is for transactional consistency. The second is the canonical “doing an update for each row of a result.” And even then, only the first situation would I put into production code.

The thing is MARS can severely impact the performance of your application. Connections are actually relatively cheap – and with connection pooling, they’re almost free. In order for MARS to work, however, the server has to open multiple “sessions” per connection. A session is a server-side object, and SQL Server will pool up to 9 of them for you. Now, I’m obviously not on the SQL Server team, but I am assured that the session are very expensive objects (certainly much more expensive than connections).

So what’s the point of MARS, if opening multiple connections is cheaper than reusing the same one? Transactions are the answer. Here’s sample code for a situation where MARS is invaluable (and if you need these semantics in your application, then the MARS feature alone should convince you to upgrade to SQL Server 2005):

string connectionString = "...;MultipleActiveResultSets=True;...";

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT ...", conn);

conn.Open();
try
{
    SqlTransaction trans = conn.BeginTransaction();
    cmd.Transaction = trans;
    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
            SqlCommand cmd2 = new SqlCommand(
                  "UPDATE ...", conn, trans);
            cmd2.ExecuteNonQuery(); // Important line
        }
    }
}
finally
{
    conn.Close();
}

The line marked “Important line” is the one. Without MARS enabled, that line would throw the dreaded “There is already an open DataReader associated with this command” exception.

Also, that line which creates a transaction is important – without it, it would be far more efficient to simply open a second connection to the database to perform the UPDATE statement. (I suppose if it’s just a one-off test application, it might be OK, and a little cleaner, to just use MARS. But certainly in a production program, I would only use it when transactions were involved).

In summary: if you don’t absolutely 100% need MARS (and the above example shows the only situation I can think of where that is true), then don’t use it. But if you do need MARS, it’s an invaluable feature!