How does Multiple Active Result Set (MARS) work in SQL Server?

Question

Just wondering if someone could explain how Multiple Active Result Set (MARS) works in SQL Server as it is not very clear on the MSDN site.

(A) On a MARS connection, do queries execute async or do requests go in async but still execute sequentially?

(B) What is the difference when executing 10 threads that call 1 stored proc concurrently in the following scenarios:

1. using 1 MARS connection for all threads (that implicitly creates 10 sessions)
2. using 10 connections (1 for each thread)?

(C) If using a MARS connection how do you see what SQL each session is executing?

Answer 1

This answers all your questions.

A. Queries execute in parallel, as if multiple clients had connected and executed them, only now you can do this from a single connection. Apparently the actual way its processed is "complicated", but as far as a client is concerned, they run simultaneously.

B. The difference between 1 connection or 10 connections is shared server resources. You only need a single security context for example.

C. Use SQLServer Profiler.