Multiple Active Result Sets (MARS)

Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.

(source - Microsoft)

How MARS Works

The MARS architecture is based on multiplexing or interleaving. Because I'm from an electrical engineering background I'll be using the term multiplexing as it is closer to my heart. Simply put multiplexing means that the input signals are processed one by one and not in parallel based on a clock count. Same applies to MARS connections, only the clock count is replaced with well defined points. Most statements must be run atomically in a batch, which means that they must fully complete before another statement can be run. Statements that don't need to run atomically can be multiplexed before they finish thus enabling another MARS statement to execute.

These multiplexing-enabled statements are:

• SELECT
• FETCH
• RECEIVE
• READTEXT
• BULK INSERT / BCP
• ASYNC CURSOR POPULATION

The best way to describe is is with an example: Say you are retrieving 1 million rows. In the middle of the retrieval an INSERT statement comes in via MARS. Because a SELECT can be multiplexed the retrieval is stopped and the INSERT is performed. Because an INSERT can't be multiplexed, it must fully complete. When it does the SELECT is resumed. A little later in the retrieval an UPDATE statement comes in. So again the SELECT is stopped and the UPDATE is fully executed since it also can't be multiplexed. After the UPDATE fully completes the SELECT is able to finish.

However if we are updating 1 million rows first and in comes a SELECT via MARS, the UPDATE will fully finish before the SELECT can be started.

There are a few exceptions worth mentions in the multiplexing-enabled statements in the list above:

• RECEIVE can be multiplexed when the rows begin to arrive and if the SET XACT ABORT ON is specified for the batch. If the RECEIVE is in the waiting state then it can't be multiplexed.
• BULK INSERT / BCP can be multiplexed if the SET XACT ABORT ON is specified for the batch and the execute triggers on the target table has been disabled
• Managed code (stored procedures, functions, triggers) can't be multiplexed.

Is MARS really that great?

Like any technology it can benefit you or you can shoot yourself in the foot with it. MARS uses "firehose mode" to retrieve data. Firehose mode means that the server will produce data as fast as possible. This also means that your client application must receive inbound data at the same speed as it comes in. If it doesn't the data storage buffers on the server will fill up and the processing will stop until those buffers empty.

So what? You may ask... But as long as the processing is stopped the resources on the SQL server are in use and are tied up. This includes the worker thread, schema and data locks, memory, etc. So it is crucial that your client application consumes the inbound results as quickly as they arrive.

Also the important thing to realize is that multiplexed execution DOES NOT mean parallel execution.

(source - SQL TEAM)