Using Multiple Active Result Sets (MARS) in SQL Server Native Client

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

SQL Server Native Client (SNAC) isn't shipped with:

• SQL Server 2022 (16.x) and later versions
• SQL Server Management Studio 19 and later versions

The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) aren't recommended for new application development.

For new projects, use one of the following drivers:

• Microsoft ODBC Driver for SQL Server
• Microsoft OLE DB Driver for SQL Server

For SQLNCLI that ships as a component of SQL Server Database Engine (versions 2012 through 2019), see this Support Lifecycle exception.

SQL Server 2005 (9.x) introduced support for multiple active result sets (MARS) in applications accessing the Database Engine. In earlier versions of SQL Server, database applications could not maintain multiple active statements on a connection. When using SQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 (9.x) introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

MARS simplifies application design with the following new capabilities:

• Applications can have multiple default result sets open and can interleave reading from them.

• Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while default result sets are open.

Applications using MARS will find the following guidelines beneficial:

• Default results sets should be used for short lived or short result sets generated by single SQL statements (SELECT, DML with OUTPUT, RECEIVE, READ TEXT, and so on).

• Server cursors should be used for longer lived or large result sets generated by single SQL statements.

• Always read to the end of results for procedural requests regardless of whether they return results or not, and for batches that return multiple results.

• Wherever possible, use API calls to change connection properties and manage transactions in preference to Transact-SQL statements.

• In MARS, session-scoped impersonation is prohibited while concurrent batches are running.

Note

By default, MARS functionality is not enabled by the driver. To use MARS when connecting to SQL Server with SQL Server Native Client, you must specifically enable MARS within a connection string. However, some applications may enable MARS by default, if the application detects that the driver supports MARS. For these applications, you can disable MARS in the connection string as needed. For more information, see the SQL Server Native Client OLE DB provider and SQL Server Native Client ODBC driver sections, later in this topic.

SQL Server Native Client does not limit the number of active statements on a connection.

Typical applications which do not need to have more than a single multi-statement batch or stored procedure executing at the same time will benefit from MARS without having to understand how MARS is implemented. However, applications with more complex requirements do need to take account of this.

MARS enables the interleaved execution of multiple requests within a single connection. That is, it allows a batch to run, and within its execution, it allows other requests to execute. Note, however, that MARS is defined in terms of interleaving, not in terms of parallel execution.

The MARS infrastructure allows multiple batches to execute in an interleaved fashion, though execution can only be switched at well defined points. In addition, most statements must run atomically within a batch. Statements which return rows to the client, which are sometimes referred to as yield points, are allowed to interleave execution before completion while rows are being sent to the client, for example:

• SELECT

• FETCH

• RECEIVE

Any other statements that are executed as part of a stored procedure or batch must run to completion before execution can be switched to other MARS requests.

The exact manner in which batches interleave execution is influenced by a number of factors, and it is difficult to predict the exact sequence in which commands from multiple batches that contain yield points will be executed. Be careful to avoid unwanted side effects due to interleaved execution of such complex batches.

Avoid problems by using API calls rather than Transact-SQL statements to manage connection state (SET, USE) and transactions (BEGIN TRAN, COMMIT, ROLLBACK) by not including these statements in multi-statement batches that also contain yield points, and by serializing execution of such batches by consuming or canceling all results.

Note

A batch or stored procedure which starts a manual or implicit transaction when MARS is enabled must complete the transaction before the batch exits. If it does not, SQL Server rolls back all changes made by the transaction when the batch finishes. Such a transaction is managed by SQL Server as a batch-scoped transaction. This is a new type of transaction introduced in SQL Server 2005 (9.x) to enable existing well-behaved stored procedures to be used when MARS is enabled. For more information about batch-scoped transactions, see Transaction Statements (Transact-SQL).

For an example of using MARS from ADO, see Using ADO with SQL Server Native Client.

In-Memory OLTP

In-memory OLTP supports MARS using queries and natively compiled stored procedures. MARS enables requesting data from multiple queries without the need to completely retrieve each result set before sending a request to fetch rows from a new result set. To successfully read from multiple open result sets you must use a MARS enabled connection.

MARS is disabled by default so you must explicitly enable it by adding MultipleActiveResultSets=True to a connection string. The following example demonstrates how to connect to an instance of SQL Server and specify that MARS is enabled:

