Using MARS with SQL Native Client

Using MARS with SQL Native Client [Chris Lee]

[This was originally posted on the DataWorks blog which is now suspended]

As stated in my previous post, the majority of new features available through SQL Native Client are accessed by connection or statement properties. MARS is about the simplest new feature to use in SQL Server 2005, and possibly the one we’ve all been waiting for the longest, so it’s a good candidate for your first excursion into the new features available with SQL Native Client.

MARS lets an application perform multiple operations on a single connection even if there are default result sets open on the connection. Prior to MARS, ADO and SQLOLEDB applications would (under default conditions) use implicit multiple connections and ODBC applications would get ‘connection busy’ errors.

Pre-MARS behavior
Let’s look at MARS using ADO for simplicity (I’ll mention how to enable MARS with ODBC or OLE DB at the end of this post, but don’t skip there just yet as most of what follows is still going to be useful to you even if you’re not using ADO). Try executing the following code (which assumes you have a SQL Server instance called SQL2K5 with the AdventureWorks demo database available on your local machine). This is just ‘nonsense’ code that just opens a default result set and then opens another result set using the same query multiple times, to make a point. The extension of the point it makes to your own applications shouldn’t be hard to grasp.

    Dim i As Integer
Dim cn As ADODB.Connection
Dim qryCustomers1 As ADODB.Command
Dim qryCustomers2 As ADODB.Command
Dim rsCustomers1 As ADODB.Recordset
Dim rsCustomers2 As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open ("Provider=sqlncli;Data Source=.\SQL2K5" & _
";Database=AdventureWorks;Integrated Security=SSPI;")
Set qryCustomers1 = New ADODB.Command
Set qryCustomers1.ActiveConnection = cn
qryCustomers1.CommandText = "SELECT CustomerID" & _
" FROM Sales.Customer" & _
" WHERE CustomerID=1" & _
" ORDER BY CustomerID"
Set qryCustomers2 = New ADODB.Command
Set qryCustomers2.ActiveConnection = cn
qryCustomers2.CommandText = qryCustomers1.CommandText

Set rsCustomers1 = qryCustomers1.Execute
For i = 1 To 3
Set rsCustomers2 = qryCustomers2.Execute
rsCustomers2.Close
Next i
rsCustomers1.Close
cn.Close

If you take a default SQL Server Profiler trace you’ll see something like this:

The second result set is using a new connection each time it is opened. This clearly has some overhead (and it turns out that the additional connections aren’t pooled, so the overhead is for a full server connection network protocol exchange each time). This is the default behavior for SQLOLEDB and SQL Native Client (OLE DB) – a new implicit connection is spawned when the main connection is busy with a default result set. You can prevent multiple implicit connections being spawned by changing the connection lines to

    cn.Open ("Provider=sqlncli;Data Source=.\SQL2K5;Database=AdventureWorks;Integrated Security=SSPI;")
cn.Properties("Multiple Connections") = False

The effect of this is as follows. This is similar to ODBC where multiple implicit connections aren’t used and the application gets a ‘connection busy’ error.
 

Behavior with MARS
Now we’re ready for MARS. In addition to providing a great deal more convenience to the programmer, MARS can reduce server overhead too. Change the connection string to: cn.Open ("Provider=sqlncli;Data Source=.\SQL2K5;MARS Connection=True;Database=AdventureWorks;Integrated Security=SSPI;")

And the code works again, but now the Profiler trace looks like this:

Is MARS all good news, or is there any downside? Well, mostly MARS is good news. The downsides are twofold. First, existing code optimized to run in the non-MARS world may show a slight performance dip when run un-modified with MARS. Default result sets make very efficient use of network resources and adding the MARS capability incurs some additional network overhead. The message here is “if it ain’t broke don’t fix it”, or – as with any feature – you need to design your code to make best use of MARS.

This brings us to the second caveat when using MARS – don’t try to get too smart. With MARS you can send multiple multi-statement batches to the server. The server will interleave execution of such batches, which means that if the batches change server state via SET or USE statements, for example, or use TSQL transaction management statements (BEGIN TRAN, COMMIT, ROLLBACK), both you and the server can get confused about what your actual intent is. The outcome may be an error, or it may be that the application doesn’t behave as you expected it to. It is because of these potential difficulties when MARS is ‘over used’, that we decided to turn it off by default rather than have it enabled by default, as it was for Beta 2.

Without MARS you can only execute one multi-statement batch at a time, which results in completely predictable performance. Stay with this model with MARS – but of course with the relaxation that you can execute multiple single statement queries and have their result sets pending at the point at which you execute your multi-statement batch – and you’ll stay out of trouble. This model is all that most people want and expect to do with MARS anyway.

It’s likely that the first thing you’ll want to try with MARS is convert server cursors to default result sets. Is this the right thing to do? If the result set is fairly small, or will be consumed in its entirety fairly quickly and you don’t require locking on rows in the result set and you haven’t optimized your code to use block fetches, then conversion to a default result set with MARS will be beneficial. If you’re not going to process all the rows in a result set, or the result set is very large and will not be read quickly, or you need locking or scrolling, then you should stay with a server cursor.

There are some other considerations you need to take into account. Firstly, you can’t begin a transaction when you have a default result set open. Secondly, if you commit or rollback a transaction any open default result sets become unavailable (attempts to use them result in errors). These restrictions are the same whether you use MARS or not, although the error messages are different in some cases. If you need ‘Preserve On Commit’ behavior, then you must use a server cursor.

Using MARS from ODBC and OLE DB
To use MARS with ODBC the connection string addition is “MARS_Connection=yes”, or you can a connection attribute as follows:
SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED,SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
before calling SQLDriverConnect or SQLBrowseConnect. With OLE DB, the provider string addition for IDBInitialize::Initialize is “MarsConn=yes”, or “MARS Connection=true” if you use an initialization string with IDataInitialize::GetDataSource. You can also enable MARS programmatically as follows:

IDBInitialize *pIDBInitialize = NULL;
IDBProperties *pIDBProperties = NULL;
// Create the data source object.
hr = CoCreateInstance(CLSID_SQLNCLI, NULL, CLSCTX_INPROC_SERVER,IID_IDBInitialize, (void**)&pIDBInitialize);
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)&pIDBProperties);

// Set the MARS property.
DBPROP rgPropMARS;
IDBProperties 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();

Conclusion
MARS is a great new feature that fosters a more natural programming style when working with SQL Server 2005, especially if you’ve ever worked with other databases. With ADO and OLE DB your applications will probably require fewer server connections. MARS will be problem free when used sensibly, but you still need to give some thought to the choice between default result sets and server cursors, although you have more flexibility than before.

Chris Lee
Program Manager, DataWorks

Comments

• Anonymous
  March 27, 2008
  There are problems in creating temporary tables using ADO Connection and Command objects using SQLNativeClient. In the following cases i have enabled MARS connction. My connection string is "DSN=MNACConnection ;UID=bob; PWD=password; MARS_Connection=yes;" When you assign a Connection object to Command object's ActiveConnection, you can no longer create temp tables(global or local) using connection object. Connection object will create and drop the temp table with in a second. and returns no error. Also If you create a global temp table using command object, it will be deleted when the Command object's ActiveConnection is set to Nothing. Please let me know what should I do to preserve the temp tables. Thanks and Best Regards Madhu Gorthi

• Anonymous
  September 09, 2012
  Jamesi does not have a business but he has set up a business under some other name