Angel Saenz-Badillos

does not contain a definition for

angelsb — Sat, 24 Feb 2007 06:06:00 GMT

Have you heard about extension methods yet? If you have then you probably know exactly what the title of this blog means. I can see this becoming one of the most asked about exceptions in the coming release. At least until the dust settles. <Edit>...(read more)

Blogging about link Extensions

angelsb — Mon, 29 Jan 2007 20:45:00 GMT

There are not a lot of guidelines about blogging, the one that I like to adhere to is that I only blog about bits that you can go and play with. This ends up creating gaps in my blogging history as I move to new unpublished products. I have moved, oh...(read more)

JDBC V1.1 Distributed Transactions with Read Committed and Snapshot isolation.

angelsb — Wed, 30 Aug 2006 00:57:00 GMT

One of the biggest changes you may not have noticed in the v1.1 2005 JDBC driver is that we have changed the default isolation level for Distributed Transactions from Serializable to Read Committed. This is in line with other JDBC drivers like our 2000...(read more)

JDBC Snapshot isolation level

angelsb — Thu, 24 Aug 2006 23:43:00 GMT

I have gotten some questions about using Sql Server 2005 with the 2005 JDBC driver. There are unfortunately a lot of misconceptions in this area. Here are some notes to make this a little bit easier to understand. The first thing you need to understand...(read more)

JDBC 2005 v1.1 is now available.

angelsb — Wed, 23 Aug 2006 03:51:00 GMT

The v1.1 2005 JDBC driver is now available for download here: http://msdn.microsoft.com/data/ref/jdbc/ This is a release I am personally really proud of, and we could not have done it without you! Maybe it’s the fact that not that many...(read more)

JDBC June 2006 Community Tech Preview available.

angelsb — Mon, 26 Jun 2006 22:03:00 GMT

Announcing the June 2006 Community Tech Preview of the JDBC 2005 driver: http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en This preview contains all the changes of CTP 1 including...(read more)

JDBC v1.1 CTP and the Product Feedback Center

angelsb — Fri, 21 Apr 2006 02:35:00 GMT

When you get down to it Customer feedback is what makes a project like this interesting and worthwhile. This is the first project I have worked in where the Product Feedback Center has made such an important difference and a BIG thank you to those of you that have taken the time to file bugs directly on the JDBC provider: http://lab.msdn.microsoft.com/productfeedback/default.aspx

I want to make sure that you understand that we take these bugs very seriously. We triage them daily and treat them as the highest priority bugs come release time. Here is a list of customer bugs that we have received since we shipped RTM.

All of these bugs have been fixed in our latest JDBC V1.1 CTP. you can download this CTP by contacting the JDBC project manager directly at shelbyg<at>Microsoft<dot>com.

Bug Details: Statement.execute() returns incorrect results for stored procedures

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=dc43eb80-69b5-4137-8671-9f4e813ce259

Bug Details: JDBC 2005 GA Driver error when running a callablestatement on SQLServer 2000

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=3fcf439d-79ed-44d9-bbbf-320e4ee25d89

Exception Message: “SQL Error: A server cursor is not allowed on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor”

Bug Details: JDBC Driver executeBatch does not work with INSERT statement

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=e13a8009-7466-4803-ba19-6bfd4b5f8966

Exception Message: “java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.“

Bug Details: SQL2005 JDBC Driver bug with time zone handling **Available as a QFE**

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=e73c8b79-53a6-4779-98f1-71c0043179eb

Additional customer issues addressed that have no associated external bugs:

Bug Detail: Using sendStringParametersAsUnicode does not work with XA transactions.

Exception Message: “java.sql.SQLException: Failed to create the XA control connection.”

JDBC v1.1 CTP Using Type 2 Integrated Security

angelsb — Sat, 15 Apr 2006 02:22:00 GMT