Data Source=MSSQL; Initial Catalog=AdventureWorks; Integrated Security=SSPI; MultipleActiveResultSets=True  

MARS with In-Memory OLTP is essentially the same as MARS in the rest of the SQL engine. The following lists the differences when using MARS in memory-optimized tables and natively compiled stored procedures.

MARS and memory-optimized tables

The following are the differences between disk-based and memory-optimized tables when using a MARS enabled connection:

• Two statements can modify data in the same target object but if they both attempt to modify the same record a write-write conflict will cause the new operation to fail. However, if both operations modify different records, the operations will succeed.

• Each statement runs under SNAPSHOT isolation so new operations cannot see changes made by the existing statements. Even if the concurrent statements are executed under the same transaction the SQL engine creates batch-scoped transactions for each statement that are isolated from each other. However, batch-scoped transactions are still bound together so rollback of one batch-scoped transaction affects other ones in the same batch.

• DDL operations are not allowed in user transactions so they will immediately fail.

MARS and natively compiled stored procedures

Natively compiled stored procedures can run in MARS enabled connections and can yield execution to another statement only when a yield point is encountered. A yield point requires a SELECT statement, which is the only statement within a natively compiled stored procedure that can yield execution to another statement. If a SELECT statement is not present in the procedure it will not yield, it will run to completion before other statements begin.

MARS and In-memory OLTP transactions

Changes made by statements and atomic blocks that are interleaved are isolated from each other. For example, if one statement or atomic block makes some changes, and then yields execution to another statement, the new statement will not see changes made by the first statement. In addition, when first statement resumes execution, it will not see any changes made by any other statements. Statements will only see changes that are finished and committed before the statement starts.

A new user transaction can be started within the current user transaction using the BEGIN TRANSACTION statement - this is supported only in interop mode so the BEGIN TRANSACTION can only be called from a T-SQL statement, and not from within a natively compiled stored procedure.You can create a save point in a transaction using SAVE TRANSACTION or an API call to transaction.Save(save_point_name) to rollback to the savepoint. This feature is also enabled only from T-SQL statements, and not from within natively compiled stored procedures.

MARS and columnstore indexes

SQL Server (starting with 2016) supports MARS with columnstore indexes. SQL Server 2014 uses MARS for read-only connections to tables with a columnstore index. However, SQL Server 2014 does not support MARS for concurrent data manipulation language (DML) operations on a table with a columnstore index. When this occurs, SQL Server will terminate the connections and abort the transactions. SQL Server 2012 has read-only columnstore indexes and MARS does not apply to them.

SQL Server Native Client OLE DB Provider

The SQL Server Native Client OLE DB provider supports MARS through the addition of the SSPROP_INIT_MARSCONNECTION data source initialization property, which is implemented in the DBPROPSET_SQLSERVERDBINIT property set. In addition, a new connection string keyword, MarsConn, as been added. It accepts true or false values; false is the default.

The data source property DBPROP_MULTIPLECONNECTIONS defaults to VARIANT_TRUE. This means the provider will spawn multiple connections in order to support multiple concurrent command and rowset objects. When MARS is enabled, SQL Server Native Client can support multiple command and rowset objects on a single connection, so MULTIPLE_CONNECTIONS is set to VARIANT_FALSE by default.

For more information about enhancements made to the DBPROPSET_SQLSERVERDBINIT property set, see Initialization and Authorization Properties.

SQL Server Native Client OLE DB Provider Example

In this example, a data source object is created using the SQL Server Native OLE DB provider, and MARS is enabled using the DBPROPSET_SQLSERVERDBINIT property set before the session object is created.

#include <sqlncli.h>  
  
IDBInitialize *pIDBInitialize = NULL;  
IDBCreateSession *pIDBCreateSession = NULL;  
IDBProperties *pIDBProperties = NULL;  
  
// Create the data source object.  
hr = CoCreateInstance(CLSID_SQLNCLI10, NULL,  
   CLSCTX_INPROC_SERVER,  
   IID_IDBInitialize,   
    (void**)&pIDBInitialize);  
  
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)&pIDBProperties);  
  
// Set the MARS property.  
DBPROP rgPropMARS;  
  
// The following is necessary since MARS is off by default.  
rgPropMARS.dwPropertyID = SSPROP_INIT_MARSCONNECTION;  
rgPropMARS.dwOptions = DBPROPOPTIONS_REQUIRED;  
rgPropMARS.dwStatus = DBPROPSTATUS_OK;  
rgPropMARS.colid = DB_NULLID;  
V_VT(&(rgPropMARS.vValue)) = VT_BOOL;  
V_BOOL(&(rgPropMARS.vValue)) = VARIANT_TRUE;  
  
