What is MARS and how do I use it?
What is MARS?
Using Multiple Active Result Sets (MARS), you can maintain multiple pending requests on a single SQL Server connection. This allows you to issue multiple SQL statements or batch statements against a single connection, which eliminates the overhead of opening and closing the connection for each.
For example, suppose you need to update a record for each customer in a database. Before MARS, your flow might look like this:
Define an SQL Connection using the connection string
For each customer
Open Connection
Update Record
Close connection
Loop
For each command, you must reopen a connection to the server, execute your statements, and close the connection. Modern programming languages include objects to encapsulate some of these steps, but all of the steps must be performed. With MARS, your flow is like this:
Define an SQL Connection using the connection string
Open Connection
For each customer
Update Record
Loop
Close connection
The connection is open and closed only once. You should note that MARS executes in a synchronous, interleaved manner. While it gives the impression of parallel execution, it is not a true parallel implementation. If you require parallel execution, design this into your own application.
How do I enable MARS?
Using MARS is as easy as adding an additional parameter to your connection string. The specific syntax varies depending on the connection type used. Some examples are:
SQL Native Client ODBC Driver
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;MARS_Connection=yes;
SQL Native Client OLEDB Provider
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;MarsConn=yes;
NOTE: The Key-value pairs “MultipleActiveResultSets=true" and “MARS_Connection=yes” are interchangeable when using the SQL Native Client.
Sql Connection (.NET)
Server=myServerAddress;Database=myDataBase; MultipleActiveResultSets=true; User
ID=myUsername;Password=myPassword;Trusted_Connection=False;
When should I use MARS?
MARS can improve performance for many applications, but not all. Eliminating the number of connections open and closed reduces the overall overhead and speeds performance. However, it is important to note there is some overhead associated with starting a MARS connection. This is a worthwhile investment if multiple commands will be executed over the same connection but it can impact performance if a single batch statement is run.
It is also important to note that applications may require some redesign to take advantage of MARS. Using the earlier example, simply enabling MARS in the connection string for the first flow would not necessarily improve performance because the connection is till being explicitly open and closed for each command. To see the benefits of MARS this application would need to be rewritten to match the second flow. The amount of rework required depends on a specific applications implementation.
MARS requires ADO.NET 2.0 (available when your account is configured to use ASP.NET 2.0) and SQL Server 2005.
NOTE: Applications using earlier versions of ADO.NET cannot use MARS.
For more information about MARS, see Multiple Active Result Sets (MARS) in SQL Server 2005 on the MSDN Web site.