To use the Type 2 Integrated Security feature with the v1.1 CTP (if you don't have this already make sure to get it! see my previous blog) you only need to make sure that the Windows authentication DLL is visible to the application that is going to use it (This usually means it is in the same directory or in a directory specified in your Path). Then specify integratedSecurity in your connection string and you are done.

The Windows authentication DLL is located under:

\sqljdbc_1.1\enu\auth\x86\sqljdbc_auth.dll //for x86 client OS versions
\sqljdbc_1.1\enu\auth\x64\sqljdbc_auth.dll //for AMD 64 clients running Windows 2003 64bit OS

import java.sql.*;

public class test {
public static void main(String[] args) {
try {
   java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   Connection conn = java.sql.DriverManager.getConnection("jdbc:sqlserver://servername;integratedSecurity=true");
   System.out.println("Connected!");
   conn.close();

} catch (Exception ex) {
ex.printStackTrace();
}
}
}

JDBC V1.1 CTP Available

angelsb — Sat, 15 Apr 2006 02:00:00 GMT

I am proud to say that the first community preview release of the SQL Server 2005 JDBC v1.1 driver is now available.

This build includes Type-2 integrated authentication for Windows XP and Windows 2003 clients (windows 2000 client support to come in the next CTP). It also incorporates bug fixes from customer feedback of the v1.0 driver and a fair ammount of plumbing under the covers. We really want to get as much feedback on this as possible.

To get the v1.1 CTP1 please email the JDBC Project Manager directly at
shelbyg<at>microsoft<dot>com.

I have a bet going with him on the number of requests he will receive so don't let me down!

JDBC 2005 beta 2 getting started with SQLEXPRESS

angelsb — Fri, 21 Oct 2005 00:35:00 GMT

Sql Express is going to be a good match for the Sql Server 2005 JDBC provider, it is free, it is relatively easy to set up and deploy and it has all the power of Sql Server 2005 for low end applications.

There are a few things to keep in mind when using Sql Express with the JDBC driver:

·         You need to enable mixed mode security when you install the server so that you can connect using user name and password.
·         Make sure that the sqlbrowser is enabled. Sql Server configuration Manager ->SQL Server 2005 Services ->SQL Server Browser ->Start
·         The JDBC provider only works with the TCP/IP protocol which is disabled by default on Sql Express. You need to enable the TCP/IP Protocol from the SQL Server Configuration Manager that ships with SqlExpress and re-start the server. Look under SQL Server 2005 Network Configuration -> Protocols for SQLEXPRESS-> TCP/IP->Enable.

Once you have set up Sql Express accordingly you can connect like this:

import java.sql.*;

public class test{
public static void main(String[] args) {
    try{
       java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;user=myuser;password=mypass;");
       System.out.println("Connected!");
    }catch(Exception ex){
       ex.printStackTrace();
    }
}
}

If you are interested enough in this driver to have read this far you may be interested in learning more about our Data Coercion story:
http://blogs.msdn.com/dataaccess/archive/2005/10/19/482792.aspx

Rambling out, the information in this blog is provided "AS IS" and confers no rights.

JDBC Beta 2 connection string properties

angelsb — Sat, 01 Oct 2005 03:11:00 GMT

As you have probably seen by now we have shipped beta 2 of the Sql Server 2005 JDBC driver.

http://www.microsoft.com/sql/downloads/2005/jdbc.mspx

We have put a lot of work into this beta and it is with some trepidation that I see it go out the door. We have made a lot of hard decisions, our design philosophy has been to "do it right". This means that there are a lot of changes, some that may break some scenario that worked before (those are the ones I am most interested in hearing about). I can assure you that for every single change we have spent hours pouring over the "Maydene Fisher/Jon Ellis/Jonathan Burce, JDBC API Tutorial and Reference, third edtion" trying to do the right thing and I want to write a few blogs about some of these changes.

I will start with the easy one, we have cleaned up the connection string properties. We no longer allow duplicate connection string values, we now allow escaping values (using the '{' and '}' brackets) we will now throw an exception whenever you enter a name value pair that we don't understand etc. Besides all of that house keeping we have made some hard decisions, feel free to leave feedback on any of the following:

codepage has been removed, we now keep track of the codepage that you are using. There are some problems with this approach, we are currently supporting the 850+ codepages that the JVM supports. The problem is that there are a number of Codepages that are not supported by the VM that will now fail! In beta 2. We feel that explicitly failing is better than the possible data corruption that we were exposing before. Important: If you depend on a specific codepage that is _not_ supported by the JVM please contact us directly.

selectMethod has become forwardReadOnlyMethod. We wanted to make it very explicit that this method only affects the statements opened with Forward Only Read Only cursors (the default)

workstationID has been added, use this to identify the specific workstation in various SQL Server profiling and logging tools

trustedAuthentication has become integratedSecurity and ntlmAuthentication + domain have been removed. We are working on this feature but quite frankly we are seriously looking at this feature.

sqlVersion has been removed, we only support Sql Server 2000 and Sql Server 2005.

enableFailover has been removed, the implementation had some security issues so we are removing it until we can do it correctly.

logfile has been removed we have moved diagnostics to java.util.logging.Logger

asciiStringParameters becomes logically inverse sendStringParametersAsUnicode. We don’t always send the string parameters as ascii when not sending them as Unicode, renaming this property makes this clearer.

booleanLiterals has been removed, we were parsing SQL statements and translating Oracle boolean literas to Sql Server numeric values. This is a very incomplete feature so we have decided to postpone Oracle migration features to a later release.

JDBC getting started with the 2005 JDBC driver for Sql Server 2000 and Sql server 2005

angelsb — Tue, 02 Aug 2005 08:24:00 GMT

If you had told me a year ago that I would be working on a JDBC driver I would have honestly thought you were kidding. If you had suggested a ship date concurrent with Sql Server 2005 (within a reasonable window) I would have laughed out loud. This is turning out to be a tremendously interesting project in so many ways. I find that I am having a very hard time blogging about this project in general terms, so I am just going to start at the beginning.

Getting started with the new 2005 JDBC driver,

download the beta driver from:
http://www.microsoft.com/sql/downloads/2005/jdbc.mspx

sample code:
import java.sql.*;
public class test{
public static void main(String[] args) {
    try{
         java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         Connection connection1 = java.sql.DriverManager.getConnection("jdbc:sqlserver://server;user=myuser;password=mypassword");
         System.out.println("Connected!");

         Statement statement1 = connection1.createStatement();
         ResultSet resultset1 = statement1.executeQuery("select * from foo");
            while(resultset1.next() ){
                        System.out.println("Foo result = "+resultset1.getObject(1) );
            }
    }catch(Exception ex){
        ex.printStackTrace();
    }
}
}

JDBC 3.0 is a well stablished standard, there are no surprises in the code itself. More interesting however is what is going on under the covers, and more importantly what _should be going on_. This is where we are looking for feedback, let us know what you think:

java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
You may have a sense of deja vue looking at the name of the new driver, yes you are not mistaken we just switched the name of the old driver around. Confusing? Well maybe a little bit, but have you tried running names through legal? Let me just make it very clear that this driver is not in any way related to the 2000 driver.

Connection connection1 = java.sql.DriverManager.getConnection("jdbc:sqlserver:/server;user=myuser;password=mypassword");
This is actually a lot more interesting than it looks (especially considering that this is a non pooled connection). I guess a good starting point would be asking the following question: What is a connection string url? Well, it is url-looking thanks to that “:/” but what it is really for is to allow you to provide information to connect to your server. Lets go over some of the confusing concepts here:

Duplicate Keywords:
What happens when you enter more than one "user=" keyword value pairs? In ado.net and in oledb the last keyword wins, in odbc the first keyword can win (driver dependent). This has been historically done so that you can override an existing connection string without having to parse it, but it is confusing and bug prone. I would like to break with tradition and throw an exception on duplicates, what do you think?

Synonyms/Aliases:
Another source of confusion, is it database? databaseName? Both? In what order? Synonyms may be necessary for backwards compatibility but this is a 1.0 driver. The current beta has an interesting bug with synonyms and I see no reason to keep them, any thoughts?

Handling unknown keywords:
Generic drivers like Oledb or Odbc drivers need to be able to ignore any connection string keywords that they don’t understand. This is not true for a type four driver, I don’t see a good reason to allow these and many reasons to avoid them, we probably need to throw an exception here.

Case sensitivity:
Is database the same as Database? Well it turns out that String manipulation in java is not particularly performant, it is well worth enforcing case sensitivity on connection string keywords to avoid the performance penalty. As long as we start throwing on unknown keywords (see above) this will not be a major customer pain point.

Separators:
Currently we allow “;”, “/”, “:” and a few more separators between connection string keywords, I am not sure that this is a clean model. We should be able to stick to one separator (“;”) plus the (“:”) for port numbers, what do you think?

Escape characters:
This is a tough one, there is no doubt that we need them, what do we do with names with separators (see above) embedded in them? What about empty as in empty database or empty password? The simplest way to handle this would be to use something like “ ‘ “ as a separator and force you to escape any single quotes that you are going to use in your connection string, ex: user=’O’’neil’. This is not an ideal solution.

Statement statement1 = connection1.createStatement();
I will definitely have to have a separate blog about this, there is just too much going on under the covers for a quick overview. I am just going to point out that currently by default we are using a pseudo firehose cursor that may not scale well. I am personally not a great fan of server side cursors but a) JDBC requires them and b) the JDBC api seems to be geared towards using cursors. We may have to make some changes in this space.

ResultSet resultset1 = statement1.executeQuery("select * from foo");
Finally a straightforward code statement, you executeQuery and you get a resultset.

Rambling out, the information in this blog is provided "AS IS" and confers no rights.

JDBC the new member of the DataWorks family, Sql Server 2005 JDBC Driver (beta 1)

angelsb — Fri, 01 Jul 2005 21:30:00 GMT

One of the hardest things for me this last few months was to go into “radio silence” over the project I am currently working on. Now that we have officially shipped a beta I can come back to blogging, albeit from a slightly different angle.