// Create the structure containing the properties.  
DBPROPSET PropSet;  
PropSet.rgProperties = &rgPropMARS;  
PropSet.cProperties = 1;  
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDBINIT;  
  
// Get an IDBProperties pointer and set the initialization properties.  
pIDBProperties->SetProperties(1, &PropSet);  
pIDBProperties->Release();  
  
// Initialize the data source object.  
hr = pIDBInitialize->Initialize();  
  
//Create a session object from a data source object.  
IOpenRowset * pIOpenRowset = NULL;  
hr = IDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&pIDBCreateSession));  
hr = pIDBCreateSession->CreateSession(  
   NULL,             // pUnkOuter  
   IID_IOpenRowset,  // riid  
  &pIOpenRowset ));  // ppSession  
  
// Create a rowset with a firehose mode cursor.  
IRowset *pIRowset = NULL;  
DBPROP rgRowsetProperties[2];  
  
// To get a firehose mode cursor request a   
// forward only read only rowset.  
rgRowsetProperties[0].dwPropertyID = DBPROP_IRowsetLocate;  
rgRowsetProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;  
rgRowsetProperties[0].dwStatus = DBPROPSTATUS_OK;  
rgRowsetProperties[0].colid = DB_NULLID;  
VariantInit(&(rgRowsetProperties[0].vValue));  
rgRowsetProperties[0].vValue.vt = VARIANT_BOOL;  
rgRowsetProperties[0].vValue.boolVal = VARIANT_FALSE;  
  
rgRowsetProperties[1].dwPropertyID = DBPROP_IRowsetChange;  
rgRowsetProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;  
rgRowsetProperties[1].dwStatus = DBPROPSTATUS_OK;  
rgRowsetProperties[1].colid = DB_NULLID;  
VariantInit(&(rgRowsetProperties[1].vValue));  
rgRowsetProperties[1].vValue.vt = VARIANT_BOOL;  
rgRowsetProperties[1].vValue.boolVal = VARIANT_FALSE;  
  
DBPROPSET rgRowsetPropSet[1];  
rgRowsetPropSet[0].rgProperties = rgRowsetProperties  
rgRowsetPropSet[0].cProperties = 2  
rgRowsetPropSet[0].guidPropertySet = DBPROPSET_ROWSET;  
  
hr = pIOpenRowset->OpenRowset (NULL,  
   &TableID,  
   NULL,  
   IID_IRowset,  
   1,  
   rgRowsetPropSet  
   (IUnknown**)&pIRowset);  

SQL Server Native Client ODBC Driver

The SQL Server Native Client ODBC driver supports MARS through additions to the SQLSetConnectAttr and SQLGetConnectAttr functions. SQL_COPT_SS_MARS_ENABLED has been added to accept either SQL_MARS_ENABLED_YES or SQL_MARS_ENABLED_NO, with SQL_MARS_ENABLED_NO being the default. In addition, a new connection string keyword, Mars_Connection, as been added. It accepts "yes" or "no" values; "no" is the default.

SQL Server Native Client ODBC Driver Example

In this example, the SQLSetConnectAttr function is used to enable MARS before calling the SQLDriverConnect function to connect the database. Once the connection is made, two SQLExecDirect functions are called to create two separate result sets on the same connection.

#include <sqlncli.h>  
  
SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);  
SQLDriverConnect(hdbc, hwnd,   
   "DRIVER=SQL Server Native Client 10.0;  
   SERVER=(local);trusted_connection=yes;", SQL_NTS, szOutConn,   
   MAX_CONN_OUT, &cbOutConn, SQL_DRIVER_COMPLETE);  
  
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1);  
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt2);  
  
// The 2nd execute would have failed with connection busy error if  
// MARS were not enabled.  
SQLExecDirect(hstmt1, L"SELECT * FROM Authors", SQL_NTS);  
SQLExecDirect(hstmt2, L"SELECT * FROM Titles", SQL_NTS);  
  
// Result set processing can interleave.  
SQLFetch(hstmt1);  
SQLFetch(hstmt2);  

See Also

SQL Server Native Client Features
Using SQL Server Default Result Sets