Multiple Active Result Sets

Tuesday, June 16, 2009

Multiple Active Result Sets (MARS) is a feature in ADO.NET 2.0. It allows execution of multiple batches against Database on a single connection. Previously, only one batch could be executed at a time against a single connection. But, execution of multiple batches with MARS does not mean like simultaneous execution of operations.

In previous versions of SQL Server; to access multiple result sets using SqlDataReader objects, a separate SqlConnection object must be used with each SqlCommand object. In ADO.NET 2.0 / SQL Server 2005; MARS feature is disabled by default. It can be enabled by adding the "MultipleActiveResultSets=True" keyword pair to your connection string.

MARS can improve performance for many applications, but not all. Eliminating the number of connections open and closed reduces the overall overhead and speeds performance. However, it is important to note there is some overhead associated with starting a MARS connection. This is a worthwhile investment if multiple commands will be executed over the same connection but it can impact performance if a single batch statement is run.

It is also important to note that applications may require some redesign to take advantage of MARS. Simply enabling MARS in the connection string for the first flow would not necessarily improve performance because the connection is till being explicitly open and closed for each command. To see the benefits of MARS this application would need to be rewritten to match the second flow. The amount of rework required depends on a specific applications implementation.

MARS requires ADO.NET 2.0

For more information about MARS, see Multiple Active Result Sets (MARS) in SQL Server 2005 on the MSDN Web site.