Multiple Active Result Sets (MARS) were introduced with SQL Server 2005. When you connect to an SQL Server database you are able to request and receive a TDS (Tabular Data Stream) from the database. Using MARS you are able to receive more than one TDS stream concurrently over the same connection. MARS controls the routing of each stream to the correct client listener.
MARS should not be confused with parallel execution. A query can be broken into multiple actions in the database engine, and even return multiple result sets. However, the receipt of those multiple result sets is synchronous. If you want to have multiple, concurrent result sets returned from SQL Server, you must have two separate commands running in separate threads, each with their own query, sharing a single connection instance, and the connection must allow Multiple Active Result Sets, as defined in the connection string (see www.connectionstring.com for more info).
I was reading about a number of developers having problems with SqlDataReaders using EF, NHibernate, and other ORM technologies. One developer recommended a modification of the Repository pattern where each command created its own connection instance. His point was that applications were experiencing this problem as load increased, and queries took longer to execute. Some folks fixed the problem by turning on MARS in their connection string. According to this developer, MARS was a false fix. It allowed multiple things to run concurrently, but had the potential for cross over or more.
I believe the answer is something different. Each thread in a repository has its own connection when a repository object is instantiated. This is by design. The primary reason is so that all activities can share a single transaction. All activities may be committed or rolled back. A single save to the repository posts all the changes as a single transaction.
Without a lot of research, my guess is that the problem is that they were using Linq2Sql, EF, NHibernate, etc. using asynchronous methods. I need to be clear here. Just because you use something asynchronously does not mean it runs concurrently. However, if you have established a process that retrieves a SqlDataReader, and another asynchronous process is trying to create another query, it is possible to have conflict on the SqlDataReader. We didn’t have this problem when we had to build all of the Asynchronous code ourselves by hand, because it was too complicated to do it unless it was essential. Now, Asynchronous is the preferred implementation regularly taught.
You can use Asynchronous methods such that they do actually run in parallel. There are times when this is really what you want to happen. In those cases, you need to be sure not to share anything except the connection instance.
The take away here is that MARS is a good thing. Concurrent queries make sense when you need them. It can help your application performance when you can break up your process. However, always forcing Async methods to be called has performance overhead when it is not needed, and needs to be understood when implemented.
Cheers,
Ben