Microsoft has determined that there is a business need for a type four JDBC driver to Sql Server 2000 and 2005, this driver will have full support including PSS and a full time product group to not only maintain this product but to enable new functionality going forward. Make no mistake about it we are truly serious about supporting and improving this driver.

Presenting the Sql Server 2005 JDBC Driver (beta 1)
http://www.microsoft.com/sql/downloads/2005/jdbc.mspx

What it is:
Type 4 JDBC driver to Sql Server 2000 and Sql Server 2005. Designed to work on Java 1.4.2

Expected release date
Of course we cannot guarantee a release date, but this driver is intended to ship within a reasonable window of Sql Server 2005 launch.

Primary discussion newsgroup (Hope to see you there soon):
microsoft.public.sqlserver.jdbcdriver

Entering a bug:
Go to http://lab.msdn.microsoft.com/productfeedback/default.aspx

      Product/Technology:
          SQL Server 2005

      Version:
          SQL Server 2005 Community Technology Preview June 2005 - Developer Edition

      Product Language:
          English

      Category:
          JDBC Driver

For me walking into the JDBC space is a great challenge, there is a lot that I don’t know and given the extreme time constraints placed on this product I have very little time to get up to speed on this. For me the fastest way to do this has always been through customer feedback.
If you or your company is interested in trying this JDBC driver drop me a comment. Bugs, suggestions, concerns and “must have” feature requests always welcome.

Say hello to the DataWorks blog

angelsb — Mon, 21 Mar 2005 19:44:00 GMT

It is a fair assumption that you have not heard about DataWorks, that is ok most folks arround Microsoft haven't either. MDAC on the other hand probably sounds familiar... DataWorks is the Microsoft Product Unit in which I work, it is responsable for most of the general data access technologies in Microsoft. If you are reading this blog you may also be familiar with many of DataWorks other "inmates": Brad Rhodes, Pablo Castro, Sushil Chordia, David Sceppa, Shelby Goerlitz ...

Now you can contribute to this product unit directly :

http://blogs.msdn.com/dataaccess

So far it consists of only a welcome message from our PUM (Product Unit Manager) - Alyssa. Expect good things in the future!

ADO.NET 2.0 and System.Transactions, downlevel functions and nested scopes

angelsb — Sat, 12 Feb 2005 08:59:00 GMT

As with most of my blog posts this started as a customer question, the core of the problem was that he was not sure how he was expected to use TransactionScopes in methods that call other methods or "downlevel" functions as he named them.

There are two important concepts to keep in mind.

1) downlevel functions should not know or care about what happens on the functions that call them. Don't pass TransactionScope as properties, don't assume that there is an existing transaction, etc.

2)You can nest TransactionScopes (with the default TransactionScopeOption, "Required") or isolate downlevel functions from outer TransactionScopes (with the optional Suppress and RequiresNew TransactionScopeOption(s) )

Here is some pseudo code for a small stress app I wrote that shows some of this off:

InsertMoneyIntoAccount() //doesn’t use transactions

RemoveMoneyFromAccount() //doesn’t use transactions

TransferMoney() //creates TransactionScope then calls RemoveMoney and InsertMoney

TransferMoneyWithExtraCharges //creates TransactionScope, calls TransferMoney and RemoveMoneyFromAccount

// the TransactionScope in TransferMoney will be nested inside the outer Scope.

A more advanced (and to my way of thinking less recommended) concept is nesting Scopes where you do not want the behavior in the downlevel functions to affect outer scopes, you can do this by using the TransactionScopeOption.RequiresNew and Suppress enum values.

TransferVerifyAndTransfer() //creates a TransactionScope, calls Transfer, uses retry logic to call VerifyTransfer multiple times and once the transfer is verified calls Transfer again. The main problem here is that we don’t want a fatal exception in VerifyTransfer to roll back the transaction since we are building in logic to retry on exception.

VerifyTransfer() //creates a TransactionScope with TransactionScopeOption.Suppress, this guarantees that it will not affect the scopes in uplevel functions.

//It is important to realize that some exceptions even when handled can affect the transaction. In the example below if we don’t use the Suppress option for B’s TransactionScope then the ExecuteNonQuery (level 20 exception) will cause A’s outer TransactionScope to be rolled back.

