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.

Answer 2

Just want to a bit clarify to the first point of the accepted answer, from the same resourse

The third thing you need to understand is that multiple active requests are not actually running in parallel, they are running in an interleaving way. From client application point of view, server supports running multiple requests in parallel, and client can pull result from multiple requests at their will. But at server, out of multiple requests sent to server, only one request can actually make progress at a given time, other requests have to wait for this request to give up its connection resource.