MARS (Multiple Active Result Sets)

MARS for (Multiple Active Result Sets) is a new feature supported in SQL Server 2005 Data access that allows multiple requests to interleave in the server. It allows execution of multiple requests within a single connection through allowing request to run and, within the execution, allows another requests to execute. However execution of MARS is interleaving and not performing parallel execution.

The MARS infrastructure allows multiple batches to execute in an interleaved fashion, though execution can only be switched at well-defined points. As a matter of fact, most statements must run atomically within a batch. The following statements are supported for MARS

• SELECT


• FETCH


• READTEXT


• RECEIVE


• BULK INSERT (or BCP interface)

The behavior of MARS with more than one request running under the same transaction under different scenarios is explained in detail

Conclusion

Support for Multiple Active Result Sets (MARS) in Microsoft SQL Server 2005 increases the performance tuning options of the application. It brings the cursor-programming model closer together with the performance and power of the default-processing mode of the relational engine. However it cannot be considered as a replacement for cursor programming.