public void A(){

using (TransactionScope transactionscope1 = new TransactionScope()){

this.B( );

transactionscope1.Complete()

}

public void B(){

//always create a TransactionScope with the “using” language construct.

using (TransactionScope transactionscope1 = new TransactionScope(TransactionScopeOption.Suppress)){

//always create a connection with the “using” language construct.

using (SqlConnection sqlconnection1 = new SqlConnection(connectionstring)){

sqlconnection1.Open();

SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "raiserror ('my error', 20, 20) with log";

try {

sqlcommand1.ExecuteNonQuery();

}

catch(Exception e){

Console.WriteLine(e.Message);

}

transactionscope1.Complete();

} //Here sqlconnection1 is _guaranteed_ to be disposed. I think it is a good idea to do this before the scope is disposed.

}

Rambling out: This post is provided "AS IS" and confers no rights.

Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

angelsb — Fri, 14 Jan 2005 05:50:00 GMT

Mars session pooling,

Well, it has been a long time since my last blog and I am finding it very hard to get this one started. You start thinking that you need to have something really good to start up the train again or you might as well not bother. I am afraid that session pooling does not fall under the “really good” category. It is transparent to users and nothing bad happens if session pooling does not work. I could argue that it is important to know how this feature works so as to maximize MARS performance scenarios (this is true) but as I mentioned in a previous blog I am NOT a big fan of using MARS for performance, it is possible but too painful for the gains you will find IMO.

So… it doesn’t really affect you, you will probably not be able to tell it’s there, and I don’t recommend using this knowledge to your advantage, why bother reading the rest of this blog? Well I think that this is one of those features that you can use to out geek the competition. I would start the conversation with something like “so you _think_ you know how that works do you?” and go from there.

If you don’t know what MARS is I would scroll start with this excellent article to get an idea of how things work http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp and continue reading down this blog to my MARS faq http://weblogs.asp.net/angelsb/archive/2004/09/07/226597.aspx

Onwards to business:

Q: what is ado.net 2.0 SqlClient Session Pooling?

A: When you are using SqlClient to connect to Sql Server 2005 we will pool up to ten sessions per connection.

</end of blog>

Rambling out. Standard disclaimer: All the information in this blog is posted “AS IS” and confers no rights.

Huh? What is that you say? Well I never! Of course it makes sense. Don’t let little things like the fact that we have effectively made up new definitions for “connection” and “session” confuse you. The word buzzes around Redmond, “the ado.net team has been taken unawares. They cannot cope with the situation” - one hates to draw attention to oneself after all. Right Ho then. Connect to a Sql Server 2005 with your favorite query analyzer tool and bring up the sys.dm_exec_connections table.

Q: What is a session?

A: As far as I am concerned it is a row in the Sql Server 2005 “sys.dm_exec_connections” table.

Q: What different types of sessions are there?

A: As far as I am concerned there are two types. Sessions with a “Parent_connection_id” =null and their offspring.

Q: What does it mean when a session has a “Parent_connection_id” that is null?

A: It means that this session is a “physical connection”. Every SqlConnection needs to be associated with a single “physical connection”

Q: How do I know which “physical connection” is associated with a SqlConnection?

A: Simple, sys.dm_exec_connection.session_id = @@spid (ah! Now we are getting somewhere)

Q: So what does it mean for a session to have a Parent_connection_id that is not null?

A: It means that it is a “logical connection”. Every SqlCommand _that is executing_ requires a “logical connection” that has a session_id equal to the session_id of the "physical connection". All of the "logical connections" will have the same GUID parent_connection_id. I have no idea how this can be related to the physical connection, but there it is.

Q: So if I create five SqlCommands associated to a single SqlConnection I will see five “logical connections” in the sys.dm_exec_connections table?

A: No, this is actually the tricky part. Sql Server 2005 does not care how many SqlCommands you have in the client, it only cares about how many are executing at the same time (MARS).

Q: Is there a cost associated with creating a “logical connection”?

A: Yes, definitely. “logical connections” are a valuable resource. That is why we pool them.

Q: So how does session pooling work?

A: It is very similar conceptually to connection pooling, but without all the bells and whistles. When you create a SqlConnection we will create a physical connection and a logical connection. The logical connection goes in a “pool” (it would be better to call it a cache). When you attempt to execute a SqlCommand we will look to see if we have a logical connection available, if we do we will hand it out to you and you can execute. If we don’t then we create a new logical connection and hand that out to you. When your SqlCommand completes executing we will return the logical connection to the pool _as long as_ we have less than ten logical connections.

Q: What does this mean from a practical standpoint?

A: A person after my own heart! This means that if I ExecuteReader on five SqlCommands associated with the same SqlConnection at the same time I will see one physical and five logical connections when I execute “select * from sys.dm_exec_connections where session_id=@@spid” and that

I will continue seeing the same results after closing all the datareaders.

Q: Why cache ten logical connections?

A: Well, ten logical plus one physical makes eleven, that sounded like a good round number to us I guess. Realistically this is a number that seems to us to be more than sufficient. The downside is that if you use more than ten SqlCommands at the same time using the same SqlConnection you will see a (possibly) substantial slowdown. (just don’t do it!)

Q: Do the logical connections get cleaned out when I close the connection?

A: As long as you are using pooling the connection is not really closed and the logical connections are not disposed.

ado.net 2.0 changes in latest community drop.

angelsb — Wed, 17 Nov 2004 02:17:00 GMT

Well, it has been a long time since my last blog and it is going to be a while before I get going again. Whenever this happens you can count on me being either insanely behind or on vacation.

I am happy to report that the insanely behind part is over. The ado.net team has hit ZBB on time and on schedule (I have the WE ROCK mail to prove it :). I am really looking forward to getting some feedback on the latest community drop. There have been some changes… some minor some not so minor. A lot of improvements, bug fixes and sweat lots of sweat. Now it is time to see how it holds out in the real world.

The reason I am not going to be able to pick up blogging where I left off is that next week I am off on vacation. I am thinking it is time to go to a warm country and pass out by a swimming pool.

Important (to me *grin*) changes:

1)The System.Transactions namespace has received a fairly significant makeover.

The System.Transactions team has been busy, some of their changes affect our components so I am writing what I know about them. This is definitelly not an official source, I am only commenting on the changes in the latest comunity drop.

Transaction.Create has been removed, we now have a CommittableTransaction class to represent a lightweight transaction.

CommittableTransaction committabletransaction1 = new CommittableTransaction();

You can still specify timeout (default 1 minute) and isolation level by using the newly renamed TransactionOptions. This now supports Snapshot isolation level.

TransactionOptions transactionoptions1 = new TransactionOptions();

transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted

CommittableTransaction committabletransaction2 = new CommittableTransaction(transactionoptions1);

Checking the GUID of the distributed transaction will no longer promote the transaction to return a valid value, we now support returning null for lightweight transactions.

We can no longer change the default serialization level of a distributed transaction in the config file. Isolation level is a development time concern, not a runtime issue.

The TransactionScope now has only 8 constructors (down from something like 14?). Only Required, RequiresNew and Supress TransactionScopeOptions are now available (making things much less confusing I think)

The TransactionScope Consistent property had a fundamental design problem, it can be set multiple times making it confusing to use, setting it to true and false multiple times inside of the same scope is considered bad. This has been replaced with a method Complete(). This method does not actually complete the distributed transaction, commit will still happen on TransactionScope dispose. The main difference is that after calling this method you can no longer use the distributed transaction.

2) We are trying to get OleDb and Odbc managed connections to auto enlist in System.Transactions transactions.

Currently only SqlClient and the Oracle managed provider support this since they are managed providers instead of wrappers, we are trying to bring this functionality to the managed wrappers (it is hard, so no promises)

3)The ExecuteRow method is now on the verge of being removed.

I hate duplicating the same work with a different api and code path, it always always results in trouble. Removing this (to my eyes)

unnecessary api would be a big win.

Rambling out,

This post is provided AS IS and confers no rights. The information posted applies only to the latest community drop of the Whidbey framework, NOT to the beta1. Information posted in this post is DEFINITELLY not final.

ado.net creating a single connection versus relying on connection pooling, my two cents.

angelsb — Sat, 09 Oct 2004 05:33:00 GMT

Creating a connection object at the start of the process and passing it versus relying on connection pooling and the “using” construct?

This seems to be a hot topic in the adonet newsgroup, I would like to put in my two cents. This post deals with the managed pooler in the SqlClient and the Oracle Managed provider.

For intranet winform applications that are not multi-threaded there really aren’t many differences. You will get practically the same performance. Creating a single _unpooled_ connection has some advantages since you can control its lifetime. Pooled connections that are idle will be cleaned up after eight minutes and this can cause unnecessary load on the server by either keeping connections around longer than they should be in applications where you know you are not going to be using the connection any more, or by opening too many connections in applications that use the connection and then idle for more than eight minutes.

If you are dealing with a multi threaded application for me there is no question. I would always rely on connection pooling .The chance of shooting yourself in the foot with code that may use the connection in two separate threads at the same time is too great, most of the time this will work fine so you will only catch these bugs in production. They will be hardware dependent bugs of the “everything works on my dev machine but…” variety. I would not risk it. (I will blog more on this at a later time).

If you are dealing with asp.net every fraction of a second that you hold a connection open while not using it is stealing resources from your other threads. You need to rely on connection pooling and you need to open the connection as late as possible and close it as soon as you can.

Some people are concerned about the cost of Opening and Closing the connection repeatedly, to see how this may affect your application try something like the code below:

using System;

using System.Data;

using System.Data.SqlClient;

public class Example

{

public static void Main()

{

SqlConnection sqlconnection1 = new SqlConnection(ConnectionString);

sqlconnection1.Open();

SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "create table foo123 (myid int)";

try {sqlcommand1.ExecuteNonQuery();}catch(Exception){}

sqlconnection1.Close();

DateTime start = DateTime.Now;

for (int i = 0; i < 1000; i++)

{

Execute1();

}

DateTime stop = DateTime.Now;

Console.WriteLine("Opening new connection every time spent: ="+((TimeSpan) stop.Subtract(start)).Ticks.ToString("N") + "Ticks.");

Console.WriteLine("="+((TimeSpan) stop.Subtract(start)) +" seconds");

SqlConnection sqlconnection2 = new SqlConnection(ConnectionString);

sqlconnection2.Open();

start = DateTime.Now;

for (int i = 0; i < 1000; i++){

Execute2(sqlconnection2);

}

stop = DateTime.Now;

Console.WriteLine("Opening a single connection: ="+((TimeSpan) stop.Subtract(start)).Ticks.ToString("N") + "Ticks.");

Console.WriteLine("="+((TimeSpan) stop.Subtract(start)) +" seconds");

}

static void Execute1()

{

using (SqlConnection sqlconnection1 = new SqlConnection(ConnectionString ))

{

sqlconnection1.Open();

SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "insert into foo123 values (1)";

Int32 int321 = sqlcommand1.ExecuteNonQuery(); // 1

}

static void Execute2(SqlConnection sqlconnection1)

{

SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "insert into foo123 values (1)";

Int32 int321 = sqlcommand1.ExecuteNonQuery(); // 1

}

In this code I am comparing the cost of inserting 1000 rows using a single connection to the cost of relying on the pool and the using statement to insert the same 1000 rows.

In my computer and running against a non local database these where the results:

(these results mean absolutely nothing since they are completely dependent on network/computer/random factors, I am only posting to give a rough idea to those that won’t test this on their own network)

time in seconds. Results of three test runs.

Open and close 1000 times. Vs Single Connection,

00:00:04.1718750 vs 00:00:04.1093750

00:00:04.1406250 vs 00:00:04.1875000

00:00:04.2031250 vs 00:00:04.1718750

Notes about the code: I am making sure that a connection is open and available in the pool before running Execute1, only fair since I am opening a connection before calling Execute2.

Q: So are you saying that opening 1000 connections is almost free?

A: We are not really opening 1000 connections in Execute1, we are relying on the managed pooler.

Q: How does the managed pooler work?

A: It is surprisingly hard to explain, let me give you some pseudo code for this code:

On Connection.Open we check the pool, currently it has 0 connections so we do a roundtrip to the server (expensive!) and retrieve a connection.

You use the connection and call Close

The connection is a precious resource, we can’t just throw it away! When you call close on the connection we save the connection into a managed pool, this is very fast.

Foreach 1000 Calls to Execute1

On Connection Open we check the pool, it currently has one available connection and there is no need to do a server roundtrip.

Retrieving the connection from the pool is very fast.

Execute insert statement

On Connection Dispose we place the connection back in the managed pool (Connection dispose is guaranteed to happen when we exit the "using" statement.

Rambling out, This post is provided “AS IS” and confers no rights. I am not trying to imply that the test results posted are official in any way, please try it out for yourself.

ADO.NET 2.0 The connection pool and distributed transactions

angelsb — Thu, 07 Oct 2004 21:37:00 GMT

I am a big fan of pop quiz style blogs like the ones you can find here: http://blogs.msdn.com/brada/ .

I am not really sure that there are enough people looking at this blog to make this fun but I am going to give it a try. I will incorporate the answer in the blog at a latter time for reference.

Pop quiz,

Connection pooling can get pretty confusing when distributed transactions are in use, in the following code I am setting the following connection string keywords:

Max Pool Size=1

Enlist =false

using System.Transactions.Ltm;

using System.Transactions;

using System;

using System.Data.SqlClient;

namespace DataViewer.Repro {

public class Repro {

public static int Main(string[] args) {

try{

TransactionScope transactionscope1 = new TransactionScope();

ITransaction itransaction1 = Transaction.Current; // LightweightTransaction

SqlConnection sqlconnection1 = new SqlConnection(ConnectionString+";Max Pool Size=1;Enlist=false");

sqlconnection1.Open();

sqlconnection1.EnlistTransaction((ITransaction)itransaction1);

sqlconnection1.Close();

//System.Threading.Thread.Sleep(61*1000); //ß Line 1

sqlconnection1.Open(); //ß What is the behavior here?

}catch(Exception ex){Console.WriteLine(ex.Message.ToString());}

return 1;

}

Well, time to aknowledge that this is not going to work, let me just explain how this works:

“What is the behavior here?” sqlconnection1.Open will work or throw an exception given the information specified below:

1) With the code as shown. (no waiting 61 seconds)

The connection will fail to open. On connection close we will realize that the connection is currently participating in a distributed transaction and we will not place it back into the regular pool. This transacted connection will be placed in a sub-pool and will only be taken out when the transaction completes (we get a notification) or if you open another connection with the same connection string and enlisted to the same distributed transaction (impossible in this case due to Enlist=false). When you call Open again we compare the size of the pool (including the subpool) with the Max Pool Size of 1 and fail to Open with a "timeout exception" The timeout period elapsed prior to obtaining a connection from the pool.

2) If you uncomment “Line 1” so that you wait 61 seconds before calling Open.

The connection will open. As above we will place the connection on the transacted subpool, the difference here is that the default timeout of

a distributed transaction is 1 minute. When the minute is up the transaction will roll back and we will get a notification saying that it is completed.

We will then take the connection out of the subpool and place it back in the regular pool. When we call Open a second time there will be a free

connection waiting in the pool.

ADO.NET new for 2.0, Clearing the pool on Server recycle

angelsb — Tue, 05 Oct 2004 17:43:00 GMT

This blog talks about behavior in the SqlClient and the Oracle managed provider exclusively.

One of the biggest problems with pooling before ado.net 2.0 has to be the fact that we did not know how to handle the pool when the server went down. Let me describe just how ugly this behavior was using the following scenario:

PRE-ADO.NET 2.0

Open 3 connections, close 3 connections, we now have 3 connections in the pool. Now stop Server and start Server.

All 3 connections in the pool are no longer valid since they hold references to Server connections that no longer exist, the pool is corrupted.

So far this is by design pooling behavior, the ugly part comes next.

You open a connection, the pooler checks to see if we have connections waiting in the pool. We have 3 connections so it just hands you back one of these. No exception

You execute a command. Exception, when we tried to do a roundtrip to the Server it told us that it had no idea what the connection we were trying to use was.

The connection becomes closed and we throw it away.

You open a connection, the pooler checks to see if we have connections waiting in the pool. We have 2 connections so it just hands you back one of these. No exception.

You execute a command. Exception, when we tried to do a roundtrip to the Server it told us that it had no idea what the connection we were trying to use was.

The connection becomes closed and we throw it away.

You open a connection, the pooler checks to see if we have connections waiting in the pool. We have 1 connections so it just hands you back one of these. No exception

You execute a command. Exception, when we tried to do a roundtrip to the Server it told us that it had no idea what the connection we were trying to use was.

The connection becomes closed and we throw it away.

You open a connection, the pooler checks to see if we have connections waiting in the pool. We have no connections in the pool so we do a roundtrip to the server to get a new connection.

You execute a command. Everything works as expected.

As you can see we did a terrible job of handling something as simple as a server stopping and starting again. Developers that had applications that dealt with server reboots (like a failover cluster) definitely felt the pain. Some people tried to automate the process above to flush the pool and some people tried changing connection strings on exception (this creates a new pool), the result is quite ugly code and it is directly our fault.

In ADO.NET 2.0 we have improved this scenario (somewhat) by realizing that after certain types of exceptions our entire pool must be corrupted, so instead of throwing away an individual connection we will mark the entire pool as doomed and the next time that you attempt to open a connection you will always attempt to establish a fresh connection.

ADO.NET 2.0

Open 30 connections, close 30 connections, we now have 30 connections in the pool. Now stop Server and start Server.

All 30 connections in the pool are no longer valid since they hold references to Server connections that no longer exist, the pool is corrupted.

You open a connection, the pooler checks to see if we have connections waiting in the pool. We have 30 connections so it just hands you back one of these. No exception

You execute a command. Exception, when we tried to do a roundtrip to the Server it told us that it had no idea what the connection we were trying to use was.

The connection becomes closed and we realize that this exception means that the pool is corrupted, so we mark all the connections in the pool as doomed.

You open a connection, the pooler checks to see if we have connections waiting in the pool. All of the connections in the pool are marked as doomed so we do a roundtrip to the server to get a new connection.

You execute a command. Everything works as expected.

We still throw an exception in ado.net 2.0, but when we realize that the exception means that the pool is corrupted we walk the pool and mark each connection as doomed. A doomed connection is a connection that cannot be taken out of the pool, if the connection is currently out of the pool and in use marking it as doomed only means that the next time the connection comes into the pool it will not come out again. This is to protect against scenarios where I open 2 connections, start and stop the server and then try to execute on one connection. This will throw an exception and mark the pool as doomed. If I go to the second connection now and call Close() I will not get an exception and the connection will go back in the pool, but since it was marked as doomed when the exception happened it will not come out again.

Q: What do you mean by “realize that an exception means that the pool is corrupted”? Are you going to be throwing away pools that are perfectly fine just because I get an exception?

A: We determine that a pool is corrupted _only_ when we get a fatal exception from the network layer on a previously opened connection. Exceptions from the server, exceptions that happen during log in and of course client side exceptions are ignored.

Q: So when you get an exception from server X you will clear all the pools that connect to server X right?

A: No, we will only clear the pool where this exception occurred.

Q: It is really bad that you still throw the first exception, couldn’t you have caught the exception, cleared the pool, opened a new connection and continued?

A: Yes we could have, this was too hard to get right at this time but I certainly hope it makes it into a future version.

Q: What if I know that the server has stopped and started again, is there anything I can do?

A: Yes, you can use the new static SqlConnection and OracleConnection methods ClearPool(connection) and ClearAllPools(). These methods expose the pool clear functionality described here. They will walk the pool (or all the pools) and mark all of the connections as doomed.

Q: So I can use ClearPool and ClearAllPools() to synchronously close all of my connections?

A: No, actually not at all. The only thing that these methods do is walk the pool and mark the connections as doomed. The only thing that marking a connection as doomed does is to stop it from being taken out of the pool. The connections are still cleaned in the background using the same 4-8 minute algorithm that we have for current idle connections in the pool.

Rambling out, The information posted here is provided “AS IS” and confers no rights.

Design time Side by Side poll (installing different versions of visual studio)

angelsb — Fri, 24 Sep 2004 17:40:00 GMT

I became involved in the Side by Side and compatibility work items after making the mistake of voicing the fact that I did not like these scenarios. Life can laugh at you that way well enough on it’s own, but in Microsoft a comment like that is like playing with fire. I have grudgingly come to accept runtime side by side behavior, I make no claims of understanding the path forward (how do you go from three to four runtimes?) and I am now looking for information on design time side by side.

You have probably noticed by now that there is more than one version of the framework, we tried to mask this at first by making our second framework a point release but now that 2.0 is coming out there is nowhere to hide. I am really trying to get a feel for how people are using our design time components (Visual Studio). It would be extremely helpful if you could leave some feedback with your thoughts/opinions.

1. Have you installed VS2002 and VS2003 Side by Side on the same machine?
2. Are you thinking about installing VS2003 and VS2005 (when it releases)?
3. If you are a developer that needs to use two versions of VS are you just using different boxes or Virtual PC / VMWare?
4. What are the most important install / uninstall scenarios (the ones we need to get right)? For example, a trial scenario where someone has VS2003, installs VS2005 trial, migrates all their work and then uninstalls VS2003 and activates VS2005.

I would really appreciate any feedback, I will compile this information and present it to the side by side working group so your vote definitely counts. Thanks for your help.
Rambling out

ADO.NET The misunderstood “Connection Lifetime” managed pooler connection string keyword.

angelsb — Mon, 20 Sep 2004 20:20:00 GMT

I find that we have not done a good job of explaining the “knobs” used to tweak the managed connection pool (the one used by SqlClient and OracleClient), the worst knob definitely has to be the “Connection Lifetime” keyword. First of all, the name is bad. Really really bad. In Whidbey we are renaming it to Load Balance Timeout to help make it clearer, but we need to keep backward compatibility so Connection Lifetime will continue working. The second problem is that it does something really bad, it throws away connections that should have been pooled.

I have found that it is really hard to explain what this keyword does. Here is some pseudo code:

On SqlConnection.Close

Check if time the connection has been open is greater than Connection Lifetime if it is, throw the connection away

Else Put connection on the pool

This is the _only_ time that we will check Connection Lifetime. We do not check the lifetime when the connection is idle in the pool and we certainly don't use this value to determine how long it can stay in there.

As you can see we are throwing away perfectly good connections, this will certainly affect performance since the next time we require a connection we will have to go to the server to get it, in effect you are telling us to turn pooling off after Connection Lifetime has been reached. This is a terrible idea for most applications and I would highly recommend not using this connection string keyword _unless_ you are connecting to a cluster _and_ you understand the trade offs.

This brings us to the design discussion, what exactly where we trying to solve when we came up with this knob? Let’s look at this scenario:

You have a cluster of servers with currently two servers up, we start running our middle tier applications against this server cluster and eventually we find that the load is too high, we add a third cluster and expect the load to be distributed. When we look at the load a few minutes later we see that the third server is sitting idle while the first two servers are overworked, what happened?

Pooling happened. The middle tier applications are caching the connections and never opening new ones. Connection Lifetime is the knob that allows this scenario to work, by throwing away perfectly good connections we will force the middle tier applications to create new connections, these new connections will be load balanced into the new server. It is up to the developer to fine tune how much performance they are willing to give up versus how important it is to load balance the application.

Summary: Do not use Connection Lifetime unless you are connected to a cluster and understand the trade offs.

Rambling out. Standard Disclaimer: This post is provided “AS IS” and confers no rights.

Sql Server 2005 User Defined Types and the "File or assembly name not found" exception.

angelsb — Fri, 10 Sep 2004 03:18:00 GMT

"File or assembly name ‘<udtname>’, Version=1.2.0.0, Culture=neutral, PublicKeyToken=389619d4c1235f8a', or one of its dependencies, was not found."

If you have started using Sql Server 2005 User Defined Types it is likely that you have run into the exception above. Why is this happening? The short answer is that Fusion has not been able to find your UDT on the client.

Introducing fuslogvw.exe http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptutorials/html/assembly_binding_log_viewer_resources.asp

The link above is the best I have been able to find, but it is out of date for the 2.0 framework. Fuslogvw.exe is a tool included in the SDK that allows you to look at fusion related logs.

To start using v2.0 of fuslogvw.exe click on “Settings” and choose “log bind failures to disk”, then Enable custom log path and enter a valid directory name.

Re-run your app and click “Refresh” to see the fusion related log entries. You need to double click on the Application column to see the actual log.

Example log file:

I registered a UDT called Udt_NewHugeInteger on the server and attempted to retrieve it from the client without having the udt dll locally or in the gac, here is the fuslogvw log.

*** Assembly Binder Log Entry  (9/9/2004 @ 7:56:18 PM) ***
 
The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.
 
Assembly manager loaded from:  C:\WINDOWS\Microsoft.NET\Framework\v2.0.<version>\mscorwks.dll
Running under executable  
--- A detailed error log follows.

=== Pre-bind state information ===

LOG: User = REDMOND\<somebody>

LOG: DisplayName = Udt_NewHugeInteger, Version=1.2.0.0, Culture=neutral, PublicKeyToken=<sometoken>

(Fully-specified)

LOG: Appbase = file:///C:/scratchpad/DataViewer/Whidbey/

LOG: Initial PrivatePath = NULL

LOG: Dynamic Base = NULL

LOG: Cache Base = NULL

LOG: AppName = DataViewer.exe

Calling assembly : System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=<sometoken>

===

LOG: This bind is in default load context.

LOG: No application configuration file found.

LOG: Using machine configuration file from C:\WINDOWS\Microsoft.NET\Framework\v2.0.<something>\config\machine.config.

LOG: Post-policy reference: Udt_NewHugeInteger, Version=1.2.0.0, Culture=neutral, PublicKeyToken=<sometoken>

LOG: GAC Lookup was unsuccessful.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger.dll.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger/Udt_NewHugeInteger.dll.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger.exe.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger/Udt_NewHugeInteger.exe.

LOG: All probing URLs attempted and failed.

As you can see first we searched in the GAC

LOG: GAC Lookup was unsuccessful.

Then we attempted to load from the local directory:

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger.dll.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger/Udt_NewHugeInteger.dll.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger.exe.

LOG: Attempting download of new URL file:///C:/scratchpad/DataViewer/Whidbey/Udt_NewHugeInteger/Udt_NewHugeInteger.exe.

When we fail to find Udt_NewHugeInteger, Version=1.2.0.0 in any of these locations we get the file not found exception.

Ok, so how do you fix it? Drop your dll into one of the directories shown above and re-start your application (it may not work without re-starting). You should be good to go.

Is this really all there is to fusion and User Defined Types? No of course not, more to blog at another time.

Rambling out: Standard disclaimer: This post is provided AS IS and confers no rights.

ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

angelsb — Wed, 08 Sep 2004 02:24:00 GMT

ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Q: What is MARS?

A: MARS is a new feature in ado.net 2.0 and Sql Server 2005 that allows for multiple forward only read only result sets.

EDIT There is a great article on MARS up at the technet site. It is a must read for basic understanding of MARS. I am specially impressed with the "Transaction Semantics" section http://www.microsoft.com/technet/prodtechnol/sql/2005/marssql05.mspx

Q: What is MARS for an ado.net developer?

A: MARS allows you to avoid seeing the dreaded “There is already an open DataReader ..” exception when executing on separate SqlCommands associated with the same connection. You can have multiple SqlDataReaders open on a single connection (again, each reader must be started on a new SqlCommand) and you don’t have to worry about Transaction isolation level scope locks.

Q: What is MARS for Sql Server?

A: This has been a thorn in our side for too long. Other databases have supported this functionality for a long time and this has been one of the most important customer requested features to our model. I wish that I could explain how hard it has been to enable this behavior (completely anathema to our implementation) and how proud I am that we have driven this feature to market. That said I am concerned that this feature is going to be misused.

Q: So MARS allows for better performance right?

A: I would like to say no outright, it would make my life much easier. You are trading the expense of opening new connections (almost free with pooling) for the hidden expenses of using MARS (fairly high IMO) The truth is that in some scenarios you will see actual perf improvement by using MARS, mostly thanks to Session Pooling. These scenarios are somewhat contrived and I would highly recomend that you do not modfify existing code or write new ugly code just to attempt to improve performance with MARS.

Q: What is Session Pooling?

A: This question should ideally come after I have explained the hidden costs of MARS, for now let me just explain what it does and I will blog on this with more detail at a later time. If you understand how pooling works then you are familiar with the idea of not destroying a valuable resource just because the user decides to close or dispose it. In this case the SqlCommand has become a valuable resource because we have to associate it with a Sql Server 2005 batch to enable MARS functionality. Session Pooling keeps up to 9 of these valuable disposed SqlCommands in a pool and hands them out the next time you create a command associated with the same connection.

Q: What does Session Pooling mean to a SqlClient ado.net developer?

A: It means that you should _NOT_ use more than 9 SqlCommands per connection, if you do you will be forcing us to create/dispose very very expensive SqlCommands and you will definitelly notice the performance drop.

Q: Can I get better performance by combining MARS with ASYNC?

A: IMO, no. In ASP.NET applications you will not get better performance with MARs, and on Winform applications you should not use ASYNC with callbacks (See ASYNC FAQ) I would not mix these two features expecting to get better performance.

Q: So if MARS is not for performance what is it good for?

A: I like to use MARS in two core scenarios, 1) When using MARS results in cleaner looking code and 2) when I am using Transactions and need to execute in the same isolation level scope.

Q: When does using MARS result in cleaner looking code?

A: The quintessential MARS example involves getting a datareader from the server and issuing insert/delete/update statements to the database as you process the reader.

Q: What about the transaction isolation level scope?

A: Same example above but you have a transaction active and you have placed locks on the database. Please note that this behavior was _completelly_ broken before MARS! If you try to fake MARS by opening a second connection under the covers (like native SqlOledb does) you are outside of the transaction scope of the original connection. This is imo the biggest win with this feature.

Q: What are the costs of using MARS?

A: There are a lot of hidden costs associated with this feature, costs in the client, in the network layer and in the server. On the client we run into an issue where creating a new batch is not free, we kind of work around this issue by pooling mars commands but it is still expensive if you don’t used the pooled functionality. On the network layer there is a cost associated with multiplexing the TDS buffer, opening too many batches can be more expensive than opening another connection. On the server all MARS batches run in the same scope, worst case scenario your queries will end up running in the order received.

Q: Can I disable MARS?

A: Yes and No, you can disable MARS with the connection string keyword “MultipleActiveResultSets=false”. Under the covers we still use MARS headers so we will still have some of the overhead associated with MARS. You will not get better performance if you disable MARS, this option was only added to allow the developer to enable backward compatibility with applications that depend on SqlClient throwing an exception when more than one Result Set is used.

Q: What providers/backends support MARS?

A: The only provider/backend combination that supports the MARS feature that I am talking about in this FAQ is SqlClient talking to Sql Server 2005.

Q: Does this mean that none of the other providers support multiple DataReaders?

A: No it doesn’t. The OracleClient managed provider supports multiple DataReaders against all versions of Oracle. The OleDb managed provider is MUCH more problematic. It fully supports multiple DataReaders when talking to Sql Server 2005 when using MDAC 9. (IMPORTANT) In all other cases the OleDb managed provider will FAKE the ability of having multiple active result sets.

Q: So OleDb may fake MARS?

A: Unfortunately yes. In what has to be one of my least favorite “features” the SqlOledb native provider will fake the ability of having multiple active result sets by opening a separate non-pooled connection to the server when unable to provide real MARS behavior. What this really means is that your code may be opening and throwing away connections that can bring your server to its knees during heavy traffic.

Q: Wait a minute; in v1.1 I was not able to open multiple DataReaders with OleDb.

A: This was an artificial restriction on the client, we have removed this restriction going forward since OleDb with the latest version of MDAC will now support true MARS behavior.

Q: What does the removal of this client side restriction mean?

A: It means that you can now shoot yourself in the foot when using the managed OleDb provider. IMHO it means that you should ONLY use SqlClient to talk to Sql Server, the risk of running into this fake MARS behavior is too great. I have seen this “feature” (fake MARS) cost hundreds of thousands of dollars an hour in lost sales as the server was inundated with unnecessary non-pooled connection open requests.

Rambling out. Standard Disclaimer: This post is provided "AS IS" and confers no rights. The information in this post is just my opininon.

ADO.NET 2.0 Asynchronous Command Execution (ASYNC) FAQ

angelsb — Thu, 02 Sep 2004 23:05:00 GMT

ADO.NET 2.0 Asynchronous Command Execution (ASYNC) FAQ (DISCLAIMER: Based on Whidbey beta1 and subject to change)

Before going into the Q&A below I would highly recommend reading Pablo’s excellent Async article located here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp

Q: What is the new ADO.NET 2.0 Asynchronous Command Execution feature.
A: ASYNC allows you to execute a command in a non-blocking manner. We are exposing in the SqlCommand the following asynchronous methods: BeginExecuteNonQuery, BeginExecuteReader and BeginExecuteXmlReader with polling, synchronization and (*shudder*) callbacks.

Q: Wait, I have seen this before, you just create a new thread and block it right?
A: No! This is a very important concept since this feature would be useless if implemented like that. Ado.net is not thread safe and blocking threads is eviel.

Q: So if you don’t create new threads how do you execute without blocking?
A: The secret is in the network layer, when you specify ASYNC=TRUE in the connection string we will open the TCP socket in overlapped mode and bind it to the I.O completion port.

Q: So does this mean that every command I execute (sync or async) will happen in overlapped mode when I add ASYNC=TRUE to the connection string?
A: Yes it does, _everything_ that we execute on this connection will be done in overlapped mode. For synchronous operations we internally wait for the completion before returning, we are basically faking the synchronous behavior on this connection. This is the reason why we require a connection string keyword.

Q: Does this have a perf impact?
A: Definitely, only use ASYNC=TRUE when you know that you are going to be using the async functionality.

Q: You mention that this feature is dependent on the network layer, does this mean that the version of MDAC in the machine matters?
A: In most scenarios it does not, in ado.net we have built the network layer for SqlClient into System.Data and you have everything you need out of the box.

Q: In most scenarios? Are there any exceptions?
A: Yes ASYNC does not work when using shared memory against Sql Server 2000 or lower. If you want to use ASYNC against a local Sql Server 2000 server you need to force the use of a different network layer. Take a look at this blog for more information: http://weblogs.asp.net/despos/archive/2004/07/22/190984.aspx

Q: Any other instance where ASYNC does not work?
A: Yes ASYNC does not work with Win9x.

Q: No win9x? doesn’t that make it really hard to create a winforms app to distribute?
A: I would highly recommend not using ASYNC with winforms. It is really not what it was designed for. Take a look at Pablo’s document for more information.

Q: (IMPORTANT) So the main scenario for this feature is to create WinForms apps that are responsive while doing data access right?
A: NO! I would not use callbacks on winforms for no money. We are fortunate in that Ken Getz is writing the documentation for this feature and has done a great job of going over the pitfalls that occur when trying this scenario, I still believe that people trying to use ASYNC with WinForms is going to be one of the worst problem areas in the newsgroups for the 2.0 release. There are a lot of pitfalls; you end up with really ugly code and you will only find the problems after you deploy. Take a look at the Whidbey BackgroundWorker class for a better option.

Q: So what are the pitfalls of using ASYNC Callbacks with WinForms?
A: ado.net objects are not thread safe, when using polling or synchronization there are no problems since everything happens on the same thread, callbacks however are by definition on a separate thread. You need to _guarantee_ that all of your ado.net objects are only used on one thread at a time. This includes having a global connection and command, guaranteeing that the command is only used by one thread, guaranteeing that the connection does not close before the callback occurs and since WinForm controls have pretty much the same limitations you need to guarantee that you only databind in the thread where the UI control was created.

Q: This does not sound so bad…
A: The biggest problem IMO is the fact that when you fail to do any of the above your application may look like it is working fine most of the time. It is only when you deploy it and customers start using it that you will start seeing random exceptions that are very hard to duplicate (machine dependent in some cases) and debug. The exception message can be absolutely anything and it will not be obvious that it is a threading issue.

Rambling out: Disclaimer this post is provided AS IS and confers no rights. Everything in this post is in my opinion only.
I will be happy to incorporate any questions you may have into this FAQ, just leave me feedback.